Mysql千万级自增主键单表改复合主键分区记录

 

场景: 拨号记录表 ，表数据达到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) )
;