MySQL连贯查询超级详解
如下:
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 ;
}
}
}固然，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 ;
}
}
}比拟于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 ;
}
}总共需要轮回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 连贯查询超级详解的细致内容，更多请关注 百分百源码网 其它相干文章！
