join 查询是如何执行的

join 联表查询的性能

有时候可能会听说尽量不要使用 join 联表查询，或者更直接一点就禁止使用 join 联表查询，这时如果我们要查询的数据分布在两张关联表中，就不得不先去一张表里面查出要查询的数据，然后再根据关联字段去另一张表里面查出数据，但你有想过从查询性能上来说，这样做的查询速度就一定比 join 联表查询速度要快吗？不妨来看一个具体的例子，下面是活动表和城市编码表的表结构，活动表里面的 city_code 对应于城市编码表中的 code 字段:

create table activities
(
    id bigint unsigned auto_increment comment '主键' primary key,
    name varchar(255) default '' not null comment '活动名称',
    city_code varchar(64) default '' not null comment '城市编码',
    start_time timestamp default CURRENT_TIMESTAMP not null comment '开始时间',
    end_time timestamp default CURRENT_TIMESTAMP not null comment '结束时间',
    status tinyint unsigned default '0' not null comment '状态:0-待启用,1-待生效,2-生效中,3-已结束,4-已禁用',
    create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
    update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
) comment '活动表' CHARSET=utf8mb4;
create index idx_city_code on activities(city_code);
create index idx_create_time on activities (create_time);
create index idx_end_time_start on activities (end_time, start_time);

create table cities
(
    id bigint unsigned auto_increment comment '主键' primary key,
    name varchar(255) default '' comment '城市名称',
    code varchar(64) default '' not null comment '城市编码'
) comment '城市编码表';
create index idx_name on cities(name);
create unique index uk_code on cities (code);

现在如果要查询出活动的基本信息，包含城市名称，这时候就有两种方法，一种是先从活动表查出 city_code 列的值，然后用 city_code 列的值去 cities 表中查询，还一种就是直接 join 联表查询，先来看第一种，首先去查 activities 表，得到结果之后将 city_code 列字段提取出来组装成 list，再去 cities 表里面做 in 查询，对应 SQL 分别是：

select * from activities;
select * from cities where code in (cityCodeList);

上面 activities 表中有 1000 条数据，cities 表里面有 3525 条数据，正常来说上面两个 SQL 其实都不算耗时操作，第一个仅仅是全表扫描 1000 行，第二个则是通过索引树所搜索 1000 行，加起来也就是扫描 2000 行，然后就是需要和 MySQL 服务建立两次连接

接下来再来看另一种直接进行 join 联表查，通过城市编码字段进行关联查询，对应 SQL explain 结果如下：

从 explain 结果来看，对表 t1 采取的是全表扫描，扫描 1000 行，表 t2 采取的是等值索引查询，整体的执行流程是这样的:

1.先从 activities 表里面读出一行数据，提取出 city_code 字段的值

2.然后去 cities 表中对 code 字段进行索引树搜索，找到了则保存到结果里面

3.继续重复步骤 1，2，直到 activities 表中的数据都读取完毕

其实也就是遍历 activities 表，对每一条数据都去 cities 表里面查询，同样也是扫描 activities 表 1000 行，然后循环 1000 次去 cities 表里面搜索，加起来也是扫描 2000 行，同时只需要建立一次连接，但如果采用第一种方式的话，在 activities 表数据量更多的情况下还需要在业务代码里面进行分段 in 查询，这么看来使用 join 查询反而会更好一些。

join 查询的执行流程

既然使用 join 联表反而会更好一些，那什么情况下我们不能使用 join 查询呢，如果你仔细看的话，上面参与 join 联表查询的字段无论是 activities 表里面的 city_code 还是 cities 表里面的 code 字段，这两个字段上其实都是建有索引的，如果我们把这两个字段上的索引都去掉会怎么样呢？

DROP INDEX idx_city_code ON activities;
DROP INDEX uk_code ON cities;

执行完上面的删除索引的语句后，再来看使用 join 查询 explain 的结果:

从图中可以看到，这时无论 activities 表还是 cities 表都是全表扫描，暂时先不去看 t1 表 Extra 里面的信息的话，整体查询流程是这样的:
1.先从 activities 表里面读出一行数据，提取出 city_code 字段的值

2.然后去 cities 表中对 code 字段全表扫描，找到了则保存到结果里面

3.继续重复步骤 1，2，直到 activities 表中的数据都读取完毕

