SQL中子查询、CTE与临时表的性能对比及适用场景咨询

阿华AIGC实验室

2026-4-23

我有一个关于SQL中“子查询”的问题。假设我想写一个使用子查询的查询，哪种方法性能最好？
方法1：嵌套子查询写法
SELECT a.name, a.surname
FROM (
SELECT tab1.ID, tab1.name, tab2.surname
FROM tab1
INNER JOIN tab2 on tab1.ID = tab2.ID
) a
WHERE a.name = 'John'
方法2：CTE写法
with cte1 as (
SELECT tab1.ID, tab1.name, tab2.surname
FROM tab1
INNER JOIN tab2 on tab1.ID = tab2.ID
)
select cte1.name, cte1.surname
from cte1
where cte1.name = 'John'
方法3：临时表写法
if object_id('tempdb.dbo.#cte1', 'U') is not null
drop table #cte1;

select tab1.ID, tab1.name, tab2.surname
into #cte1
from tab1
inner join tab2 on tab1.ID = tab2.ID

select #cte1.name, #cte1.surname
from #cte1
where #cte1.name = 'John' -- 修正原代码笔误：原写法为#cte1 = 'John'
我通常用方法1，如果子查询太复杂就用方法2。从代码清晰度和简洁性来看，我觉得方法2最好。
我认为方法3里的临时表不会用我的本地内存，而是在服务器上创建表，因此对于大表或者像下面这样在一个窗口里写两个独立查询的情况，性能应该最好：
if object_id('tempdb.dbo.#cte1', 'U') is not null
drop table #cte1;

select tab1.ID, tab1.name, tab2.surname
into #cte1
from tab1
inner join tab2 on tab1.ID = tab2.ID
;

select #cte1.name, #cte1.surname
from #cte1
where #cte1.name = 'John'
;

select #cte1.name, #cte1.surname
from #cte1
where #cte1.name = 'Max'
;

嘿，我来帮你拆解这三种写法的性能差异和适用场景，帮你更清晰地做选择：

1. 嵌套子查询（方法1）

你平时用的这种写法，对于简单逻辑来说，绝大多数现代数据库（比如SQL Server、MySQL、PostgreSQL）的查询优化器会直接把嵌套的子查询和外层查询合并，生成的执行计划和你直接写tab1 JOIN tab2 WHERE name='John'几乎没区别——性能上不会有额外开销。但如果子查询逻辑复杂、嵌套层数多，优化器可能难以生成最优的执行计划，而且代码可读性会直线下降，后期维护起来很头疼。

2. CTE（方法2）

你觉得它清晰简洁，这点完全戳中了CTE的核心优势！CTE本质是逻辑上的临时结果集，不会实际存储数据，优化器通常会把它和外层查询合并执行，所以性能上和嵌套子查询基本一致。但它的可读性和维护性远胜子查询，尤其是当你需要把复杂逻辑拆分成多个步骤的时候，CTE能让代码像流水线一样一目了然。

不过要注意：有些数据库（比如旧版本的SQL Server）如果多次引用同一个CTE，会重复执行CTE的底层逻辑，这时候性能就会打折扣。但新版本的数据库大多已经优化了这个问题，或者你可以用MATERIALIZED CTE（部分数据库支持）来强制存储结果。

3. 临时表（方法3）

你说临时表存在服务器的tempdb里，这点是对的——它不会占用你的本地内存，而是存储在数据库服务器的tempdb中（数据可能在内存或磁盘，取决于数据量和服务器配置）。它的优势很明显：

复用数据集时性能拉满：比如你举的例子里要查John又查Max，临时表只需要执行一次JOIN生成数据，后续查询直接用现成的表，避免重复计算，这时候比CTE和子查询高效太多；
支持索引优化：对于大数据量的情况，你可以给临时表创建合适的索引，能大幅提升查询速度；
执行计划更稳定：因为数据是实际存储的，优化器不需要反复解析复杂的逻辑，执行计划更可靠。

当然临时表也有缺点：创建和维护临时表会有额外的开销（比如写磁盘的成本），如果数据量很小，反而不如CTE或子查询高效；而且代码量会多一些，需要处理临时表的创建和删除逻辑。

实操建议

简单查询：三种写法性能差异不大，优先选CTE，代码最清晰；
需要多次复用数据集/大数据量：果断用临时表，尤其是需要加索引的场景；
尽量少用多层嵌套子查询，除非逻辑特别简单，不然可读性太差；
最重要的一步：实际测试！ 不同数据库、不同数据量、不同索引配置下，性能表现可能不一样，建议用数据库的执行计划工具（比如SQL Server的“显示估计执行计划”、MySQL的EXPLAIN）对比每种写法的执行计划，再做最终选择。

备注：内容来源于stack exchange，提问作者Saguro