慢 SQL 优化实战：索引失效场景大全

提出问题

面试官问"你线上遇到慢 SQL 怎么排查的"，大多数人会答"看 EXPLAIN，看 type 是不是 ALL"。但追问一句"那你知道哪些写法会导致索引失效"，很多人就卡住了，只能说出"函数、左模糊、隐式转换"这三板斧——就三个，还说不清为什么。

真实情况是：线上一个慢查询，往往是多个索引失效叠加的结果。比如 WHERE DATE(create_time)='2026-01-01' AND status IN (1,2,3) ORDER BY name，函数 + IN + 排序方向不一致，三层问题叠在一起，加一个索引解决不了。所以索引失效不是背 list，是能一眼从 SQL 里看出哪部分走不了索引、为什么、怎么改。

分析问题

函数操作和隐式类型转换：最隐蔽的索引杀手

对索引列使用函数，MySQL 优化器无法直接匹配索引上的有序值，因为索引存的是原始值，不是函数结果。这是索引失效里最难排查的一类，因为 SQL 看起来正常，但 EXPLAIN 出来就是 type=ALL。

-- 错误写法：DATE() 函数导致 create_time 索引失效
SELECT * FROM order WHERE DATE(create_time) = '2026-01-01';

-- 正确写法：范围查询，走索引
SELECT * FROM order 
WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';

隐式类型转换更隐蔽——MySQL 会自动把字符串列转成数字，或者数字列转成字符串，但转换发生在索引列上，索引就废了。

-- 假设 phone 是 VARCHAR(20)，有索引
-- 错误：phone 是字符串，但传入数字，MySQL 隐式转换后索引失效
SELECT * FROM user WHERE phone = 13800138000;

-- 正确：显式字符串比较，走索引
SELECT * FROM user WHERE phone = '13800138000';

验证方法：EXPLAIN 看 type 字段，如果本该是 ref 却变成了 ALL，大概率是类型转换。再用 SHOW WARNINGS 看优化器重写后的 SQL，能直接看到转换语句。

模糊匹配和 OR 条件：索引覆盖范围的边界陷阱

LIKE '%keyword' 左模糊匹配不走索引，因为 B+ 树是按前缀排序的，后缀匹配无法利用有序性。但有一个例外：如果 LIKE '%keyword%' 配合覆盖索引（Extra 显示 Using index），MySQL 5.6+ 的优化器可能选择索引全扫描（type=index）而不是全表扫描，但前提是索引覆盖了所有查询字段。

-- 左模糊 → 不走索引
SELECT * FROM user WHERE name LIKE '%杰';

-- 覆盖索引 + 右模糊 → 走索引且无需回表
SELECT id, name FROM user WHERE name LIKE '杰%';
-- Extra: Using index (覆盖索引生效)

OR 的索引失效更隐蔽：MySQL 优化器对 OR 条件采取保守策略——只要其中一个条件没有索引，就全表扫描。因为优化器需要做索引合并（Index Merge Union），而合并的成本往往比全表扫描大。

-- id 有索引，name 没索引 → OR 导致全表扫描
SELECT * FROM user WHERE id = 1 OR name = '祥哥';

-- 解法一：两个条件都建立索引
ALTER TABLE user ADD INDEX idx_name(name);

-- 解法二：改写为 UNION
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = '祥哥';
-- 注意：UNION 各自走索引，结果去重

联合索引最左前缀和排序方向：复合索引的"用一半"问题

联合索引 (a, b, c) 的生效规则是最左前缀：从最左列开始，连续匹配到第一个范围查询或跳过的列为止。注意：MySQL 8.0 没有改变这个规则，但增加了索引跳跃扫描（Index Skip Scan）作为有限补偿。

-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1 AND c = 3;  -- 只走 a 列，c 走 ICP
SELECT * FROM t WHERE b = 1 AND c = 2;  -- 不走索引
SELECT * FROM t WHERE a = 1 ORDER BY c; -- 走 a 列，但 ORDER BY c 要文件排序

ORDER BY 与索引排序方向不一致也是一个常见陷阱。索引默认是 ASC（升序）存储，如果 ORDER BY a DESC, b ASC，索引无法同时满足两个方向，MySQL 会走文件排序。

-- 索引 (a, b) 默认 ASC
SELECT * FROM t WHERE a = 1 ORDER BY b DESC;  -- 可以走索引，反向扫描
SELECT * FROM t WHERE a = 1 ORDER BY b ASC, a DESC;  -- 文件排序

不走索引但优化器说"全表扫描更快"：数据分布问题

这是最容易被忽略的一类。即使索引有效，优化器也可能选择不走索引——因为回表成本 > 全表扫描成本。

-- 假设 gender 列只有两个值 'M' 和 'F'，各占 50%
-- 优化器判断：走索引回表 50% 的行，还不如全表扫描快
SELECT * FROM user WHERE gender = 'M';
-- 即使有 gender 索引，可能还是 type=ALL

优化器判断依据：rows 预估扫描行数占全表比例。通常超过 20-30% 就会选择全表扫描，因为随机 IO 回表的成本比顺序扫描更大。这就是为什么选择性低的列（如性别、状态码）不适合建索引。

验证方法：EXPLAIN FORMAT=JSON 看 cost_info 中的 read_cost 和 eval_cost，直接比较两种执行计划的代价。

总结

索引失效的排查思路可以归纳为一条线：SQL 写法 → EXPLAIN 看 type → 看 Extra → 看 key_len → 看 rows → 改 SQL 或加索引。

失效场景	典型表现	解法
函数操作	type=ALL, 索引列被函数包裹	改范围查询
隐式转换	type=ALL, key 为空	加引号保持类型一致
左模糊	type=ALL, Like 前有 %	改右模糊或全文索引
OR 条件	type=ALL, 部分条件无索引	全加索引或改 UNION
不满足最左前缀	key_len 比预期短	调整查询顺序
排序方向不一致	Extra=Using filesort	保持 ASC 一致
数据分布不均	type=ALL 但 rows 很大	考虑 Force Index 或改查询

面试话术示例："我线上排查慢 SQL 时，先看 EXPLAIN 的 type 和 Extra，type 不是 ref/range 就说明索引没用对。然后我会用 SHOW WARNINGS 看优化器改写后的 SQL，定位到具体写法问题。最常见的坑是隐式类型转换和被忽略的 OR 条件——这两个在代码 review 里几乎看不出来。"

参考：MySQL 官方文档 - Optimization and Indexes；《高性能 MySQL》第 5 章索引失效分析