Jiayu Lab
← 返回技术博客

MySQL 索引为什么没有被使用：一份排查路径

从查询形态、选择性、类型转换和统计信息逐步定位索引失效。

数据库发布约 8 分钟阅读
MySQLIndexSQLEXPLAIN
本页目录

先保存可以复现的查询

索引问题不能只记录一句“这个 SQL 很慢”。排查前应保存完整 SQL、参数值、表结构、索引定义、MySQL 版本和大致数据量。相同 SQL 使用不同参数时，选择性可能完全不同，执行计划也可能变化。

SHOW CREATE TABLE orders;
SHOW INDEX FROM orders;
SELECT VERSION();

如果问题来自生产环境，还应记录当时的并发、锁等待和缓存状态。单次查询慢不一定是索引问题，也可能是磁盘抖动、锁竞争或连接池拥塞。

从 EXPLAIN 开始而不是猜测

EXPLAIN 可以观察优化器计划使用哪些表、索引和访问方式：

EXPLAIN FORMAT=TREE
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 1001
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

需要重点关注：

  • possible_keys：理论上可以选择的索引。
  • key：实际使用的索引。
  • type：访问类型，如 const、ref、range、index、ALL。
  • rows：优化器估计需要读取的行数。
  • filtered：过滤后预计保留比例。
  • Extra：是否出现临时表、文件排序或索引条件下推。

key 不为空也不代表查询一定高效。一个索引扫描可能读取大量条目，最终只返回少量结果。

EXPLAIN ANALYZE 提供实际执行数据

在支持的 MySQL 版本中，EXPLAIN ANALYZE 会真正执行查询并返回实际行数与耗时。它可以揭示优化器估计和真实数据之间的差距：

EXPLAIN ANALYZE
SELECT ...;

因为语句会执行，不能对有副作用的语句随意使用，也要避免在高峰期直接分析代价很大的查询。对于只读查询，仍应先评估运行风险。

检查隐式类型转换

如果字符串列使用数字条件比较，MySQL 可能对列执行转换，导致索引无法按原始顺序使用：

-- phone 是 VARCHAR，不推荐
WHERE phone = 13800138000

-- 参数类型与列一致
WHERE phone = '13800138000'

关联字段也应保持相同类型、长度和字符集。一个表使用 INT，另一个表使用 VARCHAR，或两个字符串列排序规则不同，都可能增加转换成本并影响索引选择。

避免在索引列上包裹函数

下面的条件直观，但函数作用在列上后，普通索引通常无法直接定位原始值：

WHERE DATE(created_at) = '2026-07-14'

可以改写成范围：

WHERE created_at >= '2026-07-14 00:00:00'
  AND created_at <  '2026-07-15 00:00:00'

如果业务经常按表达式查询，也可以评估生成列或函数索引，但应先确认改写条件是否已经足够。

联合索引遵循最左前缀

假设索引为：

CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

它适合从 user_id 开始的查询条件。只查询 status 或 created_at 时，通常无法完整利用前面的索引结构。

联合索引列顺序应结合实际查询：等值过滤列通常放在前面，范围列放在后面，同时考虑排序和返回数据。不能仅按“选择性最高的列永远放第一”这一条规则机械决定。

范围条件会影响后续列利用

当联合索引中的某列使用范围查询后，后续列可能仍参与索引条件下推，但不一定继续缩小索引扫描范围。例如：

WHERE user_id = ?
  AND created_at >= ?
  AND status = ?

索引列顺序需要结合最常见过滤方式和排序要求测试。应通过执行计划与实际行数验证，而不是只看索引定义是否包含所有字段。

选择性低时全表扫描可能更合理

性别、布尔状态或少量枚举值的选择性通常较低。若条件命中表中大部分记录，使用二级索引意味着先扫描大量索引条目，再回表读取数据，成本可能高于顺序扫描整表。

优化器放弃索引不一定是错误。应比较命中比例、回表次数、数据页分布和最终返回行数，而不是强制要求每条查询都显示某个索引名。

覆盖索引可以减少回表

InnoDB 二级索引叶子节点保存主键值。查询二级索引未包含的列时，需要根据主键再次访问聚簇索引。若查询只返回少量固定列，可以设计覆盖索引：