由于步骤二里面不再是索引搜索而是全表扫描，于是步骤二里面每次都要扫描 3525 行数据，对 cities 表的扫描行数就是 1000 * 3525 行，总的扫描行数就是 1000 + 1000 * 3525 行，相比有索引的情况下扫描行数直接提高了数倍，这时 join 查询也就不再有优势了。

这里还有两个问题需要注意，先说第一个问题，上面提到如果不去看 t1 表 Extra 里面的信息的话，执行流程是上面这样的，那 t1 表 Extra 里面的信息代表什么意思呢？你可以去看下之前的文章 <Explain 使用解析一>，里面描述了什么情况下会出现这种信息，其实就是当被驱动表的联表字段上没有索引的时候就会出现，这里的 Using join buffer (hash join) 是 MySQL 8.0 版本之后才会出现的，而我测试使用的正是 8.0 版本的 MySQL，如果是之前的版本的话，Extra 里面应该是 Using join buffer (Block Nested Loop)，我们分开来介绍这两种情况。

Using join buffer (Block Nested Loop)

上面我们说不去看 Extra 里面的信息执行的话，需要扫描的行数是 1000 * 3525，主要是由于步骤二里面去被驱动表里面查询的时候每次都是全表扫描，因此如果驱动表数据量为 M，被驱动表数据量为 N，总的扫描行数就是 M + M * N，如果这时两张表的数据量都是 10000，总的扫描行数就上亿了，这样的查询代价就太大了，因此 MySQL 进行了优化，首先将步骤一中驱动表查询得到的数据整个放入 join buffer 里面，然后扫描被驱动表，再和 join buffer 里面的数据进行对比，整体执行流程就变成下面这样：

1.全表扫描 activities，将查询得到的数据全部放入 join buffer 内存中，保存的是一个无序数组

2.全表扫描 cities，每扫描一行数据都去 join buffer 内存中遍历步骤一放入的 1000 条数据，判断是否满足 join 条件

这样的话，扫描行数就变成 1000+3525，然后就是多了步骤二里面 join buffer 内存中 3525 * 1000 次的判断，由于判断是在内存中进行的，因此速度并不算慢，执行效率对应的计算方式也由 M + M * N 变成 M+N 外加 M * N 次内存判断。

值得注意的是如果步骤一中驱动表中的数据太大，join buffer 内存不够的话怎么办呢？这时会采用**分段(block)**的方式存入 join buffer，若是采用的分段的方式，假设 join buffer 只能存储 100 条数据，执行流程如下：

1.全表扫描 activities，将查询得到的数据放入 join buffer 内存中，保存的是一个无序数组，到了 100 条的时候 join buffer 内存满了，执行下一步

2.全表扫描 cities，每扫描一行数据都去 join buffer 内存中遍历步骤一放入的 100 条数据，判断是否满足 join 条件

3.清空 join buffer，接着扫描 activities 表，将剩下 900 条里面的 100 条继续放入 join buffer，接着执行步骤二，直到驱动表的数据全部扫描完毕

采取分段的方式，扫描行数为 1000+1000/100 * 3525，内存中的判断次数依然是 3525 * 1000 次，扫描行数对应的计算方式为 M+M/buffer_size * N=M+(M * N)/buffer_size，执行效率对应就是 M+(M * N)/buffer_size 扫描行数外加 M * N 次内存判断。

Using join buffer (hash join)

上面是 MySQL 8.0 之前的 Using join buffer (Block Nested Loop) 的执行过程，同样的如果两张表的数据量都是 10 万的话，那么就要在内存中进行 100 亿次的判断，尽管是在内存中进行判断，但执行 100 亿次的判断也是挺耗时的，于是 MySQL 8.0 做了进一步的优化。

上面由于步骤一里面将驱动表的数据放入 join buffer 内存时保存的是一个无序数组，所以步骤二每次和 join buffer 里面的数据对比都要循环遍历进行判断，但如果保存的是一个 hash 的结构，步骤二里面就只要将每一行数据都在 hash 里面进行一次查找定位就可以了，优化之后的执行流程如下：

1.全表扫描 activities，将查询得到的数据全部放入 join buffer 内存中，保存的是一个 hash 结构

2.全表扫描 cities，每扫描一行数据都去 join buffer 内存 hash 结构中查找一次，找到了则表示满足 join 条件

