join语句优化

关联查询的算法

MySQL 使用以下两种嵌套循环算法或它们的变体在表之间执行连接：

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

Nested-Loop Join 算法

Nested-Loop Join(NLJ)算法一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。们试想一下，如果在被驱动表中这个关联字段没有索引，那么每次取出驱动表的关联字段在被驱动表查找对应的数据时，都会对被驱动表做一次全表扫描，成本是非常高的（比如驱动表数据量是m，被驱动表数据量是 n，则扫描行数为 m * n ）。好在 MySQL 在关联字段有索引时，才会使用 NLJ，如果没索引，就会使用 Block NestedLoop Join。

select * from t1 inner join t2 on t1.a = t2.a; 

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

驱动表是 t2，被驱动表是t1。原因是：explain分析join语句时，在第一行的就是驱动表；选择 t2 做驱动表的原因：如果没固定连接方式（比如没加straight_join）优化器会优先选择小表做驱动表。所以使用 innerjoin时，前面的表并不一定就是驱动表。
使用了 NLJ。原因是：一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer（***）；则表示使用的 join 算法是 NLJ。

sql1 的大致流程如下：

从表 t2 中读取一行数据；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行。

Block Nested-Loop Join 算法

Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。

//表 t1 和表 t2 中的 b 字段都没有索引
select * from t1 inner join t2 on t1.b = t2.b;

sql2 的执行流程：

把 t2 的所有数据放入到 join_buffer 中；
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比；
返回满足 join 条件的数据；

如果被驱动表的关联字段没索引，为什么会选择使用 BNL 算法而不继续使用 Nested-Loop Join 呢？

在被驱动表的关联字段没索引的情况下，比如 sql2：如果使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。如果使用 BNL，那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。显然后者磁盘扫描的次数少很多，因此是更优的选择。因此对于 MySQL 的关联查询，如果被驱动表的关联字段没索引，会使用 BNL 算法。

Batched Key Access 算法

从 MySQL 5.6 开始，出现了这种集NLJ和BNL两种算法优点于一体的新算法：Batched Key Access(BKA)。其原理是：

将驱动表中相关列放入 join_buffer 中；
批量将关联字段的值发送到 Multi-Range Read(MRR) 接口；
MRR 通过接收到的值，根据其对应的主键ID进行排序，然后再进行数据的读取和操作；
返回结果到客户端；

开启BKA命令：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

参数解释：

mrr=on 开启 mrr
mrr_cost_based=off 不需要优化器基于成本考虑使用还是不使用 MRR，也就是一直使用MRR

batched_key_access=on 开启 BKA

explain select * from t1 inner join t2 on t1.a = t2.a;

优化关联查询

关联字段添加索引

在被驱动表的关联字段上添加索引，让BNL变成NLJ或BKA,可以明显优化关联查询。

小表做驱动表

select * from t1 straight_join t2 on t1.a = t2.a;

使用 straight_join 可以固定连接方式，让前面的表为驱动表

select * from t1 straight_join t2 on t1.a = t2.a;

MySQL-08丨join语句优化