MySQL Join底层原理和实用优化技巧

在平时的开发过程中，大家应该经常会用到join进行多个表的之间的关联查询，但是如果使用不合理的话，会导致查询性能下降。本文就MySQL中的关联查询的实现原理以及一些优化建议等内容做一个讲解。
关联查询介绍
关联查询，指两个或更多个表一起完成查询操作。

1. 内连接（INNTER JOIN）

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行，语法如下：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

• 返回的结果集是A表和B匹配的行。
• A表或者B表哪个表是驱动表(主表)或者被驱动表(从表)由查询优化器决定。

1. 左连接(LEFT JOIN)

两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行。

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

• 结果集中返回匹配的行，也返回A表中不匹配的行，不匹配字段用NULL表示。
• A表是驱动表(主表)，B表是非驱动表(从表)。

1. 右连接(Right JOIN)

两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。

#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

• 结果集中返回匹配的行，也返回B表中不匹配的行，不匹配字段用NULL表示。
• B表是驱动表(主表)，A表是非驱动表(从表)。

关联查询原理

前面讲解了连接查询的几种方式，现在谈谈MySQL底层是支持这几种连接查询的。
关联查询中涉及到多张表的的查询，根据驱动类型分为驱动表和被驱动表，驱动表就是主表，被驱动表就是从表。我们可以在执行计划中看出来。

• 执行计划从上向下看，上面的属于驱动表。
• 内连接的驱动表选择由查询优化器决定。
• 左连接的驱动表一般是左边的表，右连接的驱动表一般是右边的表。

了解了驱动表和被驱动表以后，现在我们看下MySQL究竟是怎么做join查询的。MySQL使用了多种算法来实现join操作，包括嵌套循环算法(简单嵌套循环连接,块嵌套循环连接,索引嵌套循环连接,MRR,BKA)、排序合并算法、哈希算法等。

简单嵌套循环连接
简单嵌套循环连接（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的特点决定的。

排序合并算法（Sort-Merge Join）

排序合并算法是一种比较高效的join算法，它的基本原理是将两个表中的数据按照指定的排序规则进行排序，然后将排序后的数据进行比较，找出符合条件的数据行并将它们组合成新的结果集。排序合并算法的时间复杂度为O(nlogn)，比嵌套循环算法的复杂度要低得多。但是，排序合并算法需要对两个表中的数据进行排序，因此在处理大量数据的情况下，其性能可能也会受到影响。

下面，我们来总结一下上述三种算法的区别和优缺点。

	Nested Loop Join	Hash Join	Sorted Merge Join
连接条件	适用于任何条件	只适用于等值连接（=）	等值或非等值连接(>，<，=，>=，<=)，‘<>’除外
主要消耗资源	CPU、磁盘I/O	内存、临时空间	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果	当缺乏索引或者索引条件模糊时，Hash Join 比 Nested Loop 有效。通常比 Merge Join 快。在数据仓库环境下，如果表的纪录数多，效率高	当缺乏索引或者索引条件模糊时，Sort Merge Join 比 Nested Loop 有效。当连接字段有索引或者提前排好序时，比 hash join 快，并且支持更多的连接条件
缺点	无索引或者表记录多时效率低	建立哈希表需要大量内存，第一次的结果返回较慢	所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据
需要索引	是(没有索引效率太差)	否	否

优化建议

前面讲解了关联查询Join的实现原理，那么对于关联查询模式我们可以从中总结出下面的一些优化点：

1. 优先保证被驱动表的连接字段建立索引，因为建立索引的查询方式是效率最高的。
2. left join或者 right join这种外连接的情况，要保证小表（小结果集）作为驱动表，大表(大结果集)作为被驱动表，这样性能更好。
3. 在查询字段的话，要避免select *或者select 全部字段，而是按需，因为这些字段也会加入到join buffer中。
4. 能够直接多表关联的尽量直接关联，不用子查询，因为子查询的效率更加低。
5. 在sql的查询计划的extra中，尽量避免出现Using join buffer，有这个表示使用了块嵌套循环连接算法，尽量通过索引去解决。
6. 尽量避免超过3张表以上的关联查询。

总结
本文分享了日常工作中使用非常频繁的关联查询，主要关注关联查询的实现原理，这样我们可以在平时写关联查询的SQL时候性能才会更佳。如果本文对你有帮助，请留下一个赞吧。

相关文章：

拒绝慢查询！深入探究MySQL Join底层原理和实用优化技巧

MySQL慢查询优化之联结原理

MySQL 的 join 功能弱爆了？