前言
在平时的开发过程中，大家应该经常会用到join进行多个表的之间的关联查询，但是如果使用不合理的话，会导致查询性能下降。本文就MySQL中的关联查询的实现原理以及一些优化建议等内容做一个讲解。
关联查询介绍
关联查询，指两个或更多个表一起完成查询操作。
- 内连接（INNTER JOIN）
合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行，语法如下：
SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句;
- 返回的结果集是A表和B匹配的行。
- A表或者B表哪个表是驱动表(主表)或者被驱动表(从表)由查询优化器决定。
- 左连接(LEFT JOIN)
两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行。
#实现查询结果是A SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句;
- 结果集中返回匹配的行，也返回A表中不匹配的行，不匹配字段用NULL表示。
- A表是驱动表(主表)，B表是非驱动表(从表)。
- 右连接(Right JOIN)
两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。
#实现查询结果是B SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;
- 结果集中返回匹配的行，也返回B表中不匹配的行，不匹配字段用NULL表示。
- B表是驱动表(主表)，A表是非驱动表(从表)。
关联查询原理
前面讲解了连接查询的几种方式，现在谈谈MySQL底层是支持这几种连接查询的。
关联查询中涉及到多张表的的查询，根据驱动类型分为驱动表和被驱动表，驱动表就是主表，被驱动表就是从表。我们可以在执行计划中看出来。
- 执行计划从上向下看，上面的属于驱动表。
- 内连接的驱动表选择由查询优化器决定。
- 左连接的驱动表一般是左边的表，右连接的驱动表一般是右边的表。
了解了驱动表和被驱动表以后，现在我们看下MySQL究竟是怎么做join查询的。
简单嵌套循环连接
简单嵌套循环连接（Simple Nested-Loop join）是从驱动表A中取出一条数据，遍历表B，将匹配到的数据放到result.. 以此类推, 如下图所示：
- 算法简单粗暴，比如驱动表A有10条，被驱动表B有100条，那么扫描次数是A+A*B, 每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO，而IO是最大的瓶颈，所以效率低下，开销如下表：
| 开销统计 | 简单嵌套循环连接 |
| 驱动表扫描次数 | 1 |
| 被驱动表扫描次数 | A |
| 读取记录数 | A+B*A |
| JOIN比较次数 | B*A |
| 回表读取记录次数 | 0 |
- 当然MySQL默认没有采用这种算法。
块嵌套循环连接
块嵌套循环连接(Block Nested-Loop Join)是对上面一种算法的优化，竟然逐条的去驱动表中获取数据去匹配，和磁盘IO交互太多了，那么能否批量的方式呢？而这种算法就是借鉴了这样的思想。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列、缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。整体如下图所示：
- 注意一点，从驱动表中缓存的列不仅仅是关联的的列，select后面的列也会缓存起来。因此，为了能让join buffer缓存更多的数据，我们的SQL尽量不要
select *, 而是select 用到的字段。 - 整体的开销如下表所示：
| 开销统计 | 简单嵌套循环连接 | 块嵌套循环连接 |
| 驱动表扫描次数 | 1 | 1 |
| 被驱动表扫描次数 | A | A*used_column_size/join_buffer_size+1 |
| 读取记录数 | A+B*A | A+B*(A*used_column_size/join_buffer_size) |
| JOIN比较次数 | B*A | B*A |
| 回表读取记录次数 | 0 | 0 |
- join buffer的大小是可以设置的，默认情况下
join_buffer_size=256k。
show variables like '%join_buffer%';
索引嵌套循环连接
那还有没有效率更加高的关联查询算法呢？索引嵌套循环连接(Index Nested-Loop Join)就是效率最高的，前提条件是被驱动表的关联字段建立了索引。通过驱动表匹配条件直接与被驱动表的索引进行匹配，避免和内存表的每条记录去进行比较，这样极大的减少了对内存表的匹配次数。如下图所示：
- 整体的开销成本如下表所示：
| 开销统计 | 简单嵌套循环连接 | 块嵌套循环连接 | 索引嵌套循环连接 |
| 驱动表扫描次数 | 1 | 1 | 1 |
| 被驱动表扫描次数 | A | A*used_column_size/join_buffer_size+1 | 0 |
| 读取记录数 | A+B*A | A+B*(A*used_column_size/join_buffer_size) | A+B(match) |
| JOIN比较次数 | B*A | B*A | A*Index(Height) |
| 回表读取记录次数 | 0 | 0 | B(match)(if possible) |
因为索引查询的成本基本一样，为了降低开销，驱动表是小表更加合适。
Hash Join(MySQL 8)
从MySQL8后面的版本开始废弃块嵌套循环连接，默认使用了Hash Join的方式。
- 块嵌套循环连接：对于被连接的数据子集较小的情况下，它是个较好的选择。
- Hash Join: 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列值，然后扫描较大的表并探测散列值，找出与Hash表匹配的行。它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join只能应用于等值连接，这是由Hash的特点决定的。
优化建议
前面讲解了关联查询Join的实现原理，那么对于关联查询模式我们可以从中总结出下面的一些优化点：
- 优先保证被驱动表的连接字段建立索引，因为建立索引的查询方式是效率最高的。
left join或者right join这种外连接的情况，要保证小表（小结果集）作为驱动表，大表(大结果集)作为被驱动表，这样性能更好。- 在查询字段的话，要避免
select *或者select 全部字段，而是按需，因为这些字段也会加入到join buffer中。 - 能够直接多表关联的尽量直接关联，不用子查询，因为子查询的效率更加低。
- 在sql的查询计划的extra中，尽量避免出现
Using join buffer，有这个表示使用了块嵌套循环连接算法，尽量通过索引去解决。 - 尽量避免超过3张表以上的关联查询。
总结
本文分享了日常工作中使用非常频繁的关联查询，主要关注关联查询的实现原理，这样我们可以在平时写关联查询的SQL时候性能才会更佳。如果本文对你有帮助，请留下一个赞吧。
