MySQL查询优化器索引选择异常：同结构库查询性能差异

• 统计信息失效：MySQL优化器完全依赖表的统计数据（比如行数、索引基数、字段值分布）计算执行成本。部分库的数据分布和其他库差异极大（比如table_a的table_b_id重复率极高，或是table_b行数大幅变化），但统计信息未及时更新，导致优化器误判——错误认为走ix_table_a_table_b_id索引+全扫table_b的成本更低，而非先取table_a前100行再关联table_b主键。
• 数据分布特殊：部分库中table_a前100行对应的table_b_id在table_b里大量不存在，优化器估算先取table_a前100行可能会过滤掉大部分无效行，最终拿不到100条结果，因此选择先全扫table_b再关联，反而弄巧成拙。
• 索引基数估算偏差：ix_table_a_table_b_id索引的基数（唯一值数量）被高估，优化器误以为用这个索引能快速缩小关联范围，实际却因数据分布问题触发全表扫描+临时表排序。

显式指定索引属于硬编码方案，会让优化器失去自适应调整能力，后续数据变化时可能反而引发性能问题，更优方案如下：

• 更新表统计信息：执行ANALYZE TABLE table_a, table_b;，让优化器获取最新的表数据分布，重新计算合理的执行计划。这是最优先、成本最低的操作。
• 调整查询写法引导优化器：用子查询明确先取table_a前100行的优先级，避免优化器选错执行顺序：

SELECT *
FROM (SELECT * FROM table_a ORDER BY id LIMIT 100) AS a
JOIN table_b ON table_b.id = a.table_b_id
ORDER BY a.id

这种写法会强制优化器先执行子查询拿到table_a的前100行，再去关联table_b的主键，和显式指定索引效果一致，但保留了优化器的自适应空间。

• 优化索引或表结构：如果业务允许，给table_a的table_b_id添加外键约束，让优化器明确该字段关联table_b的主键，更倾向于选择主键关联的执行计划；另外检查ix_table_a_table_b_id索引是否必要，如果该索引仅服务于这类查询且数据分布不佳，可以考虑删除，避免优化器误选。
• 谨慎调整优化器参数：比如调整optimizer_switch中的相关参数（如关闭join_buffer_use_hashed_keys），但这类操作影响全局，需在测试环境验证后再应用到生产，优先推荐前三种方案。

内容的提问来源于stack exchange，提问作者Beowulfenator