SQL 军规到底能不能用 join

互联网上一直流传着各大公司的 MySQL 军规，其中关于 join 的描述，有些公司不推荐使用 join，而有些公司则规定有条件的使用 join, 它们都是教条式的规定，也没有详细说其中的原因，这就很容出现只知道这么用，但是不知道为什么的情况

那到底能不能使用 join, 什么情况下适合用join，什么情况下不适合用 join， join 有哪些使用原则呢？

本文将详细讲述 join 的执行流程、分析 join 的复杂度，并解答上面的几个常见问题，让读者能详细了解 join 的原理，做到知其然，知其所以然

测试数据准备

为了更好的分析 join 的工作原理，需要准备一些测试数据，我们分别创建 ta 和 tb 两个表，定义 insert_data 函数，然后往 ta 、 tb 表添加测试数据，具体如下所示：

• 创建测试表

Create Table: CREATE TABLE `ta` (
  `id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 定义插入数据函数

定义一个插入数据的函数，用于往 tb 表中插入数据

delimieter ;;

create procedure insert_data()

begin

    declare i int;
    
    set i=1;
    
    while( i <= 1000 ) do
    
        insert into tb values(i,i,i);
        
        set i=i+1;
        
    end while;
    
end;;

delimiter ;

• 添加测试数据

调用 insert_data 函数 往tb 表 插入1000条数据，tb表前100条数据插入到 ta 表中

mysql> call insert_data();

mysql> insert into ta (select * from tb where tb.a <= 100);

简述

MySQL中join主要用到 Simple nested-loop、Block nested-loop、Index nested-loop 三种算法，下面针对这几种算法的流程逐一进行分析，在这之前，有几点需要说明下

1. 本文所有测试都是在 mysql5.7 版本上进行的

2. 由于mysql会对join的连接方式做优化，为了便于分析 join 的执行过程，文中的 sql 语句 统一使用我们指定的连接方式执行查询，关键字是 straight_join

Simple nested-loop 算法

在 MySQL中执行 select * from ta straight_join tb on ta.a = tb.b; ，语句的 explain 的结果如下:

在这个语句中，ta 是驱动表，tb 是被驱动表，由于表 tb 上 b 字段没有索引，所以针对 tb 表需要走全表扫描

语句的执行流程如下：

1. 从 ta 表读取一条数据,记为Da
2. 从 Da 中取出字段 a 去 tb 表查询
3. 取出 tb 表满足条件的数据，跟 Da数据组合成一行，作为结果集返回
4. 接着读取 ta 表剩下的数据，重复执行步骤1 到 步骤3，直到读取完 ta 表记录

上面的流程是循环读取 ta 表记录，把每一行记录中的 a字段值， 去 tb 表中查询满足条件的记录，这个过程就类似下面代码中的嵌套for循环

for each row in ta matching range 
{
     for each row in tb matching 
     {
        if row satisfies join conditions, send to client
     }
}

• 性能分析

每读取一条 ta 表记录，就需要扫描一次 tb 表的记录并与记录中 b 字段的值进行比较

所以总共扫描了 100 * 1000 = 10 万 行记录，进行了 10万次 比较操作

可以看出，总的扫描行数和总比较次数跟 ta 和 tb 表总记录数有关，记录数越大，总扫描行数和比较次数越大

假如 ta 表和 tb 表总记录数 各增加 10 倍，那么，总的扫描行数和比较次数会增加 100 倍，这个效率就非常低下了

基于以上原因，MySQL 没有选择使用 Simple nested-loop 算法了，而是使用了 Block nested-loop 算法

Block nested-loop算法

Block nested-loop 算法对 Simple nested-loop 算法进行了优化，它引入了 join buffer

join buffer 主要用于优化不带索引条件的 join 查询，它会缓存连接 过程中的用到的字段，对于减少扫描次数和比较次数很有帮助

join_buffer_size 参数可以控制 join_buffer 的大小，MySQL 默认的

join_buffer_size 是 256K，可以通过 select @@join_buffer_size

查询目前设置的大小

现以执行 select * from ta straight_join tb on ta.a = tb.b; 语句为例来说说明 Block nested-loop的执行流程

1. 把 ta 表所有记录读入 join buffer 中，join buffer 只缓存连接过程中必须的数据，这里使用了 select * ，所以，ta 表的记录的所有字段都会放入 join buffer 中

2. 扫描 tb 表，读取每一行记录，跟 join buffer 中的数据对比，满足条件的数据，将会作为结果集返回

• 性能分析

整个过程中，读取 ta 表所有记录，读取 tb 表所有记录，相当于 扫描了一遍 ta 表和 tb表，所以扫描总行数是 100 + 1000 = 1100 行

逐行遍历 tb 表，与 join buffer 中的数据比较，由于 join buffer 是无序的，所以，对于 tb 表每一条记录，都需要在 join buffer 中比较 100 次，因此总的比较次数是 1000 * 100 = 10万次

和 Simple nested-loop 比起来，由于有 join buffer 的帮助，Block nested loop 总扫描行数减少了很多,总共扫描了 100 + 1000 = 1100 行

虽然两者总比较次数都是 10 万次，但是，Block nested loop 的比较是在内存中的 join buffer 中进行的，所以，速度上应该会快很多，性能相对也好一些

如果驱动表 ta 数据行数是 M ，被驱动表 tb 数据行数是 N ，总的扫描行数为 M + N , 总的比较次数为 M * N , 所以整个执行过程 近似的复杂度是：M + N + M * N

上述复杂度公式中，M 和 N 调换的话，复杂度不变，所以，这时候选择小表还是大表作为驱动表都一样

join buffer 大小不够用的问题

上面 Block nested loop 执行流程中，第一步是把 整个ta表数据全部读入 join buffer 中，这里由于 ta 表数据少，join buffer 可以放得下全部的数据

如果 join buffer 的大小不足以缓存 ta表所有数据，该怎么办呢？这时候的执行流程又是怎样的呢 ？

答案是：join buffer 分段缓存 ta 表数据，处理完之后，清空缓存，然后再缓存 ta 表剩下的数据

现假设 join buffer 只能放得下 60 行 ta 表记录，执行流程就变成了：

1. 遍历 ta 表，顺序读取 60 条记录放入 join buffer 中，此时，join buffer 满了

2. 遍历 tb 表，取出得每一行，跟 join buffer 中得数据比较，组合满足条件得数据，作为结果集返回

3. 清空 join buffer

4. 接着上次的位置继续顺序扫描 ta 表，把剩下得 40行数据读入 join buffer 中，紧接着执行第 2 步 到 第 4 步，直到 读取完 ta 表记录

• 性能分析

由于 ta 表是分两次读入 join buffer 的，所以需要扫描两次 tb 表，所以总扫描行数是 100 + 1000 * 2 = 2100 行

总的比较次数依然保持不变，是：(60 + 40) * 1000 = 10万次

从上面的结果可以看出，join buffer 分段缓存 的性能要比 一次缓存全部驱动表必需数据的方式 要差一些，也就是说，join buffer 分的段数越多，性能相对越差，在驱动表数量行数不变的情况下，分段数的取决于 join_buffer_size 参数的大小，这个参数越大，分段数或越小，反之越大， 所以有些地方建议通过调大 join_buffer_size 参数来提升 join 查询速度的方法，原因也在于此

如果驱动表 ta 数据行数是 M ，被驱动表 tb 数据行数是 N, join buffer 分段数是 K ，则总扫描行数为 M + K * N , 总的比较次数为 M * N , 所以整个执行过程 近似的复杂度是: M + K * N + M * N

显然 K 越小，复杂度越小，性能就越好，K 的最小值是 1 ，也就是 驱动表中所必需的字段能全部缓存到 join buffer 中

而 K 是与 驱动表数据行数 M 和 join_buffer_size 参数相关的，后者通常不会经常变化，所以 M 越小， K 就越小，K 越小，复杂度越小，性能就越好，K 的最小值是 1 ，也就是 驱动表中所必需的字段能全部缓存到 join buffer 中

因此，选择小表作为驱动表，查询性能更好

Index nested-loop算法

分析完上面两种算法，接着来看下 Index nested-loop 算法的执行流程

在 MySQL 中执行 select * from ta straight_join tb on ta.a = tb.a;, 语句的 explain 结果如下:

上述结果中，被驱动表 tb 的字段 a 上有索引，所以，连接的过程中能用上索引，它的执行流程是:

1. 读取一行 ta 表记录, 记为 Da

2. 从 Da 中取出 a 字段去 tb 表查询

3. 读取 tb 表中满足条件的记录，和 Da 组合，作为结果集返回

4. 重复执行 步骤1 到 步骤 3，直到读取完 ta 表的记录

• 性能分析

上面的流程是遍历 ta 表，然后从读出的每行记录中取出 a 的值 去 tb 表查询

由于 tb 表的 a 字段上有索引，所以查询 tb 表记录的时候，走的是 B+ 树的查询

我们准备的测试数据中 ta 表 a 字段和tb表 a 字段是一一对应的，因此对于 ta 表的一行记录，在 tb 表中需要做两次 B+ 树查询，一次是普通索引树的查询，一次是回表查询

总的扫描行数是: 100 + 100 = 200 行( tb 表的两次B+树查询是由扫描一次表导致的，所以这里把tb表总扫描次数当作100次)

总的比较次数是: 100 次

如果驱动表 ta 数据行数是 M ，被驱动表 tb 数据行数是 N, 对于驱动表的一行数据，被驱动表需要先查询 a 索引的 B+ 树，再查询主键索引 B+ 树，所以被驱动表查询一行的复杂度是：2 * log2N

总扫描行数为 M + M * 2 * log2N , 总的比较次数为 M , 所以整个执行过程 近似的复杂度是: M + M * 2 * log2N + M = 2 * M * ( 1 + log2N )

近似复杂度公式中，变量是 M 和 N , 很显然，M 对结果影响更大，M 表示的是 驱动表 的数据行数，因此，选择 小表 作为驱动表能显著降低复杂度，提升查询速度

结果分析

上面分析了 Simple nested loop 、 Block nested loop、Index nested loop 这三种算法的执行流程，并详细分析了每种算法的复杂度，根据分析的结果就可以回答本文开头的几个问题了

• 能不能使用join

1. 如果能用上被驱动表上的索引，即可以用上 Index nested loop 算法的话，是可以使用 join 的

2. 如果使用 Block nested loop 算法的话，扫描行数和比较次数比较多，会占用大量的系统资源，特别是对于大表来说，查询速度和系统性能是无法接受的，所以，这种情况下，能不用join就不用join了

如果 explain 结果的 Extra 字段包含 ' Using join buffer (Block Nested Loop) ' 字符串的话，表示使用了 ' Block nested loop ' 算法

• join 使用原则

在分析 Index nested loop 的复杂度之后，得出一个结论: 选择小表作为驱动表，查询性能更好

对于 Block nested loop 的复杂度分两种情况

1. join buffer 没有分段, 此时不论选择小表还是大表作为驱动表，复杂度都一样

2. join buffer 分段了，此时选择小表作为驱动表，复杂度更低，查询性能更好，相比join buffer 没有分段，实际的场景中，join buffer 分段的情况应该更多

综合来讲: 使用 join 应该选择小表 作为驱动表

• 如何选择 "小" 表

上面 join 使用原则 中讲到选择小表作为驱动表，这里的 小表 并不是指表数据行数少，而是指参与join的表，按照查询条件分别得到结果集，结果集小的表作为驱动表

比如：有以下两个 SQL 语句

语句1：select * from ta straight_join tb on ta.b = tb.b where tb.id <= 20;
语句2：select * from tb straight_join ta on ta.b = tb.b where tb.id <= 20;

ta 表和 tb表么一行数据大小是一样的，语句1 使用 ta 作为驱动表，需要把 ta 表所有行数据(100行)放入 join buffer 中，但是 语句2 使用 tb 作为驱动表，只需要把 tb表前20行数据放入 join buffer， 也即 tb 表只有20行数据参与join, 这么一比较，tb 表查询的结果集更小，所以应该选择数据行数更多但是查询的结果集更小的 tb 表作为驱动表

小结

本文详细讨论了 Simple nested loop 、 Block nested loop、Index nested loop 这三种算法，根据算法的执行流程定量的分析了各个算法的复杂度，再根据复杂度分析了 join 常见的一些问题，更多关于 join 的介绍请参考 MySQL 官网