三道MySQL联合索引面试题，淘汰80%的面试者，你能答对几道

超、凢脫俗 2024-04-06 09:28 85阅读 0赞

众所周知MySQL联合索引遵循最左前缀匹配原则，在少数情况下也会不遵循（有兴趣，可以翻一下上篇文章）。

创建联合索引的时候，建议优先把区分度高的字段放在第一列。

至于怎么统计区分度，可以按照下面这种方式。

创建一张测试表，用来测试：

CREATE TABLE \`test\` (  
\`id\` int NOT NULL AUTO\_INCREMENT COMMENT '主键',  
\`a\` int NOT NULL,  
\`b\` int NOT NULL,  
\`c\` int NOT NULL,  
PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB COMMENT='测试表';  
统计每个字段的区分度：

select  
count(distinct a)/count(\*),  
count(distinct b)/count(\*),  
count(distinct c)/count(\*)  
from test;

![995ca96e886d43d7a34ff9080d1f3dac.png][]

值越大，区分度越高，优先放在第一列。

很多人不知道联合索引在B+树中是怎么存储的？我简单画一下。

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

![49ddb54431e844389a5b67d6390dbe15.png][]  
叶子节点存储全部数据，用顺序指针相连，数据都是先按a字段排序，a字段的值相等时再按b字段排序。

a字段的值是全局有序的，分别有1,1,1,2,2,2。

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

所以在进行SQL查询的时候，如果where条件中没有a字段，只有b字段，是无法用到索引的，像下面这样：

select \* from test where b=1;  
像有些文章上面说的，在（a,b）两个字段上创建联合索引，就会创建两个索引，分别是（a）和（a,b），这其实是一种不恰当的表述，虽然结果是对的。

下面做几道联合索引的经典面试题，试一下大家掌握的怎么样？

第一题：

下面这条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个字段的索引，而不是只用到前两个字段的索引呢？

有个非常简单的方法，看执行计划的索引长度。

![3451987da85344dbbd303bed3ba05053.png][]  
由于int类型的字段占4个字节，3个字段长度刚好是12个字节。

第三题：

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

SELECT \* FROM test WHERE a in (1,2,3) and b > 1;  
答案是（a,b）。in条件查询会被转换成等值查询，可以验证一下：

![6ffd0f403f384375b7efcb741fdf4744.png][]  
可以看到用到了两个字段的索引。

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

[995ca96e886d43d7a34ff9080d1f3dac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/087ca61981e24573b0f5301766e7fa8c.png
[49ddb54431e844389a5b67d6390dbe15.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b63be4db4bbf4d02bd9bb12fed10921d.png
[3451987da85344dbbd303bed3ba05053.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/2e7382afdc2f49d789146acd76d6e6ad.png
[6ffd0f403f384375b7efcb741fdf4744.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c8ea7d289bcb455fa40b9d6a77d535b4.png