场景: 拨号记录表 ,表数据达到1500万后,明显感觉查询速度下降。mysql只支持主键分区,优先测试按主键分区后,对列表查询基本没有起到优化效果,所以将记录产生时间和自增id加入设置成复合主键,然后再按时间进行分区处理。
#1.移除现有分区
alter table clue_call_record remove partitioning;
#2.移除现有主键,以及自增功能
ALTER TABLE `jgb`.`clue_call_record`
MODIFY COLUMN `id` int(11) NOT NULL COMMENT '主键ID' FIRST;
ALTER TABLE `jgb`.`clue_call_record`
DROP PRIMARY KEY;
#3.添加 id 与创建时间为复合主键
alter table organization_clue_library add primary key (id,createTime);
# 4.id重新设为自增主键
ALTER TABLE `jgb`.`clue_call_record`
MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID' FIRST;
ALTER TABLE clue_call_record
PARTITION BY RANGE (to_days(createTime)) (
PARTITION p201910 VALUES LESS THAN (to_days('2019-10-01')),
PARTITION p201912 VALUES LESS THAN (to_days('2019-12-01')),
PARTITION p202006 VALUES LESS THAN (to_days('2020-06-01')),
PARTITION p202008 VALUES LESS THAN (to_days('2020-08-01')),
PARTITION p202010 VALUES LESS THAN (to_days('2020-10-01')),
PARTITION p202012 VALUES LESS THAN (to_days('2020-12-01')),
PARTITION p202102 VALUES LESS THAN (to_days('2021-02-01')),
PARTITION p202104 VALUES LESS THAN (to_days('2021-04-01')),
PARTITION p202106 VALUES LESS THAN (to_days('2021-06-01')),
PARTITION p202108 VALUES LESS THAN (to_days('2021-08-01')),
PARTITION p202110 VALUES LESS THAN (to_days('2021-10-01')),
PARTITION p202112 VALUES LESS THAN (to_days('2021-12-01')),
PARTITION p202202 VALUES LESS THAN (to_days('2022-02-01')),
PARTITION p202204 VALUES LESS THAN (to_days('2022-04-01')),
PARTITION p2022 VALUES LESS THAN (MAXVALUE) )
;