MySQL中比较in和exists的区别

 

目录

前言

结论

in查询分析

EXISTS查询分析

---

 

前言

 

本文转载自https://blog.csdn.net/weixin_39539399/article/details/80851817

 

 

结论

 

exists和in都用于两个表的连接查询中，最好遵循小表驱动大表的原则。

exists适合B表比A表数据大的情况，in适合A表比B表数据大的情况

当A表数据和B表数据一样大时，in与exists效率差不多，可任选一个使用

 

 

in查询分析

 

SELECT
	* 
FROM
	A 
WHERE
	id IN ( SELECT id FROM B );

 这条SQL等价于

1. SELECT id FROM B 

2. SELECT * FROM A WHERE A.id = B.id   

 

解析

上面的SQL中先执行in后面的查询，in后面的查询只执行了一次，它查询出B表中所有的id并缓存，然后检查A表中的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中的所有结果未知

 

性能分析

public class testIN {
	public static void main(String[] args){
		List result = new ArrayList();	//定义结果集存储A
		String A[] = {"SELECT * FROM A"};	//定义数组A存储从A表查询到的结果集
		String B[] = {"SELECT id FROM B"};	//定义数组B存储从B表查询到的结果集
		for(int i=0;i<A.length;i++){
			for(int j=0;j<B.length;j++){
				result.add(A);
				break;
			}
		}
	}
}

通过上面的程序可以看出，当B表数据较大时，in操作会让B表中的数据全部遍历一遍，因此B表数据较大时使用in查询效率很低

当A表中有100条记录，B表中有1000条记录，那么最多可能遍历1000次，效率很差

当A表中有1000条记录，B表中有100条记录，最多遍历100次，内循环次数减少，效率大大提升。

 

结论：IN()查询时候B表数据比A表数据小的情况，IN()查询是从缓存中取数据

 

 

EXISTS查询分析

 

SELECT
	* 
FROM
	A 
WHERE
	EXISTS ( SELECT 1 FROM B WHERE B.id = A.id );

这条SQL等价于

1.SELECT * FROM A;

2.SELECT 1 FROM B WHERE B.id = A.id;

 

解析

EXISTS查询会先执行SELECT * FROM A查询，执行A.length次，并且不会将先执行的查询结果进行缓存，因为EXIST查询返回的是一个布尔值，它只在乎EXISTS的查询中是否有记录，与具体的结果集无关。

EXISTS查询将主查询的结果集放到子查询中做验证，根据验证结果是true或false决定主查询的数据结果是否得以保存。

 

性能分析

public class testEXISTS {
	public static void main(String[] args){
		List result = new ArrayList();	//定义结果集存储A
		String A[] = {"SELECT * FROM A"};	//定义数组A存储从A表查询到的结果集
	
		for(int i=0;i<A.length;i++){
			if(exists(A[i].id)){ //执行select 1 from B where B.id=a.id是否有记录返回
				result.add(A);
			}
		}
	}
}

通过上面的程序可以看出，当B表的数据比A表的数据大时适合使用EXISTS查询，因为它不用遍历B操作，只执行一次查询即可

当A表有100条记录，B表有1000条记录，那么EXISTS会执行100次去判断A表中的id是否与B表中的id相等，因为它只执行A.length次，可见B表的数据越多，越适合EXISTS发挥效果

当A表有10000条记录，B表有100条记录，那么EXISTS还是会执行10000次，此时不如使用in遍历100次效率高。因为IN是在内存中遍历比较，而EXISTS需要查询数据库。查询数据库消耗的性能更多，效率更低。

 

结论：exists适合B表比A表数据大的情况