MySQL之按年月分区

都说程序猿是靠爬坑积累起来的,对于这话深感认同。。。

最近公司新运行的项目,数据量已经过百万级了,为公司带来业绩、利润的同时,程序一些操作的耗时也越来越大,好吧,我承认这是前期着急上线,对于数据量的考量没有做到位,现在开始补救。。(开启填坑模式o(╯□╰)o)

在索引、字段类型已经建立好的情况下,将大表拆分
要对表的时间字段(类型:datetime)基于月进行分区。于是遍历MySQL官方文档分区章节,总结如下:

 
主要是以下几种:

1. RANGE分区
 
2. LIST分区
 
3. HASH分区

4. KEY分区

测试的维度主要从两个方面进行
 

 
针对特定的查询,是否能进行分区剪裁(即只查询相关的分区,而不是所有分区)
 
一、分区剪裁 
针对特定的查询,是否能进行分区剪裁(即只查询相关的分区,而不是所有分区)
 
二、查询时间
 
鉴于该批测试数据是静止的(即没有并发进行的insert,update和delete操作),数据量也不太大,从这个维度来考量貌似意义也不是很大。
 
因此,重点测试第一个维度。
 
基于RANGE的分区方案
 
在这里,选用了TO_DAYS函数


1、创建表并且设置按年月分区

CREATE TABLE bdm_range_datetime(
    id INT,
    hiredate DATETIME,
    time INT
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20170101') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20170201') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20170301') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20170401') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20170501') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20170601') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20170701') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20170801') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20170901') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171001') )
);

1.png

2、批量插入10W条数据

public function ceshi(){
	for($i=0;$i<99999;$i++){
		$time = rand(1488297600, 1501430399);			//3月1日-7月30日
		$data['hiredate'] = date("Y-m-d H:i:s",$time);
		$data['time'] = time();
		$data['id'] = $i;
		M('range_datetime') -> add($data);
	}
}

2.png

3、查看各分区数据条数

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_range_datetime';

4.png

4、查看特定查询语句的执行计划

explain partitions select * from bdm_range_datetime where hiredate > '20170501001000' and hiredate<='20170630235959';

3.png

从执行结果可以看出,查询2017年5月1日 - 2017年6月30日的数据,查询39623行,确实是走的所属分区,但是为什么会多走了p1这个分区呢??百思不得其解,查询了很多资料,还是没能找到答案,有知道原因的小伙伴可以在下方给我留言噢


==========华丽的分割线==========


TO_DAYS函数出现的这个p1分区没有解决的情况下,换了一种思路

基于RANGE COLUMNS的分区方案

RANGE COLUMNS可以直接基于列,而无需像上述RANGE那种,分区的对象只能为整数。


1、创建表并且设置按年月分区

CREATE TABLE bdm_range_datetime(
    id INT,
    hiredate DATETIME,
    time INT
)
PARTITION BY RANGE COLUMNS(hiredate) (
    PARTITION p1 VALUES LESS THAN ( '20170101' ),
    PARTITION p2 VALUES LESS THAN ( '20170201' ),
    PARTITION p3 VALUES LESS THAN ( '20170301' ),
    PARTITION p4 VALUES LESS THAN ( '20170401' ),
    PARTITION p5 VALUES LESS THAN ( '20170501' ),
    PARTITION p6 VALUES LESS THAN ( '20170601' ),
    PARTITION p7 VALUES LESS THAN ( '20170701' ),
    PARTITION p8 VALUES LESS THAN ( '20170801' ),
    PARTITION p9 VALUES LESS THAN ( '20170901' ),
    PARTITION p10 VALUES LESS THAN ('20171001' )
);

5.png

2、批量插入10W条数据

public function ceshi(){
	for($i=0;$i<99999;$i++){
		$time = rand(1488297600, 1501430399);			//3月1日-7月30日
		$data['hiredate'] = date("Y-m-d H:i:s",$time);
		$data['time'] = time();
		$data['id'] = $i;
		M('range_datetime') -> add($data);
	}
}

2.png

3、查看各分区数据条数

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_range_datetime';

6.png

4、查看特定查询语句的执行计划

explain partitions select * from bdm_range_datetime where hiredate > '20170501001000' and hiredate<='20170630235959';

7.png


总结: 
1. 经过对比,个人倾向于第二种方案,即基于RANGE COLUMNS的分区实现。
 
2. 在5.7版本之前,对于DATA和DATETIME类型的列,如果要实现分区裁剪,只能使用YEAR() 和TO_DAYS()函数,在5.7版本中,又新增了TO_SECONDS()函数。
 
3. 其实LIST也能实现基于天的分区方案,但在这个需求上,相比于RANGE,还是显得很鸡肋。
 
4. TIMESTAMP类型的列,只能基于UNIX_TIMESTAMP函数进行分区,切记!


==========华丽的分割线==========

普通表转分区表

1、查看原表结构

desc bdm_order;

1.png

这是一张普通表,oid为主键


2、新增字段(datetime类型,用于分区)

2.png


3、修改原表结构(关联主键、索引)

3.png

4.png

设置关联主键,关联索引


4、将原普通表更改为分区表

alter table bdm_order partition by RANGE COLUMNS(hiredate) (
    PARTITION p201612 VALUES LESS THAN ( '20170101' ),
    PARTITION p201701 VALUES LESS THAN ( '20170201' ),
    PARTITION p201702 VALUES LESS THAN ( '20170301' ),
    PARTITION p201703 VALUES LESS THAN ( '20170401' ),
    PARTITION p201704 VALUES LESS THAN ( '20170501' ),
    PARTITION p201705 VALUES LESS THAN ( '20170601' ),
    PARTITION p201706 VALUES LESS THAN ( '20170701' ),
    PARTITION p201707 VALUES LESS THAN ( '20170801' ),
    PARTITION p201708 VALUES LESS THAN ( '20170901' ),
    PARTITION p201709 VALUES LESS THAN ( '20171001' ),
    PARTITION p201710 VALUES LESS THAN ( '20171101' ),
    PARTITION p201711 VALUES LESS THAN ( '20171201' ),
    PARTITION p201712 VALUES LESS THAN ( '20180101' ),
    PARTITION p201801 VALUES LESS THAN ( '20180201' ),
);

5.png


5、查看分区数据情况

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_order';

6.png

从结果中看出,分区裁剪已经成功,所有数据都存在于2017年之前的分区中


6、将原time类型时间戳转换为可视化时间写入新增的字段中

UPDATE bdm_order SET hiredate=FROM_UNIXTIME(orderTime, '%Y-%m-%d %H:%i:%S') WHERE sid!=0;

7.png

8.png

从结果中看出,时间戳已经转换为可视化时间写到新字段中了


7、再次查看分区数据情况和查看特定查询语句的执行计划

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_order';

9.png

explain partitions select * from bdm_order where hiredate > '20170501001000' and hiredate<='20170630235959';

10.png

至此填坑完毕!



客官,点击下方打赏一个呗~

点赞

发表评论