都说程序猿是靠爬坑积累起来的,对于这话深感认同。。。 最近公司新运行的项目,数据量已经过百万级了,为公司带来业绩、利润的同时,程序一些操作的耗时也越来越大,好吧,我承认这是前期着急上线,对于数据量的考量没有做到位,现在开始补救。。(开启填坑模式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') ) );
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); } }
3、查看各分区数据条数
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_range_datetime';
4、查看特定查询语句的执行计划
explain partitions select * from bdm_range_datetime where hiredate > '20170501001000' and hiredate<='20170630235959';
从执行结果可以看出,查询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' ) );
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); } }
3、查看各分区数据条数
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_range_datetime';
4、查看特定查询语句的执行计划
explain partitions select * from bdm_range_datetime where hiredate > '20170501001000' and hiredate<='20170630235959';
总结: 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;
这是一张普通表,oid为主键
2、新增字段(datetime类型,用于分区)
3、修改原表结构(关联主键、索引)
设置关联主键,关联索引
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、查看分区数据情况
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_order';
从结果中看出,分区裁剪已经成功,所有数据都存在于2017年之前的分区中
6、将原time类型时间戳转换为可视化时间写入新增的字段中
UPDATE bdm_order SET hiredate=FROM_UNIXTIME(orderTime, '%Y-%m-%d %H:%i:%S') WHERE sid!=0;
从结果中看出,时间戳已经转换为可视化时间写到新字段中了
7、再次查看分区数据情况和查看特定查询语句的执行计划
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'bdm_order';
explain partitions select * from bdm_order where hiredate > '20170501001000' and hiredate<='20170630235959';
至此填坑完毕!
客官,点击下方打赏一个呗~