一系列MySQL索引问题引起的争论

题目

题目1：表t(id,a,b,c)有索引(a,b,c)，判断下列查询语句走不走索引

题目

查询语句
select * from t where a=?
select * from t where a=? and b=?
select * from t where a=? and c=?
select * from t where a=? and b=? and c=?
select * from t where b=?
select * from t where b=? and a=?
select * from t where b=? and c=?
select * from t where c=?

答案

查询语句	是否走索引
select * from t where a=?	是
select * from t where a=? and b=?	是
select * from t where a=? and c=?	是
select * from t where a=? and b=? and c=?	是
select * from t where b=?	是
select * from t where b=? and a=?	是
select * from t where b=? and c=?	是
select * from t where c=?	是

题目2：表t(id,a,b,c,d)有索引(a,b,c)，判断下列查询语句走不走索引

题目

（同上）

答案

查询语句	是否走索引
select * from t where a=?	是
select * from t where a=? and b=?	是
select * from t where a=? and c=?	是
select * from t where a=? and b=? and c=?	是
select * from t where b=?	否
select * from t where b=? and a=?	是
select * from t where b=? and c=?	否
select * from t where c=?	否

解释

解释一：为什么题目一没有条件a的都走索引，题目二就不走了？

主要就是【最左前缀匹配】和【覆盖索引】

我们以where c=?为例

• 首先是【最左前缀匹配】

最左前缀匹配，也就是索引树是先按索引的一个字段排序，再按第二个，以此类推，本题中就是先按a排序，a相等的节点内，再按b排序，a、b都相等了，再按c排。

如果直接是c的话，索引是会失效的。因为：

• 有序的是a，而c的有序是在a、b有序的前提下
• 如果先走索引树，是需要遍历整棵二级索引树的，并且需要再【回表】，成本较高

所以，无论是b=?，还是c=?，还是他们的排列组合，都会失效，因此题目二是“否”。

那题目一呢？题目一为什么就行，题目二就不行？往下看

• 接着就是【覆盖索引】

题目一的select *翻译出来就是select id,a,b,c

题目二的select *翻译出来就是select id,a,b,c,d

覆盖索引就是说如果在二级索引上已经覆盖了所有select条件，便不会回表，直接返回二级索引树上的结果就好（二级索引树的叶子节点value为主键id）

• 题目一的表中只有(id,a,b,c)，覆盖索引生效，索引的key为(a,b,c)，value为id，因此优先走二级索引树
• 题目二的表中有(id,a,b,c,d)，覆盖索引失效，有个d没办法cover掉，肯定需要回表去取，所以就会这样

如果是where c=?，即使索引失效，也会优先去走二级索引树，因为他已经cover住所有值了，这是一个成本问题

解释二：为什么select * from t where b=? and a=?也能走？

【优化器】会重排where条件