MySQL connection query super detailed explanation

2 连接关键字

连接两个表我们可以用两个关键字：on，using。on可以指定具体条件，using则指定相同名字和数据类型的列作为等值判断的条件，多个则通过逗号隔开。
如下:

on: select * from A join B on A.id=B.id and B.name=''
using: select * from A join B using(id,name) = select * from A join B on 
A.id=B.id and A.name=B.name

3 连接类型

3.1 内连接

内连接和交叉连接

• 语法：A  join | inner join |  cross join B
• 表现：A和B满足连接条件记录的交集，如果没有连接条件，则是A和B的笛卡尔积
• 特点：在MySQL中,cross join ，inner join和join所实现的功能是一样的。因此在MySQL的官方文档中，指明了三者是等价的关系。

隐式连接

• 语法：from A,B,C
• 表现：相当于无法使用on和using的join
• 特点：逗号是隐式连接运算符。 隐式连接是SQL92中的标准内容，而在SQL99中显式连接才是标准，虽然很多人还在用隐私连接，但是它已经从标准中被移除。从使用的角度来说，还是推荐使用显示连接，这样可以更清楚的显示出多个表之间的连接关系和连接依赖的属性。

3.2 外连接

左外连接

• 语法：A left join B
• 表现：左表的数据全部保留，右表满足连接条件的记录展示，不满足的条件的记录则全是null

右外连接

• 语法：A right join B
• 表现：右表的数据全部保留，左表满足连接条件的记录展示，不满足的条件的记录则全是null

全外连接

MySQL不支持全外连接，只支持左外连接和右外连接。如果要获取全连接的数据，要可以通过合并左右外连接的数据获取到，如 select * from A left join B on A.name = B.name  union  select * from A right join B on B.name = B.name;。

这里union会自动去重，这样取到的就是全外连接的数据了。

3.3 自然连接

• 语法：A natural join B ==== A natural left join B ==== A natural right join B
• 表现：相当于不能指定连接条件的连接，MySQL会使用左右表内相同名字和类型的字段作为连接条件。
• 特点：自然连接也分自然内连接，左外连接，右外连接，其表现和上面提到的一致，只是连接条件由MySQL自动判定。

4 执行顺序

在连接过程中，MySQL各关键字执行的顺序如下：

from -> on|using -> where -> group by -> having -> select -> order by -> 
limit

可以看到，连接的条件是先于where的，也就是先连接获得结果集后，才对结果集进行where筛选，所以在使用join的时候，我们要尽可能提供连接的条件，而少用where的条件，这样才能提高查询性能。

5 连接算法

join有三种算法，分别是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文档中提到，MySQL只支持Nested Loop Join这一种算法。

具体来说Nested Loop Join又分三种细分的算法：

• SNLJ
• BNLJ
• INLJ

我们来看下对于连接语句select * from A left join B on A.id=B.tid，这三种算法是怎么连接的。

5.1 Simple Nested Loop Join（SNLJ）

SNLJ是在没有使用到索引的情况下，通过两层循环全量扫描连接的两张表，得到符合条件的两条记录则输出。也就是让两张表做笛卡尔积进行扫描，是比较暴力的算法，会比较耗时。其过程如下：

for (a in A) {
     for (b in B) {
         if (a.id == b.tid) {
             output <a, b>;
         }
     }
 }

当然，MySQL即使在无索引可用，或者判断全表扫描可能比使用索引更快的情况下，还是不会选择使用过于粗暴的SNLJ算法，而是采用下面的算法。

5.2 Block Nested Loop Join（BNLJ）

INLJ是MySQL无法使用索引的时候采用的join算法。会将外层循环的行分片存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数，具体逻辑如下:

for (blockA in A.blocks) {
     for (b in B) {
         if (b.tid in blockA.id) {
             output <a, b>;
         }
     }
 }

相比于SNLJ算法，BNLJ算法通过外层循环的结果集的分块，可以有效的减少内层循环的次数。

原理

