MySQL 8.0 EXISTS 与 IN 性能对比：基于 10 万条数据的 3 种场景实测

在数据库查询优化中，EXISTS 和 IN 是两种常用的子查询操作符，它们都能实现类似的数据过滤功能，但底层执行机制和性能表现却大相径庭。本文将基于 MySQL 8.0 版本，通过 10 万条测试数据的三种典型场景，深入分析两者的性能差异，并提供可落地的优化建议。

1. 测试环境与数据准备

1.1 数据库配置

本次测试使用 MySQL 8.0.28 社区版，关键配置如下：

[mysqld] innodb_buffer_pool_size = 4G innodb_log_file_size = 512M max_connections = 200 query_cache_type = 0

1.2 测试数据生成

我们创建了两个关联表，并插入 10 万条测试数据：

-- 学生表（外表） CREATE TABLE `students` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT '', `age` tinyint unsigned DEFAULT NULL, `class_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_class_id` (`class_id`), KEY `idx_age` (`age`) ) ENGINE=InnoDB; -- 班级表（内表） CREATE TABLE `classes` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT '', `teacher` varchar(50) DEFAULT '', PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB; -- 生成测试数据的存储过程 DELIMITER // CREATE PROCEDURE generate_test_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 100000 DO INSERT INTO students(name, age, class_id) VALUES (CONCAT('Student', i), FLOOR(10 + RAND() * 10), FLOOR(1 + RAND() * 1000)); IF i <= 1000 THEN INSERT INTO classes(name, teacher) VALUES (CONCAT('Class', i), CONCAT('Teacher', FLOOR(1 + RAND() * 100))); END IF; SET i = i + 1; END WHILE; END // DELIMITER ; CALL generate_test_data();

注意：实际测试时建议分批插入数据，避免单次事务过大导致性能问题。

2. 三种测试场景设计

2.1 场景一：外表大内表小（10万 vs 1千）

-- EXISTS 写法 SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id); -- IN 写法 SELECT * FROM students s WHERE class_id IN (SELECT id FROM classes);

2.2 场景二：外表小内表大（1千 vs 10万）

-- 临时创建小外表 CREATE TEMPORARY TABLE small_students AS SELECT * FROM students LIMIT 1000; -- EXISTS 写法 SELECT * FROM small_students s WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id); -- IN 写法 SELECT * FROM small_students s WHERE class_id IN (SELECT id FROM classes);

2.3 场景三：关联字段有无索引

-- 无索引情况（先删除索引） ALTER TABLE students DROP INDEX idx_class_id; -- 有索引情况（恢复索引） ALTER TABLE students ADD INDEX idx_class_id(class_id);

3. 性能测试方法与结果

3.1 测试方法

使用 MySQL 的EXPLAIN ANALYZE获取实际执行计划和时间：

EXPLAIN ANALYZE SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id);

3.2 性能对比表格

下表展示了三种场景下的平均执行时间（单位：毫秒）：

3.3 执行计划分析

EXISTS 典型执行计划：

-> Nested loop semijoin (cost=...) (actual time=0.1..120 rows=100000 loops=1) -> Table scan on s (cost=...) (actual time=0.1..40 rows=100000 loops=1) -> Single-row index lookup on c using PRIMARY (id=s.class_id) (cost=...) (actual time=0.001..0.001 rows=1 loops=100000)

IN 典型执行计划：

-> Hash join (inner join) (cost=...) (actual time=5..450 rows=100000 loops=1) -> Table scan on s (cost=...) (actual time=0.1..40 rows=100000 loops=1) -> Hash -> Table scan on <temporary> -> Materialize (cost=...) (actual time=0.5..0.5 rows=1000 loops=1) -> Index scan on c using PRIMARY (cost=...) (actual time=0.1..0.3 rows=1000 loops=1)

4. 优化决策树与实践建议

基于测试结果，我们总结出以下决策流程：

1. 数据分布判断

   • 外表行数 > 10×内表行数 → 优先考虑 EXISTS
   • 内表行数 > 10×外表行数 → 优先考虑 IN

2. 索引检查

   • 关联字段无索引 → 无论哪种写法性能都差，必须先建索引
   • 有关联索引 → 根据数据分布选择

3. NULL 值处理

   • 子查询结果可能包含 NULL → 使用 EXISTS 更安全（NOT IN 对 NULL 处理有问题）

4. 具体优化技巧

   -- 使用 JOIN 改写（有时比两者都好） SELECT DISTINCT s.* FROM students s JOIN classes c ON s.class_id = c.id; -- EXISTS 的极限优化写法 SELECT * FROM students s WHERE EXISTS (SELECT 1 FROM classes c WHERE c.id = s.class_id LIMIT 1);

5. 深度原理剖析

5.1 EXISTS 的工作机制

EXISTS 是典型的"半连接"（Semi-Join）操作，其核心特点：

• 子查询只需要返回 true/false
• 找到第一条匹配记录后立即停止扫描
• 执行次数 = 外表行数 × (找到匹配所需的平均内表扫描行数)

5.2 IN 的底层实现

MySQL 对 IN 子查询有三种处理策略：

1. 物化（Materialization）：将子查询结果存入临时表
2. EXISTS 转换：MySQL 8.0 优化器会自动转换
3. JOIN 重写：某些情况下会转为等值连接

5.3 版本差异

MySQL 5.7 vs 8.0 的关键改进：

• 8.0 引入了更智能的子查询物化策略
• 新增了SEMIJOIN和ANTIJOIN优化器提示
• 对NOT EXISTS的处理效率大幅提升

6. 真实案例分享

最近优化过一个电商平台的订单查询系统，原始查询类似：

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip_level > 3);

问题现象：

• 执行时间超过 2 秒
• customers 表有 50 万行，orders 表有 300 万行

优化方案：

SELECT o.* FROM orders o WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.vip_level > 3 ) LIMIT 1000;

优化效果：

• 查询时间降至 200ms
• 关键是在 customers 表上建立了 (id, vip_level) 的复合索引