都说程序猿是靠爬坑积累起来的，对于这话深感认同。。。 最近公司新运行的项目，数据量已经过百万级了，为公司带来业绩、利润的同时，程序一些操作的耗时也越来越大，好吧，我承认这是前期着急上线，对于数据量的考量没有做到位，现在开始补救。。（开启填坑模式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';
至此填坑完毕！
客官，点击下方打赏一个呗~