MySQL索引失效的场景

1. 不满足最左前缀法则

1. 索引列的顺序很重要：

   • 在联合索引中，查询条件必须从索引的最左边列开始，否则后续的索引列可能无法被利用。

   • 例如，对于联合索引 (name, age, phone)，查询条件必须包含 name，否则无法利用索引。

2. 连续性原则：

   • 查询条件中的索引列必须是连续的，不能跳过中间的列，否则后续的索引列将失效。

   • 例如，查询条件为 name 和 phone，但跳过了 age，那么 phone 这一列的索引将失效。

3. 全范围匹配原则：

   • 如果查询条件中某个索引列使用了范围查询（如 >、<、BETWEEN），那么该列之后的索引列将失效。

   • 例如，查询条件为 name = 'Alice' AND age > 20，那么 phone 这一列的索引将失效，因为 age 是范围查询。

---

假设有一个联合索引 (name, age, phone)，以下是一些查询条件的分析：

1. 有效查询条件：

   • WHERE name = 'Alice' AND age = 25 AND phone = '123456'
     分析：完全符合最左前缀法则，name、age 和 phone 都能利用索引。

   • WHERE name = 'Alice' AND age = 25
     分析：符合最左前缀法则，name 和 age 都能利用索引。

   • WHERE name = 'Alice'
     分析：符合最左前缀法则，name 能利用索引。

2. 部分失效的查询条件：

   • WHERE name = 'Alice' AND phone = '123456'
     分析：phone 的索引失效，因为跳过了中间的 age。只有 name 能利用索引。

   • WHERE age = 25 AND phone = '123456'
     分析：name 没有出现在查询条件中，整个联合索引失效，会走全表扫描。

3. 范围查询导致失效：

   • WHERE name = 'Alice' AND age > 20 AND phone = '123456'
     分析：age 是范围查询，phone 的索引失效。只有 name 和 age 能利用索引。

2. 索引参与运算

1. 索引列被计算

当在查询条件中对索引列进行计算或使用函数时，MySQL 通常无法利用索引，因为索引是基于原始列值构建的，而计算后的值与索引结构不一致。

示例

假设有一个表 users，其中 age 列上有索引：

sql复制

SELECT * FROM users WHERE age + 1 = 30;

上述查询中，age + 1 是对索引列的计算，导致索引失效，MySQL 会进行全表扫描。

---

2. 隐式类型转换

当查询条件中的数据类型与索引列的数据类型不一致时，MySQL 会尝试进行隐式类型转换，这可能导致索引失效。

示例

假设 user_id 列是 INT 类型，并且有索引：

sql复制

SELECT * FROM users WHERE user_id = '123';

上述查询中，user_id 是 INT 类型，但查询条件使用了字符串 '123'，MySQL 会尝试将字符串转换为数字，这可能导致索引失效。

3. 使用or和like不规范

在使用or的时候要求两个条件都必须是索引列，而使用like的时候只能后模糊匹配，否则都将走全表扫描