MySQL 连贯查询超级详解

1 作用

在数据库中join操纵被称为连接，作用是能连接多个表的数据（通过连接前提），从多个表中猎取数据合并在一起作为结果集返回给客户端。例如:

表A：

表B：

通过连接可以猎取到合并两个表的数据：

select A.*,B.gender from  A left join B on A.id=B.uid

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 留意点

• 尽量增添连接前提，减少join后数据集的大小
• 用小结果集驱动大结果集，将挑选结果小的表第一连接，再去连接结果集比力大的表
• 被驱动表的被join的字段要创立索引，且使用上索引。使用上索引包罗使用该字段，且不会有索引失效的状况显现
• 设定足够大的join_buffer_size

7 外连接常见问题

Q：假如想挑选驱动表的数据，例如左连接挑选左表的数据，该在连接前提还是where挑选？
A：要通过where挑选，连接前提只影响连接历程，不影响连接返回的结果数（某些状况下连接前提会影响连接返回的结果数，例如左连接中，右侧匹配的数据不惟一的时候）

Q：被驱动表匹配的数据行不惟一致使终究连接数据超越驱动表数据量该如何办？例如关于左连接，右表匹配的数据行不惟一。
A：join此前先对被驱动表去重，例如通过group by去重：A lef join (select * from B group by name)。

相关学习引荐：mysql视频教程

以上就是MySQL 连接查询超级详解的具体内容，更多请关注百分百源码网其它相关文章！