MySQL 优化器与 OPTIMIZER_TRACE：把‘为什么选这条计划‘问到水落石出

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL 优化器与 OPTIMIZER_TRACE把为什么选这条计划问到水落石出一、一条 SQL 在优化器里经历了什么二、成本模型优化器的账本2.1 成本 IO 成本 CPU 成本2.2 这些成本常数是**可以改的**2.3 怎么调三、三个升级版 EXPLAIN四、OPTIMIZER_TRACE 上手4.1 开关与相关变量4.2 trace 的骨架五、逐个段落读懂 trace5.1 join_preparation看看 MySQL 把你 SQL 改成了什么样5.2 condition_processing条件是怎么被改写的5.3 ref_optimizer_key_uses哪些索引本来可用5.4 rows_estimation → range_analysis★ 索引选择的决定性战场5.5 considered_execution_plansJOIN 顺序的决策现场5.6 attaching_conditions_to_tables 与 refine_plan六、实战案例为什么有索引却全表扫描6.1 现象6.2 抓 trace 找答案6.3 第二个案例range 被 heuristic 排除七、实在说服不了优化器时的干预手段7.1 optimizer_switch7.2 传统 / 新版 hint八、三个常见误区九、小结MySQL 优化器与 OPTIMIZER_TRACE把为什么选这条计划问到水落石出EXPLAIN只能告诉你结果用了哪个索引、扫了多少行。但它永远不会告诉你为什么—— 为什么不用你建的那个索引、为什么 join 顺序是这样、为什么我们认为很明显的路径它视而不见。要回答为什么唯一的工具是OPTIMIZER_TRACE它把优化器做决策时的每一步、每个估算成本和每次取舍都记下来。本文从成本模型讲到 trace 的每个关键段落最后用两个真实感很强的案例演示怎么读它。一、一条 SQL 在优化器里经历了什么先建立全局视图。一条 SQL 从进来到出结果大致走四步阶段谁负责做什么解析ParserServer 层词法/语法分析生成解析树预处理PreprocessorServer 层语义检查表/列是否存在、权限、展开*优化OptimizerServer 层逻辑重写 成本估算 挑选执行计划执行ExecutorServer 引擎按计划调用引擎接口取数据优化阶段内部又分为两层① 逻辑优化基于规则一定会做变换例子常量传播WHERE b 5 AND a b→a 5等值派生a b AND b c→ 三者组成同一个等价集琐碎条件移除WHERE 1 1被删掉WHERE 0 1直接判定为空结果集外连接化简LEFT JOINWHERE 右表列 IS NOT NULL→ 可化简为 INNER JOIN派生表合并derived_merge把派生表直接并进外层查询② 物理优化基于成本挑最便宜的每张表用什么访问方式全表、ref、range、index scan…多表 JOIN 的顺序是否用索引合并、是否物化子查询、是否用临时表做排序物理优化是穷举 贪心混合搜索表数量少于某个阈值默认受optimizer_search_depth影响时近似穷举超过则用贪心策略。这就是为什么10 张表以上的 JOIN 特别容易出坏计划。二、成本模型优化器的账本2.1 成本 IO 成本 CPU 成本Cost Server Cost Engine Cost CPU Cost IO CostCPU 成本在 Server 层做事的开销比如比较索引键值、比较记录、给结果集排序IO 成本从引擎层读数据的开销MySQL 8.0 能区分数据在内存中和要读磁盘两种成本2.2 这些成本常数是可以改的很多人不知道MySQL 把成本常数放进了两张可写的系统表SELECT*FROMmysql.server_cost;SELECT*FROMmysql.engine_cost;mysql.server_cost的关键项与默认值cost_name默认值含义row_evaluate_cost0.2在 Server 层判断一条记录是否满足条件的成本key_compare_cost0.1比较一次索引键值的成本memory_temptable_create_cost2.0创建一张内存临时表memory_temptable_row_cost0.2往内存临时表写一行disk_temptable_create_cost40.0创建一张落盘临时表disk_temptable_row_cost1.0往磁盘临时表写一行mysql.engine_costcost_name默认值含义io_block_read_cost1.0从磁盘读一个页的成本memory_block_read_cost1.0从缓冲池读一个页的成本读出几个重要结论磁盘临时表的创建成本是内存临时表的 20 倍。所以一旦 GROUP BY / 排序把结果撑爆了tmp_table_size计划成本会突然暴涨一个量级 —— 这就是很多莫名变慢的根因。2.3 怎么调SSD / NVMe 普及之后读一次磁盘的成本远低于默认值 1.0所以全表扫描被高估了很多实例上把io_block_read_cost调低反而更合理UPDATEmysql.engine_costSETcost_value0.4WHEREcost_nameio_block_read_costANDengine_nameInnoDB;FLUSH OPTIMIZER_COSTS;-- 让它生效-- 恢复默认把 cost_value 置回 NULL 即可UPDATEmysql.engine_costSETcost_valueNULLWHEREcost_nameio_block_read_cost;FLUSH OPTIMIZER_COSTS;⚠️不要在没有充分压测的情况下在生产调这些值。它会全局改变所有 SQL 的计划选择救了一片 SQL 的同时很可能打死另一片。三、三个升级版 EXPLAIN在讲 trace 之前先把能被替代掉的简单场景解决掉形式版本价值代价EXPLAIN FORMATJSON5.6给出read_cost/eval_cost/prefix_cost能定量看成本只是估算EXPLAIN FORMATTREE8.0.16树形展示可读性比 JSON 好得多只是估算EXPLAIN ANALYZE8.0.18实际执行给出actual time和真实行数⚠️会真的跑一遍 SQLEXPLAINFORMATTREESELECT*FROMorders oJOINuseruONo.uidu.idWHEREo.status1;-- - Nested loop inner join (cost1204.10 rows980)-- - Index lookup on o using idx_status (status1)-- - Single-row index lookup on u using PRIMARY (ido.uid)EXPLAINANALYZESELECT*FROMordersWHEREstatus1;-- - Index lookup on orders using idx_status (status1)-- (cost430.25 rows1024) (actual time0.041..8.732 rows9811 loops1)EXPLAIN ANALYZE里rows1024vsactual rows9811这个 10 倍落差就是统计信息失真的直接证据那是统计信息与直方图那篇的主题。⚠️EXPLAIN ANALYZE会真正执行这条 SQL。别在生产的 INSERT / UPDATE / DELETE 上直接跑除非你明确知道后果。四、OPTIMIZER_TRACE 上手4.1 开关与相关变量SETSESSIONoptimizer_traceenabledon,end_markers_in_jsonon;SEToptimizer_trace_max_mem_size10485760;-- 默认仅 1MB复杂 SQL 很容易被截断SEToptimizer_trace_offset-10,optimizer_trace_limit10;-- 保留最近 10 条-- 跑你要分析的 SQLSELECT*FROMordersWHEREshop_id88ANDcreated_at2026-01-01;-- 读跟踪结果SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G-- 用完务必关掉SETSESSIONoptimizer_traceenabledoff;变量默认说明optimizer_traceenabledoff,one_lineoff总开关one_lineon存成单行省空间但没法看optimizer_trace_features全开控制跟踪哪些优化贪心搜索、range 优化、动态 range、重复子查询optimizer_trace_limit1最多展示几条optimizer_trace_offset-1偏移量-1表示最新一条配合 limit 很像 LIMIToptimizer_trace_max_mem_size1MB超了会被截断复杂 SQL 必须调大end_markers_in_jsonoff在 JSON 右括号旁加注释方便配对阅读三条注意事项只能会话级看自己的即使SET GLOBAL开启每个 session 也只能查到自己执行的语句有性能开销生产上开全局是灾难务必 session 级用完就关被截断时结果里会出现MISSING_BYTES_BEYOND_MAX_MEM_SIZE调大 max_mem_size 重来4.2 trace 的骨架一份完整的 trace 是steps数组典型包含三段{steps:[{join_preparation:{...}},// 准备SQL 被规范化重写{join_optimization:{...}},// 优化核心绝大多数内容都在这里{join_execution:{...}}// 执行只有真正执行时才有]}EXPLAIN SELECT ...语句只会有前两段因为它不真正执行。五、逐个段落读懂 trace5.1 join_preparation看看 MySQL 把你 SQL 改成了什么样join_preparation:{select#:1,steps:[{expanded_query:/* select#1 */ select orders.id AS id, ... from orders where ...}]}expanded_query就是被展开、补全程限名之后的 SQL。从这里能一眼看出表名/别名是不是写错了、*被展开成哪些列、视图有没有按预期 merge。5.2 condition_processing条件是怎么被改写的condition_processing:{condition:WHERE,original_condition:((orders.shop_id 88) and (orders.status 1)),steps:[{transformation:equality_propagation,resulting_condition:...},{transformation:constant_propagation,resulting_condition:...},{transformation:trivial_condition_removal,resulting_condition:...}]}这三个 transformation 是固定的三步曲。看这里能发现你写的WHERE a b AND a 5到底有没有被优化器推导出b 5。5.3 ref_optimizer_key_uses哪些索引本来可用ref_optimizer_key_uses:[{table:orders,field:shop_id,equals:88,null_rejecting:false},{table:orders,field:status,equals:1,null_rejecting:false}]这一步列出所有可以做等值 ref 访问的索引列。如果你认为某个列应该在这里却不在说明条件写成了不可用的形式函数、隐式转换、类型不匹配。5.4 rows_estimation → range_analysis★ 索引选择的决定性战场这是最重要的段落也是回答为什么不用我的索引的地方rows_estimation:[{table:orders,range_analysis:{table_scan:{rows:2838216,cost:286799// ← 全表扫描的成本},potential_range_indexes:[{index:PRIMARY,usable:false,cause:not_applicable},{index:idx_shop_status_time,usable:true,key_parts:[shop_id,status,created_at]}],skip_scan_range:{potential_skip_scan_indexes:[{index:idx_shop_status_time,usable:false,cause:query_references_nonkey_column}]},analyzing_range_alternatives:{range_scan_alternatives:[{index:idx_shop_status_time,ranges:[88 shop_id 88],index_dives_for_eq_ranges:true,index_only:false,rows:86,cost:50.909,chosen:true}],analyzing_roworder_intersect:{usable:false,cause:too_few_roworder_scans}},chosen_range_access_summary:{range_access_plan:{type:range_scan,index:idx_shop_status_time,rows:86},rows_for_plan:86,cost_for_plan:50.909,chosen:true}}}]读法就一句话把table_scan.cost和chosen_range_access_summary.cost_for_plan对比。全表扫描 286799 索引 50.909 → 走索引反过来若全表扫描更便宜 → 优化器合理地选了全表扫描你的索引确实不该用常见的cause取值cause含义not_applicable这个索引压根不适用于该条件too_few_roworder_scans无法做 row order 交集index mergequery_references_nonkey_column查询引用了索引之外的列无法做 skip scan / 覆盖扫描heuristic_index_cheaper已有更便宜的访问路径这个被启发式排除了not_group_by_or_distinctgroup_index_range 不适用5.5 considered_execution_plansJOIN 顺序的决策现场considered_execution_plans:[{plan_prefix:[],table:orders,best_access_path:{considered_access_paths:[{access_type:ref,index:idx_shop,rows:86,cost:50.412,chosen:true},{access_type:range,range_details:{used_index:idx_shop},chosen:false,cause:heuristic_index_cheaper}]},condition_filtering_pct:100,rows_for_plan:86,cost_for_plan:50.412,chosen:true}]considered_access_paths罗列了优化器比较过的所有路径每条都带cost和chosen。多表 JOIN 时会看到嵌套的plan_prefix那就是驱动表顺序的搜索过程。condition_filtering_pct就是 EXPLAIN 里filtered字段的来源。5.6 attaching_conditions_to_tables 与 refine_planattaching_conditions_to_tables:{attached_conditions_summary:[{table:orders,attached:((orders.status 1))}]},refine_plan:[{table:orders}]这一步确认哪些条件跟着表走可以 push down 到引擎哪些留在 Server 层。如果本该由 ICP 处理的条件没出现在attached里ICP 就不会生效。六、实战案例为什么有索引却全表扫描6.1 现象EXPLAINSELECT*FROMordersWHEREcreated_at2026-09-01;-- type: ALL, key: NULL, rows: 2800000created_at上明明有idx_created_at。6.2 抓 trace 找答案SETSESSIONoptimizer_traceenabledon,end_markers_in_jsonon;SEToptimizer_trace_max_mem_size10485760;SELECT*FROMordersWHEREcreated_at2026-09-01;SELECTQUERY,TRACEFROMinformation_schema.OPTIMIZER_TRACE\GSETSESSIONoptimizer_traceenabledoff;在rows_estimation.range_analysis里会看到类似analyzing_range_alternatives:{range_scan_alternatives:[{index:idx_created_at,ranges:[0x99b201 created_at],rows:950000,// ← 优化器认为要返回 95 万行cost:228050,chosen:true}],...},chosen_range_access_summary:null// ← 最终没选它对照table_scan.costtable_scan:{rows:2838216,cost:286799}结论优化器认为范围要返回 95 万行占全表 33%走索引要 95 万次回表成本 228050 接近全表扫描的 286799再叠加回表的随机 IO 惩罚最终选了全表扫描。这时候正确的动作不是加FORCE INDEX而是三选一现象真正的问题解法实际只返回 5000 行预估 95 万统计信息失真ANALYZE TABLE/ 加直方图实际真的返回 95 万行这是合理选择改业务逻辑加更强的过滤条件或做分页确实要用索引且只取少量列回表太贵做成覆盖索引见回表那篇6.3 第二个案例range 被 heuristic 排除trace 里经常看到{access_type:range,chosen:false,cause:heuristic_index_cheaper}这不是错误。MySQL 有个启发式规则能用ref等值就用ref它通常比等价的range更便宜。看到这个 cause 不用慌说明优化器已经找到了更好的路。真正要警惕的是chosen: false且没有cause或者potential_range_indexes里你期望的索引直接usable: false—— 那多半是条件写法有问题。七、实在说服不了优化器时的干预手段7.1 optimizer_switchSELECToptimizer_switch\GSETSESSIONoptimizer_switchderived_mergeoff;常用的几项开关默认什么时候要动它index_condition_pushdownon排查 ICP 是否生效时临时关掉对比derived_mergeon派生表被过早 merge 导致慢时关掉让它先物化condition_fanout_filteron8.0 的多列级别的选择率估算怀疑估算被它搞坏时关掉验证mrr/mrr_cost_basedon调 MRR 行为做对比实验semijoin/materializationon子查询 SEMI JOIN 转换出问题时逐个关掉排查prefer_ordering_indexon8.0.21ORDER BY ... LIMIT选错索引的头号嫌疑对象skip_scanon8.0.20不走期望索引时检查是不是它捣的乱7.2 传统 / 新版 hintSELECT*FROMordersFORCEINDEX(idx_created_at)WHEREcreated_at2026-09-01;SELECT*FROMordersIGNOREINDEX(idx_created_at)WHEREcreated_at2026-09-01;SELECTSTRAIGHT_JOIN o.*,u.nameFROMorders oJOINuseruONo.uidu.idWHERE...;-- MySQL 8.0 优化器提示写在 /* */ 里SELECT/* NO_ICP(orders) SET_VAR(sort_buffer_size8M) */*FROMordersWHERE...;SELECT/* JOIN_ORDER(orders, user) */*FROMorders oJOINuseruONo.uidu.id;SELECT/* MAX_EXECUTION_TIME(1000) */*FROMordersWHERE...;-- 超时限制毫秒优先级hint optimizer_switch 系统变量 默认。使用原则hint 是止血手段不是优化方案。只要在 SQL 里写了 hint就等于把这个 SQL 的计划锁死了后续数据分布变化、版本升级都不会重新评估。写之前一定在注释里写明原因。八、三个常见误区误区 1trace 里chosen: false就是优化器出错了——绝大部分chosen: false是正常取舍而且旁边几乎都有一个cause字段解释原因。真正的信号是你的 SQL 结果很差但 trace 里没看到对你有利的路径被评估过。误区 2EXPLAIN ANALYZE可以随便在生产跑——它会真的执行这条 SQL包括 INSERT / UPDATE / DELETE。在生产上只用它分析 SELECT写操作请在测试环境用等价数据量复现。误区 3改成本常数能解决选错索引——改mysql.server_cost/engine_cost是全局行为影响的不仅有出问题的那条 SQL还有所有正常 SQL。这是最后手段且在动之前必须先在压测环境全量回归。九、小结MySQL 优化器是CBO决策链路是逻辑重写 → 物理路径成本估算 → 挑最便宜的成本 IO 成本 CPU 成本成本常数写在mysql.server_cost/mysql.engine_cost里可调改完FLUSH OPTIMIZER_COSTS磁盘临时表创建成本是内存临时表的20 倍这是很多莫名变慢的根源EXPLAIN的三件套FORMATJSON5.6 看 cost、FORMATTREE8.0.16 可读性好、ANALYZE8.0.18真实执行给实际行数OPTIMIZER_TRACE5.6 引入记录优化全过程存于information_schema.OPTIMIZER_TRACE务必 session 级开启、用完关闭常见坑optimizer_trace_max_mem_size默认仅1MB复杂 SQL 极易被截断trace 主干三段join_preparationSQL 被重写成什么样、join_optimization重点、join_executionjoin_optimization里最该看的两个地方rows_estimation → range_analysis对比table_scan.cost与索引方案cost_for_plan立刻知道索引该不该用considered_execution_plans看每条路径的cost/chosen/cause理解 JOIN 顺序的选择常见causenot_applicable、heuristic_index_cheaper正常、query_references_nonkey_column、too_few_roworder_scanschosen_range_access_summary为null 全表扫描更便宜 优化器没算错是你的过滤条件太弱干预手段优先级改 SQL / 改索引 修统计信息 optimizer_switch hintFORCE INDEX只是止血8.0.21 起的prefer_ordering_index是ORDER BY ... LIMIT选错索引的头号嫌疑人