CREATE INDEX idx_user_status_created_total
ON orders(user_id, status, created_at, total_amount);

覆盖索引可能减少回表，但会增加索引体积、写入成本和缓存压力。不能为了覆盖 SELECT * 把大量列全部加入索引。

ORDER BY 与 LIMIT 也影响索引设计

很多列表查询既有过滤又有排序。合适的联合索引可以在定位记录的同时按索引顺序返回，避免额外文件排序。

但排序方向、范围条件和多列排序都会影响是否能利用索引顺序。看到 Using filesort 时，应结合实际扫描行数和 LIMIT 判断成本，而不是把它当成必须消除的错误。

LIKE 只有部分形式能利用前缀

以下查询通常可以利用普通 B-Tree 索引的前缀范围：

WHERE title LIKE 'STM32%'

前导通配符会失去已知起点：

WHERE title LIKE '%STM32%'

大量正文检索更适合全文索引或专用搜索方案。用普通索引解决任意子串搜索，通常达不到预期。

OR 条件需要逐分支分析

OR 两侧若分别有可用索引，优化器可能使用 Index Merge，也可能认为合并成本过高。可以分别执行每个分支的 EXPLAIN，确认哪个条件扩大了扫描范围。

有时把逻辑拆成 UNION ALL 更容易使用不同索引，但必须处理重复结果和语义差异，不能把它当成固定优化模板。

JOIN 要检查驱动表和关联键

连接查询中，应观察表访问顺序、驱动表过滤后行数以及被驱动表关联键是否有索引。关联字段类型必须一致，且过滤条件应尽早减少驱动行数。

如果执行计划估计行数与实际差距很大，优化器可能选择错误的连接顺序。此时应先检查统计信息和数据分布，再考虑改写查询。

统计信息可能已经过期

大量导入、删除或数据分布变化后，优化器保存的基数估计可能偏离真实情况。可以使用：

ANALYZE TABLE orders;

更新统计后重新查看计划。如果列值分布非常不均匀，还可以评估直方图，但要记录创建和维护方式。统计信息问题不应通过长期强制索引来掩盖。

FORCE INDEX 只适合受控场景

强制索引可能暂时绕过错误计划，但数据量和分布变化后，固定选择未必仍然合理。使用前应证明优化器为何判断错误，并保留不同参数下的对照结果。

更稳妥的顺序是：修正类型和 SQL 写法、设计合适索引、更新统计信息、比较实际执行数据，最后才考虑提示优化器。

索引会增加写入成本

每个额外索引都会占用磁盘与缓冲池，并增加 INSERT、UPDATE、DELETE 的维护成本。重复索引和前缀被完全覆盖的冗余索引应定期检查。

删除索引前需要核对所有查询、外键和线上使用情况。某个索引没有被当前慢 SQL 使用，不代表它对其他业务没有价值。

建立前后对照

一次完整优化应记录：

  1. 原始 SQL、参数和执行计划。
  2. 原始返回行数与实际耗时分布。
  3. 修改的 SQL 或索引。
  4. 修改后的计划、扫描行数和耗时。
  5. 写入性能、索引体积和其他查询的回归检查。

不要只执行一次就宣布优化完成。缓存冷热、并发和参数选择都会影响耗时，应进行多次、可比较的测试。

推荐排查顺序

  1. 确认慢的是执行、锁等待还是连接获取。
  2. 保存 SQL、参数、表结构和索引。
  3. 查看 EXPLAIN，必要时安全使用 EXPLAIN ANALYZE。
  4. 检查类型转换、列函数和查询改写空间。
  5. 检查联合索引顺序、范围条件、排序和覆盖情况。
  6. 对比估计行数与实际行数，必要时更新统计信息。
  7. 建立修改前后对照并回归其他查询。

验收清单

  • SQL、参数和数据分布能够复现。
  • 执行计划中的访问类型、扫描行数和过滤比例已解释。
  • 比较与关联字段类型一致，没有意外隐式转换。
  • 联合索引顺序对应真实过滤和排序方式。
  • 新索引的读取收益与写入、空间成本同时评估。
  • 修改前后使用相同条件进行多次对照。
  • 没有把 FORCE INDEX 当作默认解决方案。