MySQL中EXISTS比IN查询快是认知误区？实测IN执行效率更高

DevOps 小满

2026-8-29

实验验证

第一步创建测试库与表结构
执行以下SQL完成库表初始化：

create database test;

use test;

create table user_purchase 
(
    order_id int primary key auto_increment,
    user_id int,
    amount int
);

create table users 
(
    user_id int primary key auto_increment,
    name varchar(15),
    age smallint(4)
);

alter table user_purchase 
    add foreign key(user_id) references users(user_id);

第二步插入随机测试数据
下载对应系统版本的mysql_random_data_load工具，解压后赋予执行权限，执行以下命令生成测试数据：

# 解压安装包后执行权限配置
chmod 744 mysql_random_data_load

./mysql_random_data_load test user_purchase 4000 --host 127.0.0.1  --password 123 --user root
./mysql_random_data_load test users 10000 --host 127.0.0.1  --password 123 --user root

第三步登录数据库执行两类查询统计耗时

-- EXISTS查询，稳定耗时约0.05秒
select * 
from users as u 
where exists (select 1 from user_purchase as up 
              where up.user_id = u.user_id);

-- IN查询，稳定耗时约0.02秒
select * 
from users 
where user_id in (select user_id from user_purchase 
                  group by user_id);

问题描述

测试结果显示，使用IN操作符的查询稳定耗时0.02秒，而使用EXISTS的查询稳定耗时0.04秒甚至更长。为何IN看似需要扫描更多数据行，实际执行速度却更快？两条查询的EXPLAIN执行计划如下：

IN查询执行计划：

mysql> EXPLAIN Select * from users where user_id IN (select user_id from user_purchase group by user_id);
+----+--------------+---------------+------------+--------+---------------+------------+---------+--------------------+-------+----------+-------------+
| id | select_type  | table         | partitions | type   | possible_keys | key        | key_len | ref                | rows  | filtered | Extra       |
+----+--------------+---------------+------------+--------+---------------+------------+---------+--------------------+-------+----------+-------------+
|  1 | SIMPLE       | users         | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL               | 11000 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2>   | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | test.users.user_id |     1 |   100.00 | NULL        |
|  2 | MATERIALIZED | user_purchase | NULL       | index  | user_id       | user_id    | 5       | NULL               |  5000 |   100.00 | Using index |
+----+--------------+---------------+------------+--------+---------------+------------+---------+--------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

EXISTS查询执行计划：

mysql> EXPlain Select * FROM users as u where exists (select 1 from user_purchase as up where up.user_id = u.user_id);
+----+--------------------+-------+------------+------+---------------+---------+---------+----------------+-------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key     | key_len | ref            | rows  | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+---------+---------+----------------+-------+----------+-------------+
|  1 | PRIMARY            | u     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL           | 11000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | up    | NULL       | ref  | user_id       | user_id | 5       | test.u.user_id |     1 |   100.00 | Using index |
+----+--------------------+-------+------------+------+---------------+---------+---------+----------------+-------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

原因解析

性能差异完全来自两条查询的执行策略不同，从执行计划字段就能直接看出核心区别：

IN查询触发了子查询物化优化
优化器没有将IN子查询和外层查询做关联执行，而是先单独运行内层子查询：由于子查询仅需返回user_id字段，直接走user_id二级索引的覆盖扫描，一次遍历即可拿到去重后的所有user_id，生成临时物化表。同时MySQL会自动为这个临时表的user_id字段生成哈希索引（即执行计划里的<auto_key>）。
后续遍历外层users表的11000行数据时，每一行的user_id直接到物化表做哈希匹配，单次查找时间复杂度为O(1)。整个流程里内层子查询仅执行1次，没有反复调用的额外开销。
EXISTS查询走了相关子查询执行逻辑
该策略下MySQL会先遍历外层users表的每一行，每取出一个user_id，就触发一次内层子查询，到user_purchase的user_id索引里查找是否存在匹配记录。
虽然单次索引查找速度很快，但总共要执行11000次子查询查找，反复的上下文切换、子查询调用的累计开销，反而高于一次物化+哈希匹配的总成本。

注：网上流传的"EXISTS性能一定优于IN"是MySQL 5.5及更早版本的旧结论。当时的优化器没有子查询物化能力，会将IN子查询改写成效率很差的相关子查询执行，才会出现IN比EXISTS慢的情况。从MySQL 5.6版本引入子查询物化、自动临时键优化之后，在子查询可通过覆盖索引扫描、结果集规模不大的场景下，IN的性能通常会优于EXISTS。

内容的提问来源于stack exchange，提问作者Steve Wu