MySQL 多表联合查询有何讲究？

2022-04-01 12:00 浏览：489

今天我们来聊聊微信中的多表联合查询，应该是小表驱动大表还是大表驱动小表?

1. in VS exists

在正式分析之前，我们先来看两个关键字 in 和 exists。

假设我现在有两张表：员工表和部门表，每个员工都有一个部门，员工表中保存着部门的 id，并且该字段是索引;部门表中有部门的 id、name 等属性，其中 id 是主键，name 是唯一索引。

这里我就直接使用 vhr 中的表来做试验，就不单独给大家数据库脚本了，小伙伴们可以查看 vhr 项目(https://github.com/lenve/vhr)获取数据库脚本。

假设我现在想查询技术部的所有员工，我有如下两种查询方式：

第一种查询方式是使用 in 关键字来查询：

select * from employee e where e.departmentId in(select d.id from department d where d.name='技术部') limit 10;

这个 SQL 很好理解，相信大家都能懂。查询的时候也是先查询里边的子查询(即先查询 department 表)，然后再执行外表的查询，我们可以看下它的执行计划：

可以看到，首先查询部门表，有索引就用索引，没有索引就全表扫描，然后查询员工表，也是利用索引来查询，整体上效率比较高。

第二种是使用 exists 关键字来查询：

select * from employee e where exists(select 1 from department d where d.id=e.departmentId and d.name='技术部') limit 10;

这条 SQL 的查询结果和上面用 in 关键字的一样，但是查询过程却不一样，我们来看看这个 SQL 的执行计划：

可以看到，这里先对员工表做了全表扫描，然后拿着员工表中的 departmentId 再去部门表中进行数据比对。上面这个 SQL 中，子查询有返回值，就表示 true，没有返回值就表示 false，如果为 true，则这个员工记录就保留下来，如果为 false，则这个员工记录会被抛弃掉。所以在子查询中的可以不用 SELECT *，可以将之改为 SELECT 1 或者其他，MySQL 官方的说法是在实际执行时会忽略SELECT 清单，因此写啥区别不大。

对比两个查询计划中的扫描行数，我们就能大致上看出差异，使用 in 的话，效率略高一些。

如果用 in 关键字查询的话，先部门表再员工表，一般来说部门表的数据是要小于员工表的数据的，所以这就是小表驱动大表，效率比较高。

如果用 exists 关键字查询的话，先员工表再部门表，一般来说部门表的数据是要小于员工表的数据的，所以这就是大表驱动小表，效率比较低。

总之，就是要小表驱动大表效率才高，大表驱动小表效率就会比较低。所以，假设部门表的数据量大于员工表的数据量，那么上面这两种 SQL，使用 exists 查询关键字的效率会比较高。

2. 为什么要小表驱动大表

在 MySQL 中，这种多表联合查询的原理是：以驱动表的数据为基础，通过类似于我们 Java 代码中写的嵌套循环的方式去跟被驱动表记录进行匹配。

以第一小节的表为例，假设我们的员工表 E 表是大表，有 10000 条记录;部门表 D 表是小表，有 100 条记录。

假设 D 驱动 E，那么执行流程大概是这样：

for 100 个部门{
    匹配 10000 个员工(进行B+树查找)
}

那么查找的总次数是 100+log10000。

假设 E 驱动 D，那么执行流程大概是这样：

for 10000 个员工{
    匹配 100 个部门(进行B+树查找)
}

那么总的查找次数是 10000+log100。

从这两个数据对比中我们就能看出来，小表驱动大表效率要高。核心的原因在于，搜索被驱动的表的时候，一般都是有索引的，而索引的搜索就要快很多，搜索次数也少。

3. 没有索引咋办?

前面第二小节我们得出的结论有一个前提，就是驱动表和被驱动表之间关联的字段是有索引的，以我们前面的表为例，就是 E 表中保存了 departmentId 字段，该字段对应了 D 表中的 id 字段，而 id 字段在 D 表中是主键索引，如果 id 不是主键索引，就是一个普通字段，那么 D 表岂不是也要做全表扫描了?那个时候 E 驱动 D 还是 D 驱动 E 差别就不大了。

对于这种被驱动表上没有可用索引的情况，MySQL 使用了一种名为 Block Nested-Loop Join (简称 BNL)的算法，这种算法的步骤是这样：

把 E 表的数据读入线程内存 join_buffer 中。
扫描 D 表，把 D 表中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

小伙伴们来看下，如果我把 E 表中 departmentId 字段上的索引删除，再把 D 表中的 id 字段上的主键索引也删除，此时我们再来看看如下 SQL 的执行计划：

可以看到，此时 E 表和 D 表都是全表扫描，另外需要注意，这些比对操作都是在内存中，所以执行效率都是 OK 的。

但是，既然把数据都读入到内存中，内存中能放下吗?内存中放不下咋办?我们看上面的查询计划，对 E 表的查询中，Extra 中还出现了 Using join buffer (Block Nested Loop)，Block 不就有分块的意思吗!所以这意思就很明确了，内存中一次放不下，那就分块读取，先读一部分到内存中，比对完了再读另一部分到内存中。

通过如下指令我们可以查看 join_buffer 的大小：