共计 2417 个字符，预计需要花费 7 分钟才能阅读完成。
明天咱们来聊聊微信中的多表联结查问，应该是小表驱动大表还是大表驱动小表？
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 的大小：
262144/1024=256KB
默认大小是 256 KB。
我当初把这个值改大，而后再查看新的执行打算，如下：
大家看到，此时曾经没有 Using join buffer (Block Nested Loop) 提醒了。
总结一下：
- 如果 join_buffer 足够大，一次性就能读取所有数据到内存中，那么大表驱动小表还是小表驱动大表都无所谓了。
- 如果 join_buffer 大小无限，那么倡议小表驱动大表，这样即便要分块读取，读取的次数也少一些。
不过诚实说，这种没有索引的多表联结查问效率比拟低，应该尽量避免。
综上所述，在多表联结查问的时候，倡议小表驱动大表。
