1. 项目背景业务场景某数据平台的一个三表 JOIN 查询——在 MySQL 9.6 上的执行计划突然从原来的 50ms 变成了 15 秒。DBA 排查发现——优化器原来选择orders → users → products的 Join 顺序驱动表是小表 users过滤后只有 100 行但现在选成了products → orders → users驱动表是中型表 products过滤后仍有 5000 行。同一个 SQL、同样的数据——仅仅是服务器重启了一次——执行计划就变了。团队需要回答三个问题优化器到底考虑了哪些 Join 顺序每个候选计划的代价是多少为什么最终选择了这个而不是那个这些答案不在 EXPLAIN 里——在优化器的源码中。痛点不懂优化器源码就无法回答为什么选这个计划候选计划的黑盒EXPLAIN 只告诉你最终计划——不告诉你它放弃了哪些更好的候选方案。代价估算的盲区优化器认为全表扫描比索引扫描代价低——但不告诉你它怎么算出来的。统计信息失真的连锁反应一个表的 rows 估算偏差 10 倍——导致整个 Join Order 的重估和代价误判。本章带你深入sql/sql_optimizer.cc和sql/join_optimizer/——追踪优化器从 Query Block 到 Access Path 到最终计划的全过程。2. 项目设计【场景小胖盯着 EXPLAIN 输出——同一个 SQL 昨天和今天的执行计划不一样】小胖“大师优化器是不是有点随机同一个 SQL 隔一天就不一样——是不是重启的时候随机种子变了”大师“不是随机——是统计信息变了。优化器在选择执行计划时——会枚举所有可能的 Join 顺序——对每个顺序估算代价——选代价最小的。代价估算依赖统计信息CARDINALITY、行数、索引选择性——这些信息在重启后可能会被重新采样——采样结果不同——代价估算就不同——最优计划也就不同。”小白“那 MySQL 8.0 的 Hypergraph Optimizer 和老的优化器有什么区别为什么有时候 Hypergraph 选出来的计划更慢”大师“老优化器是基于贪婪启发式的——一次只决定一个 Join通过optimizer_search_depth限制搜索深度。Hypergraph Optimizer 理论上可以穷举所有可能的 Join Order包括 bushy tree——左右两边都可以是 Join 结果而非必须是单表——所以找到的执行计划理论上更优。但代价估算是同一个——如果统计信息不准——越’优化’反而可能越’糟糕’。”技术映射老优化器 基于启发式的贪婪搜索左深树。Hypergraph Optimizer 基于代价的穷举搜索支持 bushy tree。后者理论更优——但都依赖准确的统计信息。小胖“那统计信息到底是怎么影响代价的我看到row_evaluate_cost默认是 0.1——这个数字从哪来的”大师“代价模型有两套参数——server_costSQL 层和 engine_cost存储引擎层。row_evaluate_cost0.1表示评估一行数据的 CPU 代价是 0.1 个’代价单位’。io_block_read_cost1.0表示从磁盘读一页的 IO 代价是 1.0。全表扫描的代价 io_block_read_cost × 页数 row_evaluate_cost × 行数。索引扫描的代价 io_block_read_cost × 索引页数 row_evaluate_cost × 匹配行数 回表代价。这些系数默认是在普通 HDD 上测试得出的——如果放到 NVMe SSD 上——io_block_read_cost可以调低到 0.25——让优化器更不怕随机 IO。”小白“既然 Hypergraph Optimizer 理论上更好——为什么不是默认的它有什么局限性”大师“Hypergraph Optimizer 的穷举搜索在 5 表以上的 Join 时——组合数量爆炸——优化器本身就需要几毫秒到几百毫秒。这对于 OLTP 的毫秒级查询来说——优化时间比执行时间还长——得不偿失。所以 MySQL 保留了老优化器作为默认——只在optimizer_switchhypergraph_optimizeron时启用。对于复杂的报表查询多表 Join 聚合 子查询——Hypergraph 通常能找到更好的计划——值得花这点优化时间。”技术映射Hypergraph Optimizer 的局限性 穷举搜索时间随表数指数增长。适合复杂报表查询执行时间长——优化时间占比小不适合简单 OLTP 查询。3. 项目实战3.1 环境准备-- 确认 optimizer 版本SELECToptimizer_switchLIKE%hypergraph_optimizeron%ASusing_hypergraph;-- 开启 optimizer traceSEToptimizer_traceenabledon;SEToptimizer_trace_max_mem_size1000000;-- 准备测试数据USEecommerce;-- 复用 orders_large (100 万行) 和 users 表-- 确保统计信息最新ANALYZETABLEorders_large,users;3.2 分步实现步骤一从源码入口追踪优化过程// 文件sql/sql_optimizer.cc// 优化器入口函数boolJOIN::optimize(){// 1. 优化前准备展平子查询、推导条件、常量传播if(optimize_constant_subqueries())returntrue;// 2. 选择访问路径Access Path// 对每个表——评估全表扫描、index scan、index lookup 的代价if(setup_access_paths())returntrue;// 3. 确定 Join Order// 老优化器greedy_search()// HypergraphFindBestQueryPlan()if(determine_join_order())returntrue;// 4. 生成最终执行计划if(make_join_query_block())returntrue;returnfalse;}步骤二追踪 Access Path 选择——扫描方式的代价对比-- 步骤目标用 optimizer trace 看每张表的候选访问路径和代价SEToptimizer_traceenabledon;EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_idu.idWHEREo.status1ANDo.created_atBETWEEN2026-01-01AND2026-06-30;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 在 trace 中搜索 considered_access_paths-- {-- considered_access_paths: [-- {-- access_type: range,-- index: idx_status_created,-- usable: true,-- chosen: true,-- cost: 1234.56,-- rows: 50000-- },-- {-- access_type: scan,-- chosen: false,-- cause: cost,-- cost: 25000.00,-- rows: 1000000-- }-- ]-- }-- 解读-- orders_large 有两个候选方案——-- 方案 Arange scan on idx_status_created代价 1234选择 ✓-- 方案 B全表扫描代价 25000放弃 ✗-- 优化器选择了代价更低的 range scanSEToptimizer_traceenabledoff;步骤三追踪 Join Order 搜索——greedy_search 和 Hypergraph 的差异-- 步骤目标对比老优化器和 Hypergraph Optimizer 的 Join Order 选择-- 使用老优化器 SEToptimizer_switchhypergraph_optimizeroff;SEToptimizer_traceenabledon;EXPLAINSELECTu.name,o.amount,p.nameFROMorders_large oJOINusers uONo.user_idu.idJOINproducts_small pONo.idp.idWHEREo.status1LIMIT100;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 greedy_search-- 老优化器从 cost 最小的表开始——每次选择与当前部分计划 JOIN 后代价最小的下一个表-- 最终得到一个左深树orders → users → productsSEToptimizer_traceenabledoff;-- 使用 Hypergraph Optimizer SEToptimizer_switchhypergraph_optimizeron;SEToptimizer_traceenabledon;EXPLAINSELECTu.name,o.amount,p.nameFROMorders_large oJOINusers uONo.user_idu.idJOINproducts_small pONo.idp.idWHEREo.status1LIMIT100;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 FindBestQueryPlan-- Hypergraph 穷举所有可能的 Join 组合——-- 包括左深树(o ⋈ u) ⋈ p 或者 (u ⋈ o) ⋈ p-- 也包括 bushy treeo ⋈ (u ⋈ p)-- 对每种组合计算代价——选最低的SEToptimizer_traceenabledoff;步骤四优化器关键数据结构——AccessPath 和 JoinHypergraph// 文件sql/join_optimizer/access_path.h// AccessPath —— 表示一种数据访问方式structAccessPath{enumType{TABLE_SCAN,// 全表扫描INDEX_SCAN,// 索引扫描INDEX_RANGE_SCAN,// 索引范围扫描REF,// 等值 ref 查找EQ_REF,// 唯一等值查找HASH_JOIN,// Hash JoinNESTED_LOOP_JOIN,// Nested Loop JoinFILTER,// 过滤SORT,// 排序LIMIT_OFFSET,// LIMITAGGREGATE,// 聚合MATERIALIZE,// 物化// ... 更多类型}type;doublecost;// 预估代价doublenum_output_rows;// 预估输出行数union{struct{TABLE*table;}table_scan;struct{TABLE*table;KEY*index;}index_scan;struct{AccessPath*outer;AccessPath*inner;}nested_loop_join;struct{AccessPath*outer;AccessPath*inner;}hash_join;// ...};};// 文件sql/join_optimizer/make_join_hypergraph.h// JoinHypergraph —— 表示所有表的 Join 关系图structJoinHypergraph{structNode{TABLE*table;doublenum_output_rows;doublecost;};structEdge{Node*left;Node*right;Item*join_condition;// JOIN 条件};std::vectorNodenodes;std::vectorEdgeedges;// 优化器在 nodes 上应用各种 Join 组合——找最优连接顺序};步骤五跟踪一个三表 JOIN 的完整优化过程-- 步骤目标记录优化器从 Access Path 选择到 Join Order 到最终计划的每一步SEToptimizer_traceenabledon;-- 执行一个带有多种访问方式的三表 JOINEXPLAINSELECTu.name,COUNT(o.id)ASorder_count,SUM(o.amount)AStotalFROMusers uJOINorders_large oONo.user_idu.idJOINproducts_small pONo.product_idp.idWHEREo.status1ANDo.created_at2026-01-01GROUPBYu.nameHAVINGCOUNT(o.id)5ORDERBYtotalDESCLIMIT20;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 在 trace 中按以下顺序阅读-- 1. join_preparation → 查询准备阶段常量折叠、子查询展平-- 2. join_optimization → 优化阶段-- a. condition_processing → 条件处理推导新条件-- b. ref_optimizer_key_uses → ref 访问评估-- c. considered_execution_plans → 各表的访问路径评估-- d. greedy_search 或 FindBestQueryPlan → Join Order 搜索-- e. attaching_conditions_to_tables → 条件下推-- 3. join_execution → 执行计划最终选定的SEToptimizer_traceenabledoff;3.3 测试验证-- 验证清单-- 1. 确认 optimizer trace 捕获了优化过程SEToptimizer_traceenabledon;SELECT1;SELECTCOUNT(*)FROMinformation_schema.OPTIMIZER_TRACE;-- 应有 1 条记录-- 2. 验证老优化器和 Hypergraph 的差异SEToptimizer_switchhypergraph_optimizeroff;EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_idu.id;-- 记录 key 和 rowsSEToptimizer_switchhypergraph_optimizeron;EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_idu.id;-- 对比是否不同-- 3. 验证代价模型参数SELECT*FROMmysql.server_cost;SELECT*FROMmysql.engine_costWHEREengine_nameInnoDB;-- 4. 通过 optimizer trace 确认条件下推生效SEToptimizer_traceenabledon;EXPLAINSELECT*FROMorders_largeWHEREuser_id1ANDstatus1;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 condition_pushdown 或 attaching_conditions_to_tablesSEToptimizer_traceenabledoff;4. 项目总结优点 缺点维度优点缺点/局限optimizer trace完整记录优化器的每一步决策——可追踪到代价数字JSON 输出极长——一条三表 JOIN 的 trace 可达 100KBHypergraph Optimizer支持 bushy tree——理论上可找到更优计划搜索空间大——复杂 JOIN 时优化器本身也耗时AccessPath 枚举清晰的枚举类型——每种数据访问方式对应一种 AccessPath扩展新访问方式需要修改多处 switch-case代价模型可配置通过 mysql.server_cost 调整 IO/CPU 代价系数代价系数全局生效——调整后其他查询也受影响适用场景执行计划选择异常的排查优化器选错了执行计划——通过 optimizer trace 定位是代价估算偏差还是统计信息失真。性能调优的深层理解知道优化器怎么选——才能调整参数让它选对。Hypergraph Optimizer 迁移评估用 optimizer trace 对比新旧优化器的计划——决定是否开启。SQL 审核自动化编写脚本解析 optimizer trace——检测全表扫描、filesort、临时表等不良模式。数据库教学研究MySQL 优化器是查询优化理论的工业级实现案例。不适用场景简单的单表查询优化路径几乎唯一——不需要追踪。高性能 OLTP 场景optimizer trace 本身有性能开销——不应在生产中常开。注意事项optimizer trace 仅对当前会话有效不会影响其他连接——排查时放心开。优化器在不同 MySQL 版本中行为可能不同Hypergraph Optimizer 在 8.x 各版本中逐步完善——升级后需验证执行计划。optimizer_search_depth设置影响搜索质量和时间太深→搜索慢太浅→遗漏好计划。常见踩坑经验故障案例一optimizer trace 输出被截断。根因optimizer_trace_max_mem_size默认 1MB——复杂 JOIN 的 trace 可能超过。修复SET optimizer_trace_max_mem_size 10000000;10MB。故障案例二优化器选了 Hash Join 但实际执行很慢。根因优化器预估 join_buffer 足够——但实际运行时数据量大于预估——Hash Join 溢出写磁盘。修复增加join_buffer_size或使用BNL/NO_HASH_JOINhint。故障案例三两个逻辑等价的 SQL——优化器选出了不同的计划。根因SQL 写法差异导致优化器能下推的条件不同——WHERE a.id b.id AND a.status 1和WHERE b.id IN (SELECT id FROM a WHERE status 1)的优化路径完全不同。修复用 optimizer trace 对比两种写法的执行计划——选择最优写法。思考题在 optimizer trace 中“cost_before_filter” 和 “cost_after_filter” 有什么区别为什么有些查询中两者差异巨大Hypergraph Optimizer 的 bushy tree 在什么场景下比左深树有显著优势举出一个 SQL 例子。答案提示第 1 题——filter 之前扫描了更多行cost_before_filter 高但 filter 后只剩少量行cost_after_filter 低——如果 filter 不能下推到存储引擎则差异巨大第 2 题——当多表 JOIN 中有两个大表各自过滤后仍很大时——分别与一个小表先 JOIN 再互 JOIN 可减少中间结果大小。延伸阅读与资源10倍开发者的 Dify 魔法书从零构建全栈 AI 应用后端工程师转型AI第一课-Ollama 与私有化大模型实战大型语言模型(LLM) vLLM 高性能推理落地实战Agent开发之LlamaIndex 实战修炼与源码进阶大语言模型Transformers 实战修炼与源码剖析