|
| 作者肖泽凡，腾讯TEG开发管理部小小后台攻城狮一枚，负责腾讯敏捷制品开发平台TAPD的基本功能的研发和守护，热爱技术，爱好分享，文案首次发布于SegmentFault，博客名“X先生”，欢迎与我交流~在数据库中join操作被叫作为连接，功效是能连接多个表的数据（经过连接要求），从多个表中获取数据合并在一块做为结果集返回给客户端。例如:
表A：
id
name
age
1
A
18
2
B
19
3
C
20
表B：
id
uid
gender
1
1
F
2
2
M
经过连接能够获取到合并两个表的数据： select A.*,B.gender from A left join B on A.id=B.uid
id
name
age
gender
1
A
18
F
2
B
19
M
3
C
20
null
连接关键字
连接两个表咱们能够用两个关键字：on，using。on能够指定详细要求，using则指定相同名字和数据类型的列做为等值判断的要求，多个则经过逗号隔开。
如下: on: select * from A join B on A.id=B.id and B.name=using: select * fromAjoin B using(id,name) = select * from A join B on A.id=B.id and A.name=B.name
连接类型
1、内连接
内连接和交叉连接
1. 语法：A join | inner join | cross join B
2. 表现：A和B满足连接要求记录的交集，倘若无连接条件，则是A和B的笛卡尔积
3. 特点：在MySQL中,cross join ，inner join和join所实现的功能是同样的。因此呢在MySQL的官方文档中，指明了三者是等价的关系。
隐式连接
1. 语法：from A,B,C
2. 表现：相当于没法运用on和using的join
3. 特点：逗号是隐式连接运算符。隐式连接是SQL92中的标准内容，而在SQL99中显式连接才是标准，虽然非常多人还在用隐私连接，然则它已然从标准中被移除。从运用的方向来讲，还是举荐运用表示连接，这般能够更清楚的表示出多个表之间的连接关系和连接依赖的属性。
2、外连接
左外连接
1. 语法：A left join B
2. 表现：左表的数据所有保存，右表满足连接要求的记录展示，不满足的要求的记录则全是null
右外连接
1. 语法：A right join B
2. 表现：右表的数据所有保存，左表满足连接要求的记录展示，不满足的要求的记录则全是null
全外连接
MySQL不支持全外连接，只支持左外连接和右外连接。倘若要获取全连接的数据，要能够经过合并上下外连接的数据获取到，如select * from A left join B onA.name = B.name union select * from A right join B on B.name = B.name;
这儿union会自动去重，这般取到的便是全外连接的数据了。
3、自然连接
语法：A natural join B ==== A natural left join B ==== A natural right join B
表现：相当于不可指定连接要求的连接，MySQL会运用上下表内相同名字和类型的字段做为连接要求。
特点：自然连接亦分自然内连接，左外连接，右外连接，其表现和上面说到的一致，只是连接要求由MySQL自动判定。
执行次序
在连接过程中，MySQL各关键字执行的次序如下： from -> on|using ->where -> group by -> having ->select ->order by -> limit
能够看到，连接的要求是先于where的，亦便是先连接得到结果集后，才对结果集进行where筛选，因此在运用join的时候，咱们要尽可能供给连接的要求，而少用where的要求，这般才可加强查找性能。
连接算法
join有三种算法，分别是Nested Loop Join，Hash join，Sort Merge Join。MySQL官方文档中说到，MySQL只支持Nested Loop Join这一种算法。
详细来讲Nested Loop Join又分三种细分的算法：
1. SNLJ
2. BNLJ
3. INLJ
咱们来看下针对连接语句select * from A left join B on A.id=B.tid，这三种算法是怎么连接的。
1、Simple NestedLoop Join（SNLJ）
SNLJ是在无运用到索引的状况下，经过两层循环全量扫描连接的两张表，得到符合要求的两条记录则输出。亦便是让两张表做笛卡尔积进行扫描，是比较暴力的算法，会比较耗时。其过程如下： for (a in A) { for (b in B) { if (a.id == b.tid) {output <a, b>; } } }
当然，MySQL即使在无索引可用，或判断全表扫描可能比运用索引更快的状况下，还是不会选取运用过于粗暴的SNLJ算法，而是采用下面的算法。
2、Block NestedLoop 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。
3、Index NestedLoop 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索引详解之索引的存储方式。
重视点
1. 尽可能增多连接要求，减少join后数据集的体积
2. 用小结果集驱动大结果集，将筛选结果小的表首要连接，再去连接结果集比很强的表
3. 被驱动表的被join的字段要创立索引，且运用上索引。运用上索引包含运用该字段，且不会有索引失效的状况显现
4. 设置足够大的join_buffer_size
外连接平常问题
Q：倘若想筛选驱动表的数据，例如左连接筛选左表的数据，该在连接要求还是where筛选？
A：要经过where筛选，连接要求只影响连接过程，不影响连接返回的结果数（某些状况下连接条件会影响连接返回的结果数，例如左连接中，右侧匹配的数据不独一的时候）
Q：被驱动表匹配的数据行不独一引起最后连接数据超过驱动表数据量该怎么办？例如针对左连接，右表匹配的数据行不独一。
A：join之前先对被驱动表去重，例如经过group by去重：A lef join (select * from B group by name)。 特惠体验云数据库
↓↓更加多惊喜优惠请点这儿~
| 
发表于 2024-8-4 15:00:18
