MySQL索引失效的底层原理

本文详细探讨了MySQL中索引失效的各种情况,包括最左前缀法则失效、范围查询右侧索引失效及模糊查询中LIKE操作符的影响。通过具体实例分析了索引的工作原理和为何在某些条件下索引会失效。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

mysql的索引在使用不当情况下会失效.

    比如:使用最佳左前缀法则,大于号右边的索引会失效,使用like索引会失效,当准备面试的时候我们为了应付面试的的时候往往会去找到这些面试题目的答案,但是往往不会去思考,为什么会失效?

      今天文章就会仔细的分析下,什么情况下mysql的索引会失效,我们都知道,索引失效的情况下都是针对联合索引

如下图:

      一个联合索引的节点上面有两个键值对,现在假设联合索引的字段是有a和b组成的,那么现在从上面的图可以看到:2 和 4 就代表我们的a和b连个字段组合成了一个联合索引,然后可以仔细观察叶子节点,左边的叶子节点都是有序排列的,并且由小到大,所以可以看到a的优先级大于b的优先级,而右边的则是无序的

 

好的,现在我们可以分析下,为什么是最左前缀法则失效?为什么大于号右边的会失效?为什么like会失效?

 

我现在新建一个测试用户表;

创建了一个复合索引由 phone和len_id和region_id组成的idx_phone_lan_region,然后我们可是测试一下

首先我们测试一个遵循最佳做前缀法则;

执行结果如下;

explan可以看到这条sql是执行了索引的,rows等于1,type=ref

 

然后我们去掉 手机号再查询一次;

可以看到rows是扫描了一行数据,当然我这个用户表是没有数据的,所以只能看到rows=1,然后type=all,说明这条sql没有走索引,

所以我们分析下,为什么没有走索引,还是由上面的那个图我们可以看出;

我们知道联合索引再b+树上的排序是先排a,当a相等的情况下再排b,然后我们刚刚看到了,条件查询存在手机号的遵循最佳左前缀法则,首先a字段再b+树上面是有序的,就能定位到a所在的节点,就是通过二分查找发找到a,当我们查找了第一个字段,然后再来查找第二个字段b,从图可以看出,当a相同的情况下b也是有序的,这是时候,我们的a已经确定了,那么我们就可以再a的基础上用二分查找发去查找b,这种情况下b也是有序的,所以它也能查找到,所以这个遵循最佳做前缀法则的sql分析下来是没有问题的,然后我们来分析不遵循最佳最佳左前缀法则的sql;

问题出在哪里?

只有当我们a相等的情况下,b才是有序的,而上面的当我们把电话号码去掉的情况下,b就是无序的,缺少b执行的索引的存在条件,没有a的情况下,b肯定是无序的,所以在无序情况下我们无法找到b这个值,所以只能进行全表扫描,不会搜索引

这就是为什么要遵循最佳左前缀法则了

 

然后我们看看,范围查找的右边,为什么索引会失效;还是看这个图

首先我们看叶子节点,查找a>1 b=1的数据,可以看到a大于1的数据由2,3,4,分别在叶子节点可以看到,然后我们再去找b=1的数据,而a>1的数据对应的b的数据是没有序的,这个无序的不仅仅体现在叶子节点上而且还体现在非叶子节点上,所以这种情况下b无序,还是无法进行索引匹配,

当我们% 放在左边,放在右边都是不走索引的,那么这又是什么原因呢?

首先解释下% 的含义,首先我们这个%放在右边,是去查找以1 开头的数据,例如,111222,这个数据就可以查询出来,但是当我们变成222111,就查询不出来,所以这个%的意思就是这个意思,所以百分号分别在左边,右边,还是两边,分别叫做,前缀,后缀,中缀,当我们一个字符串存在b+树里面存储的时候,也是按照字母的大小去排序的,如下图

你去查找以a开头的字符串,可以按照a的顺序查找到。所以当你加上%就不是前缀法则了,所以这就是like失效的原理,所以这里就可以推理出in 为什么会失效,or 为什么会失效!

