大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
什么是MySQL 连接查询?相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。
创新互联公司是一家集网站建设,正镶白企业网站建设,正镶白品牌网站建设,网站定制,正镶白网站建设报价,网络营销,网络优化,正镶白网站推广为一体的创新建站企业,帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿,时刻以成就客户成长自我,坚持不断学习、思考、沉淀、净化自己,让我们为更多的企业打造出实用型网站。
在数据库中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 * from A join B using(id,name) = select * from A join B on A.id=B.id and A.name=B.name
A join | inner join | cross join B
cross join
,inner join
和join
所实现的功能是一样的。因此在MySQL的官方文档中,指明了三者是等价的关系。from A,B,C
on
和using
的join
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
会自动去重,这样取到的就是全外连接的数据了。
A natural join B ==== A natural left join B ==== A natural right join B
在连接过程中,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
又分三种细分的算法:
我们来看下对于连接语句select * from A left join B on A.id=B.tid
,这三种算法是怎么连接的。
SNLJ
是在没有使用到索引的情况下,通过两层循环全量扫描连接的两张表,得到符合条件的两条记录则输出。也就是让两张表做笛卡尔积进行扫描,是比较暴力的算法,会比较耗时。其过程如下:
for (a in A) { for (b in B) { if (a.id == b.tid) { output ; } } }
当然,MySQL即使在无索引可用,或者判断全表扫描可能比使用索引更快的情况下,还是不会选择使用过于粗暴的SNLJ
算法,而是采用下面的算法。
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
。
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索引详解之索引的存储方式。
join
后数据集的大小join
的字段要建立索引,且使用上索引。使用上索引包括使用该字段,且不会有索引失效的情况出现join_buffer_size
Q:如果想筛选驱动表的数据,例如左连接筛选左表的数据,该在连接条件还是where
筛选?
A:要通过where
筛选,连接条件只影响连接过程,不影响连接返回的结果数(某些情况下连接条件会影响连接返回的结果数,例如左连接中,右侧匹配的数据不唯一的时候)
Q:被驱动表匹配的数据行不唯一导致最终连接数据超过驱动表数据量该怎么办?例如对于左连接,右表匹配的数据行不唯一。
A:join
之前先对被驱动表去重,例如通过group by
去重:A lef join (select * from B group by name)
。
看完上述内容,你们掌握什么是MySQL 连接查询的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注创新互联行业资讯频道,感谢各位的阅读!