我滴乖乖,MySQL联合索引不一定要求最左匹配?
你好,我是yes。
关于 MySQL 索引相关的知识点,我已经写的差不多了,今天再来补充一点关于联合索引的。
之前我在群里就看到有同学说:最左匹配已经过时了,现在 MySQL 8 都不需要最左匹配就能用上联合索引了。
那真的是这样吗?
以下实验基于 MySQL8.0.26 版本
为了故事顺利的发展,我们先建个表。
CREATETABLE`t1` (
`f1`intNOTNULL AUTO_INCREMENT,
`f2`intNOTNULL,
`f3`intNOTNULL,
PRIMARY KEY (`f1`),
KEY`idx_f2_f3` (`f2`,`f3`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
很简单的一张表,一个主键索引,一个联合索引
idx_f2_f3
。此时插入一波数据
INSERTINTO t1 (f2,f3) VALUES
(1,1), (1,2), (1,3), (1,4), (1,5),
(2,1), (2,2), (2,3), (2,4), (2,5);
INSERTINTO t1(f2,f3) SELECT f2, f3 + 5FROM t1;
INSERTINTO t1(f2,f3) SELECT f2, f3 + 10FROM t1;
INSERTINTO t1(f2,f3) SELECT f2, f3 + 20FROM t1;
INSERTINTO t1(f2,f3) SELECT f2, f3 + 40FROM t1;
这个时候表里一共有160条数据,f2 的值基数很低,只有1、2这两个值,基数为2。
数据准备完了,咱们可以开始操作一下了,此时执行:
ANALYZE TABLE t1;
更新了一下表的统计信息,防止 MySQL 误判。简单执行了一个 f2 为条件的联合索引查询,且结果只要求返回 f2 和 f3,所以从这个联合索引可以直接得到值,不需要回表。
这个查询的 explain 结果相信大家也都能理解,用上了
idx_f2_f3
,且 type 是ref,说明用上了这个索引查询。现在我们换一个查询条件,让 f3 作为条件来查询,这个时候就不是最左匹配原则了,讲道理应该用不上索引查询。
但是我的查询条件
f3=1
明明是等值呀?怎么就用上了范围扫描呢?还有 Extra 里面显示的
Using index for skip scan
又是啥?Using index for skip scan
让我们直接进行一个官方文档的查询!
Skip Scan Range Access Method
,它就是产生上述 Extra 的原因。好了,可以忘了官网这个截图了,基于我展示的例子我来解释下。
根据我们所认知的最左匹配原则,执行
where f3 =1
是无法在
idx_f2_f3
上利用 type 是 ref 的查询方式来查询的。因为只有带上索引左边的查询条件才能用上索引,例如:
where f2=1 and f3 =1
那如果无法利用索引进行查询,最终只有一个选择,全表扫描:要么全表扫描主键索引,要么全表扫描二级索引。
MySQL 就意识到这效率确实低,能不能找个优化点呢?
所以就在 8.0.13 版本引入了
Skip Scan Range Access Method
,它在一定条件下,利用了范围扫描来替代了全表扫描的发生。具体原理如下:
先统计一下索引最左字段 f2 的基数即唯一值,这里一共有 1 和 2 两个值。 然后将 f1 的值拼入查询条件中构造 where f2=1 and f3=1
和where f2=2 and f3=1
这样的条件进行查询。
这波是不是小秀?
你 SQL 不带索引最左的条件是吧,我自个儿给你拼上去!
所以一条查询就变成了多次查询,所以 type 就变成了 range 了。
因此最终还是没有逃出最左匹配原则,只是 MySQL 隐式的构造了查询条件,使得看起来好像不需要最左匹配原则而已。
这个优化的中文名叫:跳过扫描范围访问(我直译的,哈哈哈)
跳过扫描范围访问的局限性
不要高兴的太早,这个优化其实有很大的局限性!
它要求最左条件的基数很低,因为它需要列举最左条件的值来构造查询,假如最左条件有上万个,那还不如直接进行全表扫描了对吧。
其实也不需要上万个,我来改一下表里的数据来做一次实验。
Using index for skip scan
了。变成了一个基于二级索引的全表扫描。
除了上面这个关键点之外,还有很多局限性,我根据官网的内容做了一下翻译(和一点便于理解的小修改):
需要联合索引 查询不能跨表 查询不能使用 GROUP BY 或者 DISTINCT 查询只能用一个索引,即索引需要覆盖查询的值,不能回表 查询条件必须是常量,包括 IN() 运算符。
还有两条我不翻译了,我觉得讲的是废话...有点兴趣的自己去看看吧,文末会放链接。
总而言之,能用上这个优化的场景很有限,就基数很低这一个限制我就觉得很大了。
所以这也不是什么万能药,就当一个知识点咱们知晓下,到时候遇到特殊场景能用上的时候装一下,或者在面试官问你最左匹配的时候,提一下,我知道那个 MySQL 8.0.13 版本啊,它做了个优化阿巴巴阿巴巴.....
最后
所以,最左匹配没有过时,MySQL8.0.13的跳过范围查询,也是基于最左匹配的原则来构造查询的。
写着写着,我发现索引还有一个比较容易理解错的东西可以写,也是一个盲点,哈哈下篇揭晓!
MySQL 官网链接如下:https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan%EF%BC%89%EF%BC%9A
往期推荐:
我是yes,从一点点到亿点点,我们下篇见!
关键词
时候
结果
条件
联合索引
数据
最新评论
推荐文章
作者最新文章
你可能感兴趣的文章
Copyright Disclaimer: The copyright of contents (including texts, images, videos and audios) posted above belong to the User who shared or the third-party website which the User shared from. If you found your copyright have been infringed, please send a DMCA takedown notice to [email protected]. For more detail of the source, please click on the button "Read Original Post" below. For other communications, please send to [email protected].
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。
版权声明:以上内容为用户推荐收藏至CareerEngine平台,其内容(含文字、图片、视频、音频等)及知识版权均属用户或用户转发自的第三方网站,如涉嫌侵权,请通知[email protected]进行信息删除。如需查看信息来源,请点击“查看原文”。如需洽谈其它事宜,请联系[email protected]。