MySQL高级 之 in和exists

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环一次对内表进行一次查询。
如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

表A（小表），表B（大表）

注意： A表与B表的id字段应建立索引

select * from A where id in (select id from B) // in会使用子查询结果去主表匹配需要的行。子查询结果越大，去主表索引（A表上id列的索引）中检索的次数越多，效率越低

等价于:

for select id from Bfor select * from A where A.id = B.id// 比如：A表有5条记录，B表里有4万条记录，in会循环用4万个数据（id）去匹配这5条记录，成本远比用5条数据（id）去匹配4万条记录效率低

select * from A where exists (select 1 from B where B.id = A.id)// exists是根据匹配项去判断是或者否，然后根据是否决定结果，子查询的表大，用exists判断，效率就会高

等价于

for select * from Afor select * from B where B.id = A.id//比如：A表有5条记录，B表里有4万条记录，exists只进行5次判断，比in快很多。

结论：当A表的数据集小于B表的数据集时，用exists优于in。

相反

表A（大表），表B（小表）

select * from A where id in(select id from B)　//效率高select * from A where exists(select cc from B where cc=A.id)　//效率低

结论：当B表的数据集小于A表的数据集时，用in优于exists。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

网上还有人说mysql5.6版本对in查询做了很好的优化，所以效率问题要看具体的场景，要看真实测试的数据来进行优化！

补充：in 和or性能差异

如果in和or所在列有索引或者主键的话，or和in没啥差别，执行计划和执行时间都几乎一样。

如果in和or所在列没有索引的话，性能差别就很大了。在没有索引的情况下，随着in或者or后面的数据量越多，in的效率不会有太大的下降，但是or会随着记录越多的话性能下降非常厉害。

因此在给in和or的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别，如果没有索引，性能差别不是一点点！