关于mysql:为什么不让用join死磕MySQL系列-十六

大家好，我是咔咔 不期速成，日拱一卒

在平时开发工作中 join 的应用频率是十分高的，很多 SQL 优化博文也让把子查问改为 join 从而晋升性能，但局部公司的 DBA 又不让用，那么应用 join 到底有什么问题呢？

一、什么是 Nested-Loop Join

在 MySQL 中，应用 Nested-Loop Join 的算法进行优化 join 的应用，此算法翻译过去为嵌套循环连贯，并且应用了三种算法来实现。

● Index Nested-Loop Join：简称 NLJ
● Block Nested-Loop Join：简称 BNLJ
● Simple Nested-Loop Join：简称 BNL

这几种算法大抵意思为索引嵌套循环连贯、缓存块嵌套循环连贯、粗犷嵌套循环连贯，你当初看的程序就是 MySQL 抉择 join 算法的优先级。

从名字上给人感觉 Simple Nested-Loop Join 算法是非常简单同样也是最快的，但理论状况是 MySQL 并没有应用这种算法而是优化成应用 Block Nested-Loop Join，带着各种疑难一起来摸索其中的神秘。

都看到这里了，是不是对嵌套循环连贯的意思不太明确？其实是非常简单的，一个简略的案例你就能明确什么是嵌套循环连贯。

假如当初有一张文章表 article，一张文章评论表 article_detail，需要是查问文章的 id 查问出所有的评论当初的首页，那么 SQL 就会是以下的样子

select * from article a left join article_detail b on a.id = b.article_id

若应用代码来形容这段 SQL 的实现原理大抵如下，这段代码应用切片和双层循环实现冒泡排序，这段代码就能十分代表 SQL 中 join 的实现原理，第一层 for 即为驱动表，第二层 for 则为被驱动表。

func bubble_sort(arr []int) {

a := 0 
for j := 0; j < len(arr)-1; j++ {for i := 0; i < len(arr)-1-j; i++ {if arr[i] > arr[i+1] {a = arr[i]        
            arr[i] = arr[i+1] 
            arr[i+1] = a
        }
    }
}

}

好了，当初你晓得了什么是 Nested-Loop Join，也晓得了实现 Nested-Loop Join 的三种算法，接下来咱们就围绕这三种算法来进行探讨，为什么不让用 join。

二、Index Nested-Loop Join

为了避免优化器对 SQL 进行粗犷优化，接下来会应用 STRAIGHT_JOIN 来进行查问操作。

为什么会须要 STRAIGHT_JOIN，在开发过程中有没有遇到明明是驱动表的却莫名其妙的成为了被驱动表，在 MySQL 中驱动表的概念是当指定了连贯条件时，满足条件并记录行数少的表为驱动表。当没有指定查问条件时，则扫描行数少的为驱动表，优化器总是以小表驱动大表的形式来决定执行程序的。

索引嵌套循环连贯是基于索引进行连贯的算法，索引是基于被驱动表的，通过驱动表查问条件间接与被驱动表索引进行匹配，避免跟被驱动表的每条记录进行比拟，利用索引的查问缩小了对被驱动表的匹配次数，从而晋升 join 的性能。

应用前提

应用索引嵌套查问的前提是驱动表与被驱动表关联字段上有设置索引。

接下来应用一个案例来具体解析索引嵌套查问的具体执行流程，以下 SQL 是所有的表和数据，间接复制就能够用

