关于mysql:MySQL-多表联合查询有何讲究

明天咱们来聊聊微信中的多表联结查问，应该是小表驱动大表还是大表驱动小表？

在正式剖析之前，咱们先来看两个关键字 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 查问关键字的效率会比拟高。

在 MySQL 中，这种多表联结查问的原理是：以驱动表的数据为根底，通过相似于咱们 Java 代码中写的 嵌套循环 的形式去跟被驱动表记录进行匹配。

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

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

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

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

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

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

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

从这两个数据比照中咱们就能看进去，小表驱动大表效率要高。外围的起因在于，搜寻被驱动的表的时候，个别都是有索引的，而索引的搜寻就要快很多，搜寻次数也少。

后面第二大节咱们得出的论断有一个前提，就是驱动表和被驱动表之间关联的字段是有索引的，以咱们后面的表为例，就是 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 的大小：

262144/1024=256KB

默认大小是 256 KB。

我当初把这个值改大，而后再查看新的执行打算，如下：

大家看到，此时曾经没有 Using join buffer (Block Nested Loop) 提醒了。

总结一下：

如果 join_buffer 足够大，一次性就能读取所有数据到内存中，那么大表驱动小表还是小表驱动大表都无所谓了。
如果 join_buffer 大小无限，那么倡议小表驱动大表，这样即便要分块读取，读取的次数也少一些。

不过诚实说，这种没有索引的多表联结查问效率比拟低，应该尽量避免。

综上所述，在多表联结查问的时候，倡议小表驱动大表。

关于mysql:MySQL-多表联合查询有何讲究

1. in VS exists

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

3. 没有索引咋办？