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 是否相等。
所有它适合子查询中表比外表大且有索引的场景。
执行过程
- 外层查询
外层查询扫描 stock
表的所有记录,这里有 100 条记录需要处理。
- 内层查询(子查询)
对于每一条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 次对比。
如果子查询得到的结果比较大,还会将子查询的结果存储在哈希表中,这样就能快速匹配数据。
执行过程
- 子查询执行
首先。MySQL 会执行子查询:
SELECT user_id
FROM `order`;
这将从 order
表中提取所有的 user_id
,生成一个包含 10,000 条记录的结果集。
- 主查询执行
接下来,MySQL 会执行主查询:
SELECT *
FROM `stock`
WHERE user_id IN (/* 子查询结果集 */);
- 执行细节分析
a. 子查询结果集缓存:
MySQL 会将子查询的结果集(10,000 条 user_id
)存放在一个临时表或缓存中。
b. 主查询扫描 stock
表:
MySQL 会扫描 stock
表中的每一条记录(共 100 条)。
c. 检查 IN
条件:
对于每条 stock
记录,MySQL 会检查其 user_id
是否在子查询结果集中。
如果 stock.user_id
存在于子查询结果集中,则返回该记录。
总结
简单总结:外层查询表量级小于子查询表,则用 exists ,外层查询表量级大于子查询表,则用 in,如果外层和子查询表差不多,则都行。简易具体情况还是以 explain 分析为主。