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 01.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) ) ENGINEInnoDB; -- 班级表内表 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) ) ENGINEInnoDB; -- 生成测试数据的存储过程 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 性能对比表格下表展示了三种场景下的平均执行时间单位毫秒场景EXISTSIN差异率外表大内表小有索引120450275%外表小内表大有索引158-47%外表大内表小无索引22001800-18%3.3 执行计划分析EXISTS 典型执行计划- Nested loop semijoin (cost...) (actual time0.1..120 rows100000 loops1) - Table scan on s (cost...) (actual time0.1..40 rows100000 loops1) - Single-row index lookup on c using PRIMARY (ids.class_id) (cost...) (actual time0.001..0.001 rows1 loops100000)IN 典型执行计划- Hash join (inner join) (cost...) (actual time5..450 rows100000 loops1) - Table scan on s (cost...) (actual time0.1..40 rows100000 loops1) - Hash - Table scan on temporary - Materialize (cost...) (actual time0.5..0.5 rows1000 loops1) - Index scan on c using PRIMARY (cost...) (actual time0.1..0.3 rows1000 loops1)4. 优化决策树与实践建议基于测试结果我们总结出以下决策流程数据分布判断外表行数 10×内表行数 → 优先考虑 EXISTS内表行数 10×外表行数 → 优先考虑 IN索引检查关联字段无索引 → 无论哪种写法性能都差必须先建索引有关联索引 → 根据数据分布选择NULL 值处理子查询结果可能包含 NULL → 使用 EXISTS 更安全NOT IN 对 NULL 处理有问题具体优化技巧-- 使用 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 子查询有三种处理策略物化Materialization将子查询结果存入临时表EXISTS 转换MySQL 8.0 优化器会自动转换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) 的复合索引
