阅读说明:每一样技术 或者 功能都是为了解决某一个问题而诞生的。 搞清楚为什么要这么做 比 搞清楚 具体怎么做要重要。 所以我都会以问题开头,希望大家能先想想自己是否知道这些问题的答案,然后对比我给出的答案,如果不一致思考下到底谁的答案有问题,动手证明。 这种通过攻克一个个问题从而提升自己的能力,就是我希望通过一篇篇文章给大家带来的价值。

每篇文章 我会聚焦于一两个点,尽量不扩散太多。当然技术类文章无法避免一个知识点牵连另一个知识点。这种碰到遇到不懂知识点的时候我建议大家知道有这么个东西,把精力集中于当前问题,而不要太扩散。当然后续我也会根据反馈,进行填坑。

问题

什么是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 优化器自动优化,具体优化逻辑此处不展开,后续有机会详聊。

执行结果:

mysql join 只 join 一个_数据

可以看到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 这个没有索引的字段被拿来做关联条件时的情况)

mysql join 只 join 一个_字段_02

可以看到 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 。