关于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系列十五》

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