mysql给表做按天分区

最近项目的数据量每天达到了六百万，并且应用大部分只使用热数据，因此准备对表进行按天分区。

思路

由于之前的表并没有建立分区，不能直接添加分区，因此需要先把数据挪到一个分区表中，表名不能变。解决方法：新建一个表结构完全相同的分区表，分区需要包含之前表中所有数据，然后通过insert...select语句拷贝数据，拷贝完之后把原表改名，把新的表更名为原表。下面进行操作：

1.创建分区表

CREATE TABLE `table_name` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `pick_value` decimal(12,3) NOT NULL COMMENT '采集值',
  `pick_time` datetime NOT NULL COMMENT '采集时间',
  PRIMARY KEY (`id`,`pick_time`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='采集数据表'
PARTITION BY RANGE COLUMNS(pick_time) (
		PARTITION p20191102 VALUES LESS THAN ('20191102')
)
;

这里是按pick_time字段进行按天分区，这里特意随便写了一个分区，下面演示下通过SQL添加新分区：

2.添加新分区

ALTER TABLE `table_name` ADD PARTITION(PARTITION p20191103 VALUES LESS THAN ('20191103') ENGINE=InnoDB);
ALTER TABLE `table_name` ADD PARTITION(PARTITION p20191104 VALUES LESS THAN ('20191104') ENGINE=InnoDB);
ALTER TABLE `table_name` ADD PARTITION(PARTITION p20191105 VALUES LESS THAN ('20191105') ENGINE=InnoDB);

这个是手动进行创建分区。可以由服务人员手动创建，如果怕忘记可以写自动化工具进行创建。注意，最多创建8192个分区，因此对分区表可能还要考虑分区何时删除的问题。

3.拷贝数据

insert into table_name select * from origin_table_name where pick_time>='2019-11-02 00:00:00' and  pick_time<'2019-11-05 00:00:00';

拷贝插入的速度不是很快，通过where语句慢慢执行是个不错的选择。

4.更改表名

ALTER TABLE origin_table_name RENAME TO bak_table_name;
ALTER TABLE table_name RENAME TO origin_table_name ;

更改表名的速度很快的，瞬间完成。

5.验证

如何验证分区成功，通过EXPLAIN。写一条带时间条件的查询，用EXPLAIN查看执行计划：

EXPLAIN select id,pick_time,pick_value from origin_table_name where  pick_time>'2019-11-03 00:00:00'

查看结果，主要看partitions中的值，说明只使用了这两个分区。注意：其它的列中的数据是本人业务数据，数据本身无参考意义。