SQL中的IN与NOT IN、EXISTS与NOT EXISTS 的区别及性能分析

缺乏、安全感 2023-10-06 20:03 34阅读 0赞

#### 阅读目录 ####

*  in 和 exists
 *  not in 和 not exists
 *  in 与 = 的区别
 *  MySQL 中 exists 的用法详解
 *   *  数据库表设计
     *   *  学生表： t\_student
         *  班级学生表：t\_class\_student
     *  常用查询
     *   *  已分配班级的学生名单
         *  未分配班级的学生名单
         *  \* 已分配 三年级1班 的学生名单
         *  已分配 并且班级是 一年级1班 和 一年级2班 的学生名单
         *  查询全部学生名单
         *  已分配 三年级1班 的并且年龄大于10岁的学生名单
     *  exists 与 in 的效率比较
     *   *  通过 in 实现已分配班级的学生名单
         *  通过 in 实现未分配班级的学生名单
         *  循环嵌套查询执行原理
         *  循环优化策略
         *  exists 和 in 查询原理的区别

## 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)

## not in 和 not exists ##

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

create table t1(c1 int,c2 int);
    
    create table t2(c1 int,c2 int);
    
    insert into t1 values(1,2);
    
    insert into t1 values(1,3);
    
    insert into t2 values(1,2);
    
    insert into t2 values(1,null);

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]

执行结果：无
    select * from t1 where c2 not in(select c2 from t2);
    
    执行结果：1  3
    select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2)

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个 select 语句的执行计划，也会不同，后者使用了 hash\_aj，所以，请尽量不要使用 not in (它会调用子查询)，而尽量使用 not exists（它会调用关联子查询）。

如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用 not in，并且可以通过提示让它用 hasg\_aj 或 merge\_aj 连接。

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

## in 与 = 的区别 ##

select name from student where name in('zhang','wang','zhao');
    与
    select name from student where name='zhang' or name='wang' or name='zhao'

结果是相同的，MySQL or 的意思是条件都会执行。前人踩坑后人规避哈

## MySQL 中 exists 的用法详解 ##

语法

SELECT column1 FROM t1 WHERE [conditions] and EXISTS (SELECT * FROM t2 );

括号中的子查询并不会返回具体的查询到的数据，只是会返回 true 或者 false，如果外层 sql的字段在子查询中存在则返回 true，不存在则返回 false。

即使子查询的查询结果是 `null`，只要是对应的字段是存在的，子查询中则返回 true，下面有具体的例子。

执行过程

1、首先进行外层查询，在表 `t1` 中查询满足条件的 column1  
2、接下来进行内层查询，将满足条件的 column1 带入内层的表 t2 中进行查询，  
3、如果内层的表 t2 满足查询条件，则返回 true，该条数据保留  
4、如果内层的表 t2 不满足查询条件，则返回 false，则删除该条数据  
5、最终将外层的所有满足条件的数据进行返回

### 数据库表设计 ###

#### 学生表： t\_student ####

CREATE TABLE `t_student` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(100) DEFAULT '' COMMENT '学生姓名',
      `age` int NOT NULL COMMENT '年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='学生表';

导入部分数据

INSERT INTO `t_student` (`id`, `name`, `age`)
    VALUES
       (1, '小张', 10),
       (2, 'chenille', 13),
       (3, '小王', 15),
       (4, '小米', 11),
       (5, 'dong', 13),
       (6, 'xi', 12),
       (7, 'chenille', 13),
       (8, '小王地方', 15),
       (9, '米来', 11),
       (10, 'dong', 13),
       (11, '呵呵', 12),
       (12, 'chenille', 13),
       (13, '小赵', 15),
       (14, '小米-0', 11),
       (15, 'bei', 13),
       (16, 'xi-xx', 12),
       (17, 'chenille', 13),
       (18, '小王-hehe', 15),
       (19, '小米-qian', 11),
       (20, 'dong', 13),
       (21, 'xi', 12),
       (22, 'chenille', 13),
       (23, '小王-1', 15),
       (24, '小米-2', 11),
       (25, 'dong-3', 13),
       (26, 'xi-0', 12),
       (27, 'chenille-4', 13),
       (28, '小王-4', 15),
       (29, '小米-7', 11),
       (30, 'dong-1', 13),
       (31, 'xi-5', 12),
       (32, '貔貅', 10),
       (33, '耄耋', 12),
       (34, '饕餮', 9),
       (35, '龙', 13),
       (36, '青牛', 12);

