MySQL之 IN和EXISTS的区别和用法

本节内容总结自《High Performance MySQL》

IN和EXISTS的用法和区别

1. EXISTS：EXISTS对外表用loop逐条查询，每次查询都会查看EXISTS条件语句（子查询语句），条件语句中能够返回记录行则条件就为真，外表的这条记录就会被加入结果集，如果EXISTS里的子查询不能返回记录行，则当前loop到的这条记录就会被丢弃。需要注意的是，EXISTS里的子查询是可以使用索引进行查询的。
2. IN：IN相当于多个OR条件的叠加，总的来说，IN就是先将子查询条件的记录都查出来缓存到临时表中，这里可以假设记录集为多个OR条件的叠加。因此可以将这多个OR条件的叠加用来执行原查询，这里原查询也是可以使用索引查询的。MySQL中的IN是把外表和内表做哈希连接来进行查询。哈希连接的意思是，把两个表中较小的那个表（一般情况下是较小的那个表，以减少建立哈希表的时间和空间），对其中每个元组上的连接属性采用哈希函数得到哈希值，从而建立一个哈希表。对另一个表，扫描它的每一行并计算连接属性的哈希值，与前面建立的哈希表对比，哈希值相等的生成结果表。
3. EXISTS和IN的区别
   • 假设现在查询语句为 select * from A where exists (select * from B where B.id = A.id); select * from A where A.id in (select id from B);
   1. EXISTS可以对B表使用索引查询，而IN则先查出子查询的结果，然后对A表可以使用索引进行查询。因此可以看出在索引条件都相等的情况下，EXISTS适合B表较大的查询，而IN则适合A表较大的查询。
   2. IN子查询中返回结果必须只有一个字段，而EXISTS则没有这个限制。
4. NOT EXISTS：可以参考上面的EXISTS查询，还是可以使用B上建立的索引执行查询的。
5. NOT IN：我们假设上面IN的子查询结果集为(1, 2, 3)，则NOT IN之后，可以改写成 select * from A where A.id != 1 and A.id != 2 and A.id != 3; 这种 !=的范围查询无法使用任何索引，也就是说此时对于A表中的每一条记录，都要在B表中遍历一次，看B表是否不存在这条记录。也就是说一般情况下，NOT EXISTS 都会比NOT IN 效率高。