chci网站建设,许昌网络推广公司电话,企业如何进行网站推广,网站开发 财务自由前言#xff1a; 最左匹配原则在我们 MySQL 开发过程中和面试过程中经常遇到#xff0c;为了加深印象和理解#xff0c;我在这里把 MySQL 的最左匹配原则详细的讲解一下#xff0c;包括它的原理以及是否导致索引失效的场景。
在讲解 MySQL 的最左匹配原则之前#xff0c;…前言 最左匹配原则在我们 MySQL 开发过程中和面试过程中经常遇到为了加深印象和理解我在这里把 MySQL 的最左匹配原则详细的讲解一下包括它的原理以及是否导致索引失效的场景。
在讲解 MySQL 的最左匹配原则之前我们需要了解一下 MySQL 的联合索引也称复合索引因为最左匹配原则是在联合索引的基础上产生的没有联合索引就没有最左匹配原则这个概念。 一、联合索引 1、什么是联合索引
我们知道单值索引指的是只使用一个字段作为索引字段的索引而联合索引则是使用多个字段来共同构建成一个索引
KEY idx_abc (a, b, c);2、为什么要使用联合索引
2-1、减少开销
建一个联合索引 (a, b, c)实际上相当于建了 (a)、(a, b)、(a, b, c) 三个索引。这样我们就不需要创建 (a)、(b)、(c) 三个单值索引了。我们知道每多一个索引都会增加数据库写操作的开销和磁盘空间的开销对于大量数据的表使用联合索引会大大的减少开销
2-2、覆盖索引
对联合索引 (a, b, c)如果有如下的 SQLselect a, b, c from test where a1 and b2。那么 MySQL 可以直接通过遍历索引取得数据而无需回表从而减少了很多的随机 IO 操作。而减少 IO 操作而减少随机 IO 是 DBA 主要的优化策略在真正的实际应用中覆盖索引是主要的提升性能的优化手段之一。
2-3、提高效率
联合索引的字段越多通过索引筛选出的数据越少。假如有 1000W 条数据的表有如下 sql: select * from table where a1 and b2 and c3假设每个条件可以筛选出 10% 的数据如果只有单值索引那么通过该索引能筛选出 1000W * 10% 100w 条数据然后再回表从 100w 条数据中找到符合 b2 and c3 的数据然后再排序再分页。
但如果是联合索引则通过索引直接筛选出的数据为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
......虽然 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只要是按照联合索引创建的字段从左到右的顺序依次使用不管使用其中多少个字段都会命中索引。
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 这种缺失了最左边 a 字段的情况就是违背最左匹配原则的典型例子结果就是没有用到索引索引失效。
因为缺失了最左边的字段导致索引数据结构 B 树不知道第一步该查哪个节点从而需要去全表扫描了。在建立搜索树的时候 a 就是第一个比较因子必须要先根据 a 来搜索进而才能往后继续查询 b 和 c。
2-4、缺失中间的字段
假如去掉中间的字段保留最左边和右边的字段就是我们说的索引字段不连续
select * from table_name where a 1 and c 3 结果就是只用到了 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;这种情况下由于 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;当多个列同时进行范围查找时只有对索引最左边的那个列进行范围查找才用到 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%; //全表查询我们先了解一下字符型字段的比较规则当列是字符型的话它的比较规则是先比较字符串的第一个字符第一个字符小的那个字符串就比较小如果两个字符串第一个字符相同那就再比较第二个字符依次类推。
所以如果通配符 % 出现在开头B 树则无法进行比较匹配进而导致索引失效。
3、解决文件排序的问题
当我们对查询的数据进行 order by 排序时一般情况下我们是先把数据记录加载到内存中再用一些排序算法比如快速排序归并排序等在内存中对这些记录进行排序。但有时候查询的结果集太大不能在内存中进行排序时需要暂时借助磁盘空间存放中间结果排序操作完成后再把排好序的结果返回客户端。Mysql 把这种在磁盘上进行排序的方式称为文件排序Filesort。
文件排序是非常慢非常耗性能的但如果 order by 子句用到了索引列就有可能避免文件排序的问题
select * from table_name order by a, b, c limit 10;因为 B 树索引本身就是按照上述规则排序的准确来说就是索引是有序的所以得到的结果集已经排好序了不用再进行额外的排序操作。
注意order by 的子句后面的字段顺序也必须按照索引字段的顺序给出不能颠倒顺序MySQL 不会自动调整排序字段的顺序。
下面这种就是因为颠倒顺序而没有使用索引的情况
select * from table_name order by b, c, a limit 10;下面这种是用到部分索引的情况
select * from table_name order by a limit 10;
select * from table_name order by a, b limit 10;下面这种情况由于联合索引左边列为常量后边的列排序可以用到索引
select * from table_name where a 1 order by b, c limit 10;
