企业官网建设流程全解析

1. 项目背景

业务场景：某数据平台的一个三表 JOIN 查询——在 MySQL 9.6 上的执行计划突然从原来的 50ms 变成了 15 秒。DBA 排查发现——优化器原来选择orders → users → products的 Join 顺序（驱动表是小表 users，过滤后只有 100 行），但现在选成了products → orders → users（驱动表是中型表 products，过滤后仍有 5000 行）。同一个 SQL、同样的数据——仅仅是服务器重启了一次——执行计划就变了。

团队需要回答三个问题：优化器到底考虑了哪些 Join 顺序？每个候选计划的代价是多少？为什么最终选择了这个而不是那个？这些答案不在 EXPLAIN 里——在优化器的源码中。

痛点：不懂优化器源码就无法回答"为什么选这个计划"：

1. 候选计划的黑盒：EXPLAIN 只告诉你最终计划——不告诉你它放弃了哪些更好的候选方案。
2. 代价估算的盲区：优化器认为全表扫描比索引扫描代价低——但不告诉你它怎么算出来的。
3. 统计信息失真的连锁反应：一个表的 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_cost（SQL 层）和 engine_cost（存储引擎层）。row_evaluate_cost=0.1表示评估一行数据的 CPU 代价是 0.1 个’代价单位’。io_block_read_cost=1.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_switch='hypergraph_optimizer=on'时启用。对于复杂的报表查询（多表 Join + 聚合 + 子查询）——Hypergraph 通常能找到更好的计划——值得花这点优化时间。”

技术映射：Hypergraph Optimizer 的局限性 = 穷举搜索时间随表数指数增长。适合复杂报表查询（执行时间长——优化时间占比小），不适合简单 OLTP 查询。

3. 项目实战

3.1 环境准备

-- 确认 optimizer 版本SELECT@@optimizer_switchLIKE'%hypergraph_optimizer=on%'ASusing_hypergraph;-- 开启 optimizer traceSEToptimizer_trace='enabled=on';SEToptimizer_trace_max_mem_size=1000000;-- 准备测试数据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()// Hypergraph：FindBestQueryPlan()if(determine_join_order())returntrue;// 4. 生成最终执行计划if(make_join_query_block())returntrue;returnfalse;}

步骤二：追踪 Access Path 选择——扫描方式的代价对比

-- 步骤目标：用 optimizer trace 看每张表的候选访问路径和代价SEToptimizer_trace='enabled=on';EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_id=u.idWHEREo.status=1ANDo.created_atBETWEEN'2026-01-01'AND'2026-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 有两个候选方案——-- 方案 A：range scan on idx_status_created，代价 1234，选择 ✓-- 方案 B：全表扫描，代价 25000，放弃 ✗-- 优化器选择了代价更低的 range scanSEToptimizer_trace='enabled=off';

步骤三：追踪 Join Order 搜索——greedy_search 和 Hypergraph 的差异

-- 步骤目标：对比老优化器和 Hypergraph Optimizer 的 Join Order 选择-- === 使用老优化器 ===SEToptimizer_switch='hypergraph_optimizer=off';SEToptimizer_trace='enabled=on';EXPLAINSELECTu.name,o.amount,p.nameFROMorders_large oJOINusers uONo.user_id=u.idJOINproducts_small pONo.id=p.idWHEREo.status=1LIMIT100;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 "greedy_search"：-- 老优化器从 cost 最小的表开始——每次选择与当前部分计划 JOIN 后代价最小的下一个表-- 最终得到一个左深树：orders → users → productsSEToptimizer_trace='enabled=off';-- === 使用 Hypergraph Optimizer ===SEToptimizer_switch='hypergraph_optimizer=on';SEToptimizer_trace='enabled=on';EXPLAINSELECTu.name,o.amount,p.nameFROMorders_large oJOINusers uONo.user_id=u.idJOINproducts_small pONo.id=p.idWHEREo.status=1LIMIT100;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 "FindBestQueryPlan"：-- Hypergraph 穷举所有可能的 Join 组合——-- 包括左深树：(o ⋈ u) ⋈ p 或者 (u ⋈ o) ⋈ p-- 也包括 bushy tree：o ⋈ (u ⋈ p)-- 对每种组合计算代价——选最低的SEToptimizer_trace='enabled=off';

