MySQL面试题---exists 和 in 的区别

exists 和 in 的区别

exists 和 in 都用于子查询，exists 检查子查询是否返回结果集。返回布尔值。in 检查某个值是否在子查询返回的结果集中。返回具体的值。

exists

exists 的查询机制是循环外表，通过外表的每行数据去内表查询是否有匹配的值，一旦找到符合条件的记录，此次内表子查询就会停止执行。然后在通过下一个外表的值来查询，如此循环。

select * from `stock` where exists(select * from `order` where stock.user_id = order.user_id)

例如，stock表有 100 条数据，order 有 10000 条数据，那么 exists 会执行 100 次去order 中查询判断stock 的 user_id 和 order 的 user_id 是否相等。

所有它适合子查询中表比外表大且有索引的场景。

执行过程

1. 外层查询

外层查询扫描 stock 表的所有记录，这里有 100 条记录需要处理。

2. 内层查询（子查询）

对于每一条stock记录，都会执行一次子查询，检查order表中是否存在与该stock记录的user_id相匹配的记录。

• 每次子查询只需要找到一条匹配的记录即可返回TRUE，不需要扫描order表的所有记录
• 如果没有找到匹配记录，返回FALSE，外层查询丢弃该stock记录。
• 子查询的执行是通过关联条件stock.user_id = order.user_id实现的

in

而 in 子查询在执行时会先执行子查询并生成结果集，然后将结果集与外部查询的列进行比较，所以它适合子查询记录少，且主查询表大有索引的场景。

select * from `stock` where user_id in (select * from `order`)

同样stock表有 100 条数据，order有 10000 条数据，那么 in 操作会先执行子查询获取order的 10000 条数据这个结果集放在缓存中。

如果stock user__id 上有索引则可以利用索引查询，不然就是逐行检查 stock 中的 user_id 是否与结果集中的 user_id 一致执行，因此一共会进行 100 * 10000 次对比。

如果子查询得到的结果比较大，还会将子查询的结果存储在哈希表中，这样就能快速匹配数据。

执行过程

1. 子查询执行

首先。MySQL 会执行子查询：

SELECT user_id 
FROM `order`;

这将从 order 表中提取所有的 user_id，生成一个包含 10,000 条记录的结果集。

2. 主查询执行

接下来，MySQL 会执行主查询：

SELECT * 
FROM `stock` 
WHERE user_id IN (/* 子查询结果集 */);

3. 执行细节分析

a. 子查询结果集缓存：

MySQL 会将子查询的结果集（10,000 条 user_id）存放在一个临时表或缓存中。

b. 主查询扫描 stock 表：

MySQL 会扫描 stock 表中的每一条记录（共 100 条）。

c. 检查 IN 条件：

对于每条 stock 记录，MySQL 会检查其 user_id 是否在子查询结果集中。

如果 stock.user_id 存在于子查询结果集中，则返回该记录。

总结

简单总结：外层查询表量级小于子查询表，则用 exists ，外层查询表量级大于子查询表，则用 in，如果外层和子查询表差不多，则都行。简易具体情况还是以 explain 分析为主。