大家好我是小耶写功课只是为了我踩过的坑你们别再踩了周二我们讲了Extra列里的8个性能信号——Using temporary、Using filesort、Using index condition……把这些“黑话”都拆了一遍。但还有一个更深的问题没解决如果执行计划看起来不合理怎么知道优化器“为什么”会这么选EXPLAIN能告诉你“选了哪个计划”但说不出“为什么选它”。你看到typeALL知道全表扫描了但不知道为什么——明明有索引优化器为什么不走这时候就需要​Optimizer Trace​。在MySQL 5.6之前优化器就像一个黑盒子——你只能看到最终结果看不到决策过程。Optimizer Trace就是打开这个黑盒子的钥匙。它可以跟踪优化器做出的​每一步决策​——访问方式的选择、成本估算、各种转换规则的应用全部记录下来。从MySQL 5.6开始设计MySQL的大叔贴心地提供了这个功能让我们可以方便地查看优化器生成执行计划的整个过程。一、Optimizer Trace是什么——优化器的“行车记录仪”打个比方EXPLAIN是一张“行车路线图”——告诉你最后走了哪条路。而Optimizer Trace是“行车记录仪”——它录下了司机在路上做的每一个决策为什么没走高速、为什么在这个路口右转、为什么绕了一段路。Optimizer Trace记录的正是优化器从接收SQL到最终选定执行计划的完整过程包括生成了哪些候选执行计划每个计划的成本估算应用了哪些优化规则最终选择了哪个计划以及为什么有了这些信息你不仅能知道优化器“选了什么”还能知道它“为什么这么选”。二、怎么用Optimizer Trace——四步搞定Optimizer Trace默认是关闭的因为开启会产生额外开销。好在它是轻量级工具支持会话级别开启不影响其他会话。步骤一开启Optimizer TraceSET SESSION optimizer_trace enabledon; SET end_markers_in_json ON;end_markers_in_jsonON会在JSON输出的每个阶段添加结束标记方便阅读。步骤二执行需要分析的SQLSELECT * FROM orders WHERE customer_id 12345 AND order_date 2026-01-01;步骤三查询跟踪结果SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\GOPTIMIZER_TRACE表有4个字段字段含义QUERY执行的SQL语句TRACE优化过程的JSON格式文本核心MISSING_BYTES_BEYOND_MAX_MEM_SIZE被截断的字节数INSUFFICIENT_PRIVILEGES是否权限不足步骤四关闭Optimizer TraceSET SESSION optimizer_trace enabledoff;分析完记得关闭避免不必要的性能开销。三、Optimizer Trace输出结构拆解——三个核心阶段Optimizer Trace的输出是一个JSON结构主要包含三个阶段1. join_preparation准备阶段这一阶段做语法解析和预处理将*扩展为具体列、外连接转内连接、视图合并、子查询转换等。2. join_optimization优化阶段——核心这是优化器的核心工作区也是最值得关注的部分。包含以下关键子步骤​condition_processing​WHERE和JOIN条件的优化处理​table_dependencies​分析表之间的依赖关系​ref_optimizer_key_uses​评估ref类型索引的使用可能​rows_estimation​行数估算和成本分析在这个阶段你可以看到优化器为每个可能的索引计算成本对比不同访问路径的代价最终做出选择。3. join_execution执行阶段记录执行器实际执行的信息如排序操作、临时表使用等。四、真实案例用Optimizer Trace定位执行计划跑偏的根因​场景​一张订单表有500万行customer_id列有索引order_date列也有索引。执行以下查询SELECT * FROM orders WHERE customer_id 12345 AND order_date 2026-01-01;正常情况下这个查询应该走(customer_id, order_date)复合索引。但实际执行计划显示typeALL全表扫描。从EXPLAIN看只知道“走了全表扫描”但不知道为什么——明明有索引优化器却不走。用Optimizer Trace查看详情SET SESSION optimizer_trace enabledon; SELECT * FROM orders WHERE customer_id 12345 AND order_date 2026-01-01; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G在rows_estimation部分可以看到优化器评估了每个索引的成本rows_estimation: { table: orders, range_analysis: { table_scan: { rows: 5000000, cost: 1000000 }, potential_range_indices: [ { index: idx_customer_id, usable: true, chosen: false, cost: 450000 }, { index: idx_order_date, usable: true, chosen: false, cost: 420000 } ], chosen_range_access_summary: { range_scan_alternatives: [], chosen: TABLE_SCAN, chosen_cost: 1000000 } } }​关键发现​全表扫描成本约100万idx_customer_id成本约45万但优化器认为回表成本过高idx_order_date成本约42万优化器最终选择了全表扫描因为两个单列索引的回表代价都太高了——customer_id 12345可能返回很多行order_date 2026-01-01也可能返回很多行。​解决方案​创建复合索引(customer_id, order_date)让索引同时覆盖两个条件减少回表。CREATE INDEX idx_customer_date ON orders(customer_id, order_date);再跑一次EXPLAINtyperefkeyidx_customer_date查询从5秒降到0.05秒。五、Optimizer Trace的使用原则1. 不要在生产环境长期开启Optimizer Trace会产生额外的开销虽然影响很小但仍不建议在生产环境长期开启。只在需要诊断疑难查询时临时开启分析完立即关闭。2. 结合EXPLAIN一起用EXPLAIN提供“结论”Optimizer Trace提供“过程”。先用EXPLAIN发现异常再用Optimizer Trace追溯根因。3. 关注成本估算部分rows_estimation是最有价值的部分——它直接展示了优化器是如何计算每个候选计划的代价的。如果优化器选错了计划这里会给出线索。六、总结EXPLAIN告诉你优化器“选了哪个计划”Optimizer Trace告诉你优化器“为什么选它”。当你遇到一个看起来不合理的执行计划时不要直接给优化器贴“笨”的标签。打开Optimizer Trace看看它到底是怎么算的——可能是统计信息过旧、可能是索引设计不够好、可能是某个参数影响了决策。Optimizer Trace把优化器的“思考过程”完整地呈现了出来。读懂它你就能从“执行计划的阅读者”升级为“优化器的理解者”。读懂Optimizer Trace输出的JSON是DBA从“会用EXPLAIN”走向“理解优化器”的必经之路。小耶在手SQL 不愁还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~