目录
`in` 和 `exists``in``exists`概述举例`in` 和 `exists` 用法区别小结in
和exists
在mysql
的多表查询中,始终要遵循的一个原则:小表驱动大表
in
in
关键字进行子查询时,内层查询语句只能返回一个数据列
,这个数据列的值将提供给外层查询语句进行比较操作
SELECT * FROM A WHERE id IN (SELECT id FROM B);
如上述sql
语句,它的执行流程
先执行in
中的查询,即SELECT id FROM B
其次在执行SELECT * FROM A WHERE A.id = B.id
假设SELECT id FROM B
查询出有m
条记录,然后检查A
表中查询出的id
在m
条记录中是否存在,如果存在则将A
的查询数据加入到结果集中,直到遍历完A
表中所有的结果集为止
当B
表数据量很大的时候不适合用in
,因为它有可能最多会将B
表数据全部扫描一次如果A
表有1000
条记录,B
表有10000
条记录,那么最多有可能扫描10000 * 1000
次表,效率很差如果A
表有1000
条记录,B
表有100
条记录,那么最多有可能扫描100 * 1000
次,扫描表次数大大减少,效率大大提升
总结:当外层查询表数据量大于子查询表时,则用in
,此时也正是遵循了小表驱动大表
的原则
exists
概述
exists
关键字后面的参数是一个任意的子查询
,sql
对子查询进行运算以判断它是否返回行
如果至少返回一行,那么exists
的结果为true
,此时外层的查询语句将进行查询如果子查询没有返回任何行,那么exists
的结果为false
,此时外层语句将不进行查询注意:当我们的子查询为SELECT NULL
时,mysql
仍然认为它是true
exists
对外表用loop
逐条查询,每次查询都会查看exists
的子查询语句,当exists
里的子查询语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop
到的这条记录;反之,如果exists
里的条件语句不能返回记录行,则当前loop
到的这条记录被丢弃
举例
select * from user where exists (select 1);
对user
表的记录逐条取出,由于子条件中的select 1
永远能返回记录行,那么user
表的所有记录都将被加入结果集,所以与select * from user
是一样的
select * from A where exists (select * from B where user_id = 0);
可以知道对A
表进行loop
时,检查条件语句select * from B where user_id = 0
由于user_id
永远不为0
,所以条件语句永远返回空集,条件永远为false
,那么A
表的所有记录都将被丢弃
总结:如果A
表有n
条记录,那么exists
查询就是将这n
条记录逐条取出,然后判断n
遍exists
条件
in
和exists
用法区别
in
查询的子查询返回结果必须只有一个字段,例如
select * from user where user_id in (select id from B);
不能是
select * from user where user_id in (select id, age from B);
而exists
就没有这个限制,可以是任意的子查询
小结
in
查询在内部表和外部表上都可以使用到索引exists
查询仅在内部表上可以使用到索引当子查询结果集很大,而外部表较小的时候,exists
的Block
嵌套循环的作用开始显现,并弥补外部表无法用到索引的缺陷,查询效率会优于in
当子查询结果集较小,而外部表很大的时候,exists
的Block
嵌套循环优化效果不明显,in
的外表索引优势占主要作用,此时in
的查询效率会优于exists
表的规模不是看内部表和外部表,而是外部表和子查询结果集