阅读说明：每一样技术 或者 功能都是为了解决某一个问题而诞生的。 搞清楚为什么要这么做 比 搞清楚 具体怎么做要重要。 所以我都会以问题开头，希望大家能先想想自己是否知道这些问题的答案，然后对比我给出的答案，如果不一致思考下到底谁的答案有问题，动手证明。 这种通过攻克一个个问题从而提升自己的能力，就是我希望通过一篇篇文章给大家带来的价值。
每篇文章 我会聚焦于一两个点，尽量不扩散太多。当然技术类文章无法避免一个知识点牵连另一个知识点。这种碰到遇到不懂知识点的时候我建议大家知道有这么个东西，把精力集中于当前问题，而不要太扩散。当然后续我也会根据反馈，进行填坑。
问题
什么是Index Nested-Loop Join?
什么是Block Nested-Loop Join？
join buffer 做什么用的？
工作当中到底应不应该用join ?
准备工作
CREATE TABLE `school` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`school_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_school` (`school_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
delimiter;;
CREATE PROCEDURE idata ( ) BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
( i <= 1000 ) DO
INSERT INTO school
VALUES
( i, 'school' );
SET i = i + 1;
END WHILE;
END;;
delimiter;
CALL idata ( );
drop procedure idata;
delimiter;;
CREATE PROCEDURE idata ( ) BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
( i <= 1000 ) DO
INSERT INTO student
VALUES
( i, i%2+1, 'stu');
SET i = i + 1;
END WHILE;
END;;
delimiter;
CALL idata ( );复制代码
现在有两张表，一张 shool表，一张 student表。 各插入1000条数据
Index Nexted-Loop Join
EXPLAIN select * from student STRAIGHT_JOIN school on school.id = student.school_id复制代码
(使用STRAIGHT_JOIN) 是防止Mysql 优化器自动优化，具体优化逻辑此处不展开，后续有机会详聊。
执行结果：
可以看到student 为 驱动表， school 为 被驱动表.
执行流程：首先对student 做了全表扫表，这里扫描了1000行。
循环遍历这1000行， 拿一行的 school_id 去 表 school 中查找对应的数据。找到匹配的数据 ，放入结果集中。 由于school 的id 为主键，所以使用主键索引能够迅速定位到那一行。
循环遍历结束，输出结果集到客户端。
上面使用的是join 也就是内连接，如果换成Left join 或者 Right join 外连接有什么区别呢？区别就在于上面第二个步骤中，内连接需要找到匹配的数据放到结果集中， 而外连接是不管有没有找到数据，都需要把驱动表的那行数据加入到结果集中。
那么不管内连接还是外连接， 这种拿到驱动表数据 再去循环遍历查询被驱动表，并且被驱动表查询过程中使用到了索引的方式称为 Index Nested-Loop Join。(从英文其实也可以看出意思了， Index 索引，Nested-loop 嵌套循环)
Block Nested-Loop Join
那么如果 被驱动表上的搜索的字段没有索引呢？
EXPLAIN select * from student STRAIGHT_JOIN school on school.name = student.name复制代码
(虽然现实场景中不会存在 用学校名称 字段关联学生名称这种情况，这里只是说明如果 学校的name 这个没有索引的字段被拿来做关联条件时的情况)
可以看到 school 因为无法用到索引，所以必须要进行全表扫表。
那么我们猜测的执行流程是：扫描student 表，取出1000行。
对取出的1000行数据进行循环遍历，每一行都 去school 表中 进行搜索，由于name 字段没有索引，所以要找到对应的数据需要扫描school全表。 找到数据，放入结果集中。
输出结果集。
这个过程 我们可以看到 需要扫描的行数为 1000 * 1000 。 如果school 或者student 数量再大一定，1万的学校，几百万的学生，那么这个查询的效率可想而知会非常低。
所以针对这种被驱动表无法使用索引的join，Mysql 做出了优化，并不是我们上面所猜测的流程。
join buffer
首先，mysql 引入了join buffer 的概念。这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小是256K，最小可以设置为128字节。
所以真实的流程为：扫描驱动表student，取出1000行 放入join buffer。
扫描被驱动表school, 取出其中一行， 与 join_buffer 中的数据做对比， 如果满足了join 的条件，那么加入到结果集中。 对比完一行取下一行，直到全表扫描结束。
所以上面的情形是 school 中的数据，一行行的取出来 去与 join_buffer 中的数据做对比，查找到 符合条件的结果。那么 需要扫描的行数是 school 的1000 行， 每一行又要与join_buffer 中的1000行进行对比。
这种情况 需要扫描的行数为 student 的1000行，用来放入join_buffer , 和 school 的1000行。也就是扫描了2000行。 单内存中进行判断的次数是 1000 * 1000；可以看到通过引入join buffer 将 磁盘的扫描 变成了内存的判断，以此来提升效率
如果驱动表数据很大，导致join buffer 放不下呢？ 例如 student 有 10000万行，join buffer 每次只能放1000行。
那么流程就会变为：
1.扫描 student ，直到join buffer 放满。
2.扫描 school ，取出其中一行， 与 join_buffer 中的数据做对比， 如果满足了join 的条件，那么加入到结果集中。school 扫描结束后，清空join buffer。
继续扫描student 剩下的， 循环 2，3 步骤。直到student 全部被扫描结束。
这种按 一块块 进行读取放入join buffer 并比较的方式 就是Block Nested-Loop Join 了。
优化方式
可以看到如果 join 的时候使用了Block Nested-Loop Join,那么它的执行效率与join buffer 的大小相关，join buffer 越大，分块的次数越小， 效率越高。
当然充分利用join buffer 空间也是一种方式， 放入join buffer 空间的数据是 驱动表的 查询字段 和 搜索字段。所以避免使用 * ，列清楚需要查询的字段也有利于 join buffer 空间的利用。
当然最理想的优化方式是添加索引，把 Block Nested-Loop Join 转为 Index Nested-Loop Join。
索引也不是随便添加，如果业务场景中很少拿这个字段来进行搜索，单单为了某个join 而添加索引也得不偿失，还需要根据具体业务来考量。
结语
根据join 过程中 被驱动表 能不能用到索引 将查询方式分为 Index Nested-Loop Join 和 Block Nested-Loop JoinIndex Nested-Loop Join 的效率高，可以在业务中使用。
Block Nested-Loop Join 虽然mysql 引入join buffer 来进行优化，但是效率还是偏低，应该尽量避免。
ps: 个人认为在实际业务场景中即使多个表格进行join 过程中都使用到了Index Nested-Loop Join的方式，但是由于每次join 都是进行笛卡尔积的过程，如果其中一张或多张表随着业务发展数据量增加，会导致 整个sql 查询效率也不是很理想。 所以还是尽量避免多表join 的场景。最多允许2 到 3个表格进行join 。
喜欢，支持请关注我的个人公众号
扫一扫
6115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
