in、exists、not in、not exists

 我们先讨论IN和EXISTS。
 select * from t1 where x in ( select y from t2 )
 事实上可以理解为：
 select * 
   from t1, ( select distinct y from t2 ) t2
  where t1.x = t2.y;
 ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
 select * from t1 where exists ( select null from t2 where y = x )
 可以理解为：
 for x in ( select * from t1 )
 loop
    if ( exists ( select null from t2 where y = x.x )
    then 
       OUTPUT THE RECORD!
    end if
 end loop
 ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
 综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

 个人总结：in 用的是A表的索引，exists走的是B表的索引，索引的优势就在于处理量大的表数据。如果AB表不能同时走索引，只能取其一，那么尽量走大表索引，放弃小表。因此使用规律应该是：
 A大，B小，用in
 A小，B大，用exists
 A与B一样大，尽量走全索引，in,exists没有很大差别

如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。
个人总结：对于not in/not exists。not exists在B表较大时可以使用到索引，not in在任何情况下都没什么用，都是要走全部扫描，所以尽量不使用not in

个人这几天又重温了一下：
数据库在进行多表连接时的处理算法：
nested loop join
　 嵌套循环连接，是比较通用的连接方式，分为内外表，每扫描外表的一行数据都要在内表中查找与之相匹配的行，没有索引的复杂度是O(N*M)，这样的复杂度对于大数据集是非常劣势的，一般来讲会通过索引来提升性能。　

　　sort merge-join
　　merge join需要首先对两个表按照关联的字段进行排序，分别从两个表中取出一行数据进行匹配，如果合适放入结果集；不匹配将较小的那行丢掉继续匹配另一个表的下一行，依次处理直到将两表的数据取完。merge join的很大一部分开销花在排序上，也是同等条件下差于hash join的一个主要原因。
　　
hash-join
就算讲两表中的小表（称S）作为hash表，然后去扫描另一个表(称M)的每一行数据，用得出来的行数据根据连接条件去映射建立的hash表，hash表是放在内存中的，这样可以很快的得到对应的S表与M表相匹配的行。

A in B：hash_join. A索引，B全部扫描，适合 A表大，B表小，
A exists B： loop-join. A全表扫描，B索引，适合A表小，B表大；
A not in B :A全表扫描，B全表扫描，性能很差
A not exists B：A索引，B索引，都不错