MySQL / 技术文章
MySQL 优化器为什么会选错索引
明明建了看起来更合适的索引,EXPLAIN 却显示 MySQL 走了另一个索引,甚至产生远超预期的扫描,这类问题通常不是优化器“看不见索引”,而是它根据当时掌握的统计信息和成本估算,判断另一条执行路径更便宜。对于已经能阅读 EXPLAIN 的后端工程师与 DBA,关键是把“选错索引”继续拆成估算偏差、成本比较偏差或目标函数与人的直觉不同;完成这一步后,就能用证据判断应该刷新统计信息、调整索引,还是暂时干预执行计划。
先别问“为什么不用这个索引”
看到慢 SQL 时,一个很自然的判断是:WHERE 里明明出现了某个字段,也已经为它建立索引,优化器为什么不用?问题在于,优化器选择的不是“最符合 WHERE 条件的索引”,而是一条预计总成本更低的执行路径。
例如有这样两个联合索引:
CREATE INDEX idx_tenant_status
ON orders (tenant_id, status);
CREATE INDEX idx_tenant_created
ON orders (tenant_id, created_at DESC);查询同时包含过滤、排序和 LIMIT:
SELECT id, status, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'PAID'
AND created_at >= '2026-08-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;从过滤角度看,idx_tenant_status 很诱人,因为前两个条件都能参与索引定位;但它不能直接提供 created_at DESC 的结果顺序,后续可能还需要排序。idx_tenant_created 对 status 的约束较弱,却能够按时间顺序扫描,并且理论上找到 50 行后就提前结束。
所以仅仅看到 key=idx_tenant_created,还不能证明优化器选错了。真正的问题应该改成:优化器估计每条候选路径要读取多少记录、过滤掉多少记录,还要付出多少回表和排序成本,它为什么认为当前路径更便宜?
优化器比较的是预计成本,不是索引名字
MySQL 使用基于成本的优化方式。对同一个查询块,能够参与竞争的访问路径可能包括全表扫描、某个索引的 ref 或 range 访问、覆盖索引扫描,以及不同 JOIN 顺序下产生的访问方式。优化器会给候选方案估计一个成本,然后选择它认为成本最低的组合。
其中最容易被忽视的是,“扫描记录少”并不是唯一目标。一次索引访问可能还伴随大量聚簇索引回表;另一条路径虽然读取的索引记录更多,却可能避免排序、减少随机访问,或者利用 LIMIT 很早停止。因此两个索引不能只比较 Cardinality,也不能只看 EXPLAIN 中哪个 rows 更小。
可以把一次索引选择粗略理解成下面这个问题:预计需要读取多少索引记录,多少记录能够通过剩余条件,得到完整行需要付出多少额外访问,之后是否还要排序、物化或继续驱动下一层 JOIN。任何一个输入估错,都可能让最终成本排序发生变化。
这也解释了一个常见现象:你通过 FORCE INDEX 得到一个实际快得多的计划,并不意味着优化器的成本模型“随机失败”了。更常见的情况是,优化器在优化阶段得到的输入和查询真正执行时发生的工作量不一致。
最常见的根因是行数估错
索引选择错误往往首先表现为基数与选择性估算偏差。优化器不知道查询执行之后究竟会得到多少行,只能依赖表、索引和列分布的统计信息进行预测。
最直接的情况是统计信息已经无法代表当前数据。表经过大量写入、删除或数据分布迁移后,优化器仍可能根据已有统计信息估算某个索引能过滤掉大量记录,而真实情况已经不同。
更隐蔽的是数据倾斜。假设 status 大多数记录都是 PAID,而少数租户中的比例又明显不同,那么“全表平均选择性”就未必能代表 tenant_id = 42 AND status = 'PAID' 这一具体组合。单列统计能够描述某一列的大体分布,却很难完整表达两个业务字段之间的相关关系。
这类误差会沿着计划继续放大。某个访问路径预计返回 100 行,实际却返回 10000 行,不仅意味着本层扫描增加;如果这些记录继续作为 Nested Loop 的外层输入,下一张表的索引探测次数也可能随之扩大两个数量级。
因此遇到疑似错误索引时,最值得比较的并不是“哪个索引看起来更合理”,而是估算行数和真实行数从哪一个算子开始明显分叉。
排序、LIMIT 与回表最容易制造错觉
前面的 orders 查询正好体现了一种容易误判的场景。idx_tenant_created 能按时间倒序读取,如果最近几十条订单中大部分都是 PAID,扫描少量索引记录就能得到 50 行结果,避免额外排序,它可能确实比 idx_tenant_status 更便宜。
但假设某个租户最近产生了大量其他状态的订单,而 PAID 记录非常稀疏,情况就反过来了。优化器可能预计扫描 80 条记录就能找到 50 条 PAID,实际却需要沿着 idx_tenant_created 扫描数万条记录。此时“利用索引顺序并提前结束”的成本优势只存在于估算中。
回表也会造成类似问题。二级索引先找到大量候选主键,再到聚簇索引读取完整记录,如果最终只有少数记录满足剩余条件,那么 EXPLAIN 中看似合理的索引范围并不等于低成本访问。尤其当查询读取的列无法被索引覆盖时,候选记录数量与实际回表次数是判断计划质量的重要证据。
因此,“选择性最高的索引应该优先”并不是可靠规则。查询同时包含排序、LIMIT、非覆盖读取和多表 JOIN 时,真正比较的是这些行为组合后的总成本。
用三层证据追到错误发生的位置
第一层看 EXPLAIN,确认优化器最后选择了什么:访问类型是什么、使用哪个 key、预计读取多少 rows,以及排序、临时表等行为是否出现。这一层回答的是“它准备怎么执行”,还不能证明估算是否正确。
EXPLAIN FORMAT=TREE
SELECT id, status, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'PAID'
AND created_at >= '2026-08-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;第二层使用 EXPLAIN ANALYZE,把估算和真实执行放在同一棵算子树上比较。重点不是只看总耗时,而是寻找最早出现明显偏差的位置。如果某一步预计得到几十行,实际却处理数万行,后续所有成本判断都可能建立在错误输入上。
EXPLAIN ANALYZE
SELECT id, status, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'PAID'
AND created_at >= '2026-08-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;第三层再看 optimizer_trace。它的价值不是重复告诉你最终用了哪个索引,而是展示优化阶段比较过哪些候选访问路径、为它们估计了多少记录和成本,以及某条路径为什么胜出。
SET optimizer_trace = 'enabled=on';
SELECT id, status, created_at, amount
FROM orders
WHERE tenant_id = 42
AND status = 'PAID'
AND created_at >= '2026-08-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;
SELECT TRACE
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';阅读 trace 时,可以重点搜索 range_analysis、considered_access_paths、rows、cost 和 chosen 等节点。假如索引 A 的真实执行明显更快,而 trace 中索引 A 的预计成本却高于索引 B,问题就已经从“为什么 MySQL 不听我的”缩小成了一个可以继续验证的成本差异。
修复时先校正信息,再强行指定路径
确认优化器确实因为估算偏差选择了较差计划后,优先处理它获得的信息,而不是立刻把 FORCE INDEX 写进业务 SQL。对于明显经历过数据变化的表,可以先检查并刷新索引统计信息,再重新比较计划。
ANALYZE TABLE orders;如果问题来自明显的数据倾斜,还需要检查相关列的分布是否能够被优化器正确表达;适合使用直方图的场景,可以在验证收益后补充列分布信息。若长期查询模式同时要求固定的等值过滤、范围访问与排序,更根本的方案通常是重新评估联合索引列顺序,让一个访问路径直接表达查询真正需要的过滤与顺序,而不是依赖优化器在两个各有缺陷的索引之间取舍。
索引或优化器提示更适合作为诊断工具和受控的临时止损手段。通过提示强制索引后,如果 EXPLAIN ANALYZE 证明真实扫描量和耗时显著下降,这是一条很有价值的反证;但提示本身并没有修复导致错误估算的数据分布或统计信息,数据继续变化之后,被固定的计划同样可能变成新的坏计划。
判断一次“选错索引”是否真正解决,可以用一个简单闭环:先证明默认计划在真实执行上较差,再找到优化阶段哪项行数或成本估算导致它胜出,随后修正统计信息、查询结构或索引设计,最后确认不加提示时优化器也能稳定得到合理计划。做到这一步,索引选择就不再是依靠经验猜测,而是能够从执行事实反推优化器决策。