解读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？ 这是一个常见问题，并非语法错误，而是优化器悄然绕过了它。常见原因包括： - 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'`关闭，但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`并非越大越好，而应略大于预估的构建表字节大小——注意，是字节数，而非行数。