一文浅谈sql中的 in与not in,exists与not exists的区别以及性能分析

灰太狼 2023-09-25 15:07 33阅读 0赞

#### 文章目录 ####

*  1. 文章引言
 *  2. 查询对比
 *   *  2.1 in和exists
     *  2.2 not in 和not exists
     *  2.3 in 与 = 的区别
 *  3. 性能分析
 *   *  3.1 in和exists
     *  3.2 NOT IN 与NOT EXISTS
 *  4. 重要总结

## 1. 文章引言 ##

我们在工作的过程中，经常使用`in，not in，exists，not exists`来查询，比如现在一张项目(`project`)表，表的结构和数据：

CREATE TABLE `project` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `status` varchar(255) DEFAULT NULL,
      `project_name` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    INSERT INTO `project` VALUES ('1', 'finish', '太湖佳园');
    INSERT INTO `project` VALUES ('2', 'during', '尚东雅园');
    INSERT INTO `project` VALUES ('3', 'start', '水乡苑一区');
    INSERT INTO `project` VALUES ('4', 'during', '水乡苑二区');

查询状态为`已完成和进行中`的记录，我们可以写成如下的SQL语句：

select * from project where `status` in ('finish','during');

查询结果如下图：

![在这里插入图片描述][c9bcbeaed7c74fccb05f862241bd4fab.png]

这只是我们开发中的一个简单示例，接下来，我们详细解说`in与not in,exists与not exists的区别以及性能分析`。

## 2. 查询对比 ##

### 2.1 in和exists ###

`in`是把外表和内表作`hash`连接。

`exists`是对外表作`loop`循环，每次`loop`循环再对内表进行查询，一直以来认为`exists`比`in`效率高的说法是不准确的。

如果查询的两个表**大小相当**，那么用`in`和`exists`差别不大。

如果两个表中一个较小一个较大，则子查询表大的用`exists`，子查询表小的用`in`。

例如：表A(小表)，表B(大表)

-- 效率低，用到了A表上cc列的索引
    select * from A where cc in(select cc from B)　　
    
    -- 效率高，用到了B表上cc列的索引
    select * from A where exists(select cc from B where cc=A.cc)

相反的：

-- 效率高，用到了B表上cc列的索引
    select * from B where cc in(select cc from A)　
    
    -- 效率低，用到了A表上cc列的索引。
    select * from B where exists(select cc from A where cc=B.cc)

### 2.2 not in 和not exists ###

`not in`逻辑上不完全等同于`not exists`，如果你误用了`not in`，**小心你的程序存在致命的BUG**，请看下面的例子：

-- 创建t1表
    create table t_1(c1 int,c2 int);
    
    -- 创建t2表
    create table t_2(c1 int,c2 int);
    
    -- 向t1表中插入数据
    insert into t_1 values(1,2);
    insert into t_1 values(1,3);
    
    -- 向t2表中插入数据
    insert into t_2 values(1,2);
    insert into t_2 values(1,null);

先后执行如下两条查询语句：

1.  语句1

SELECT
    	*
    FROM
    	t_1
    WHERE
    	c2 NOT IN (SELECT c2 FROM t_2);

查询结果是空值，如下图：

![在这里插入图片描述][d9fc6d69468b42bfabbdf923c45f3287.png]

1.  语句2

SELECT
    	*
    FROM
    	t_1
    WHERE
    	NOT EXISTS (
    		SELECT
    			1
    		FROM
    			t_2
    		WHERE
    			t_2.c2 = t_1.c2
    	);

查询结果`c1 = 1,c2 = 3`，如下图所示：

![在这里插入图片描述][d7891783152f4a518579206850376be4.png]

正如你所看到的，`not in`出现了不期望的结果集，存在逻辑错误。

如果看一下上述两个`select`语句的执行计划，也会不同，`语句2`使用了`hash_aj`，所以，请尽量不要使用`not in`(它会调用子查询)，而尽量使用`not exists`（它会调用关联子查询）。

如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。

如果子查询字段有非空限制，这时可以使用`not in`，并且可以通过提示让它用`hasg_aj`或`merge_aj`连接。

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

### 2.3 in 与 = 的区别 ###

SELECT
    	NAME
    FROM
    	student
    WHERE
    	NAME IN ('zhang', 'wang', 'zhao');

与

SELECT
    	NAME
    FROM
    	student
    WHERE
    	NAME = 'zhang'
    OR NAME = 'wang'
    OR NAME = 'zhao'

的结果是相同的。

## 3. 性能分析 ##

### 3.1 in和exists ###

1.  `EXISTS`的执行流程

SELECT
    	*
    FROM
    	t1
    WHERE
    	EXISTS (SELECT NULL FROM t2 WHERE y = x)

可以理解为:

