MySQL联合索引面试题详解？

思考一个问题，联合索引在B+树中是怎么存储的？

比如在（a,b）字段上面创建联合索引，存储结构类似下面这样：

 

数据都是先按a字段排序，a字段的值相等时再按b字段排序。

a字段的值是全局有序的，b字段的值是全局无序的，只有在a字段的值相等时才呈现出局部有序。

下面做几道联合索引的经典面试题。

第一题：下面这条SQL，该怎么创建联合索引？

• SELECT * FROM test WHERE a = 1 and b = 1 and c = 1;

你以为的答案是（a,b,c），其实答案是6个，abc三个的排列组合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。

MySQL优化器为了适应索引，会调整条件的顺序。再给面试官补充一句，区分度高的字段放在最前面，大大加分。

第二题：下面这条SQL，该怎么创建联合索引？

• SELECT * FROM test WHERE a = 1 and b > 1 and c = 1;

考察的知识点是： 联合索引遇到范围匹配会停止，不会再匹配后面的索引字段。

所以答案应该是：（a,c,b）和 （c,a,b）。

当创建（a,c,b）和 （c,a,b）索引的时候，查询会用到3个字段的索引，效率更高。

怎么判断是用到了3个字段的索引，而不是只用到前两个字段的索引呢？有个非常简单的方法，看执行计划的索引长度。

 

由于int类型的字段占4个字节，3个字段长度刚好是12个字节。

第三题：下面这条SQL，该怎么创建联合索引？

• SELECT * FROM test WHERE a in (1,2,3) and b > 1;

答案是（a,b）。in条件查询会被转换成等值查询，可以验证一下：

 

可以看到用到了两个字段的索引。

所以我们在平时做开发，尽量想办法把范围查询转换成in条件查询，效率更高。