【MySQL】之联合索引与最左匹配原则
王廷云的博客
已于 2023-12-10 09:48:32 修改
阅读量2k
收藏 24
点赞数 28
分类专栏： MySQL 文章标签： mysql 数据库
版权
MySQL
专栏收录该内容
27 篇文章3 订阅
订阅专栏
前言：
最左匹配原则在我们 MySQL 开发过程中和面试过程中经常遇到，为了加深印象和理解，我在这里把 MySQL 的最左匹配原则详细的讲解一下，包括它的原理以及是否导致索引失效的场景。
在讲解 MySQL 的最左匹配原则之前，我们需要了解一下 MySQL 的联合索引（也称复合索引），因为最左匹配原则是在联合索引的基础上产生的，没有联合索引就没有最左匹配原则这个概念。
一、联合索引
1、什么是联合索引
我们知道，单值索引指的是只使用一个字段作为索引字段的索引，而联合索引则是使用多个字段来共同构建成一个索引：
KEY idx_abc (a, b, c);
1
2、为什么要使用联合索引
2-1、减少开销
建一个联合索引 (a, b, c)，实际上相当于建了 (a)、(a, b)、(a, b, c) 三个索引。这样我们就不需要创建 (a)、(b)、(c) 三个单值索引了。我们知道，每多一个索引，都会增加数据库写操作的开销和磁盘空间的开销，对于大量数据的表，使用联合索引会大大的减少开销！
2-2、覆盖索引
对联合索引 (a, b, c)，如果有如下的 SQL：select a, b, c from test where a=1 and b=2。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，从而减少了很多的随机 IO 操作。而减少 IO 操作，而减少随机 IO 是 DBA 主要的优化策略，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
2-3、提高效率
联合索引的字段越多，通过索引筛选出的数据越少。假如有 1000W 条数据的表，有如下 sql: select * from table where a=1 and b=2 and c=3，假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W * 10% = 100w 条数据，然后再回表从 100w 条数据中找到符合 b=2 and c=3 的数据，然后再排序，再分页。
但如果是联合索引，则通过索引直接筛选出的数据为：1000w * 10% * 10% * 10% = 1w，这效率的提升可想而知！
二、最左匹配原则
1、最左匹配原则的规则
在联合索引当中，索引匹配时：最左字段优先，以最左边的字段为起点任何连续的字段索引都能匹配上，如果遇到范围查询 (>、<、between、like) 时就会停止匹配。
2、索引是否生效的场景
是否满足最左匹配原则是衡量联合索引命中与否的依据。存在的场景比较多，假设我们创建了以 a, b, c 三个字段的联合索引 idx_abc(a, b, c)，下面我们分别展开讨论索引是否失效的场景。
2-1、全字段全值匹配
索引的全部字段都在查找条件当中，并且都是使用 = 进行全值匹配的情况下，索引是命中生效的：
select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'
......
1
2
3
4
虽然 where 子句几个搜索条件顺序调换了，但不影响查询结果，这是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。
2-2、从左到右按顺序匹配
select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'
1
2
3
只要是按照联合索引创建的字段从左到右的顺序依次使用，不管使用其中多少个字段，都会命中索引。
2-3、缺失最左边的字段
select * from table_name where b = '2'
select * from table_name where c = '3'
select * from table_name where b = '1' and c = '3'
1
2
3
这种缺失了最左边 a 字段的情况就是违背最左匹配原则的典型例子，结果就是没有用到索引（索引失效）。
因为缺失了最左边的字段，导致索引数据结构 B+ 树不知道第一步该查哪个节点，从而需要去全表扫描了。在建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询 b 和 c。
2-4、缺失中间的字段
假如去掉中间的字段，保留最左边和右边的字段（就是我们说的索引字段不连续）：
select * from table_name where a = '1' and c = '3'
1
结果就是只用到了 a 列的索引，而 b 列和 c 列都没有用到。
因为在这种情况下进行数据检索时，B+ 树可以用 a 来指定第一步的搜索方向，但由于下一个字段 b 的缺失，所以只能先把 a = 1 的数据主键 ID 都找出来，然后通过查到的主键 ID 回表查询相关行，再去匹配 c 值的数据了。当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了
2-5、匹配范围值
出现匹配范围值的情况可能比较复杂或难以理解，但我们只需要牢记最左匹配原则的规则：遇到范围查询 (>、<、between、like) 时就会停止匹配。
比如下面这种情况：
select * from table_name where a = 1 and b > 3 and c = 'mm';
1
这种情况下，由于 a 是等值匹配，所以 B+ 树走完 a 索引之后 b 还是有序的，但走完 b 索引之后，由于 b 是范围匹配，所以此时 c 已经是无序的了，最终只使用了 (a, b) 两个索引（由于此时 c 就没法走索引，所以优化器只能根据 a, b 得到数据的主键 ID 回表查询，最终影响了执行效率）。
再比如下面的情况：
select * from table_name where a > 1 and b > 1
select * from table_name where a > 1 and a < 3 and b > 1;
1
2
当多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到 B+ 树索引，也就是只有 a 用到索引，在 a > 1 和 1 < a < 3 的范围内 b 是无序的，所以 b 不能用索引，找到 a 的记录后，只能根据条件 b > 1 继续逐条过滤。
2-6、like 语句匹配问题
当索引列是字符型，并且使用了 like 语句进行模糊查询时，如果通配符 % 不出现在开头，则可以用到索引，否则将会违背了最左匹配原则，而不会使用索引，走的是全表扫描：
select * from table_name where a like 'As%'; //走索引查询
select * from table_name where a like '%As'; //全表查询
select * from table_name where a like '%As%'; //全表查询
1
2
3
我们先了解一下字符型字段的比较规则：当列是字符型的话，它的比较规则是先比较字符串的第一个字符，第一个字符小的那个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个字符，依次类推。
所以，如果通配符 % 出现在开头，B+ 树则无法进行比较匹配，进而导致索引失效。
3、解决文件排序的问题
当我们对查询的数据进行 order by 排序时，一般情况下，我们是先把数据记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序。但有时候查询的结果集太大不能在内存中进行排序时，需要暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。Mysql 把这种在磁盘上进行排序的方式称为文件排序（Filesort）。
文件排序是非常慢非常耗性能的，但如果 order by 子句用到了索引列，就有可能避免文件排序的问题：
select * from table_name order by a, b, c limit 10;
1
因为 B+ 树索引本身就是按照上述规则排序的，准确来说就是：索引是有序的，所以得到的结果集已经排好序了，不用再进行额外的排序操作。
注意：order by 的子句后面的字段顺序也必须按照索引字段的顺序给出，不能颠倒顺序（MySQL 不会自动调整排序字段的顺序）。
下面这种就是因为颠倒顺序而没有使用索引的情况：
select * from table_name order by b, c, a limit 10;
1
下面这种是用到部分索引的情况：
select * from table_name order by a limit 10;
select * from table_name order by a, b limit 10;
1
2
下面这种情况，由于联合索引左边列为常量，后边的列排序可以用到索引：
select * from table_name where a =1 order by b, c limit 10;
1
————————————————
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/aiwangtingyun/article/details/134897381