MySQL进阶【六】—— MySQL的嵌套循环连接与基于块的嵌套循环连接

示例表

CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();

MySQL中常见的两种表关联算法

Nested-Loop Join 算法
Block Nested-Loop Join 算法

1、嵌套循环连接 Nested Loop Join 算法（BLJ）

一次一行循环地从第一张表(称为驱动表)中读取行，在这行数据中取到关联字段，根据关联段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果合集
inner join MySQL默认用小表作为驱动表

mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
+----+-------------+-------+------------+------+---------------+-------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | idx_a         | NULL  | NULL    | NULL       |  100 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | nacos.t2.a |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

解析：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql);优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ
这条SQL的执行流程
- 从表 t2 中读取一行数据(如果t2表有查询过滤条件的，会从过滤结果里取出一行数据);
- 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找;
- 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端;
- 重复上面 3 步。

2、基于块的嵌套循环连接 Block Nested Loop Join 算法（BNL）

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

mysql> EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   100 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10337 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.05 sec)
- extra 中的类型是 Using join buffer (Block Nested Loop) 说明使用了BNL算法

上面sql的大致流程如下:
1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比 3. 返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。
这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢?

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

总结

嵌套循环连接（NJL）算法是驱动表和被驱动表均在磁盘中进行匹配，磁盘读写性能差
基于块的嵌套循环连接（BNL）算法，引入了一个join_buffer的内存区域，使得大量的计算挪到内存中进行，内存性能远远大于磁盘，所以整体性能提升比较大
整体思路还是用内存操作替代磁盘操作以提高性能

表连接的优化

上述两个SQL，表连接字段有索引的SQL扫描的行数远远小于表连接字段的扫描行数，所以表连接尽量给连接字段加索引
使用小表驱动大表，可以使用straight_join指定驱动表（其实有些鸡肋的，inner join MySQL的查询优化器会自动选择小表做驱动，left join 和 right join 实际已经指定了驱动表）
straight_join解释:straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
- 比如:select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
  straight_join只适用于inner join，并不适用于left join，right join。(因为left join，right join已经代表指定了表的执行顺序)
  尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。