join算法

在MySQL8.0.18之前，使用嵌套循环算法或其变体执行表之间的连接操作，MySQL 8.0.18 开始，尽量使用哈希连接算法

嵌套循环

简称NLJ。简单的嵌套循环连接算法一次从循环中的第一个表中读取一行，将每一行传递到嵌套循环中，该循环处理连接中的下一个表。此过程重复多次，直到剩余要连接的表为止。

例如：查询每个班级的班主任信息及其所教课程信息

mysql> mysql> EXPLAIN SELECT * FROM classes c  LEFT JOIN teachers t ON c.head_teacher_id = t.teacher_id LEFT JOIN courses c1 ON t.teacher_id = c1.teacher_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                                  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                 |    5 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | student_management.c.head_teacher_id |    1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | c1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                 |    5 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------------------------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

3张表的join类型分别为c(ALL)，t(eq_ref)， c1(ALL)

// 从最开始进行连接的表开始，首先筛选classes表要参与join的行
// 这里没有对classes表加where条件，所以是classes表的所有数据
for each row in classes matching range {
  // 将teachers表与classes表的每行根据join条件进行匹配
  for each row in teachers matching reference key {
    // 最后与courses表的每行根据join条件进行匹配
    for each row in courses {
      // 匹配成功，将该行发送到客户端
    }
  }
}

因为 NLJ 算法每次将一行从外循环传递到内循环，所以它通常会多次读取内循环中处理的表。

块嵌套循环

块嵌套循环连接算法，简称BNL。它使用缓冲在外循环中读取的行来减少必须读取内循环中的表的次数。例如，如果将 10 行读入缓冲区并将缓冲区传递到下一个内循环，则可以将内循环中读取的每一行与缓冲区中的所有 10 行进行比较。这将必须读取内表的次数减少了一个数量级。

在 MySQL 8.0.18 之前，当无法使用索引时，此算法适用于等值连接；在 MySQL 8.0.18 及更高版本中，在这种情况下采用hash join优化。从 MySQL 8.0.20 开始，MySQL 不再使用块嵌套循环，并且在所有以前使用块嵌套循环的情况下都采用hash join。

使用join buffer的情况有以下几种

1. 连接类型为 ALL
2. 连接类型为 index，即没有可能的索引可用，并且分别对数据或索引行进行完整扫描时
3. 连接类型为 range 时

缓冲的使用也适用于外连接
https://dev.mysql.com/doc/refman/8.0/en/bnl-bka-optimization.html

永远不会为第一个非常量表分配连接缓冲区，即使其类型为 ALL 或index

只有与连接相关的列才会存储在其连接缓冲区中，而不是整行数据

join_buffer_size 系统变量确定用于处理查询的每个连接缓冲区的大小

mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

为每个可缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定的查询。
在执行连接之前分配一个连接缓冲区，并在查询完成后释放。

对于前面描述的 NLJ 算法的示例，使用连接缓冲进行连接如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
  	// 存进join buffer
    store used columns from t1, t2 in join buffer
    // 缓冲区满了
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
     // 清空缓冲区
    }
  }
}
// 考虑到可能缓冲区没满，外层循环就结束了，所以这里进行
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

假设S时t1,t2在join buffer中的大小，C是缓冲区中组合的数量，则t3表扫描的次数为
(S * C)/join_buffer_size + 1
t3 扫描的次数随着 join_buffer_size 值的增加而减少，直到 join_buffer_size 大到足以容纳所有先前的行组合。此时，将其增大不会提高速度。

哈希连接

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

Hash Join 通常用于执行涉及较大数据集的连接操作。它特别适合在没有索引的情况下，对大表进行高效的连接操作。其基本思想是通过构建哈希表来加速连接操作

Hash Join 通常分为两个阶段：

1. 构建阶段 (Build Phase)：数据库从较小的表（称为inner table）中读取数据，并使用连接的key构建一个哈希表。连接的key通常是表中要参与连接的列。在这个过程中，每条记录的该列的值会被作为哈希表的key，记录自身作为value存储在哈希表中。
2. 探测阶段 (Probe Phase)：数据库扫描另一个较大的表（称为outer table），对于每一条记录，根据连接的key计算出哈希值，然后在之前构建的哈希表中查找匹配项。如果在哈希表中找到匹配的记录，则这两条记录就满足连接条件，结果将会被返回。

假设有这么一条sql：查询所有班级及其班主任信息
SELECT class_id, class_name, teacher_name
FROM classes JOIN teachers ON classes.head_teacher_id = teachers.teacher_id;

1. 构建阶段
   对teachers表构建hash表
   
   这里使用的是teacher_id作为hash的key，由于主键唯一，不会有hash碰撞

2. 探测阶段
   扫描班级表
   

使用场景

假设现在有3张表

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

默认情况下，MySQL（8.0.18 及更高版本）会尽可能使用hash join。从 MySQL 8.0.18 开始，MySQL 对任何查询都采用哈希连接，其中每个连接都有等值连接条件，并且没有可应用于任何连接条件的索引，例如这个：SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;在MySQL 8.0.17和MySQL 8.0.26版本执行计划如下图所示

哈希连接通常比以前版本的 MySQL 中使用的块嵌套循环算法更快，并且旨在在这种情况下使用哈希连接来代替块嵌套循环算法。从 MySQL 8.0.20 开始，对块嵌套循环的支持被删除，并且服务器在以前使用块嵌套循环的地方都使用哈希连接。

在 MySQL 8.0.20 之前，必须包含 FORMAT=TREE 选项才能查看给定连接是否使用了哈希连接

涉及多个连接的查询，只要每对表至少有一个连接条件是等值连接

EXPLAIN FORMAT=TREE
SELECT * FROM t1
JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3 ON (t2.c1 = t3.c1)\G;

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM t1
    ->     JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    ->     JOIN t3 ON (t2.c1 = t3.c1)\G;
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
    -> Table scan on t3  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

1 row in set (0.00 sec)

在 MySQL 8.0.20 之前，如果任何一对连接表没有至少一个等值连接条件，则无法使用哈希连接，而是采用较慢的块嵌套循环算法。在 MySQL 8.0.20 及更高版本中，在这种情况下使用哈希连接，如下所示：

默认情况下，MySQL 8.0.18 及更高版本会尽可能使用哈希连接。可以使用 BNL 和 NO_BNL 优化器提示之一来控制是否使用哈希连接。

MySQL 8.0.18 支持 hash_join=on 或者 hash_join=off 作为optimizer_switch这个变量的一部分，也可以使用HASH_JOIN或者NO_HASH_JOIN这两个optimizer hint来暗示优化器使用hash join。但是MySQL 8.0.19及以后，这些参数不再起作用

可以通过SHOW VARIABLES LIKE 'optimizer_switch';查看optimizer_switch变量的值