最左匹配原则(转)

一、最左匹配原则的原理

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