MySQL 8.0 执行计划中新增的 Hash Join 匹配过程 如何看懂？

在MySQL 8.0中，若要直观查看Hash Join的匹配过程，`EXPLAIN FORMAT=TREE`是核心工具。其他格式，例如默认的`EXPLAIN`或`EXPLAIN FORMAT=TRADITIONAL`，仅会返回`Using join buffer (hash join)`这类模糊提示，无法展示构建表、探测表、哈希键等关键结构。 面对堆叠的缩进树形输出，应从理解每一层缩进对应的执行层级开始，同时关注Hash Join节点本身的标注。 ### 如何理解树形结构中的Hash Join节点 输出以嵌套树形式呈现，缩进越深，执行顺序越靠内。Hash Join节点会明确标注`Inner hash join`或`Left hash join`，并直接列出连接键，例如`Inner hash join (t2.c1 = t1.c1)`。 关键点在于： - 等号左侧为探测表（被驱动表）的连接列，如示例中的`t2.c1`。 - 等号右侧为构建表（驱动表）的连接列，即`t1.c1`。 继续向下查看子节点：带有`Hash`标签的分支代表构建阶段扫描的表（例如`Table scan on t1`），而不带`Hash`标签的分支代表探测阶段扫描的表（例如`Table scan on t2`）。若遇到多层`hash join`嵌套，例如三表JOIN的情况，说明MySQL已将整个连接拆分为多个两表Hash Join步骤，按代价估算顺序从右向左执行。 ### `EXPLAIN ANALYZE`能补充哪些关键信息？ 仅观察树形结构并不足够，实际执行时的性能表现更为重要。此时应使用`EXPLAIN ANALYZE`。它会在实际执行SQL后返回运行时统计信息，相比`EXPLAIN FORMAT=TREE`多出三类关键数据： - 每张表实际扫描行数（`rows`字段），用于验证优化器是否确实选择小表作为构建表。 - 哈希表构建耗时（`build time`）与探测耗时（`probe time`）。若两者差距过大，说明内存不足，已触发磁盘溢出。 - 是否出现`disk overflow`——一旦出现该字样，说明构建表已超出`join_buffer_size`，被分块写入磁盘，性能必然下降。 ### 为何有时明明没有索引，却未使用Hash Join？ 这是一个常见问题。并非语法错误，而是优化器悄悄绕过了Hash Join。常见原因包括： - JOIN条件中混入了`OR`、`LIKE`、函数表达式（如`UPPER(t1.name) = t2.name`），即便只有一处，整个ON子句也不再满足纯等值条件。 - 存在可用的单表索引，例如`WHERE t1.id = `有主键索引，优化器更倾向于使用`Index Nested-Loop Join`，即便连接列本身没有索引。 - 表的统计信息过时，未执行过`ANALYZE TABLE`，导致优化器误判构建表大小，选错驱动方。 - 在MySQL 8.0.18至8.0.19版本中，可通过`SET optimizer_switch='hash_join=off'`关闭Hash Join，但8.0.20及更高版本此开关已失效，无需检查。 ### 如何稳定触发并验证Hash Join生效？ 最可靠的方法不是猜测配置，而是直接控制输入结构： - 使用派生表强制“断开”原始表与索引的关联：`SELECT * FROM (SELECT * FROM t1 WHERE ... ) AS a JOIN (SELECT * FROM t2 WHERE ... ) AS b ON a.c1 = b.c1` - 确保ON子句中仅包含简单等号，且两侧字段均为基础列（不能使用表达式，也不能使用NULL安全比较`<=>`） - 执行前先运行`ANALYZE TABLE t1, t2`，更新统计信息 - 最后使用`EXPLAIN FORMAT=TREE`查看树形结构，再用`EXPLAIN ANALYZE`查看真实耗时与溢出标记 需要强调的是：Hash Join的性能优势高度依赖于构建表能否完全驻留内存。一旦触发磁盘溢出，I/O开销反而可能超过Nested-Loop。因此，`join_buffer_size`并非越大越好，应略大于预估的构建表字节大小——注意是字节数，而非行数。