“我明明加了索引，为什么查询还是慢？”
“EXPLAIN 显示没走索引，是不是索引坏了？”

在日常开发和 DBA 工作中，这类问题屡见不鲜。其实，MySQL 的索引本身并不会“失效”，而是优化器在特定条件下无法使用或主动放弃使用索引，导致查询退化为全表扫描。

本文将带你深入剖析 8 种最常见“索引失效”场景，结合原理、示例与解决方案，助你写出真正高效的 SQL。

---

一、先澄清一个误区：索引不会“失效”

索引一旦创建，就一直存在于存储引擎中。所谓“索引失效”，准确说法是：

“查询语句未能命中预期索引，导致执行计划未使用索引。”

判断依据很简单：用 EXPLAIN 查看执行计划，若 key 为 NULL 且 type = ALL，说明走了全表扫描。

EXPLAIN SELECT * FROM users WHERE name LIKE '%Tom';
-- 如果 key: NULL, type: ALL → 索引未被使用

接下来，我们逐个击破 8 大高频“失效”场景。

---

二、场景 1：对索引列使用函数或表达式

❌ 错误示例：

SELECT * FROM orders WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price * 1.1 > 100;

🔍 原因：

MySQL 无法直接在索引上计算 YEAR(create_time) 或 price * 1.1。即使 create_time 有索引，也必须先取出每行数据计算后再判断——等于全表扫描。

✅ 正确写法：

SELECT * FROM orders 
WHERE create_time >= '2023-01-01' 
  AND create_time < '2024-01-01';

📌 黄金法则：索引列必须“裸露”出现在 WHERE 条件中，不能被包裹在函数、运算符或表达式里。

---

三、场景 2：隐式类型转换

❌ 错误示例：

-- user_id 是 INT 类型
SELECT * FROM users WHERE user_id = '123';

-- phone 是 VARCHAR 类型
SELECT * FROM users WHERE phone = 13800138000;

🔍 原因：

MySQL 遵循“将字段转为目标类型”的规则。

• 第一个例子：会把 user_id 转成字符串比较 → 无法用索引；
• 第二个例子：会把 phone 列转成数字 → 同样索引失效。

✅ 正确写法：

SELECT * FROM users WHERE user_id = 123;
SELECT * FROM users WHERE phone = '13800138000';

💡 提示：开启 sql_mode=STRICT_TRANS_TABLES 可减少此类问题。

---

四、场景 3：违反联合索引的最左前缀原则

假设索引：

INDEX idx_name_age_city (name, age, city)

✅ 能用索引：

WHERE name = 'Tom'
WHERE name = 'Tom' AND age = 20
WHERE name = 'Tom' AND city = 'Beijing' AND age = 20  -- 顺序无关，但字段要连续从左开始

❌ 索引失效：

WHERE age = 20                    -- 缺少最左列 name
WHERE city = 'Beijing'            -- 跳过 name 和 age
WHERE age = 20 AND city = 'Beijing' -- 未包含最左列

🔍 原因：

B+ 树索引按 (name, age, city) 排序，必须从最左侧字段开始连续使用。

⚠️ 注意：MySQL 8.0 引入了 Index Skip Scan，可在特定条件下跳过最左列，但要求最左列为等值且区分度极低，不可依赖。

---

五、场景 4：使用 !=、<>、NOT IN、NOT EXISTS

❌ 示例：

SELECT * FROM users WHERE status != 'deleted';
SELECT * FROM orders WHERE user_id NOT IN (1,2,3);

🔍 原因：

这类操作通常匹配大量数据，优化器认为：

“走索引 + 回表 N 次” 的成本 > “直接全表扫描”。

尤其当排除的值很少时，MySQL 更倾向全表。

✅ 替代方案：

• 改用正向条件：

SELECT * FROM users WHERE status IN ('active', 'pending');

• 确保字段 NOT NULL（避免 NOT IN 遇到 NULL 返回空结果）。

---

六、场景 5：LIKE 以通配符开头

❌ 索引失效：

SELECT * FROM users WHERE name LIKE '%Tom';
SELECT * FROM users WHERE name LIKE '%Tom%';

✅ 能用索引：

SELECT * FROM users WHERE name LIKE 'Tom%';  -- ✅ 可走 range 扫描

🔍 原因：

B+ 树索引是前缀有序的，'Tom%' 可快速定位区间；但 '%Tom' 无法确定起始位置。

💡 解决方案：

• 业务上避免后模糊查询；
• 使用 全文索引（FULLTEXT）；
• 引入 Elasticsearch 等搜索引擎处理复杂文本匹配。

---

七、场景 6：OR 条件中部分字段无索引

❌ 示例：

-- name 有索引，status 无索引
SELECT * FROM users WHERE name = 'Tom' OR status = 'active';

🔍 原因：

MySQL 难以对 OR 的两个分支分别高效处理，可能直接放弃索引。

✅ 正确写法：改用 UNION

SELECT * FROM users WHERE name = 'Tom'
UNION
SELECT * FROM users WHERE status = 'active';

✅ 每个子查询独立使用索引，效率更高（注意 UNION 会去重，如需保留重复用 UNION ALL）。

---

八、场景 7：数据分布极端倾斜（低区分度）

示例：

-- gender 只有 'M'/'F'，各占 50%
SELECT * FROM users WHERE gender = 'M';

🔍 原因：

即使 gender 有索引，但因为选择性太低（匹配一半数据），回表成本过高，优化器主动选择全表扫描。

✅ 如何验证？

EXPLAIN SELECT * FROM users WHERE gender = 'M';
-- 若 key 为 NULL，说明优化器放弃了索引

💡 建议：

• 对低区分度字段（状态、性别、布尔值）慎建单独索引；
• 可考虑将其放在联合索引的右侧，配合高区分度字段使用。

---

九、场景 8：人为强制忽略索引（少见但存在）

SELECT * FROM users IGNORE INDEX(idx_name) WHERE name = 'Tom';

这种写法会显式禁用指定索引，通常用于调试，生产环境应避免。

---

十、如何快速诊断索引是否生效？

使用 EXPLAIN 关注关键字段：

字段	说明
type	const > ref > range > index > ALL（越靠右越差）
key	实际使用的索引名，NULL 表示未使用
rows	预估扫描行数，越小越好
Extra	出现 Using index 表示覆盖索引；Using filesort 表示额外排序

---

十一、总结：索引“失效”全景图

场景	是否真无法用索引	优化建议
函数/表达式	✅ 是	改写为范围查询
隐式类型转换	✅ 是	保证类型一致
违反最左前缀	✅ 是	按索引顺序使用字段
!= / NOT IN	⚠️ 优化器放弃	改用正向条件
LIKE '%xxx'	✅ 是	避免前导通配符
OR 含无索引字段	✅ 是	改用 UNION
低区分度字段	⚠️ 优化器放弃	谨慎建索引
人为忽略	✅ 是	不要乱用 IGNORE INDEX

---

结语

索引是数据库性能的基石，但不是加了就万事大吉。
理解优化器的行为逻辑，写出“索引友好”的 SQL，才是高性能系统的真正保障。

记住：最好的索引，是让优化器愿意用的那个。