共计 1354 个字符,预计需要花费 4 分钟才能阅读完成。
家喻户晓 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 条件查问,效率更高。
文章继续更新,微信搜一搜「一灯架构」第一工夫浏览更多技术干货。
本文由 mdnice 多平台公布