IN与=子查询的性能差异及大表执行机制技术咨询

背景说明 #

用户针对获取表中最新日期数据，编写了两个等价的SQL查询：

• Query 1：SELECT * FROM tbl WHERE dw_date = (SELECT MAX(dw_date) FROM tbl)
• Query 2：SELECT * FROM tbl WHERE dw_date IN (SELECT MAX(dw_date) FROM tbl)

二者返回结果完全一致，现针对大表场景的性能差异、核心执行逻辑问题做如下分析：

核心问题解答 #

1. 内部子查询是否会针对外部查询的每一行执行？ #

不会。这种子查询属于无关联子查询（子查询未引用外部表的任何字段），主流数据库的优化器都会识别出它只需要计算一次，不会逐行触发子查询执行。优化器会优先执行子查询得到MAX(dw_date)的结果，再将该值代入外部查询做过滤。

2. 无论使用IN还是=，max(dw_date)是否都会重复计算？ #

不会重复计算。只要是无关联子查询，优化器会将其从主查询中提取出来，作为独立的预计算步骤仅执行一次，计算结果会被缓存后复用在外部查询中。由于MAX(dw_date)只能返回单个值，IN和=的逻辑完全等价，部分数据库优化器甚至会直接将IN语法转换为=处理，因此二者的计算次数没有区别。

后端执行逻辑与性能表现 #

执行流程 #

1. 子查询预计算：优化器先执行SELECT MAX(dw_date) FROM tbl
   • 若dw_date字段存在索引：数据库直接通过索引的有序特性定位最大值（无需全表扫描），时间复杂度为O(log n)甚至O(1)
   • 若dw_date无索引：则需全表扫描一次计算最大值，时间复杂度为O(n)
2. 外部查询过滤：将子查询得到的最大值代入主查询，执行SELECT * FROM tbl WHERE dw_date = [最大值]
   • 若dw_date有索引：通过索引快速匹配所有符合条件的行
   • 若dw_date无索引：需再次全表扫描过滤出符合条件的行

大表场景性能差异 #

两个查询在大表场景下的性能几乎无差异，核心影响因素是dw_date字段是否有索引：

• 有索引时：子查询和外部查询都能通过索引高效执行，整体性能优异，二者耗时基本一致
• 无索引时：子查询和外部查询各需一次全表扫描，总耗时为两次全表扫描的时间，但两个查询的开销完全相同

内容的提问来源于stack exchange，提问作者amarjeet kushwaha