在平时开发工作中 join 的使用频率是非常高的，很多 SQL 优化博文也让把子查询改为 join 从而提升性能，但部分公司的 DBA 又不让用，那么使用 join 到底有什么问题呢？
image.png
一、什么是 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结果。
网友评论