MySQL joinmysql join;Nest Loop join;Hash Join;Merge join;嵌套循

目标：join

分析：

详解：

第一部分常见连接方式

1、Nest Loop join

小表做驱动表，大表有索引

嵌套循环连接的两个表在做连接时最朴素的一种连接方式。在嵌套循环连接中，内表被外表驱动，外表返回的每一行都要在内表中检索并找到与它匹配的行，因此整个查询返回的结果集不能太大（>10000不合适），要把返回子集较小的表作为外表，并且在内表的连接字段上要有索引，否则会很慢。
执行过程：假如有两个表A、B，A小于B，则查询优化器会先从A表中取出一条数，然后到B中找到匹配的行并放入等待返回的结果集中，然后再从A中取出第二条记录，继续到B中匹配，如此循环直到找到所有匹配结果并返回。

2、Hash Join

大表做驱动表，小表在内存中建立散列表（2叉树）

优化器使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表可以完全放于内存中的情况，这样总成本就是访问两个表的成本之和。但是如果表很大不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段，此时要有较大的临时段以便尽量提高I/O性能。
执行过程：假如有两个表A、B，A小于B，查询优化器会先将A表在内存中建立一张以散列形式存在的查询二叉树C，然后取出B中的第一条记录，从C中查找匹配的记录，如果有则放入等待返回的结果集中。然后从B中取出第二条记录，重复上述过程，直到B中没有记录。

3、Merge join

A、B表先排序成C、D，然后对C、D进行合并连接

排序合并连接中，首先会对进行连接的两张表进行排序操作，然后对排序后的结果集进行合并连接。
执行过程：优化器首先对表A、B进行排序操作，生成两张临时表C、D。然后C、D进行合并连接。\

通常情况下，散列连接的效果比合并连接要好，但如果源数据上有索引，或者结果已经排过序，在执行排序合并的时候就不需要排序了，这时合并连接的性能会优于散列连接。

第二部分 join使用

MySQL 使用嵌套循环算法或它的变体来执行表之间的连接。

1、Nested-Loop Join

嵌套循环连接算法
一个简单的嵌套循环连接 (NLJ) 算法一次一个地从循环中的第一个表中读取行，将每一行传递给处理连接中下一个表的嵌套循环。重复此过程的次数与要连接的表的剩余次数相同。

假设要使用以下连接类型执行三个表t1、t2和之间 t3的连接：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单的 NLJ 算法，则连接的处理方式如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

由于 NLJ 算法一次将一行从外循环传递到内循环，因此它通常会多次读取在内循环中处理的表。

2、Hash Join 优化

从 MySQL 8.0.18 开始，MySQL 对每个连接具有等连接条件的任何查询使用散列连接，并且其中没有可应用于任何连接条件的索引，例如：

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

当有一个或多个索引可用于单表谓词时，也可以使用散列连接。

散列连接通常比以前版本的 MySQL 中使用的块嵌套循环算法更快，并且旨在用于这种情况。从 MySQL 8.0.20 开始，删除了对块嵌套循环的支持，并且服务器在以前使用块嵌套循环的任何地方都使用了散列连接。

第三部分延申阅读

mysql的几种join blog.csdn.net/u012410733/…
8.2.1.4 Hash Join 优化 dev.mysql.com/doc/refman/…
8.2.1.7 嵌套循环连接算法 dev.mysql.com/doc/refman/…

MySQL join