for x in ( select * from t1 ) loop 
    
    if ( exists ( select null from t2 where y = x.x ) then 
    OUTPUT THE RECORD 
    end if 
    end loop

1.  `in`和`exists`的性能区别

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用`in`。

反之，如果外层的主查询记录较少，子查询中的表大，又有索引时使用`exists`。

其实我们区分`in`和`exists`主要是造成了驱动顺序的改变（这是性能变化的关键）:

*  如果是`exists`，那么以外层表为驱动表，先被访问
 *  如果是`IN`，那么先执行子查询

所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 。

**另外，`IN`时不对`NULL`进行处理**，如下`SQL`所示：

SELECT
    	1
    FROM
    	DUAL
    WHERE
    	NULL IN (0, 1, 2, NULL)

查询结果为空。

### 3.2 NOT IN 与NOT EXISTS ###

1.  `NOT EXISTS`的执行流程

SELECT
    	.....
    FROM
    	ROLLUP R
    WHERE
    	NOT EXISTS (
    		SELECT
    			'Found'
    		FROM
    			title T
    		WHERE
    			R.source_id = T.Title_ID
    	);

可以理解为:

for x in ( select * from rollup ) loop 
    if ( not exists ( that query ) ) then 
    OUTPUT 
    end if; 
    end loop;

**注意：`NOT EXISTS`与`NOT IN`不能完全互相替换，看具体的需求。如果选择的列可以为空，则不能被替换。**

例如下面语句，看他们的区别：

select x,y from t;

查询`x`和`y`数据如下所示：

x      y 
    ------ ------ 
    1      3 
    3      1 
    1      2 
    1      1 
    3      1 
    5

1.  使用`not in`和`not exists`查询结果，如下

SELECT
    	*
    FROM
    	t
    WHERE
    	x NOT IN (SELECT y FROM t t2);

查询无结果：`no rows`

SELECT
    	*
    FROM
    	t
    WHERE
    	NOT EXISTS (
    		SELECT
    			NULL
    		FROM
    			t t2
    		WHERE
    			t2.y = t.x
    	);

查询结果为：

x       y 
    ------ ------ 
    5      NULL

所以要具体需求来决定

1.  `not in`和`not exists`的性能区别

`not in`只有当子查询中，`select`关键字后的字段有`not null`约束，或者有这种暗示时用`not in`。另外，如果主查询中表大，子查询中的表小但是记录多，则应当使用`not in`，并使用`anti hash join`。

如果主查询表中记录少，子查询表中记录多，并有索引，可以使用`not exists`，另外，`not in`最好也可以用`/*+ HASH_AJ */`或者外连接`+is null`。

`NOT IN`在基于成本的应用中较好，比如:

SELECT
    	.....
    FROM
    	ROLLUP R
    WHERE
    	NOT EXISTS (
    		SELECT
    			'Found'
    		FROM
    			title T
    		WHERE
    			R.source_id = T.Title_ID
    	);

最好修改成如下方式：

SELECT
    	......
    FROM
    	title T,
    	ROLLUP R
    WHERE
    	R.source_id = T.Title_id (+)
    AND T.Title_id IS NULL;

或者（佳）:

SELECT
    	/*+ HASH_AJ */...
    FROM
    	ROLLUP R
    WHERE
    	ource_id NOT IN (
    		SELECT
    			ource_id
    		FROM
    			title T
    		WHERE
    			ource_id IS NOT NULL
    	)

## 4. 重要总结 ##

讨论`IN`和`EXISTS`。

select * from t1 where x in ( select y from t2 )

事实上可以理解为：

SELECT
    	*
    FROM
    	t1,
    	(SELECT DISTINCT y FROM t2) t2
    WHERE
    	t1.x = t2.y;

如果你有一定的`SQL`优化经验，从这句很自然的可以想到`t2`绝对不能是个大表，因为需要对`t2`进行全表的`唯一排序`。

如果`t2`很大，这个排序的性能是不可忍受的，但是`t1`可以很大，为什么呢？

最通俗的理解就是因为`t1.x=t2.y`可以走索引。但这并不是一个很好的解释。

试想，如果`t1.x`和`t2.y`都有索引，我们知道索引是种有序的结构，因此`t1`和`t2`之间最佳的方案是走`merge join`。

另外，如果`t2.y`上有索引，对`t2`的排序性能也有很大提高。

select * from t1 where exists ( select null from t2 where y = x )

可以理解为：

for x in ( select * from t1 ) 
    loop 
    if ( exists ( select null from t2 where y = x.x ) 
    then 
    OUTPUT THE RECORD! 
    end if 
    end loop

这个更容易理解，`t1`永远是个表扫描！因此`t1`绝对不能是个大表，而`t2`可以很大，因为`y=x.x`可以走`t2.y`的索引。

综合以上对`IN/EXISTS`的讨论，我们可以得出一个基本通用的结论：

1.  `IN`适合于外表大而内表小的情况；
2.  `EXISTS`适合于外表小而内表大的情况。

我们要根据实际的情况做相应的优化，不能绝对的说谁的效率高谁的效率低，所有的事都是相对的

[c9bcbeaed7c74fccb05f862241bd4fab.png]: https://img-blog.csdnimg.cn/c9bcbeaed7c74fccb05f862241bd4fab.png
[d9fc6d69468b42bfabbdf923c45f3287.png]: https://img-blog.csdnimg.cn/d9fc6d69468b42bfabbdf923c45f3287.png
[d7891783152f4a518579206850376be4.png]: https://img-blog.csdnimg.cn/d7891783152f4a518579206850376be4.png