MySQL中JOIN关键字原理

一、JOIN 简介

JOIN 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。例如我们可以通过JOIN来从用户表和订单表两个表中获取数据：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

JOIN类型分为以下几种：

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

二、JOIN 实现原理

JOIN 实现算法包括以下三种：

• Simple Nested-Loop Join（简单的嵌套循环连接）
• Index Nested-Loop Join（索引嵌套循环连接）
• Block Nested-Loop Join（缓存块嵌套循环连接）

1. Simple Nested-Loop Join

简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果。例如执行以下sql语句时：

select * from user tb1 left join order tb2 on tb1.id=tb2.user_id

按Simple Nested-Loop Join算法实现就是一个双重循环：

for (User user:users){
	for (Order order:orders){
		if (user.id == order.user_id){
			//结果集添加user和order
		}
	}
}
	

整个匹配过程如下图：

Nested-Loop Join 简单粗暴容易理解，就是通过双层循环比较数据来获得结果。每次从左表中先读取一行记录，然后将这行记录再和右表中的每一行进行匹配。但是这种算法显然太过于粗鲁，如果每个表有1万条数据，那么对数据比较的次数=1万 * 1万 =1亿次，很显然这种查询效率会非常慢。

当然mysql 肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-Loop Join 优化算法，在执行join 查询时mysql 会根据情况选择 后面的两种优join优化算法的一种进行join查询。

2. Index Nested-Loop Join

这种算法其实就是在Simple Nested-Loop Join的基础上为右表构建索引，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数，从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。例如执行以下sql语句时：

select * from user tb1 left join order tb2 on tb1.id=tb2.user_id

右表Order表已经为user_id构建了索引，所以执行的过程如下图：

Index Nested-Loop Join算法对于左表来说还是要扫描全表，并且每次只读取一行记录。

3. Block Nested-Loop Join

Simple Nested-Loop Join算法的缺点在于其对于内表的扫描次数太多，从而导致扫描的记录太过庞大。Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数。

Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表（左表）的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表（右表）的访问频率。也就是多条驱动表记录只需要访问一次S表。

如果无法使用Index Nested-Loop Join的时候，数据库是默认使用的是Block Nested-Loop Join算法的。

三、总结

不论是Index Nested-Loop Join 还是 Block Nested-Loop Join 都是在Simple Nested-Loop Join的算法的基础上进行优化，这里 Index Nested-Loop Join 和Nested-Loop Join 算法是分别对Join过程中循环匹配次数和IO 次数两个角度进行优化。

Index Nested-Loop Join 是通过索引的机制减少内层表的循环匹配次数达到优化效果，而Block Nested-Loop Join 是通过一次缓存多条数据批量匹配的方式来减少内层表的扫表IO次数，通过 理解join 的算法原理我们可以得出以下表连接查询的优化思路。

• 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
• 为匹配的条件增加索引(减少内层表的循环匹配次数)
• 增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
• 减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

假设外表记录数为LC，内表记录数为RC，看一下三种算法的开销统计：

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数：O	1	1	1
内表扫描次数：I	LC	0	LC(UsedColumnSize)/join_buffer_size+1
读取记录总数：R	LC+LC*RC	LC+RC(match)	LC+RC*I
JOIN 比较次数：M	LC*RC	LC*IndexHeight	LC*RC
回表读取记录次数：F	0	RC(match)	0

其中，RC(match)表示通过左表通过右表索引匹配到的记录数量。LC(UsedColumSize)表示左表需要查询的列所占空间大小，LC(UsedColumSize)/join_buffer_size表示需要多少次才能将左表全部装进join_buffer。

---

THE END.