SQL子查询效率更低？为何替换为常量列表后性能提升10倍

首先明确：子查询从来都不是“坏选择”，你的场景里的性能差异是特定条件下数据库优化器执行计划的问题，而非子查询本身的问题。

为什么手动拼常量列表更快？ #

你的原查询中，虽然子查询是不相关的（不依赖外部表table1的字段），但部分数据库的优化器可能没有正确将其优化为“物化子查询”（即先一次性算出table2的结果集，再和table1做匹配），反而错误地将其处理为相关子查询——也就是对table1的每一行都执行一次子查询，这在table1有数十万行的情况下会直接拉垮性能。

而手动拼出逗号分隔的常量列表后，数据库可以直接把这些常量转换成哈希集合，再利用table1的indexedCol索引快速匹配，避免了重复执行子查询的开销，自然效率提升明显。

你忽略的关键要点 #

• 常量数量上限：几乎所有数据库对IN子句中的常量数量都有默认限制（比如MySQL默认是1000），当你传入5000个值时，要么会触发报错，要么数据库会自动将其拆分为多个IN条件（比如IN (a,b,c) OR IN (d,e,f)...），虽然当前可能没报错，但后续数据量增长后会出问题。
• 数据一致性风险：手动拼常量是基于table2的快照数据，如果在你查询table1的过程中，table2的数据发生了变更（比如新增、删除符合iCol2="somevalue"的行），那么查询结果会和实时数据不一致；而子查询是实时执行的，能保证结果的一致性。
• 代码维护成本：手动拼接SQL需要额外的步骤（先查table2，再处理结果拼入主查询），代码复杂度上升，后期如果修改table2的过滤条件，需要同时修改两处逻辑，容易出错。

更优的替代方案 #

不用纠结子查询还是手动拼常量，试试这两种更稳妥的方式：

1. 改用JOIN查询
   大部分数据库对JOIN的优化逻辑更成熟，能高效利用两张表的索引：

   SELECT DISTINCT table1.*
   FROM table1
   JOIN table2 ON table1.indexedCol = table2.indexedCol
   WHERE table2.iCol2 = 'somevalue'
   

   加DISTINCT是为了避免table2中有重复indexedCol时，table1的行被重复返回，和原IN子查询的逻辑保持一致。

2. 强制优化器物化子查询
   如果坚持用子查询，可以给数据库加优化提示（不同数据库语法不同），让优化器先计算子查询的结果并缓存，再和table1匹配：

   • MySQL：SELECT * FROM table1 WHERE indexedCol IN (SELECT /*+ MATERIALIZE */ indexedCol FROM table2 WHERE iCol2 = 'somevalue')
   • PostgreSQL：SELECT * FROM table1 WHERE indexedCol IN (SELECT indexedCol FROM table2 WHERE iCol2 = 'somevalue') WITH MATERIALIZED

总结 #

子查询本身没有问题，你的性能差异是优化器执行计划的异常导致的。手动拼常量虽然暂时提升了效率，但带来了一致性、维护性和上限的问题，更推荐用JOIN或者优化器提示来解决性能问题。

内容的提问来源于stack exchange，提问作者Steven Teo