#### 班级学生表：t\_class\_student ####

CREATE TABLE `t_class_student` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `student_id` int(11) NOT NULL COMMENT '学生ID',
      `class_id` int(11) NOT NULL COMMENT '班号',
      `class_name` varchar(100) DEFAULT NULL COMMENT '班级名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班级学生表';

导入部分数据

INSERT INTO `t_class_student` (`id`, `student_id`, `class_id`, `class_name`)
    VALUES
    	(1, 1, 1, '一年级1班'),
    	(2, 2, 1, '一年级1班'),
    	(3, 3, 1, '一年级1班'),
    	(4, 4, 1, '一年级1班'),
    	(5, 5, 1, '一年级1班'),
    	(6, 6, 1, '一年级1班'),
    	(7, 7, 1, '一年级1班'),
    	(8, 8, 1, '一年级1班'),
    	(9, 9, 1, '一年级1班'),
    	(10, 10, 1, '一年级1班'),
    	(11, 11, 2, '一年级2班'),
    	(12, 12, 2, '一年级2班'),
    	(13, 13, 2, '一年级2班'),
    	(14, 14, 2, '一年级2班'),
    	(15, 15, 2, '一年级2班'),
    	(16, 16, 2, '一年级2班'),
    	(17, 17, 2, '一年级2班'),
    	(18, 18, 2, '一年级2班'),
    	(19, 19, 2, '一年级2班'),
    	(20, 20, 2, '一年级2班'),
    	(21, 21, 3, '二年级2班'),
    	(22, 22, 3, '二年级2班'),
    	(23, 23, 3, '二年级2班'),
    	(24, 24, 3, '二年级2班'),
    	(25, 25, 3, '二年级2班'),
    	(26, 26, 3, '二年级2班'),
    	(27, 27, 3, '二年级2班'),
    	(28, 28, 3, '二年级2班'),
    	(29, 29, 3, '二年级2班'),
    	(30, 30, 3, '二年级2班'),
    	(31, 31, 4, '三年级1班'),
        (32, 32, 4, null);

### 常用查询 ###

#### 已分配班级的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	EXISTS (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    	);

+----+--------------+-----+
    | id | name         | age |
    +----+--------------+-----+
    |  1 | 小张         |  10 |
    |  2 | chenille     |  13 |
    |  3 | 小王         |  15 |
    |  4 | 小米         |  11 |
    |  5 | dong         |  13 |
    |  6 | xi           |  12 |
    |  7 | chenille     |  13 |
    |  8 | 小王地方     |  15 |
    |  9 | 米来         |  11 |
    | 10 | dong         |  13 |
    | 11 | 呵呵         |  12 |
    | 12 | chenille     |  13 |
    | 13 | 小赵         |  15 |
    | 14 | 小米-0       |  11 |
    | 15 | bei          |  13 |
    | 16 | xi-xx        |  12 |
    | 17 | chenille     |  13 |
    | 18 | 小王-hehe    |  15 |
    | 19 | 小米-qian    |  11 |
    | 20 | dong         |  13 |
    | 21 | xi           |  12 |
    | 22 | chenille     |  13 |
    | 23 | 小王-1       |  15 |
    | 24 | 小米-2       |  11 |
    | 25 | dong-3       |  13 |
    | 26 | xi-0         |  12 |
    | 27 | chenille-4   |  13 |
    | 28 | 小王-4       |  15 |
    | 29 | 小米-7       |  11 |
    | 30 | dong-1       |  13 |
    | 31 | xi-5         |  12 |
    | 32 | 貔貅         |  10 |
    +----+--------------+-----+
    32 rows in set (0.00 sec)

#### 未分配班级的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	NOT EXISTS (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    	);

+----+--------+-----+
    | id | name   | age |
    +----+--------+-----+
    | 33 | 耄耋   |  12 |
    | 34 | 饕餮   |   9 |
    | 35 | 龙     |  13 |
    | 36 | 青牛   |  12 |
    +----+--------+-----+
    4 rows in set (0.00 sec)

#### \* 已分配 三年级1班 的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	EXISTS (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    		AND class_id = 4
    	);

+----+--------+-----+
    | id | name   | age |
    +----+--------+-----+
    | 31 | xi-5   |  12 |
    | 32 | 貔貅   |  10 |
    +----+--------+-----+
    2 rows in set (0.00 sec)

