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

发表于 更新于 分类于
本文字数： 286 阅读时长 ≈ 1 分钟

背景

最近在工作工程中遇到一个关于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) */;

总结

后记