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列的索引)中检索的次数越多,效率越低
- 1
- 2
等价于:
for select id from B
for select * from A where A.id = B.id
// 比如:A表有5条记录,B表里有4万条记录,in会循环用4万个数据(id)去匹配这5条记录,成本远比用5条数据(id)去匹配4万条记录效率低
- 1
- 2
- 3
select * from A where exists (select 1 from B where B.id = A.id)
// exists是根据匹配项去判断是或者否,然后根据是否决定结果,子查询的表大,用exists判断,效率就会高
- 1
- 2
等价于
for select * from A
for select * from B where B.id = A.id
//比如:A表有5条记录,B表里有4万条记录,exists只进行5次判断,比in快很多。
- 1
- 2
- 3
结论:当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) //效率低
- 1
- 2
- 3
结论:当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的效率下定义的时候,应该再加上一个条件,就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别,如果没有索引,性能差别不是一点点!