CREATE TABLE article (id INT (11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,author_id INT (11) NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT=’ 文章表 ’;

CREATE PROCEDURE idata () BEGIN DECLARE i INT; SET i=1; WHILE (i<=1000) DO INSERT INTO article VALUES (i,i); SET i=i+1; END WHILE; END;

call idata();

CREATE TABLE article_comment (id INT (11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,article_id INT (11) NOT NULL COMMENT ‘ 文章 ID’,user_id INT (11) NOT NULL COMMENT ‘ 用户 ID’,PRIMARY KEY (id),INDEX idx_article_id (article_id)) ENGINE=INNODB CHARSET=utf8mb4 COLLATE utf8mb4_german2_ci COMMENT=’ 用户评论表 ’;

DROP PROCEDURE idata;

CREATE PROCEDURE idata () BEGIN DECLARE i INT;
SET i=1; WHILE (i<=1000)
DO
INSERT INTO article_comment VALUES (i,i,i);
SET i=i+1; END WHILE; END;

CALL idata ();

能够看到，此时 article 表和 article_comment，数据都是 1000 行

需要是查看文章的所有评论信息，执行 SQL 如下

SELECT*FROM article STRAIGHT_JOIN article_comment ON article.id=article_comment.article_id;

当初，咱们来看一下这条语句的 explain 后果。

能够看到，在这条语句中，被驱动表 article_comment 的字段 article_id 应用了索引，因而这个语句的执行流程是这样的

● 从 article 表读取一行数据 R
● 从 R 中去除 id 字段到表 article_comment 去查找
● 取出 article_comment 中满足条件的行，跟 R 组成一行
● 反复前三个步骤，直到表 article 满足条件的数据扫描完结

在这个流程中咱们简略的梳理一下扫描行数

● 对 article 表须要做全表扫描，扫描行数为 1000
● 没行 R 数据，依据 article 表的 id 去表 article_comment 查找，走的是树搜寻，因而每次的搜寻的后果都是一一对应的，也就是说每次只会扫描到一行数据，共须要扫描 1000
● 所以，这个执行流程，总扫描行数为 2000 行

若在代码中如何实现

● 全表扫描 article 数据，这里是 1000 行
● 循环这 1000 行数据
● 应用 article 的 id 作为条件，在循环中进行查问

执行过程扫描行数也是 2000 行，先不波及这样写性能如何，光与 MySQL 进交互就进行了 1001 次。

论断

显然这么做还不如间接应用 join 好

三、Simple Nested-Loop Join

简略嵌套循环连贯查问是表连贯应用不上索引，而后就粗犷的应用嵌套循环，article、article_comment 表都有 1000 行数据，那么扫描数据的行数就是 1000*1000= 1 千万，这种查问效率可想而知是怎么样的。

执行 SQL 如下

SELECT * FROM article STRAIGHT_JOIN article_comment ON article.author_id=author_id.user_id;

在这个流程里：

● 对驱动表 article 做了全表扫描，这个过程须要扫描 1000 行
● 从驱动表读取一行数据都须要在 article_comment 表中进行全表扫描，没有应用索引就须要全表扫描
● 因而，每次都须要全表扫描被驱动表的数据

这还是两个十分小的表，在生产环境的表动辄就是上千万，如果应用这种算法预计 MySQL 就没有当初的盛况

当然了，MySQL 也没有应用这种算法，而是用了分块嵌套查问的算法，这种思维在 MySQL 中很多中央都在应用

扩大

例如，索引是存储在磁盘中的，每次应用索引进行检索数据时会把数据从磁盘读入内存中，读取的形式也是分块读取，并不是一次读取完。

假如当初操作系统需在磁盘中读取 1kb 的数据，实际上会操作系统读取到 4kb 的数据，在操作系统中一页的数据是 4kb，在 innodb 存储引擎中默认一页的数据是 16kb。

为什么 MySQL 会采纳分块来读取数据，是因为数据的局部性原理，数据和程序都有汇集成群的偏向，在拜访到一行数据后，在之后有极大的可能性会再次拜访这条数据和这条数据相邻的数据。

四、Block Nested-Loop Join

应用简略嵌套查问的形式通过上文的剖析必定是不可取的，而是抉择了分块的思维进行解决。

这时，执行流程是这样的

● 从驱动表 article 中读取数据寄存在 join_buffer 中，因为是应用的没有条件的 select，因而会把 article 全表数据放入内存
● 拿着 join_buffer 中的数据跟 article_comment 中的数据进行逐行比照

对应的，这条 SQL 的 explain 后果如下所示

为了复现 Block Nested Loop，咔咔装了三个版本的 MySQL，别离为 MySQL8，MySQL5.5，MySQL5.7 在后两个版本中都应用的是 Block Nested Loop，但在 MySQL8 中却产生了变动。

对于 hash join 下期会聊到，在这个查问过程中，对表 article、article_comment 都做了一次全表扫描，因而扫描行数是 2000。

把 article 中的数据读取到 join_buffer 中是以无序数组的形式存储的，对于 article_comment 表中的每一行，都须要做 1000 次判断，那么就须要判断的次数就是 1000*1000=1000 万次。

这时你发现应用分块嵌套循环跟简略嵌套查问扫描行数是一样的，但 Block Nested Loop 算法利用了 join_buffer 的这么一个内存空间，因而速度上必定会比 Simple 快很多。

五、总结

本期咱们用三个问题来总结全文，以帮忙你更好的了解。

第一个问题：能不能应用 join？

通过三个演示案例，当初你应该晓得当关联条件的列是被驱动表的索引时，是齐全没有问题的，也就是说当应用索引嵌套查问时，是能够应用 join 的。

但当应用的是分块嵌套查问，这种形式扫描行数为两张表行数的乘，扫描行数会十分的大，会占用大量的系统资源，所以这种算法的 join 是十分不倡议应用的。

因而当应用 join 时，最大可能的让关联查问的列为被驱动表的索引列，若不能达到这个条件则能够思考表结构设计是否正当

第二个问题：如果应用 join，抉择大表还是小表作为驱动表？

好的习惯都是缓缓养成的，因而你要记住无论在什么状况下都用小表驱动大表，先记住这个论断。

如果是 Nested-Loop Join 算法，应该抉择小表作为驱动表。

如果是 Block Nested-Loop Join，当 join_buffer 足够大的时候，应用大表还是小表作为驱动表都是一样的，然而当 join_buffer 没有手动设置更大的值时，还是应该抉择小表作为驱动表。

这里还须要晓得一点 join_buffer 的默认值为在 MySQL8.0 位 256kb。

第三个问题：什么样的表是小表？

这里的小表不是数据量十分小的表，这点肯定不能搞错，在所有的 SQL 查问中绝大多数状况是有条件进行筛选的。

看是否为小表是依据同一条件下两张表那个检索的数据量小，那张表就是小表。

举荐浏览

死磕 MySQL 系列总目录

关上 order by 的大门，一探到底《死磕 MySQL 系列 十二》

重重封闭，让你一条数据都拿不到《死磕 MySQL 系列 十三》

闯祸了，生成环境执行了 DDL 操作《死磕 MySQL 系列 十四》

聊聊 MySQL 的加锁规定《死磕 MySQL 系列 十五》

保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。