MySQL为什么有时候会选错索引

在MySQL查询优化中，索引选择是非常关键的一环。有时即使创建了合适的索引，MySQL也可能选择次优的执行计划。本文将详细介绍MySQL选择错误索引的常见原因及其解决方案。

版本说明

本文写于 2022-03，基于 MySQL 8.0，2026-08 复核订正。
本次订正了「条件书写顺序影响索引使用」这一常见误解，并补充了 optimizer_trace / EXPLAIN ANALYZE 的验证口径。

可能原因

1. 统计信息不准确

MySQL优化器依赖统计信息来评估不同执行计划的成本。统计信息不准确会导致优化器做出错误的选择。

示例： 假设有一个用户表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    status TINYINT,
    created_at DATETIME,
    INDEX idx_status_created (status, created_at)
);

如果表中数据发生大量变化，而统计信息未更新，优化器可能会错误估计 status 字段的选择性，导致在查询时选择了次优索引。

怎么量化"选择性"——区分度计算公式：

字段的区分度指该列中不同值的数量占总行数的比例，是优化器判断"走这个索引能过滤掉多少行"的核心依据：

SELECT COUNT(DISTINCT status) / COUNT(*) AS distinct_ratio
FROM users;

区分度越接近 1，说明该列的值越"独特"，索引过滤效果越好（比如主键区分度恒为 1）；越接近 0，说明该列的值高度重复（比如上面例子里 90% 都是 pending 的 status 字段，区分度可能只有 0.001），优化器大概率会判定"走索引不如全表扫描划算"而放弃该索引——这正是"明明建了索引，MySQL 却选错/不用"的最常见根因之一。建复合索引时，把区分度高的列放在前面，能让索引在联合查询中更早地过滤掉大部分数据。

2. 数据分布不均匀

当索引列的数据分布极度不均匀时，MySQL的成本估算可能出现偏差。

示例：

SELECT * FROM orders 
WHERE status = 'pending' 
  AND created_at > '2024-01-01';

如果 status 字段中 'pending' 占比非常大（比如90%），而优化器统计信息没有准确反映这一点，可能会错误地选择 status 索引而不是时间索引。

3. 范围条件导致的优化器误判

当查询同时包含等值和范围条件时，优化器可能会做出次优选择。

示例：

SELECT * FROM products 
WHERE category_id = 1 
  AND price BETWEEN 100 AND 200;

即使同时建立了 (category_id, price) 和 (price, category_id) 两个索引，优化器可能会因为范围条件的估算误差而选择次优索引。

4. 最左前缀被范围条件截断

复合索引的列顺序很重要，但要说清楚重要在哪里——这里有一个流传很广的错误说法需要先澄清。

一个常见误解

「WHERE b=2 AND c=3 AND a=1 因为把 a 写在最后，所以用不上 idx_abc(a,b,c)」——这是错的。优化器在生成执行计划前会自行重排等值条件，两种写法的执行计划完全相同。用 EXPLAIN 对比一下就能验证。

真正会让复合索引「只用上一半」的是范围条件的截断效应：索引在遇到第一个范围条件后，后续列就只能用于回表前的过滤，不能再参与索引定位。

-- 索引 idx_abc (a, b, c)

-- ✅ 三列全部用于索引定位，key_len 覆盖 a+b+c
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- ⚠️ b 是范围条件，c 无法参与定位，key_len 只覆盖 a+b
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;

怎么确认：看 EXPLAIN 的 key_len——它等于实际参与定位的列长度之和。上面第二个查询的 key_len 会明显小于第一个，这就是截断发生的证据。

推论：把范围条件的列放在复合索引的末尾，等值条件的列放前面。这条规则和「区分度高的放前面」偶尔会冲突，冲突时以实际的 EXPLAIN 结果为准，别照搬口诀。

解决方法

1. 更新统计信息

定期执行 ANALYZE TABLE 保持统计信息准确：

ANALYZE TABLE table_name;

对于 InnoDB 表，也可以调整统计信息采样页面数（默认 20）：

SET GLOBAL innodb_stats_persistent_sample_pages = 64;