步骤四：优化器关键数据结构——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::vector<Node>nodes;std::vector<Edge>edges;// 优化器在 nodes 上应用各种 Join 组合——找最优连接顺序};

步骤五：跟踪一个三表 JOIN 的完整优化过程

-- 步骤目标：记录优化器从 Access Path 选择到 Join Order 到最终计划的每一步SEToptimizer_trace='enabled=on';-- 执行一个带有多种访问方式的三表 JOINEXPLAINSELECTu.name,COUNT(o.id)ASorder_count,SUM(o.amount)AStotalFROMusers uJOINorders_large oONo.user_id=u.idJOINproducts_small pONo.product_id=p.idWHEREo.status=1ANDo.created_at>'2026-01-01'GROUPBYu.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_trace='enabled=off';

3.3 测试验证

-- 验证清单-- 1. 确认 optimizer trace 捕获了优化过程SEToptimizer_trace='enabled=on';SELECT1;SELECTCOUNT(*)FROMinformation_schema.OPTIMIZER_TRACE;-- 应有 1 条记录-- 2. 验证老优化器和 Hypergraph 的差异SEToptimizer_switch='hypergraph_optimizer=off';EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_id=u.id;-- 记录 key 和 rowsSEToptimizer_switch='hypergraph_optimizer=on';EXPLAINSELECT*FROMorders_large oJOINusers uONo.user_id=u.id;-- 对比是否不同-- 3. 验证代价模型参数SELECT*FROMmysql.server_cost;SELECT*FROMmysql.engine_costWHEREengine_name='InnoDB';-- 4. 通过 optimizer trace 确认条件下推生效SEToptimizer_trace='enabled=on';EXPLAINSELECT*FROMorders_largeWHEREuser_id=1ANDstatus=1;SELECTTRACEFROMinformation_schema.OPTIMIZER_TRACE\G-- 搜索 "condition_pushdown" 或 "attaching_conditions_to_tables"SEToptimizer_trace='enabled=off';

4. 项目总结

优点 & 缺点

适用场景

1. 执行计划选择异常的排查：优化器选错了执行计划——通过 optimizer trace 定位是代价估算偏差还是统计信息失真。
2. 性能调优的深层理解：知道优化器"怎么选"——才能调整参数让它"选对"。
3. Hypergraph Optimizer 迁移评估：用 optimizer trace 对比新旧优化器的计划——决定是否开启。
4. SQL 审核自动化：编写脚本解析 optimizer trace——检测全表扫描、filesort、临时表等不良模式。
5. 数据库教学研究：MySQL 优化器是查询优化理论的工业级实现案例。

不适用场景：

1. 简单的单表查询：优化路径几乎唯一——不需要追踪。
2. 高性能 OLTP 场景：optimizer trace 本身有性能开销——不应在生产中常开。

注意事项

• optimizer trace 仅对当前会话有效：不会影响其他连接——排查时放心开。
• 优化器在不同 MySQL 版本中行为可能不同：Hypergraph Optimizer 在 8.x 各版本中逐步完善——升级后需验证执行计划。
• optimizer_search_depth设置影响搜索质量和时间：太深→搜索慢，太浅→遗漏好计划。

常见踩坑经验

1. 故障案例一：optimizer trace 输出被截断。
   根因：optimizer_trace_max_mem_size默认 1MB——复杂 JOIN 的 trace 可能超过。
   修复：SET optimizer_trace_max_mem_size = 10000000;（10MB）。

2. 故障案例二：优化器选了 Hash Join 但实际执行很慢。
   根因：优化器预估 join_buffer 足够——但实际运行时数据量大于预估——Hash Join 溢出写磁盘。
   修复：增加join_buffer_size或使用BNL/NO_HASH_JOINhint。

3. 故障案例三：两个逻辑等价的 SQL——优化器选出了不同的计划。
   根因：SQL 写法差异导致优化器能下推的条件不同——WHERE a.id = b.id AND a.status = 1和WHERE b.id IN (SELECT id FROM a WHERE status = 1)的优化路径完全不同。
   修复：用 optimizer trace 对比两种写法的执行计划——选择最优写法。

思考题

1. 在 optimizer trace 中，“cost_before_filter” 和 “cost_after_filter” 有什么区别？为什么有些查询中两者差异巨大？
2. 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 实战修炼与源码剖析