【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，按条件过滤

总结

对于结果集在和不在索引列的两种情况，虽然都不符合最左前缀原则，执行计划还是有很大区别的