索引失效问题整理

    在面试的时候,数据库方面的问题对于开发人员来说,是绕不过的一个点,而数据库方面,很少直接面试SQL怎么写,而是问效率相关的问题,索引是提高查询效率的一种手段,但是往往,我们容易忽略的一个问题就是索引失效。

    我以前面试,人家问我索引失效,我没反应过来,心想我创建了索引,查询就完事了,怎么会失效呢?后来才知道,索引创建成功,如果查询的时候,选择的字段或者查询的条件设置不合理,虽然能够出结果,但是索引是失效的,这就是索引失效的情形。

    下面来总结一下,索引失效的情况,有点小复杂。

  1. 对于联合索引,违背索引最左前缀原则。
  2. 对索引列使用函数。
  3. 对索引列作运算操作(条件右边使用运算符,索引会生效)。
  4. 使用like关键字,并以'%'开始(使用'%'结尾索引是生效的)。
  5. 使用不等于操作符<>。
  6. 使用not null。
  7. 使用or,or两边的列索引名称不一样,而且一个有索引,另一个没有索引。(or两边的列相同或者都有索引会生效)。
  8. 索引列存在类型转换。

    从运行sql角度来验证这些失效的情况。首先,我们要准备表,其次,建立对应的索引,最后查询的时候,使用explain执行计划,查看运行结果。   

     联合索引,左列前缀 

    从截图可以看出,对emp表创建联合索引(name,age,salary)

    1)、当使用全部索引时,用到了索引;

    2)、当不使用最左列name时,索引失效。

  &n

<think>好的,我现在需要帮助用户理解索引失效的原因,特别是关于OR操作导致索引失效问题。用户提到了几个引用来源,我需要结合这些信息来组织一个清晰的回答。首先,根据引用3,OR导致索引失效的情况是在OR后面的条件不是索引的时候才会失效,如果OR连接的是同一字段,索引可能不会失效。引用4提到组合索引如果不按顺序使用,会导致全表扫描。而引用5则说明在Oracle中,当索引的第一位置是等值条件时,其他可以是任何情况,否则不走索引。 接下来,我需要整理这些信息,分点解释OR导致索引失效的原因。可能的原因包括OR连接的字段不同、组合索引顺序不遵循最匹配原则、以及隐式类型转换或函数操作。然后,需要提供解决方法,比如统一索引覆盖、拆分查询使用UNION,以及优化索引设计。 另外,用户还希望生成相关问题,我需要根据内容提出几个相关的问题,例如其他导致索引失效的情况、如何验证索引是否失效、不同数据库的差异等。最后,确保回答符合格式要求,使用中文,正确引用标注,并保持结构清晰自然。</think>### 索引失效原因分析(OR场景) #### 一、OR导致索引失效的核心原因 1. **OR连接的字段未统一索引覆盖** 当使用`OR`连接多个条件时,**若其中任意一个条件未使用索引**,数据库会直接选择全表扫描。例如: ```sql SELECT * FROM users WHERE id = 1 OR name = 'Alice'; -- 若name字段无索引,整体索引失效[^3] ``` 即使`id`字段有索引,`OR`后条件索引也会导致优化器放弃使用索引[^1]。 2. **组合索引的顺序违反最前缀原则** 对于组合索引`(A,B,C)`,若查询条件未按顺序使用索引字段(如仅使用`B`或`C`),或通过`OR`打乱顺序,索引失效[^4]。例如: ```sql SELECT * FROM orders WHERE product_id = 100 OR status = 'paid'; -- 组合索引(product_id, status)可能失效[^4] ``` 3. **隐式类型转换或函数操作** 若`OR`连接的字段存在类型转换(如字符串与数字比较)或函数操作(如`UPPER(name)`),等同于对索引施加额外计算,导致索引失效。 --- #### 二、解决方法 1. **统一索引覆盖** 为所有`OR`连接的字段建立联合索引。例如: ```sql CREATE INDEX idx_id_name ON users(id, name); ``` 2. **拆分查询+UNION优化** 将`OR`拆分为多个独立查询,利用索引后合并结果: ```sql (SELECT * FROM users WHERE id = 1) UNION (SELECT * FROM users WHERE name = 'Alice'); ``` 3. **强制索引提示(谨慎使用)** 在特定数据库(如MySQL)中可通过语法强制使用索引: ```sql SELECT * FROM users USE INDEX(idx_id) WHERE id = 1 OR name = 'Alice'; ``` --- #### 三、典型案例 **场景:** 订单表查询 ```sql SELECT * FROM orders WHERE user_id = 100 OR order_date > '2023-01-01'; ``` **问题:** 若仅`user_id`有索引,`order_date`无索引,则索引失效[^3]。 **优化:** - 为`order_date`单独建索引或创建联合索引`(user_id, order_date)` - 改写为`UNION`查询 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

luffy5459

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值