众所周知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;
值越大,区分度越高,优先放在第一列。
很多人不知道联合索引在B+树中是怎么存储的?我简单画一下。
比如在(a
,b
)字段上面创建联合索引,存储结构类似下面这样:
叶子节点存储全部数据,用顺序指针相连,数据都是先按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个字段的索引,而不是只用到前两个字段的索引呢?
有个非常简单的方法,看执行计划的索引长度。
由于int类型的字段占4个字节,3个字段长度刚好是12个字节。
第三题:
下面这条SQL,该怎么创建联合索引?
SELECT * FROM test WHERE a in (1,2,3) and b > 1;
答案是(a
,b
)。in条件查询会被转换成等值查询,可以验证一下:
可以看到用到了两个字段的索引。
所以我们在平时做开发,尽量想办法把范围查询转换成in条件查询,效率更高。
> 文章持续更新,可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。
标签:count,面试题,80%,索引,字段,联合,MySQL,test,区分度 From: https://www.cnblogs.com/yidengjiagou/p/16615287.html