【MySQL】最左前缀匹配原则
目录
准备库表
结果集在索引列中的查询
1. explain select a,b,c from t where a=1;
2. explain select a,b,c from t where a=1 AND b=2;
3. explain select a,b,c from t where a=1 order by b;
4. explain select a,b,c from t where a=1 order by d;
5. explain select a,b,c from t where b=1;
结果集不在索引列中的查询
1. explain select a,b,c,d from t where a=1;
2. explain select a,b,c,d from t where a=1 AND b=2;
3. explain select a,b,c,d from t where a=1 order by b;
4. explain select a,b,c,d from t where a=1 order by d;
5. explain select a,b,c,d from t where b=1;
总结
MySQL 版本：8.0.32
通过实战的方式加深对最左前缀匹配原则的理解
准备库表
CREATE TABLE `t` (`id` int unsigned NOT NULL AUTO_INCREMENT,`a` int NOT NULL DEFAULT '0',`b` int NOT NULL DEFAULT '0',`c` int NOT NULL DEFAULT '0',`d` int DEFAULT NULL,PRIMARY KEY (`id`),KEY `key_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;建立联合索引（a,b,c）
结果集在索引列中的查询
1. explain select a,b,c from t where a=1;
type: ref 表示走普通索引，结果可能有多行
key_len: 4 表示只用到联合索引（a,b,c）的最左前缀 a
Extra: Using index 表示走覆盖索引，不用回表
2. explain select a,b,c from t where a=1 AND b=2;
和 1 差不多，只不过 key_len 是 8，表示用到联合索引（a,b,c）的两个最左前缀 a,b
3. explain select a,b,c from t where a=1 order by b;
和 1 完全相同，因为建立联合索引（a,b,c）后，找到 a 对应的索引列，那么 b 本身就是有序的
4. explain select a,b,c from t where a=1 order by d;
因为使用了索引列之外的列进行排序，所以走了 Using filesort 外部排序
order by 的排序原理是 MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，这里会按照 a 索引列匹配，因为 d 在主键索引中，所以回表查出整行，存入 sort_buffer 中，最后对 sort_buffer 中的数据按照字段 d 做快速排序，而排序这个动作可能在内存中完成，也可能需要使用外部排序，因此可能会有性能问题
5. explain select a,b,c from t where b=1;
type: index，因为最左前缀匹配原则，没有命中索引，但因为查询的列都在联合索引列中，走了索引扫描
Extra: Using where; Using index 表示条件过滤，且走了覆盖索引
结果集不在索引列中的查询
1. explain select a,b,c,d from t where a=1;
和只查 a,b,c 唯一的区别是 Extra 列为空，因为查 d 需要回表
2. explain select a,b,c,d from t where a=1 AND b=2;
和只查 a,b,c 唯一的区别是 Extra 列为空，因为查 d 需要回表
3. explain select a,b,c,d from t where a=1 order by b;
和只查 a,b,c 唯一的区别是 Extra 列为空，因为查 d 需要回表
4. explain select a,b,c,d from t where a=1 order by d;
和只查 a,b,c 完全相同，因为排序字段也会放在 sort_buffer 中
5. explain select a,b,c,d from t where b=1;
type：ALL，因为 d 不在索引列中，索引失效，直接走了全表扫描
Extra：Using Where，按条件过滤
总结
对于结果集在和不在索引列的两种情况，虽然都不符合最左前缀原则，执行计划还是有很大区别的