综合 MySQL 9.7 能开 hypergraph optimizer 了:77 条查询变快、24 条变慢,不建议全局打开

2026-11-20 21:04:18

MySQL 9.7 能开 hypergraph optimizer 了:77 条查询变快、24 条变慢,不建议全局打开

参考:The Hypergraph Optimizer is now available in MySQL 9.7 Community Edition

MySQL 9.7 社区版把 hypergraph optimizer 作为经典 join optimizer 之外的另一条路径,默认不开启。

开启方式

SET optimizer_switch='hypergraph_optimizer=on';

关闭时改成 off。只对单条语句生效,用 hint:

SELECT /*+ SET_VAR(optimizer_switch='hypergraph_optimizer=on') */ ...;

也可以 SET GLOBAL / SET PERSIST,或者启动时传参:

mysqld --optimizer-switch='hypergraph_optimizer=on|off'

它把查询当成超图

每张表是一个节点,每个 join 谓词是一条边,多表谓词可以直接表示,不必先拆成二元 join。搜索阶段用动态规划枚举连通的子计划,思路来自 Moerkotte 和 Neumann 的 SIGMOD 2008 论文 "Dynamic Programming Strikes Back"。

经典优化器的限制在于:主要以左深 join 树为主,inner join 之外的重排能力有限,把行的顺序当作事后的 refinement 处理,hash join 应用得偏晚,在物理策略差异很大时难以做代价比较。

四个实际差异

  1. interesting orders 进入搜索过程。一条访问路径即使局部代价略高也值得保留,因为它返回的行序对后续有用:索引顺序可以省掉 ORDER BY LIMIT 的排序、直接喂给 GROUP BY、保持顺序从而不产生 filesort 或临时表。
  2. nested loop 与 hash join 成为真正的代价比较。经典优化器在内侧有可用索引时经常留在 nested loop,即使建哈希表更便宜。
  3. 支持 bushy 计划。如果 t1 JOIN t2 和 t3 JOIN t4 都很有选择性,先把两边各自缩减再 join 会更便宜。
  4. 代价模型用微基准的时间测量标定。

性能数据

TPC-DS SF1(约 1GB,工作集全在内存),101 个查询变体:77 条变快、24 条变慢,几何平均提升 22.45%;接近一半提升至少 25%,19 条提升至少 50%,同时有 14 条回退至少 50%。社区版里这个特性仍然默认关闭。

Mark Callaghan 在 MySQL 9.7.0 上的公开 sysbench 测试:read-only-distinct 提升 26%,read-only_range=10000 提升 15%。

值得试的场景

  • 多表 join 的慢页面、报表、看板、批处理任务
  • EXPLAIN ANALYZE 里出现 sort、临时表,或在产出小结果前扫描了大量行的 ORDER BY LIMIT / GROUP BY / DISTINCT 查询
  • join 扫描行数很多但最终结果很小
  • 报表查询把大的历史、事件、订单、交易表与查找表组合
  • 经典计划对同一张表反复做索引查找的查询

差别不大的场景

单表查询、主键点查、很小的 join 基本没变化。很短的查询经典优化器可能仍然更快,因为优化时间会直接体现在延迟里;hypergraph 也可能没考虑到经典优化器能用的某种策略;行数估算不准时两种优化器都可能选错。

怎么评估

挑一条慢查询,分别在开关关闭和开启时各跑一次,比较运行时间,并对照两次 EXPLAIN ANALYZE:join order、join method、rows examined,以及 sort 或临时表有没有消失。

后续修复

后续工作方向包括基表过滤条件的选择率估计、join 结果的基数估计,以及经典优化器仍能找到更优计划的回退案例。

9.7.2 的 release notes 修了一批 hypergraph 相关问题:常量表达式上 GROUP BY WITH ROLLUP 的断言、谓词很多的查询断言、常量边界的 BETWEEN 产生冗余过滤、物化派生表的索引查找计划忽略临时索引构建代价、SELECT DISTINCT LIMIT 在应用 limit 前处理完所有 distinct 行、低 LIMIT 下 hash join 不必要的磁盘溢出、ORDER BY 中带 RAND() 或有副作用存储函数的表达式每行被多次求值。

复制全文 生成海报 MySQL 数据库 查询优化 性能

推荐文章

程序员茄子在线接单