MySQL Join算法

MySQL Join算法

内容

Simple Nested Loop Join（SNLJ）

  Simple Nested Loop Join就是两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出，这也就是让两张表做笛卡尔积，比较次数是R * S，是比较暴力的算法，会比较耗时。

// 伪代码
for (r in R) {
    for (s in S) {
        if (r satisfy condition s) {
            output <r, s>;
        }
    }
}

Index Nested Loop Join（INLJ）

  Index Nested Loop Join是在Simple Nested Loop Join的基础上基于索引做了优化；通过连接条件确定可用的索引，在Inner Loop中扫描索引而不去扫描数据本身，从而提高Inner Loop的效率。

  而INLJ也有缺点，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。平时使用InnoDB存储引擎是聚簇索引，所以没有这方便的顾虑。

Block Nested-Loop Join

  一般情况下，MySQL优化器在索引可用的情况下，会优先选择使用Index Nested Loop Join算法，但是在无索引可用，或者判断full scan可能比使用索引更快的情况下，还是不会选择使用过于粗暴的Simple Nested Loop Join算法。

  这里就出现了Block Nested-Loop Join算法了，Block Nested-Loop Join在Simple Nested Loop Join的基础上使用了join buffer，会提前读取Inner Loop所需要的记录到buffer中，以提高Inner Loop的效率。

Hash Join

  MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本，带来了一些新特性和增强功能。其中最引人注目的莫过于多表连接查询支持 hash join 方式了。

  此处推荐官方博客，网上这方面内容都是抄得这篇文章。https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/

参考资料

• 官方开发者博客 https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/
• mysql8.0的hash join算法及其优化 https://blog.csdn.net/qq_35423190/article/details/120504960
• MySQL 8.0 hash join有重大缺陷？ https://zhuanlan.zhihu.com/p/133284115