登录  /  注册

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

php中文网
发布: 2016-05-27 13:46:26
原创
1020人浏览过

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 >= &#39;20151207124503&#39; and hiredate<=&#39;20151210111230&#39;; 
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 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 ( &#39;20151202&#39; ),
    PARTITION p2 VALUES LESS THAN ( &#39;20151203&#39; ),
    PARTITION p3 VALUES LESS THAN ( &#39;20151204&#39; ),
    PARTITION p4 VALUES LESS THAN ( &#39;20151205&#39; ),
    PARTITION p5 VALUES LESS THAN ( &#39;20151206&#39; ),
    PARTITION p6 VALUES LESS THAN ( &#39;20151207&#39; ),
    PARTITION p7 VALUES LESS THAN ( &#39;20151208&#39; ),
    PARTITION p8 VALUES LESS THAN ( &#39;20151209&#39; ),
    PARTITION p9 VALUES LESS THAN ( &#39;20151210&#39; ),
    PARTITION p10 VALUES LESS THAN (&#39;20151211&#39; )
);
登录后复制

插入数据并查看上述查询的执行计划

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 >= &#39;20151207124503&#39; and hiredate<=&#39;20151210111230&#39;; 
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 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 >= &#39;20151207124503&#39; and hiredate<=&#39;20151210111230&#39;;
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
| 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(&#39;2015-12-02 00:00:00&#39;) ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-03 00:00:00&#39;) ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-04 00:00:00&#39;) ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-05 00:00:00&#39;) ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-06 00:00:00&#39;) ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-07 00:00:00&#39;) ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-08 00:00:00&#39;) ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-09 00:00:00&#39;) ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP(&#39;2015-12-10 00:00:00&#39;) ),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP(&#39;2015-12-11 00:00:00&#39;) )
);
登录后复制

插入数据并查看上述查询的执行计划

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 >= &#39;20151207124503&#39; and hiredate<=&#39;20151210111230&#39;;
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| 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函数进行分区,切记!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:php中文网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号