MySQL的查询优化器是决定SQL执行效率的第一道关口。当一条SQL被解析成语法树之后，优化器会基于统计信息和成本模型，从众多可能的执行计划中挑选出它认为成本最低的一种。然而优化器的判断并不总是正确的，特别是在统计信息过期、数据分布倾斜或查询条件复杂的场景下，它可能选择错误的索引或错误的连接顺序，导致查询性能下降数倍甚至数十倍。理解并合理调整优化器参数，是数据库调优工作中非常实用的一项技能。
一、认识optimizer_switch：优化器的开关面板
optimizer_switch是MySQL中最重要的优化器参数，它以flag=value逗号分隔的形式存在，控制着几十项优化行为的开启与关闭。查看当前配置非常简单：
SELECT @@optimizer_switch\G -- 输出类似： -- index_merge=on,index_merge_union=on,engine_condition_pushdown=on, -- block_nested_loop=on,batched_key_access=off,materialization=on, -- subquery_materialization_cost_based=on,use_index_extensions=on, -- prefer_ordering_index=on ...
其中几个常见的开关值得重点关注。index_merge控制索引合并优化，当查询的WHERE条件涉及多个单列索引时，优化器可以将多个索引的结果取交集或并集，减少全表扫描的概率。但在某些数据分布下，索引合并反而比走单个索引更慢，此时可以在会话级别临时关闭它来验证：
SET SESSION optimizer_switch='index_merge=off,index_merge_union=off'; -- 执行原查询，对比执行耗时 EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 OR status = 3;
另一个容易踩坑的开关是prefer_ordering_index。当查询带有ORDER BY且LIMIT数量较小时，优化器倾向于直接按有序索引扫描来避免排序，但如果索引对应的范围条件过滤性很差，就会扫描大量无效行。在MySQL 8.0中该开关默认为on，遇到此类问题时可以显式关闭它：
SET SESSION optimizer_switch='prefer_ordering_index=off'; EXPLAIN SELECT * FROM orders ORDER BY create_time LIMIT 10;
需要注意的是，optimizer_switch的修改支持global和session两个级别。session级别只影响当前连接，适合做验证实验；确认有效后再考虑写入配置文件my.cnf的[mysqld]段，避免重启后失效。
二、用optimizer_trace透视执行计划的生成过程
调整参数之前，首先要搞清楚优化器为什么选择了某个执行计划。optimizer_trace是MySQL提供的诊断工具，能够完整记录优化器在生成执行计划时的思考过程，包括每个候选方案的估算成本、索引的选择原因、连接顺序的推导等。开启方式如下：
SET SESSION optimizer_trace='enabled=on,one_line=off'; SET SESSION optimizer_trace_max_mem_size=1000000; -- 执行需要分析的查询（EXPLAIN本身也可以） SELECT * FROM orders WHERE customer_id = 1001 AND status = 3; -- 查看跟踪结果 SELECT * FROM information_schema.OPTIMIZER_TRACE\G
跟踪结果是一个JSON结构，重点看几个字段。rows_estimated表示各表估算的扫描行数，如果与实际行数偏差很大，说明统计信息可能存在问题；chosen为false的候选执行计划会附带cause字段，说明被淘汰的原因，例如cost比其他方案高。range_analysis部分则详细记录了每个可用索引的范围估算，能直观看到为什么某个索引被放弃。
举个例子，如果trace显示某个索引的估算行数为10万，而实际只有几百行，那么问题根源在于统计信息不准，应该先执行ANALYZE TABLE重新采样，而不是盲目调参数：
ANALYZE TABLE orders; -- 之后再次查看执行计划，确认是否走上了正确的索引
对于JOIN查询，trace中的considered_execution_plans字段会列出所有评估过的连接顺序及其成本，这是分析多表连接性能问题最有价值的信息。排查大查询时，建议养成先开trace再分析的习惯，避免凭猜测调参。
三、调整成本常数与强制干预执行计划
除了开关，优化器的成本模型本身也可以微调。MySQL在mysql系统库中提供了两张成本表：server_cost和engine_cost。前者存储服务器层操作的成本常数，例如io_block_read_cost表示从磁盘读取一个数据块的成本，memory_temptable_create_cost表示创建内存临时表的成本：
-- 查看当前成本常数 SELECT * FROM mysql.server_cost; SELECT * FROM mysql.engine_cost; -- 例如提高磁盘IO成本，让优化器更倾向减少IO的执行计划 UPDATE mysql.server_cost SET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost'; FLUSH OPTIMIZER_COSTS;
修改成本常数属于全局性操作，会影响所有查询的决策，因此必须谨慎。典型应用场景是服务器使用的是SSD或NVMe磁盘，默认的IO成本估算偏保守，适当降低io_block_read_cost可以让优化器更愿意选择大范围索引扫描而非随机点查。
当参数调整仍无法解决执行计划走偏的问题时，还可以用SQL提示强制干预。FORCE INDEX强制使用指定索引，IGNORE_INDEX让优化器忽略某个索引，JOIN ORDER则固定连接顺序：
-- 强制走customer_id索引 SELECT * FROM orders FORCE INDEX(idx_customer) WHERE customer_id = 1001 AND status = 3; -- 使用优化器提示语法（MySQL 5.7及以上） SELECT /*+ INDEX(orders idx_customer) */ * FROM orders WHERE customer_id = 1001 AND status = 3;
强制干预是双刃剑：它能在数据分布变化时稳定执行计划，但也会让查询失去自适应能力。如果之后数据量增长，原本合适的索引可能变得低效，因此使用FORCE INDEX的SQL要定期复核。更稳妥的做法是通过慢查询监控加optimizer_trace分析，找到统计信息或参数层面的根本原因，从源头解决问题。
四、参数调优的实践流程与注意事项
综合以上内容，可以总结出一套可落地的调优流程。第一步通过慢查询日志或performance_schema定位问题SQL；第二步用EXPLAIN查看当前执行计划，判断是否存在全表扫描、临时表、文件排序等异常；第三步开启optimizer_trace分析优化器的决策依据，确认是统计信息问题还是成本估算偏差；第四步在session级别小范围调整optimizer_switch或执行ANALYZE TABLE，验证效果；最后才考虑修改成本常数或使用索引提示，并将稳定的配置固化到全局。
还有几个注意事项需要牢记。首先，所有参数调整都要在业务低峰期通过会话级别验证，避免直接修改global配置影响线上其他查询。其次，MySQL版本升级后优化器行为可能变化，例如MySQL 8.0引入了hash join，一些原来依赖BNL的查询计划会发生改变，升级前应充分回归测试。最后，优化器参数只是手段，合理的索引设计和清晰的数据分布才是查询性能的根本保障，参数调优不能替代索引优化。
掌握查询优化器的调整方法，本质上是理解MySQL如何思考执行计划。当你能够读懂optimizer_trace的输出，能够判断一个执行计划为什么成本高，调优就不再是碰运气，而是有理有据的工程实践。