读书笔记之 MySQL 两个表的连接原理

读书笔记之 MySQL 两个表的连接原理

本篇是《MySQL是怎样运行的》第11章 两个表的亲密接触 -- 连接的原理读书笔记，这里做一些记录和汇总，方便日后复习。

上一篇介绍了单表查询的一些访问方法，如果连表查询时怎么分析呢？

连接

把多个表的记录连起来组成一个更大的新的记录，这个查询过程就是连接查询。

笛卡尔积

如果连接查询的结果集包含一个表中的每一条记录与另一个表的每条记录相互匹配的组合，那么这样的结果集就可以称为笛卡尔积。

内连接与外连接

INNER JOIN ON ... 为内连接，匹配两表中指定条件完全一致的记录，进行查询的连接。

左外连接（LEFT OUTER JOIN ON ...） ，右外连接（RIGHT OUTER JOIN ON ...） 为外连接，专门用来补充 “外连接驱动表中的记录在被驱动表找不到匹配记录时，是否应该把该驱动表记录加入到结果集中”，这个场景提出的。

如果用外连接查询时，被驱动表中不存在与驱动表相匹配记录时，仍然把驱动表的该记录加入结果集，且被驱动表对应的列用NULL填充。

内连接的左和右结果等价，所以只称呼内连接；外连接因为左右连接时驱动表的归属不一致，进而结果集不同，所以需要单独区分。

左外连接、右外连接可以简称为左连接和右连接，关键字简写为 LEFT JOIN 与 RIGHT JOIN。

左连接查询时左表为驱动表，右表为被驱动表；右连接查询时右表为驱动表，左表为被驱动表。

连接的原理

对于两表连接具体实现，可以拆分成 => 驱动表单表查询 + 被驱动表多次单表查询。

被驱动表每次单表查询时，ON条件都对应着驱动表中获取的具体值，相当于ON条件做常量等值匹配的单表查询。

驱动表只会查询一次，但被驱动表却要访问好多次，具体次数与驱动表执行单表查询后的结果集中有多少条数据记录有关。类似

for (r in records){
	for( r2 in records2)){
	  match(r, r2)
	}
}

嵌套循环连接

这个过程就像多个循环嵌套再一起，所以这种 “驱动表访问一次，被驱动表却可能访问多次，且访问次数取决于驱动表执行单表查询后的结果集中有多少条记录”的连接执行方式称为嵌套循环连接（Nested-Loop join）。

具体过程如下：

• 步骤1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询
• 步骤2. 对步骤1中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录

PS:

嵌套循环连接算法来说，每当我们从驱动表中得到了一条记录时，就根据这条记录立即到被驱动表中查询一次。如果匹配就把组合后的记录发给客户端，然后再到驱动表中获取下一条记录；这个过程重复进行。

不要误以为把驱动表中的所有记录都先查出来放到某个地方，然后再遍历这些记录。

换言之，这种惯性的想法也是对 嵌套循环连接的一种优化直觉，单笔匹配改成批量匹配

总的连接成本 = 驱动表单表查询成本 + N*被驱动表单表查询成本，N 指驱动表查询的结果集的记录数量。

优化思路就集中在后半部分：

• 使用索引加快查询速度
• 使用块嵌套循环查询减少循环次数 N

使用索引加快连接速度

索引加速

在嵌套连接中，被驱动表需要访问多次，每一次查询相当于一次单表查询，可以使用索引来优化。

在涉及两个表的过滤条件，在查询被驱动表时对应值已经是确认的了，就相当于常数查询。

So，我们可以为关联条件建立索引，从all 扫全表变成 const/ref 查询，提升查询效率。

单表中使用主键值或者唯一二级索引列的值进行等值查找的方式 是 const，普通二级索引等值查询是 ref；
而在连接查询中对被驱动表的主键或者不允许为 NULL的唯一二级索引进行等值查找使用的方式称为 eq_ref。

索引覆盖减少回表

使用索引就要考虑回表的成本，如果查询被驱动的列都是某个二级索引的一部分，那么就可以使用 扫描全部二级索引(index访问方法)来查询。

因此 连接查询时注意查询结果，最好不要把 * 作为查询列表，最好做到被驱动表的索引覆盖。

基于块的嵌套循环连接

使用索引以及索引覆盖是一种优化思路，优化的是单次查询的效率；另一思路是减少被驱动表的查询次数。

前面我们已经知道了，驱动表结果集中有多少记录，就可能把被驱动表加载多少次。

如果被驱动表非常大，那么 I/O 成本就很高（缓冲区存不下，会内存置换，就会重复从磁盘加到内存）。

能否把被驱动表中的记录加载到内存中，再一次性地与驱动表中的多条记录进行匹配呢?

Join Buffer(连接缓冲区)的概念就被提出来了。

Join Buffer 就是在执行连接查询前申请的一块固定大小的内存。先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与Join Buffer 中的 多条驱动表记录进行匹配。

匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的 I/O 代价。

最理想的情况时Join Buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作。

这种加入 Join Buffer 的嵌套循环连接算法成为基于块的嵌套循环连接（Block Nested-Loop Join）算法。

使用 join_buffer_size 来配置 Join Buffer 大小，默认为256KB，最小可设为 128字节。

另外Join Buffer 存的并不是 驱动表 的所有列，只是查询列表中的列和过滤条件中的列。So，不要用 * 作为查询列表，只查必要字段，可以在 Join Buffer 中放更多的记录。

总结

连接查询主要分为内连接和外连接。

查询过程可以抽象成两个for循环，外层表（驱动表）结果集的记录依次去内层表（被驱动表）中循环匹配，这个过程就是嵌套循环连接。

for 循环里有耗时操作，直觉上就有性能问题，这是优化的核心。

两个方向：

• 减少耗时：使用索引查询，能索引覆盖，就减少了回表成本。
• 减少循环次数：引入Join Buffer 保存驱动表结果集的一部分，相当于外层 每次 for 循环的步长 从1 变成了 X， 减少了内层耗时循环的次数。