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

给出两个表，A和B，A和B表的数据量，

当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表里面。

更多引用：

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

 

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