查询到的 class\_name 字段为 null，但是子查询返回的结果为 true。因为 class\_name 没有作为筛选条件。

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

#### 已分配 并且班级是 一年级1班 和 一年级2班 的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	EXISTS (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    		AND class_id IN (1, 2)
    	);

#### 查询全部学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	EXISTS (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			1 = 1
    	);

+----+--------------+-----+
    | id | name         | age |
    +----+--------------+-----+
    |  1 | 小张         |  10 |
    |  2 | chenille     |  13 |
    |  3 | 小王         |  15 |
    |  4 | 小米         |  11 |
    |  5 | dong         |  13 |
    |  6 | xi           |  12 |
    |  7 | chenille     |  13 |
    |  8 | 小王地方     |  15 |
    |  9 | 米来         |  11 |
    | 10 | dong         |  13 |
    | 11 | 呵呵         |  12 |
    | 12 | chenille     |  13 |
    | 13 | 小赵         |  15 |
    | 14 | 小米-0       |  11 |
    | 15 | bei          |  13 |
    | 16 | xi-xx        |  12 |
    | 17 | chenille     |  13 |
    | 18 | 小王-hehe    |  15 |
    | 19 | 小米-qian    |  11 |
    | 20 | dong         |  13 |
    | 21 | xi           |  12 |
    | 22 | chenille     |  13 |
    | 23 | 小王-1       |  15 |
    | 24 | 小米-2       |  11 |
    | 25 | dong-3       |  13 |
    | 26 | xi-0         |  12 |
    | 27 | chenille-4   |  13 |
    | 28 | 小王-4       |  15 |
    | 29 | 小米-7       |  11 |
    | 30 | dong-1       |  13 |
    | 31 | xi-5         |  12 |
    | 32 | 貔貅         |  10 |
    | 33 | 耄耋         |  12 |
    | 34 | 饕餮         |   9 |
    | 35 | 龙           |  13 |
    | 36 | 青牛         |  12 |
    +----+--------------+-----+
    36 rows in set (0.00 sec)

#### 已分配 三年级1班 的并且年龄大于10岁的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	age > 10
    AND EXISTS (
    	SELECT
    		student_id
    	FROM
    		t_class_student
    	WHERE
    		student_id = s.id
    	AND class_id = 4
    );

### exists 与 in 的效率比较 ###

上面的这些查询其实也可以通过 in 关键字来实现，下面我们写一下 in 关键字对应的查询语句。

#### 通过 in 实现已分配班级的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	id IN (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    	);

#### 通过 in 实现未分配班级的学生名单 ####

SELECT
    	*
    FROM
    	t_student AS s
    WHERE
    	id NOT IN (
    		SELECT
    			student_id
    		FROM
    			t_class_student
    		WHERE
    			student_id = s.id
    	);

#### 循环嵌套查询执行原理 ####

循环由外向内，外层循环执行一次，内层循环则需要完整的执行一次，内层执行执行完后返回执行结果，外层循环继续执行，直到外层循环完全执行完成。

#### 循环优化策略 ####

有了上面的执行原理的说明，我们明白了一个道理：  
内层循环次数的多少不会影响到外层的次数，但是外层循环的次数直接会影响到内层循环的次数，外层循环每多一次，内层循环就需要多完整的一次循环，所以我们优化的目标其实就是使外层的循环次数尽量少。

总结来说：  
小表驱动大表。小表就是外层循环，大表就是内层循环，也就是尽量减少外层循环的次数。

#### exists 和 in 查询原理的区别 ####

exists ：  
外表先进行循环查询，将查询结果放入 exists 的子查询中进行条件验证，确定外层查询数据是否保留。

in ：  
先查询内表，将内表的查询结果当做条件提供给外表查询语句进行比较。

结论  
通过上面的优化策略分析和 exists 和 in 的查询原理的分析，将这两块内容结合起来其实就得出了我们想要的一个结论：

> 外层小表，内层大表（或者将 sql 从左到右来看：左面小表，右边大表）： exists 比 in 的效率高  
> 外层大表，内层小表（或者将 sql 从左到右来看：左面大表，右边小表）： in 比 exists 的效率高

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2djaGVufg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/8c3c77e23c0241248898f52a3df298c6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2djaGVufg==,size_20,color_FFFFFF,t_70,g_se,x_16
[6076da9585a94cd3bfe36ecfe9d33cdc.png]: https://img-blog.csdnimg.cn/6076da9585a94cd3bfe36ecfe9d33cdc.png