优化之后，扫描行数还是 1000+3525，但 3525 * 1000 次的判断变成了 3525 次查找，如果是两个大表，执行速度相比之前也会提高许多，执行效率对应就是 M+N 扫描行数外加 N 次hash 查找。

为什么要用小表作为驱动表

接下来看第二个问题，同时如果你仔细看的话会发现，我故意将 SQL 语句中 join 表的顺序调换了，将 cities 表放在前面，从 SQL 语句看起来应该是 cities 作为驱动表，activities 表作为被驱动表才对，但从 explain 的执行结果来看，MySQL 没有按照我们书写的顺序来进行执行，因为 activities 表有 1000 条数据，cities 表有 3525 条数据，相对来说 activities 是小表作为驱动表，cities 是大表作为被驱动表，所以它是先去查询 activities 表，再去查 cities 表，从这一点也可以看出，我们无需可以调整我们的 SQL 语句中 join 表的前后顺序，MySQL 会自动调整，将小表作为驱动表，大表作为被驱动表，那为什么要使用小表作为驱动表呢。

从执行效率来看的话，在被驱动表可以使用索引的情况下，执行效率是 M+N，没法用上索引的情况下如果是 Using join buffer (Block Nested Loop)，执行效率是 M + N 或者 M+(M * N)/buffer_size 外加 M * N 次内存判断，如果是 Using join buffer (hash join) 执行效率是 M+N 扫描行数外加 N 次内存 hash 查找，其中 M 表示驱动表的数据量，N 表示被驱动表的数据量，可以得出在不考虑内存中的判断和 hash 查找操作的话:

1.在有索引的情况下驱动表的 M 是全表扫描，被驱动表的 N 是索引搜索，固然 M 越小越好，应使用小表作为驱动表

2.没法用上索引(Block Nested Loop)并且 join buffer 内存足够的情况下，执行效率是 M+N，无论驱动表还是被驱动表哪个更小都是全表扫描，所以这种情况下就无所谓哪张表作为驱动表了

3.没法用上索引(Block Nested Loop)并且 join buffer 内存不够需要分段的情况下，执行效率是 M+(M * N)/buffer_size，显然 M 对结果的影响更大，因此也应尽量使用小表作为驱动表

4.没法用上索引(hash join)并且 join buffer 内存足够的情况下，执行效率是 M+N，同理也无所谓哪张表作为驱动表了

5.没法用上索引(hash join)并且 join buffer 内存不够需要分段的情况下，执行效率是 M+(M/buffer_size) * N，同理也是应尽量使用小表作为驱动表

综上几点可以得出在进行 join 查询时，最好是让小表作为驱动表，大表作为被驱动表来进行查询。

如何判断小表大表

在 join 联表查询时既然都说用小表作为驱动表，大表作为被驱动表，那么怎么去判断哪张表是大表，哪张表是小表呢，你可能会说当然数据量大的是大表，数据量小的是小表了，那真的是这样的吗？为了看出效果，我把上面两张表的索引都重新加上了，来看下面的几个例子:

同样是最开始的那个 SQL，只是在最后面给 cities 表加上了一个 where 条件，这时候虽然 cities 表的数据量更大，但是在这个 SQL 里面参与 join 的数据量其实只有一条数据(唯一索引)，所以在这个 SQL 里面 cities 应该作为驱动表，因为参与 join 联表查询的数据量更小。

还有一种情况就是当两张表参与 join 查询的数据量一样时，这时还会去看需要查询的字段大小，MySQL 也会考虑将查询字段更少的表作为驱动表，比如说下面这两个 SQL:

explain select activities.*, cities.code from activities join cities on activities.city_code = cities.code where cities.code <= 1000;
explain select activities.code, cities.* from activities join cities on activities.city_code = cities.code where cities.code <= 1000;

在没有索引的情况下，放到 join buffer 里面的字段是不一样的，第一个 SQL，cities 表只要放入 code 字段加上 id，而 activities 表则是放入整张表的字段，第二个 SQL 则刚好相反，因此 MySQL 采取的也是将放入 join buffer 里面数据更小的表作为驱动表，这样可以避免分段或者分的段数更少。

因此，我们在判断大表小表的时候，不单单是看表里面的数据量，而是要看实际参与 join 查询的数据量的大小，也就是经过条件过滤之后真正参与 join 的数据以及真正查询的字段的大小。