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左右
