MySQL基于时间字段进行分区的方案总结

MySQL支持的分区类型一共有四种：RANGE，LIST，HASH，KEY。其中，RANGE又可分为原生RANGE和RANGE COLUMNS，LIST分为原生LIST和LIST COLUMNS，HASH分为原生HASH和LINEAR HASH，KEY包含原生KEY和LINEAR HASH。关于这些分区之间的差别，改日另写文章进行阐述。最近，碰到一个需求，要对表的时间字段（类型：datetime）基于天进行分区。于是遍历MySQL官方文档分区章节，总结如下：实现方式主要是以下几种：1. 基于RANGE2. 基于RANGE COLUMNS3. 基于HASH测试数据为了测试以上三种方案，特构造了100万的测试数据，放在test表中，test表只有两列：id和hiredate，其中hiredate只包含10天的数据，从2015-12-01到2015-12-10。具体信息如下：mysql> show create table testG*************************** 1. row *************************** Table: testCreate Table: CREATE TABLE `test` （`id` int（11） DEFAULT NULL,`hiredate` datetime DEFAULT NULL） ENGINE=InnoDB DEFAULT CHARSET=latin11 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: 1000000Duplicates: 0Warnings: 0mysql> 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: 1000000Duplicates: 0Warnings: 0mysql> 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: 1000000Duplicates: 0Warnings: 0mysql> 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: 1000000Duplicates: 0Warnings: 0mysql> 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 本文永久更新链接地址