MySQL关联查询Join的实现原理和优化建议_数据库_开发者

关联查询介绍

关联查询，指两个或更多个表一起完成查询操作。

内连接（INNTER JOIN）

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

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

MySQL关联查询Join的实现原理和优化建议

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

左连接(LEwww.devze.comFT JOIN)

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

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

MySQL关联查询Join的实现原理和优化建议

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

右连接(Right JOIN)

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

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

MySQL关联查询Join的实现原理和优化建议

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

关联查询原理

前面讲解了连接查询的几种方式，现在谈谈MySQL底层是支持这几种连接查询的。

关联查询中涉及到多张表的的查询，根据驱动类型分为驱动表和被驱动表，驱动表就是主表，被驱动表就是从表。我们可以在执行计划中看出来。

MySQL关联查询Join的实现原理和优化建议

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

了解了驱动表和被驱动表以后，现在我们看下MySQL究竟是怎么做join查询的。

简单嵌套循环连接

简单嵌套循环连接（http://www.devze.comSimple Nested-Loop join）是从驱动表A中取出一条数据，遍历表B，将匹配到的数据放到result.. 以此类推, 如下图所示：

MySQL关联查询Join的实现原理和优化建议

算法简单粗暴，比如驱动表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中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。整体如下图所示：

MySQL关联查询Join的实现原理和优化建议

注意一点，从驱动表中缓存的列不仅仅是关联的的列，select后面的列也会缓存起来。因此，为了能让join buffer缓存更多的数据，我们的SQL尽量不要select *, 而是select 用到的字段。
整体的开销如下表所示：

开销统计	简单嵌套循环连接	块嵌套循环连接
驱动表扫描次数	1	1
被驱动表扫描次数	A	A*used_column_size/join_buffer_size+1
读取记录数	A+B*A	A+B(Aused_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)就是效率最高的，前提条件是被驱动表的关联字段建立了索引。通过驱动表匹配条件直接与被驱动表的索引进行匹配，避免和内存表的每条记录去进行比较，这样极大的减少了对内存表的匹配次数。如下图所示：

MySQL关联查询Join的实现原理和优化建议

整体的开销成本如下表所示：

开销统计	简单嵌套循环连接	块嵌套循环连接	索引嵌套循环连接
驱动表扫描次数	1	1	1
被驱动表扫描次数	A	A*used_column_size/join_buffer_size编程+1	0
读取记录数	A+B*A	A+B(Aused_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的特点决定的。

MySQL关联查询Join的实现原理和优化建议