一则关于MySQL分区表索引的探索

背景

最近在工作工程中遇到一个关于MySQL分区表索引的问题

研究过程

代码片段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `test_a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime NOT NULL,
`col` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`,`create_time`),
KEY `index_create_time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(create_time)
(PARTITION p1 VALUES LESS THAN ('20210223') ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN ('20210224') ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN ('20210225') ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN ('20210226') ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN ('20210227') ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN ('20210228') ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN ('20210301') ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN ('20210302') ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN ('20210303') ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN ('20210304') ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN ('20210305') ENGINE = InnoDB) */;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `test_b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime NOT NULL,
`col` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(create_time)
(PARTITION p1 VALUES LESS THAN ('20210223') ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN ('20210224') ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN ('20210225') ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN ('20210226') ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN ('20210227') ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN ('20210228') ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN ('20210301') ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN ('20210302') ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN ('20210303') ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN ('20210304') ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN ('20210305') ENGINE = InnoDB) */;

总结

后记