MySQL支持的分區類型一共有四種：RANGE，LIST，HASH，KEY。其中，RANGE又可分為原生RANGE和RANGE COLUMNS，LIST分為原生LIST和LIST COLUMNS，HASH分為原生HASH和LINEAR HASH，KEY包含原生KEY和LINEAR HASH。關於這些分區之間的差別，改日另寫文章進行闡述。
最近，碰到一個需求，要對表的時間字段（類型：datetime）基於天進行分區。於是遍歷MySQL官方文檔分區章節，總結如下：
實現方式
主要是以下幾種：
1. 基於RANGE
2. 基於RANGE COLUMNS
3. 基於HASH
測試數據
為了測試以上三種方案，特構造了100萬的測試數據，放在test表中，test表只有兩列：id和hiredate，其中hiredate只包含10天的數據，從2015-12-01到2015-12-10。具體信息如下：
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select min(hiredate),max(hiredate) from test;
+---------------------+---------------------+
| min(hiredate) | max(hiredate) |
+---------------------+---------------------+
| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |
+---------------------+---------------------+
1 row in set (0.44 sec)
mysql> select date(hiredate),count(*) from test group by date(hiredate);
+----------------+----------+
| date(hiredate) | count(*) |
+----------------+----------+
| 2015-12-01 | 99963 |
| 2015-12-02 | 100032 |
| 2015-12-03 | 100150 |
| 2015-12-04 | 99989 |
| 2015-12-05 | 99908 |
| 2015-12-06 | 99897 |
| 2015-12-07 | 100137 |
| 2015-12-08 | 100171 |
| 2015-12-09 | 99851 |
| 2015-12-10 | 99902 |
+----------------+----------+
10 rows in set (0.98 sec)
測試的維度
測試的維度主要從兩個方面進行，
一、分區剪裁
針對特定的查詢，是否能進行分區剪裁（即只查詢相關的分區，而不是所有分區）
二、查詢時間
鑒於該批測試數據是靜止的（即沒有並發進行的insert，update和delete操作），數據量也不太大，從這個維度來考量貌似意義也不是很大。
因此，重點測試第一個維度。
基於RANGE的分區方案
在這裡，選用了TO_DAYS函數
CREATE TABLE range_datetime(
id INT,
hiredate DATETIME
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ),
PARTITION p4 VALUES LESS THAN ( TO_DAYS('20151205') ),
PARTITION p5 VALUES LESS THAN ( TO_DAYS('20151206') ),
PARTITION p6 VALUES LESS THAN ( TO_DAYS('20151207') ),
PARTITION p7 VALUES LESS THAN ( TO_DAYS('20151208') ),
PARTITION p8 VALUES LESS THAN ( TO_DAYS('20151209') ),
PARTITION p9 VALUES LESS THAN ( TO_DAYS('20151210') ),
PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);
插入數據並查看特定查詢的執行計劃
mysql> insert into range_datetime select * from test; Query OK, 1000000 rows affected (8.15 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_datetime | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400061 | Using where | +----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.03 sec)
注意執行計劃中的partitions的內容，只查詢了p7，p8，p9，p10三個分區，由此來看，使用to_days函數確實可以實現分區裁剪。
基於RANGE COLUMNS的分區方案
RANGE COLUMNS可以直接基於列，而無需像上述RANGE那種，分區的對象只能為整數。
創表語句如下：
CREATE TABLE range_columns (
id INT,
hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
PARTITION p1 VALUES LESS THAN ( '20151202' ),
PARTITION p2 VALUES LESS THAN ( '20151203' ),
PARTITION p3 VALUES LESS THAN ( '20151204' ),
PARTITION p4 VALUES LESS THAN ( '20151205' ),
PARTITION p5 VALUES LESS THAN ( '20151206' ),
PARTITION p6 VALUES LESS THAN ( '20151207' ),
PARTITION p7 VALUES LESS THAN ( '20151208' ),
PARTITION p8 VALUES LESS THAN ( '20151209' ),
PARTITION p9 VALUES LESS THAN ( '20151210' ),
PARTITION p10 VALUES LESS THAN ('20151211' )
);
插入數據並查看上述查詢的執行計劃
mysql> insert into range_columns select * from test; Query OK, 1000000 rows affected (9.20 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_columns | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400210 | Using where | +----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.11 sec)
同樣，使用該分區方案也實現了分區剪裁。
基於HASH的分區方案
因HASH分區對象同樣只能為整數，所以我們無法像上述RANGE COLUMNS那種直接引用列，在這裡，同樣用了TO_DAYS函數進行轉換。
創表語句如下：
CREATE TABLE hash_datetime ( id INT, hiredate DATETIME ) PARTITION BY HASH( TO_DAYS(hiredate) ) PARTITIONS 10;
插入數據並查看上述查詢的執行計劃
mysql> insert into hash_datetime select * from test; Query OK, 1000000 rows affected (9.43 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from hash_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | hash_datetime | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL | NULL | NULL | NULL | NULL | 1000500 | Using where | +----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
不難看出，使用hash分區並不能有效的實現分區裁剪，至少在本例，基於天的需求中如此。
以上三種方案都是基於datetime的，那麼，對於timestamp類型，又該如何選擇呢？
事實上，MySQL提供了一種基於UNIX_TIMESTAMP函數的RANGE分區方案，而且，只能使用UNIX_TIMESTAMP函數，如果使用其它函數，譬如to_days，會報如下錯誤：“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。
而且官方文檔中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)”。
下面來測試一下基於UNIX_TIMESTAMP函數的RANGE分區方案，看其能否實現分區裁剪。
針對TIMESTAMP的分區方案
創表語句如下：
CREATE TABLE range_timestamp (
id INT,
hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),
PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),
PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') )
);
插入數據並查看上述查詢的執行計劃
mysql> insert into range_timestamp select * from test; Query OK, 1000000 rows affected (13.25 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from range_timestamp where hiredate >= '20151207124503' and hiredate<='20151210111230'; +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | range_timestamp | p7,p8,p9,p10 | ALL | NULL | NULL | NULL | NULL | 400448 | Using where | +----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
同樣也能實現分區裁剪。
總結：
1. 經過對比，個人傾向於第二種方案，即基於RANGE COLUMNS的分區實現。
2. 在5.7版本之前，對於DATA和DATETIME類型的列，如果要實現分區裁剪，只能使用YEAR() 和TO_DAYS()函數，在5.7版本中，又新增了TO_SECONDS()函數。
3. 其實LIST也能實現基於天的分區方案，但在這個需求上，相比於RANGE，還是顯得很雞肋。
4. TIMESTAMP類型的列，只能基於UNIX_TIMESTAMP函數進行分區，切記！
參考：
http://dev.mysql.com/doc/refman/5.7/en/partitioning.html
