在实际生产中，关于join语句的使用一般会存在一下两类问题

使用 join 语句，Mysql会如何执行
有两个不同大小的表做 join ，如何选择驱动表

今天我们来探讨一下Mysql中 join 语句到底是如何执行的，首先创建两个表 t1、t2

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;

这两个表都有一个主键索引 id 和一个普通索引 a，字段 b 上无索引，往 t1表中插入 10000 条数据，往 t2 表中插入 100 条数据

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();
------------------------------
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();

Nested-Loop Join—嵌套循环连接

嵌套循环连接就是每次从驱动表中取出一行记录，在这行数据中取到关联字段，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表的结果合集。我们执行以下语句：

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a

微信截图_20210810165445.png

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，当使用 right join 时，右表时驱动表，左表是被驱动表， 当使用 inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

该语句的执行流程大致如下：

从表 t2 中读取一行数据（如果t2表有查询过滤条件，会从过滤结果里取出一行数据）
从第1步找到的数据行中，取出 a 字段到表 t2 里去查找
取出表 t1 中满足条件的行，跟 t2 获取到的结果组成一行，作为结果集的一部分
重复上面过程，直到找到所有结果

微信截图_20210810194657.png
整个过程会读取 t2 表的所有数据，这个过程扫描100行，然后遍历这每行数据中字段 a 的值。根据 t2 表中 a 的值索引扫描 t1 表中的对应行，因为 t1 表查询走了索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行，因此整个过程扫描了 200 行，判断次数为 100*1 = 100 次。

在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为嵌套循环连接（Index Nested-Loop Join，NLJ）

Block Nested-Loop Join—块嵌套循环连接

我们执行另外一条语句，b 字段上没有索引。

EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b

微信截图_20210810175141.png
上面查询语句的执行过程大致如下：

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

微信截图_20210810195503.png
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000 (表 t1 的数据总量) + 100 (表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 10000= *100 万次。
两个表都做一次全表扫描，所以

总的扫描行数是 M+N；
内存中的判断次数是 M*N。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。
微信截图_20210810201801.png
可以看到，这时候由于表 t2 被分成了两次放入 join_buffer 中，最坏的情况可能 t1 中的记录每进行一次对比，都要把 t2 表中的所有记录重新加载一次。但是判断等值条件的次数还是不变的，依然是 (800+200)10000=*1000 万次。假设，驱动表的数据行数是 N，被驱动表的数据行数是 M。

扫描行数是 N+λNM（0<λ<1）
内存判断 N*M 次

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

关联查询优化策略

关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接（inner join）sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

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 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

Mysql专题

join 查询如何执行

Nested-Loop Join—嵌套循环连接

Block Nested-Loop Join—块嵌套循环连接

关联查询优化策略