mysql Partition(分區)初探

標籤：http   io   使用   ar   資料   div   sp   2014   art   

mysql Partition(分區)初探 表資料量大的時候一般都考慮水平分割,即所謂的sharding.不過mysql本身具有資料分割函數,可以實現一定程度 的水平切分. mysql是具有MERGE這種引擎的,就是把一些結構相同的MyIASM表作為一個表使用,但是我覺得 MERGE不如partition實用,   www.2cto.com  因為MERGE會在所有的底層表上查詢,而partition只在相應的分區上查詢. 建立了兩個表,分別為分區和未分區的,分區表按年進行分區. Sql代碼  CREATE TABLE `20130117date_par` (    `content` varchar(20) NOT NULL,    `create_time` datetime NOT NULL,    KEY `20130117date_idx_date` (`create_time`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8  PARTITION BY RANGE (YEAR(create_time))  (PARTITION p2009 VALUES LESS THAN (2010),   PARTITION p2010 VALUES LESS THAN (2011),   PARTITION p2011 VALUES LESS THAN (2012),   PARTITION p2012 VALUES LESS THAN (2013),   PARTITION p2013 VALUES LESS THAN (2014))    CREATE TABLE `20130117date` (    `content` varchar(20) NOT NULL,    `create_time` datetime NOT NULL,    KEY `20130117date_idx_date` (`create_time`)  ) ENGINE=InnoDB   用sp向分區表和普通表各插入了90w條隨機資料. 用mysqlslap進行下測試  不用分區表 Sql代碼  select SQL_NO_CACHE * from 20130117date  where create_time BETWEEN ‘2013-01-01‘ and ‘2013-01-02‘;  select SQL_NO_CACHE * from 20130117date  where create_time BETWEEN ‘2012-12-25‘ and ‘2013-01-05‘;   引用 Benchmark         Average number of seconds to run all queries: 0.881 seconds         Minimum number of seconds to run all queries: 0.062 seconds         Maximum number of seconds to run all queries: 3.844 seconds         Number of clients running queries: 1         Average number of queries per client: 2 Benchmark         Average number of seconds to run all queries: 0.703 seconds         Minimum number of seconds to run all queries: 0.062 seconds         Maximum number of seconds to run all queries: 1.922 seconds         Number of clients running queries: 1         Average number of queries per client: 2 Benchmark         Average number of seconds to run all queries: 1.250 seconds         Minimum number of seconds to run all queries: 0.109 seconds         Maximum number of seconds to run all queries: 4.032 seconds         Number of clients running queries: 1         Average number of queries per client: 2   用分區表 Sql代碼  select SQL_NO_CACHE * from 20130117date_par  where create_time BETWEEN ‘2013-01-01‘ and ‘2013-01-02‘;  select SQL_NO_CACHE * from 20130117date_par  where create_time BETWEEN ‘2012-12-25‘ and ‘2013-01-05‘;   引用 Benchmark         Average number of seconds to run all queries: 0.068 seconds         Minimum number of seconds to run all queries: 0.047 seconds         Maximum number of seconds to run all queries: 0.110 seconds         Number of clients running queries: 1         Average number of queries per client: 2 Benchmark         Average number of seconds to run all queries: 0.250 seconds         Minimum number of seconds to run all queries: 0.031 seconds         Maximum number of seconds to run all queries: 1.078 seconds         Number of clients running queries: 1         Average number of queries per client: 2 Benchmark         Average number of seconds to run all queries: 0.046 seconds         Minimum number of seconds to run all queries: 0.046 seconds         Maximum number of seconds to run all queries: 0.047 seconds         Number of clients running queries: 1         Average number of queries per client: 2          www.2cto.com  看來效能還是有一定的提升的.        執行 Sql代碼  explain PARTITIONS select * from 20130117date_par  where create_time BETWEEN ‘2012-01-01‘ and ‘2012-01-02‘;   可以看出這個query只掃描了p2012這個分區. 而且分區表的好處在於維護比較方便.比如2009年的資料不需要了,分區表的方法為 Sql代碼  alter table 20130117date_par drop PARTITION p2009   不到1s就行了 普通表為 Sql代碼  delete from 20130117date  where create_time BETWEEN ‘2009-01-01‘ and ‘2010-01-01‘   用了10.25s左右

mysql Partition(分區)初探