MySQL 索引最左前缀原则失效？

测试索引最左前缀原则，发现缺失带头索引后，索引还是生效的。

一、测试

1. 创建测试表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(100) NOT NULL DEFAULT '' COMMENT '密码',
  `sex` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2. 给username、password、sex三个字段创建联合索引：

ALTER TABLE `user` ADD INDEX idx(username,`password`, sex);

3. EXPLAIN 查看WHERE password='123456' AND sex=1执行计划：

EXPLAIN SELECT * FROM `user` WHERE `password`='123456' AND `sex`=1

二、分析

结果显示，username、password、sex联合索引并没有失效，到这里以为索引最左前缀原则失效了，但其实并没有。

查询资料后发现，该SQL执行计划type=index，即索引全表扫描，也就是只遍历了索引树：

在这个例子中，查询where条件是password和sex，select * 实际上是id、username、password、sex，idx索引中包含了username、password、sex，关联了主键索引的索引键，这时候是不需要回表查询的，可以直接返回结果。

三、验证

猜测如果新增一个字段，而联合索引没有包含，则应该走全表扫描：

验证一下

1. 新增age属性

ALTER TABLE `user` ADD COLUMN `age` INT(10) DEFAULT 0 NOT NULL AFTER `sex`; 

2. 再执行一下EXPLAIN 查看WHERE password='123456' AND sex=1执行计划：

EXPLAIN SELECT * FROM `user` WHERE `password`='123456' AND `sex`=1

3. 执行结果可以看到，type=ALL，走的是全表扫描。

到这里，可以看到联合索引的最左前缀原则并没有失效。

在MySQL 8.0开始，加入了新的优化机制，新增了Index Skip Scan索引跳跃式扫描，对于这种SQL，联合索引也能生效使用