最左匹配原则(转)
一、最左匹配原则的原理
MySQL 建立多列索引(联合索引)有最左匹配的原则,即最左优先:
如果有一个 2 列的索引 (a, b),则已经对 (a)、(a, b) 上建立了索引;
如果有一个 3 列索引 (a, b, c),则已经对 (a)、(a, b)、(a, b, c) 上建立了索引;
二、违背最左原则导致索引失效的情况
(下面以联合索引 abc_index:(a,b,c) 来进行讲解,便于理解)
1、查询条件中,缺失优先级最高的索引 “a”
当 where b = 6300 and c = ‘JJJ疾风剑豪’ 这种没有以 a 为条件来检索时;B+树就不知道第一步该查哪个节点,从而需要去全表扫描了(即不走索引)。
因为建立搜索树的时候 a 就是第一个比较因子,必须要先根据 a 来搜索,进而才能往后继续查询b 和 c,这点我们通过上面的存储结构图可以看明白。
2、查询条件中,缺失优先级居中的索引 “b”
当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时;B+ 树可以用 a 来指定第一步搜索方向,但由于下一个字段 b 的缺失,所以只能把 a = 1 的数据主键ID都找到,通过查到的主键ID回表查询相关行,再去匹配 c = ‘JJJ疾风剑豪’ 的数据了,当然,这至少把 a = 1 的数据筛选出来了,总比直接全表扫描好多了。
这就是MySQL非常重要的原则,即索引的最左匹配原则。
三、查询优化器偷偷干了哪些事儿
当对索引中所有列通过 “=” 或 “IN” 进行精确匹配时,索引都可以被用到。
1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘aaa’; 为什么还能利用到索引?
理论上索引对顺序是敏感的,但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。当然了,SQL书写的好习惯要保持,这也能让其他同事更好地理解你的SQL。
2、还有一个特殊情况说明下,下面这种类型的SQL, a 与 b 会走索引,c不会走。
select * from LOL
where a = 2 and b > 1000
and c='JJJ疾风剑豪';
对于上面这种类型的sql语句;
mysql会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配(包括like '陈%'这种)。
在a、b走完索引后,c已经是无序了,所以c就没法走索引,优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引,影响了执行效率。
其实,这种场景可以通过修改索引顺序为 abc_index:(a,c,b),就可以使三个索引字段都用到索引,建议小伙伴们不要有问题就想着新增索引哦,浪费资源还增加服务器压力。
综上,如果通过调整顺序,就可以解决问题或少维护一个索引,那么这个顺序往往就是我们DBA人员需要优先考虑采用的。
原文链接:https://blog.csdn.net/weiguang102/article/details/127895627