小表驱动大表, 兼论exists和in

给出两个表,A和B,A和B表的数据量,文章地址https://www.yii666.com/article/754099.html

当A小于B时,用exists
select * from A where exists (select * from B where A.id=B.id)
exists的实现,相当于外表循环,每次循环对内表进行查询?
for i in A
for j in B
if j.id == i.id then ....
相反,如果A大于B的时候,则用in
select * from A where id in (select id from B)
这种在逻辑上类似于
for i in B
for j in A
if j.id == i.id then ....
然后MySQL实现in,使用了hash join (哈希连接)。我猜想和逻辑上的两层循环在工作原理上是不一样的。
 
经过我的实际测试
SELECT * FROM user u where exists  (SELECT 0 FROM plan p where p.user_id=u.user_id )
0.188 sec / 6.234 sec
 
SELECT * FROM user u where  user_id in  (SELECT user_id FROM plan)
0.015 sec / 0.000 sec
使用in的可谓是瞬间的
 
其中Plan表有3300行记录,User表有3800,数据量恰好接近。
所以那个伪码(两层循环只能帮助理解),和实际情况差距很大。否则二者应该非常接近才对。
 
  Duration Fetch Rows  SQL
exists 0.17 6.2 924 SELECT * FROM user u where exists  (SELECT 0 FROM plan p where p.user_id=u.user_id )
in 0.01 0.0 924 SELECT * FROM user u where  user_id in  (SELECT user_id FROM plan)
 虽然数据量很小,但是可以想象,如果我们用exists,可能会非常糟糕,难怪都建议用in。
 
 
Hash Join
1. 什么事Hash Join。据说是Oracle7.3以后出现的一种连接技术,估计就是要解决本质形如两层循环的工作方式吧(猜测)
那么,select * from A where id in (select id from B),我猜测实际处理中,会变为。
dict = hash(B)
for i in A
if i in dict then ...

如果如此,当然会快很多了。并且之前提到过,B表小的时候,用in,在这里也正好被放到一个Hash表里面。网址:yii666.com<网址:yii666.com文章来源地址:https://www.yii666.com/article/754099.html

更多引用:
1、hash join 就是哈希连接,当一个表或多个表上没有索引时,或者数据库服务器必须从所有连接表读取大量行的时候,就用这种方法。
2、orcale7.3以后才出来一种hash join的新的连接技术,hash join只能用于相等连接。
3、相对于以前的nested loop join连接技术,hash join更适合处理大型结果集,而且不需要在驱动表上建索引。
4、hash算法就是在两个表连接的时候,首先要区分大表和小表,小表用S来表示,大表用B来表示,然后就把小表S表放在一个内存的hash table中。
5、若这个小表S表还是太大,放不进去hash table中,就应该对这个hash table要分区,对于大表B表也是要放在内存中的,若B表也是太大,那也要进行分区处理。
6、这样的话,S表就分开若干个区,B表也分成若干个区,这样就开始各个分区之间的互连。
7、除了hash table的分区后,还有一个就是hash area内存的感念,就是逐个处理S与B的各个子集相连情况,首先把小表放在内存里,内存是高速缓冲区,速度快,大表放在慢速的存储区内,从慢速区内读条数据后,再与高速区的做循环,这样肯定会很快的,反过来就慢了,所以优化始终强调小表套大表,小表在高速缓冲区内执行,大表在慢速区放着。
8、S与B的两两相连时,会出现脚色互换的的,当B表的某个分区少的话,那这个小B表进如hash area中做主表。
9、hash join的核心就是确认小表为驱动表,在算法执行之前大小表都要拆分,会有两者角色互换的情况,关键是看谁的数量小,小的进高速内存跑循环,大的在外面有IO处理过程。
10、总之hash join适合于小表与大表相连,返回大型结果集的连接。

从这里摘的:http://blog.sina.com.cn/s/blog_648760e30101b1uh.html文章来源地址https://www.yii666.com/article/754099.html

 
Not in
1. 不要使用Not in,解决办法无外乎加索引,这样会快一些,然后效果未必明显,一般是改用Left join。
select * from A where id in (select id from B)
===>
select * from A left join B on a.id=b.id where b.id is null
 
关键是为什么in很快,not in很慢,
首先,not in并非是我们想象中的那样,subquery只求一次值,然后外层select判断是否in这个集合。
事实上,subquery会被执行N次。(好吧,疯了吧,然而我没有想清楚,为什么不能(设计成)执行一次就OK了。)
参考:https://stackoverflow.com/questions/12728654/why-is-this-mysql-query-with-the-not-in-statement-so-slow/12728981#12728981
 
 

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png