代价要先算清楚：这是全局变量，调大之后每一次 ANALYZE TABLE（以及后台自动统计）都要多读那么多页。大表上从 20 调到几千，单次 ANALYZE 可能从秒级变成分钟级并伴随明显 IO。正确的做法是只给分布严重倾斜的那几张表单独调：

ALTER TABLE orders STATS_SAMPLE_PAGES = 200;

2. 使用强制索引（谨慎使用）

当确定优化器选择了次优索引时，可以强制使用特定索引：

SELECT * FROM users FORCE INDEX (idx_status_created)
WHERE status = 1 AND created_at > '2024-01-01';

注意：这应该是临时解决方案，长期应该找出优化器选错索引的根本原因。

3. 优化索引设计

根据查询模式调整索引设计：

1. 考虑列的选择性
2. 考虑查询条件的顺序
3. 避免冗余索引

示例：

-- 如果经常按状态+时间查询，但状态的选择性很低
-- 可以调整索引顺序
ALTER TABLE orders DROP INDEX idx_status_created;
CREATE INDEX idx_created_status ON orders (created_at, status);

4. 使用EXPLAIN分析执行计划

通过EXPLAIN可以查看优化器选择的执行计划：

EXPLAIN FORMAT=JSON SELECT * FROM users 
WHERE status = 1 AND created_at > '2024-01-01';

特别关注以下信息：

• possible_keys：可能使用的索引
• key：实际使用的索引
• rows：预估扫描行数
• filtered：满足条件的行数百分比

5. 监控和优化

1. 定期检查慢查询日志，识别性能问题
2. 使用性能监控工具（如 Performance Schema）跟踪索引使用情况
3. 在数据量变化较大时及时更新统计信息

最佳实践

1. 在开发环境进行充分的查询测试
2. 定期检查和更新统计信息
3. 对关键查询使用EXPLAIN进行分析
4. 在数据量较大时，考虑使用分区表
5. 保持MySQL版本更新，新版本通常包含优化器改进

通过以上方法的组合使用，可以有效减少MySQL选错索引的情况，提升查询性能。记住，索引优化是一个持续的过程，需要根据实际应用场景和数据特征不断调整。

怎么确认改动真的生效

判断"优化器现在选对了"不能只看查询变快了——那可能是缓存。三条依据：

-- 1. 执行计划里实际选中的索引与预估行数
EXPLAIN FORMAT=JSON SELECT ... ;

-- 2. 优化器的成本推导过程：它为什么放弃了另一个索引
SET optimizer_trace = "enabled=on";
SELECT ... ;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = "enabled=off";

-- 3. 真实的行数与耗时，而不是预估值
EXPLAIN ANALYZE SELECT ... ;

optimizer_trace 里 range_scan_alternatives 一节会直接给出每个候选索引的估算成本和被拒原因，这是判断"统计信息不准"与"确实不划算"的唯一可靠依据。EXPLAIN ANALYZE（8.0.18+）给的是实际执行的行数，和 rows 预估值对比就能量化统计信息的偏差有多大。

坑与边界

• FORCE INDEX 会掩盖问题而不是解决问题。 它把优化器的选择权拿走了，数据分布再变化时它不会跟着调整——上线时最优的强制索引，半年后可能是最差的。加了 FORCE INDEX 就应该同时记一条待办：找到统计信息偏差的根因。
• ANALYZE TABLE 会短暂持有表的元数据锁：它重算统计信息并使数据字典中该表的缓存条目失效，期间新到达的查询要等锁，大表在业务高峰执行可能引起短时抖动，安排在低峰。（注意 MySQL 8.0.3 起已移除查询缓存，8.0 也没有 Oracle 那种共享执行计划缓存——网上「ANALYZE TABLE 会清空查询缓存」的说法只适用于 5.7 及更早。）
• 本文的示例都是构造的。真实排查中先用 optimizer_trace 拿到成本数字再下结论，不要凭这些示例的形状去套自己的表——同样的 SQL 形状在不同的数据分布下，优化器的选择可以完全相反。
• 索引下推（ICP）与 MRR 会改变结论：Using index condition 出现时，被"截断"的列其实仍在存储引擎层做了过滤，代价与纯回表不同。判断性能时要看 EXPLAIN 的 Extra 列，不能只看 key_len。