MySQL IN查询与EXISTS子查询在大数据量下的性能差异

处理百万级以上数据时，SQL语句的写法直接决定了系统是秒级响应还是卡死超时。最常见的问题之一就是在关联查询时，究竟该用 IN 还是 EXISTS。很多人认为这两个关键字可以互换，但在大数据量下，它们的底层执行逻辑截然不同。

1. 准备测试环境

为了直观演示差异，首先需要构建两张表：一张代表“部门”，数据量较小；另一张代表“员工”，数据量巨大。

执行 以下 SQL 脚本创建表结构并填充基础数据：

-- 创建小表：部门表
CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入10条部门数据
INSERT INTO `dept` (`dept_name`) VALUES 
('研发部'), ('市场部'), ('销售部'), ('人事部'), ('财务部'),
('后勤部'), ('运维部'), ('测试部'), ('法务部'), ('客服部');

-- 创建大表：员工表（假设有100万条数据）
CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(50) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_dept_id` (`dept_id`) -- 关键：必须在关联字段上建立索引
) ENGINE=InnoDB;

-- 插入100万条模拟数据（可以使用存储过程批量生成，此处略去生成脚本）
-- 假设 emp 表中已包含 1,000,000 行数据

2. 理解核心差异：小表驱动大表

在深入代码之前，必须先建立核心认知：数据库关联查询的本质是循环嵌套。无论你写的是 JOIN、IN 还是 EXISTS，数据库都需要拿着一张表的数据，去另一张表里查找匹配项。

性能优化的黄金法则就是：用小表的数据集去驱动大表。

• 如果外层循环小，内层循环大（且内层有索引），总查询次数 = 小表行数 × 1次索引查找（极快）。
• 如果外层循环大，内层循环小，总查询次数 = 大表行数 × 1次索引查找（大表行数太多，累加起来慢）。

为了更清晰地展示两种写法的执行流程，请看以下逻辑图：

3. 场景一：当子查询表（B表）较小

假设需求是：查询所有“研发部”的员工。这里 dept（部门表）数据量极小（10条），emp（员工表）数据量极大（100万条）。

在这种子查询表很小的情况下，IN 通常是更优的选择。

分析：
MySQL 会先执行 IN 里面的子查询，从 dept 表中拿到所有的 id（比如结果是 1, 5, 8）。这个过程非常快，因为表只有10行。然后，MySQL 利用 emp 表上的索引，快速定位这3个 dept_id 对应的员工。这相当于用小表的结果去过滤大表。

编写 如下 SQL：

-- 推荐：子查询表小，使用 IN
SELECT * 
FROM emp 
WHERE dept_id IN (SELECT id FROM dept WHERE dept_name = '研发部');

执行 检查计划（在 MySQL 客户端输入）：

EXPLAIN SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE dept_name = '研发部');

观察结果中的 rows 列，你会发现扫描的行数主要集中在对 emp 表的索引查找上，效率很高。

4. 场景二：当子查询表（B表）较大

假设需求反过来，或者逻辑上子查询对应的表数据量很大（例如查询“有黑色头发的员工”对应的部门，假设员工表是子查询）。虽然实际业务中通常通过 JOIN 优化，但在必须使用子查询且子查询表巨大的情况下，EXISTS 往往表现更好。

分析：
EXISTS 的执行逻辑是：对外表（主查询的表）进行遍历。
对于 emp 表中的每一行数据，都去 dept 表里问一下：“有没有对应的部门记录？”
因为 dept 表很小且 id 为主键，这个“询问”的过程极快（索引查找）。哪怕 emp 表很大，只要 dept 表的索引利用得当，整体速度是可以接受的。

关键点在于：EXISTS 只要子查询中找到一条匹配记录，就会立刻停止继续查找该条记录，返回 TRUE，不需要遍历整个子查询表。

编写 如下 SQL：

-- 场景：外表大，子查询表相对小或逻辑复杂，使用 EXISTS
SELECT * 
FROM emp e
WHERE EXISTS (
    SELECT 1 
    FROM dept d 
    WHERE d.id = e.dept_id AND d.dept_name = '研发部'
);

注意：这里 EXISTS 后面的子查询 SELECT 1 是一种约定俗成的写法。因为 EXISTS 只关心“有没有数据”，而不关心“数据是什么”，所以写 SELECT * 或 SELECT 1 或 SELECT id 在性能上通常没有区别（优化器会忽略列列表），但写 SELECT 1 语义更清晰。

5. 性能对比总结与选择策略

为了方便记忆，我们将两种情况总结为一张对照表。

6. 进阶优化：索引是关键

无论使用 IN 还是 EXISTS，关联字段上必须有索引。

如果 emp.dept_id 没有索引：

• IN 查询会退化为全表扫描，数据库无法快速定位，只能一行行对比。
• EXISTS 查询会变成灾难，因为外表每一行都要触发一次子查询的全表扫描。

执行 以下命令确保索引存在：

-- 确保员工表的部门 ID 字段有索引
SHOW INDEX FROM emp;

-- 如果没有，创建索引
ALTER TABLE emp ADD INDEX idx_dept_id (dept_id);

7. 实战中的陷阱：NULL 值处理

在使用 IN 时有一个特殊的坑：如果子查询返回的结果中包含 NULL，可能会导致整个查询结果为空（在某些 NOT IN 场景下）或者影响性能。

原则：

• 在使用 NOT IN 时，务必确保子查询字段 NOT NULL，或者在子查询中手动过滤掉 NULL。
• EXISTS 对 NULL 不敏感，它只判断行是否存在，不会因为 NULL 值导致逻辑错误。

编写 安全的 NOT IN 语句：

-- 安全写法：过滤掉 NULL 值
SELECT * FROM emp 
WHERE dept_id NOT IN (
    SELECT id FROM dept WHERE id IS NOT NULL
);

或者，对于 NOT IN 场景，更推荐使用 NOT EXISTS 以避免 NULL 带来的逻辑陷阱：

-- 推荐写法：避免 NULL 陷阱
SELECT * FROM emp e
WHERE NOT EXISTS (
    SELECT 1 FROM dept d WHERE d.id = e.dept_id
);