好文推荐 | 一文弄懂Join语句优化

这一篇文章就来介绍一下关联查询的优化，文章有点长，请耐心看完，有问题欢迎讨论指正。

1 关联查询的算法特性总结

要想弄懂关联查询的优化，就必须先知道关联查询相关的算法：

循环驱动表中的每一行

再到被驱动表找到满足关联条件的记录

因为关联字段没索引，所以在被驱动表里的查询需要全表扫描

这种方法逻辑简单，但是效率很差

比如驱动表数据量是 m，被驱动表数据量是 n，则扫描行数为 m * n

当然，好在，MySQL也没有采用这种算法，即使关联字段没索引，也会采用Block Nested-Loop Join或者Hash Join，等下会细说。

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里，通过索引匹配，取出满足条件的行，然后取出两张表的结果合集。

为了方便理解，我们会结合实验进行讲解，先来创建测试表并写入测试数据：

use martin; drop table if exists t1; CREATE TABLE `t1` (`id` int NOT NULL auto_increment,`a` int DEFAULT NULL,`b` int DEFAULT NULL,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPCOMMENT '记录更新时间',PRIMARY KEY (`id`),KEY `idx_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; 
delimiter ;;create procedure insert_t1()begindeclare i int; set i=1;while(i<=10000)doinsert into t1(a,b) values(i, i); set i=i+1; end while;end;;delimiter ; call insert_t1(); 
drop table if exists t2; create table t2 like t1; insert into t2 select * from t1 limit 100;

我们来看一个例子：

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

Tips：表 t1 和表 t2 中的 a 字段都有索引。

执行计划如下：

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

我们一起看看下面这条 SQL 语句：

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

Tips：表 t1 和表 t2 中的 b 字段都没有索引

在 MySQL 5.7 版本下的执行计划如下：

核心思想是将驱动表的数据加载到内存中构建哈希表

然后逐行读取被驱动表的数据，并通过哈希函数将连接条件的列的值映射为哈希值，去之前构建的Hash表查找匹配的记录

一旦在Hash表中找到匹配的记录，对这些记录进行一一比较，得出最终的Join结果

跟Block Nested-Loop Join算法类似，但是不需要将被驱动表的数据块写入内存或磁盘，更少的IO以及更节省资源

6 Batched Key Access算法

在学了 NLJ 和 BNL 算法后，你是否有个疑问，如果把 NLJ 与 BNL 两种算法的一些优秀的思想结合，是否可行呢？

比如 NLJ 的关键思想是：被驱动表的关联字段有索引。

而 BNL 的关键思想是：把驱动表的数据批量提交一部分放到 join_buffer 中。

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

其原理是：

将驱动表中相关列批量放入 join_buffer 中

批量将关联字段的值发送到 Multi-Range Read(MRR) 接口

MRR 通过接收到的值，根据其对应的主键 ID 进行排序，然后再进行数据的读取和操作

返回结果给客户端。

7 补充下MRR相关知识

当表很大并且没有存储在缓存中时，使用辅助索引上的范围扫描读取行可能导致对表有很多随机访问。

而 Multi-Range Read 优化的设计思路是：查询辅助索引时，对查询结果先按照主键进行排序，并按照主键排序后的顺序，进行顺序查找，从而减少随机访问磁盘的次数。

使用 MRR 时，explain 输出的 Extra 列显示的是 Using MRR。

控制MRR的参数

optimizer_switch 中 mrr_cost_based 参数的值会影响 MRR。

如果 mrr_cost_based=on，表示优化器尝试在使用和不使用 MRR 之间进行基于成本的选择。

如果 mrr_cost_based=off，表示一直使用 MRR。

更多 MRR 信息请参考官方手册：https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html。

8 BKA开启

先来看下这条SQL的执行计划：

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

9 优化关联查询

扯了这么多，我们就来讲一下：究竟怎样优化关联查询：

关联字段添加索引

通过上面的内容，我们知道了 BNL、NLJ 和 BKA 的原理，因此让 BNL（Block Nested-Loop Join）或者Hash Join变成 NLJ （Index Nested-Loop Join）或者 BKA（Batched Key Access），可以提高 join 的效率。我们来看下面的例子

我们构造出两个算法对于的例子：

Block Nested-Loop Join 的例子：

select * from t1 join t2 on t1.b= t2.b;

需要 0.08 秒。

Index Nested-Loop Join 的例子：

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

只需要 0.01 秒。

再对比一下两条 SQL 的执行计划：

选择小表作为驱动表

从上面几种Join算法，也能看出来，驱动表需要全表扫描，再存放在内存中

如果小表是驱动表，那遍历的行也会更少。

来举个例子，看下大小表做驱动表执行计划的对比：

我们来看下以 t2 为驱动表的 SQL：

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

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

再看下以 t1 为驱动表的 SQL：

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

我们对比下两条 SQL 的执行计划：

版本升级

前面也提到了，如果被驱动表的关联字段没索引，在MySQL 8.0.20版本之前，就会使用 Block Nested-Loop Join(简称：BNL)，

从 MySQL 8.0.20 开始，MySQL 不再使用 Block Nested-Loop Join 算法，并且在以前使用 Block Nested-Loop Join 算法的所有情况下都使用 Hash Join 优化。

相对于Block Nested-Loop Join算法，Hash Join不需要将被驱动表的数据块写入内存或磁盘，使用更少的IO以及更节省资源。

所以，假如有条件，可以升级到8.0.20之后的版本。

关于Join语句的优化，我们就聊到这里，欢迎在留言区讨论。