- MySQL Join算法
- 内容
- Simple Nested Loop Join(SNLJ)
- Index Nested Loop Join(INLJ)
- Block Nested-Loop Join
- Hash Join
- 参考资料
Simple Nested Loop Join就是两层循环全量扫描连接的两张表,得到符合条件的两条记录则输出,这也就是让两张表做笛卡尔积,比较次数是R * S,是比较暴力的算法,会比较耗时。
// 伪代码
for (r in R) {
for (s in S) {
if (r satisfy condition s) {
output ;
}
}
}
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 JoinMySQL 开发组于 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