所以可以总结下;

  1. 如果是复合索引,叶子节点不仅保存了复合索引的值,还有主键的值,这就是当你使用覆盖索引的时候,加上主键也会用到索引的原因

  2. 如果是模糊查询,如果查询字段不包括索引字段,只有当%放到左边时候才会用到索引,但如果是覆盖索引,则会用到覆盖索引,

  3. 返回查询如果复合做前缀法则,而且查询的数据比较少的情况下,即使没有用到覆盖索引,也会走索引,但是如果数据过多,则会全表扫描

凡事不能二分查找的情况下都属于索引失效的情况

### MySQL 素引失效原因 索引在特定情况下可能会失效,这会对查询性能造成负面影响。以下是几种常见的索引失效情形: - **条件表达式中使用了函数**:如果在 `WHERE` 子句中的列上应用了任何函数,则即使存在针对该列的索引也不会被利用[^3]。 - **使用 "不等于" (`<>`, `!=`) 操作符**:当查询条件涉及非等值比较时(例如 `<>` 或者 `!=`),MySQL 可能不会有效地使用索引来加速查找过程。 - **列类型不匹配**:如果表定义里的字段类型与查询条件里提供的值之间存在隐式转换需求,那么也可能导致索引无法正常工作。 - **LIKE 进行模糊匹配**:特别是以通配符开头 `%abc%` 的模式匹配会使索引变得无用;因为在这种情况下数据库引擎不得不扫描整个表来找到符合条件的数据项。 - **数据量过小**:对于非常小规模的数据集来说,创建并维护额外结构所带来的开销可能超过了直接全表扫面的成本,在这种环境下索引也难以发挥其优势。 - **范围查询后的精确匹配**:在一个复合索引 `(age, name)` 上执行类似 `age = 18 AND name > 'xx'` 的查询时,虽然可以利用到第一个键 `age` 的索引部分来进行筛选,但对于第二个键 `name` 则由于进行了范围检索而失去了后续更进一步精确定位的能力[^2]。 ### 解决方案 为了应对上述提到的各种可能导致索引失效的情况,可采取如下措施: #### 针对函数调用问题 尽可能避免在查询条件中对索引列施加计算或变换处理。可以通过预先存储经过相同运算的结果作为新属性的方式绕过此障碍,从而允许这些预处理过的数值参与高效的索引访问路径。 #### 处理非等价关系操作 考虑重构 SQL 表达式以便能够更好地适应现有索引策略。比如尝试拆分复杂的选择标准成多个简单子句组合而成的形式,或是引入辅助索引来覆盖更多类型的过滤逻辑。 #### 类型一致性保障 确保应用程序层面上所提供的参数格式严格遵循底层持久化对象的设计规格说明书中所规定的预期输入样式,减少不必要的自动转型动作发生频率。 #### LIKE 使用优化建议 尽量让前缀固定的部分越长越好,并且只把不确定因素放在字符串结尾处,即采用形如 `'prefix%'` 而不是 `'%suffix'` 的写法。另外还可以借助全文搜索引擎技术实现更加灵活多样的文本检索功能而不必依赖传统 B-tree 结构下的有限支持程度。 #### 数据分布考量 评估实际业务场景下是否真的有必要为那些记录数极少的小表格建立专门用于提升读取速度的附加设施——有时候简单的顺序遍历反而更快捷有效得多! #### 综合运用最左前缀原理 理解并充分利用好 MySQL 中关于联合索引的工作机制,按照查询习惯合理安排各组成要素之间的先后次序,使得尽可能多的实际应用场景都能享受到来自硬件层面带来的增益效果[^1]。 ```sql -- 创建一个适合于频繁使用的查询模式的新索引 CREATE INDEX idx_age_name ON users (age, name); -- 对于经常性的范围查询加上必要的限制条件以提高命中率 SELECT * FROM users WHERE age BETWEEN 18 AND 20 AND name >= 'A'; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值