举例来说，外层循环的结果集是100行，使用SNLJ算法需要扫描内部表100次，如果使用BNLJ算法，假设每次分片的数量是10，则会先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中每次循环都直接匹配这10行数据，这样内层循环只需要10次，对内部表的扫描减少了9/10，所以BNLJ算法就能够显著减少内层循环表扫描的次数。

当然这里，不管SNLJ还是BNLJ算法，他们总的比较次数都是一样的，都是要拿外层循环的每一行与内层循环的每一行进行比较。

BNLJ算法减少的是总的扫描行数，SNLJ算法是外层循环要一行行扫描A表的数据，然后取A.id去表B一行行扫描看是否匹配。而BNLJ算法则是外层循环要一行行扫描A表的数据，然后放到内存分块里，然后去表B一行行扫描，扫描出来的B的一行数据与内存分块里的A的数据块进行比较。这里可以一次就是很多行A的数据与B的数据进行比较，而且是在内存中进行比较，速度更加快了。

影响因素

这里BNLJ算法总的扫描行数是由外层循环的数据量N，和分块数量K还有内层循环的数据量M决定的。其中分块数量K与外层循环的数据量N又是息息相关的，我们可以表示为λN，其中λ取值为(0~1)。则总扫描次数C=N+λNM。

可以看出，在这个式子里，N和λ的大小都会影响扫描行数，但是λ才是影响扫描行数的关键因素，这个值越小越好（除非N和M的差值非常大，这时候N才会成为关键影响因素）。

那什么会影响 λ 的大小呢？那就是 MySQL的join_buffer_size设置项的大小了。λ和join_buffer_size成倒数关系,join_buffer_size越大，分块越大，λ越小，分块数量也就越少，也就是外层循环的次数也越少。所以在使用不上索引的时候，我们要优先考虑扩大join_buffer_size的大小，这样优化效果会更明显。而在能使用上索引的时候，MySQL会使用以下算法来进行join。

5.3 Index Nested Loop Join（INLJ）

INLJ是MySQL判断能使用到被驱动表的索引的情况下采用的算法。假设A表的数据行为10，B表的数据行为100，且B.tid建立了索引，则对于select * from A left join B on A.id=B.tid，MySQL会采用Index Nested Loop Join。其过程如下：

for (a in A) {
     if (a.id in B.tid.Index) {
        output <a, tid.Index所在行>;
     }
 }

总共需要循环10次A，每次循环的时候通过索引查询一次B的数据。而如果我们反过来是B left join A的话，总共要循环100次B，由此可见如果使用join的话，需要让小表做驱动表，这样才能有效减少循环次数。但是需要注意的是，这个结论的前提是可以使用被驱动表的索引。

INLJ内层循环读取的是索引，可以减少内存循环的次数，提高join效率，但是也有缺点的，就是如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。例如上面在索引里匹配到了tid，还要去找tid所在的行在磁盘所在的位置，具体可以见我以前的文章：MySQL索引详解之索引的存储方式。

6 Notes

• Try to increase the connection conditions and reduce the size of the data set after join
• Use small result set driver For large result sets, connect the tables with small filter results first, and then connect the tables with larger result sets.
• The fields of the driven table that are join must be indexed, and use the upper index. . Using the upper index includes using this field, and there will be no index failure
• Set a large enoughjoin_buffer_size

7 Frequently Asked Questions about Outer Joins

Q: If you want to filter the data in the driver table, for example, a left join filters the data in the left table, should you filter in the join condition or where?
A: To filter by where, the connection condition only affects the connection process and does not affect the number of results returned by the connection (in some cases, the connection condition will affect the number of results returned by the connection, such as in a left connection, When the matching data on the right is not unique)

Q: What should I do if the data rows matched by the driven table are not unique and the final connection data exceeds the data volume of the driving table? For example, for a left join, the matching data rows in the right table are not unique.
A: join Deduplicate the driven table first, for example, use group by to deduplicate: A lef join (select * from B group by name).

Related learning recommendations: mysql video tutorial