MySQL各种join算法的优劣势

MySQL中join算法的知识

NLJ (Nested Loop Join)

Simple nested loop算法，说白了就是一个双重for循环遍历的算法，Simple nested loop算法匹配的过程是这样的：

 从左边的驱动表order_info中，每取出一条记录都要遍历一遍被驱动表order_item_detail，说白了就是一个双重for循环。

下面的伪代码中引自MySQL文档，以3表join，t1、t2、t3为例。会顺序读取t1表每行数据，调存储引擎一行一行数据匹配。由于join算法执行是在MySQL server层，是调存储引擎读取表数据，所以造成t2表、t3表会被反复读取很多次，导致效率降低。

如果能降低t3表的磁盘读取次数，显然可以提高执行效率。所以MySQL并没有选择使用 Simple nested loop 算法，而是使用了优化后的Block nested loop 算法。

    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
       }
      }
     }

BNL (Block Nested Loop)

 以下摘自官网

Prior to MySQL 8.0.18, this algorithm was applied for equi-joins when no indexes could be used; in MySQL 8.0.18 and later, the hash join optimization is employed in such cases. Starting with MySQL 8.0.20, the block nested loop is no longer used by MySQL, and a hash join is employed for in all cases where the block nested loop was used previously. See Section 8.2.1.4, “Hash Join Optimization”.

MySQL join buffering has these characteristics:

• Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or range. Use of buffering is also applicable to outer joins, as described in Section 8.2.1.12, “Block Nested-Loop and Batched Key Access Joins”.
• A join buffer is never allocated for the first nonconstant table, even if it would be of type ALL or index.
• Only columns of interest to a join are stored in its join buffer, not whole rows.
• The join_buffer_size system variable determines the size of each join buffer used to process a query.
• One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.
• A join buffer is allocated prior to executing the join and freed after the query is done.

For the example join described previously for the NLJ algorithm (without buffering), the join is done as follows using join buffering:

改进后的join算法使用join buf缓存t1和t2的交集列数据，这里的交集列是指join使用到的列值。server层先从存储引擎读取t1、t2表匹配的数据行列值放到join buf，当buf满了后再调存储引擎读取一次t3表，一次性匹配完join buf中的数据。此时有效的减少了t3表被磁盘读取的数量。

for each row in t1 matching range {
  for each row in t2 matching reference key {
    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
        }
      }
      empty join buffer
    }
  }
}
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
    }
  }
}

至于t3表从磁盘读取次数可以被减少多少次？取决于join buf大小和每个交集元素的大小，总结是join buf越大对join的速度提升越快，但是当join buf大到足以装下前面的多表交集数据时，就达到了阈值，此时再增大join buf大小也没用了。

If S is the size of each stored t1, t2 combination in the join buffer and C is the number of combinations in the buffer, the number of times table t3 is scanned is:

        (S * C)/join_buffer_size + 1

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.

INL（Index Nested Loop）

 BNL算法还是用在join无索引时的优化技术，总归还是要对被驱动表做磁盘聚集索引树的全表扫描，单次磁盘扫描的代价也不低。所以如果两张表的join列可以命中索引会使用基于索引树的join算法更快。相比较BNL算法优势：

• 搜索被驱动表的二级索引树，极大减少了磁盘读次数，时间复杂度log(n)
• 被驱动表的二级索引页基本会在buf pool中，较高概率是读取内存中的索引页

MySQL 8.0.18之后的Hash join

hash join也是使用join buf对无索引连接场景的优化。在8.0.18版本之后server优化器只要在能用hash join情况下都会用，取代之前的block nest loop算法。在8.0.18版本可以使用两种方式控制hash join的使用：

• 用hint关键字HASH_JOIN/NO_HASH_JOIN指定语句
• 在优化器开关配置上开启或关闭hash_join

但是在8.0.19版本之后hash join的使用就不受用户控制，由优化器决定。并且在8.0.20版本之前每一个表的join条件都必须至少有一个等值连接才能使用hash join，否则这个没有等值连接的join部分就会退化为block nest loop。

新的explain方式，explain format=tree或explain analyze，从下往上看执行顺序

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM t1
    ->     JOIN t2 ON (t1.c1 = t2.c1)
    ->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)
    -> Inner hash join (no condition)  (cost=1.05 rows=1)
        -> Table scan on t3  (cost=0.35 rows=1)
        -> Hash
            -> 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)

hash join顾名思义对t1和t2连接的条件列，缓存在哈希表中。等读取t2表时可以做顺序IO读取，将每个数据页在内存中对哈希表中的t1.c1列数据匹配。因为使用join buf缓存t1.c1连接条件，所以对t2、t3表的磁盘读取次数减少还是取决于join buf的大小和每个buf元素的大小。对内存中的匹配速度有提升，内存中匹配时间为O(1)。在大部分情况下hash join是会比block nested loop算法要快。

当驱动表的连接列数据太多，导致join buf放不下时，会存到磁盘的临时文件中。有可能引发操作系统的 最大创建文件数限制，这个时候引发语句执行失败。

到底能不能使用join？

好了，我们刚才了解了Simple nested loop 、 Block nested loop、Index nested loop 这三种算法，那么现在可以回答开头的问题了：到底能不能使用join？

问题没有标准的答案，要根据系统的并发量，业务复杂程度，表数据量，SQL质量，是否命中索引，MySQL服务器的配置等决定。其实，在并发量低、表数据量百万以下，SQL简单，能命中索引，服务器配置高，以后也不会分库分表的情况下，用join也行。

我们平常使用explain优化sql的时候，如果 explain 结果中的 Extra 字段，如果包含 ' Using join buffer (Block Nested Loop) ' 的话，这个时候就代表使用了 Block nested loop 算法了。同时也暗示可以对SQL优化，因为join没用上索引，哈哈哈哈

那么互联网业务为什么要优化掉join呢？

主要由于2个原因，首先后边我们有分库分表的计划，所以为了有更好的扩展性，我们会优化掉join，其次MySQL是专门用来做数据存储的，所以，还是尽量不要把业务相关的逻辑放到MySQL层面来做。

小表驱动大表的常识

不管是Orcale MySQL SQL Server ，在写SQL时经常听的一句话，小表驱动大表！曾经我一直不解，不都是 M*N 的时间复杂度么？

但其实对于 join，情况可能会挺复杂的，并不是一个乘法那么简单。

首先最简单的场景，如果是2张表都没有额外的搜索条件，直接连接且连接条件都有索引。

select * from a join b on a.id = b.id

a 数据量为100w，b数据量为10w。据说现在的优化器都比较智能，不会根据Join位置做驱动表选择，而是根据诸多条件做判断。先从b中 full scan 获取到所有b.id在内存中，对a做索引搜索，时间：O(b) * O(log a)。如果是 full scan a 取得a.id，对b做索引搜索，时间：O(a) * O(log b)。差距就出来了吧，如果a和b的数据量差异再增大，索引搜索的优势更加明显。

这就是为什么强调的小表驱动大表最典型的场景。

但是对于数据库分析也并没有一劳永逸的事情，还有很多链接场景会导致join算法的不同，join算法又导致效率的不同。对于2张表都没有索引且都没有搜索条件，那确实是 O(a) * O(b) 的时间。 

引自网上某段话：对于驱动表的选择基于优化器多种条件的考虑衡量。