MySQL 优化器为什么会选错索引？怎么办？

简化版

优化器选索引是基于成本估算，依赖表统计信息、索引基数、数据分布和扫描代价；统计信息不准、数据倾斜、条件相关性强、回表成本估算偏差都可能导致选错索引。处理时先用 Explain 验证，再更新统计信息、调整索引、改写 SQL，必要时才考虑索引提示。

详细版

优化器选错索引的常见原因：

• 统计信息过旧或采样不准；
• 数据分布严重倾斜；
• 多列条件相关性强，估算选择性偏差；
• 低选择性索引导致大量回表；
• limit、排序、范围条件影响成本估算；
• 可选索引过多，存在冗余或干扰。

处理思路：

• 用 EXPLAIN / EXPLAIN ANALYZE 比较估算与实际；
• 执行 ANALYZE TABLE 更新统计信息；
• 设计更贴合 SQL 的联合索引；
• 删除明显冗余索引；
• 改写 SQL 减少优化器误判；
• 最后才使用 FORCE INDEX 等提示，并持续验证。

完整版教学

一、优化器不是按规则死选索引

MySQL 优化器会估算不同执行计划的成本，包括扫描多少行、是否回表、是否排序、连接顺序等。它会选择估算成本最低的方案。

问题在于估算不等于真实。统计信息是抽样和维护出来的，数据分布一变，估算就可能偏。

例如一条 SQL 同时可以走 idx_status 和 idx_create_time。优化器会估算哪个索引扫描行数少、回表少、排序成本低，然后选估算成本更低的。它不是按“索引名字更长”“创建时间更早”这种固定规则选择。只要估算基础有偏差，结果就可能不符合真实最优。

候选计划 A：走 status 索引，估算扫描 1000 行，需排序
候选计划 B：走 create_time 索引，估算扫描 5000 行，无需排序
优化器比较成本后选择其中一个

二、数据倾斜会让估算失真

比如 status 字段有 INIT、PAID、CANCELLED，其中 PAID 占 95%。如果查询 status = 'PAID'，走 status 索引可能要扫描大部分表并大量回表，不如全表扫描。

但如果查询的是很少见的状态，索引又可能非常有效。优化器需要知道分布，统计信息不准时就容易选错。

用数字看：1000 万订单中 PAID 有 950 万，CANCELLED 有 5 万。如果统计信息只知道 status 大概有几个不同值，却不知道严重倾斜，就可能把每个状态估成 250 万行。查询 CANCELLED 时低估或高估都会影响索引选择，查询 PAID 时走索引回表也可能比全表扫描更贵。

数据分布	查询值	索引效果
均匀分布	任意值	估算较稳定
严重倾斜	少数值	索引可能非常有效
严重倾斜	热门值	索引可能不划算
统计过旧	新增热点值	优化器容易误判

易错点：优化器选错常常不是“它不懂索引”，而是它看到的数据分布画像不够准。

三、多列条件要靠联合索引表达相关性

如果 SQL 是：

where tenant_id = ?
  and user_id = ?
  and create_time >= ?

单列索引分别只能表达单个字段的选择性，无法很好表达组合过滤效果。联合索引 (tenant_id, user_id, create_time) 更能描述真实访问路径，也更容易让优化器选对。

多列相关性是一个很实际的问题。比如 province='广东' 和 city='深圳' 不是独立条件，深圳天然属于广东；优化器如果按独立分布粗略相乘，可能估错组合选择性。联合索引不只是让查询能用更多列，也能让访问路径本身更贴合真实数据组织。

四、如何判断是不是选错索引

看两个差异：

• EXPLAIN 估算的 rows 和实际扫描行数差异是否很大；
• 使用不同索引时，实际耗时是否明显不同。

MySQL 8 的 EXPLAIN ANALYZE 能看到实际执行信息，适合验证估算偏差。但在线执行要谨慎，因为它会实际运行查询。

判断时可以做受控对比：同一条 SQL 在测试或影子环境中分别尝试候选索引，记录实际耗时、扫描行数和回表情况。若 Explain 估算 rows=1000，但实际扫描接近 100 万，就说明统计信息或选择性估算有问题。MySQL 8 的 EXPLAIN ANALYZE 很有用，但会执行 SQL，生产上要避免对大更新或高成本查询随意使用。

五、索引提示要慎用

FORCE INDEX 可以强制优化器使用某个索引，看起来很直接，但它把选择权交给了开发者。数据分布变化后，当初强制的索引可能变差。

更优先的做法是：更新统计信息、优化联合索引、改写 SQL、清理冗余索引。索引提示适合短期止血或非常稳定的场景，并且要有监控。

例如某次线上故障中，FORCE INDEX(idx_create_time) 能让当天查询变快，但三个月后数据增长、时间范围扩大，这个强制索引可能变成灾难。索引提示一旦写进代码，就相当于冻结了优化器选择空间，所以更适合短期止血，并且要在后续通过索引设计或 SQL 改写消化掉。

六、处理顺序要稳

遇到优化器选错索引，可以按低风险到高风险处理：先 ANALYZE TABLE 更新统计信息，再检查是否需要直方图或更合适的联合索引，之后清理冗余索引和改写 SQL，最后才考虑 USE INDEX、FORCE INDEX。如果是 JOIN 选错顺序，还要检查各表过滤条件和关联列索引。

处理顺序：
验证计划 -> 更新统计信息 -> 优化联合索引/SQL
        -> 清理干扰索引 -> 短期索引提示 -> 持续监控

七、常见误区与追问

• 误区：优化器选错说明 MySQL 优化器很差。 优化器基于统计信息和成本模型决策，统计过旧、数据倾斜或条件相关性强都会让估算偏离真实。
• 误区：强制索引是最好的长期方案。 强制索引会限制优化器选择，数据分布变化后可能变差，通常只适合短期止血或极稳定查询。
• 误区：单列索引越多，优化器越容易选对。 过多相似索引可能增加选择复杂度并干扰计划，贴合 SQL 的联合索引更重要。
• 误区：Explain 的估算行数就是真实行数。 rows 是估算值，需要用真实执行、慢日志或 EXPLAIN ANALYZE 验证。
• 追问：ANALYZE TABLE 能解决所有选错索引吗？ 不能，它只能改善统计信息，若索引结构不贴合 SQL 或数据高度相关，仍需联合索引或 SQL 改写。
• 追问：数据倾斜为什么影响索引选择？ 热门值命中大量记录时走索引回表可能更贵，冷门值则可能非常适合索引，平均统计容易误导优化器。

八、加强记忆

优化器选索引不是死记规则，而是在候选计划之间做成本估算；估算依赖统计信息、数据分布、索引选择性、回表、排序和连接顺序。选错时先证明估算和实际有差距，再更新统计信息、设计更贴合 SQL 的联合索引、清理冗余索引或改写 SQL。FORCE INDEX 可以救急，但不是长期药方，真正稳定的方案要让优化器在正确的信息和路径下自然选对。