正文

SQL Server存储过程执行超8秒实战优化从创建临时表改用CTE到查询响应降至300毫秒的性能调优经验总结

/0 浏览量
1004

SQL Server存储过程执行超8秒实战优化从创建临时表改用CTE到查询响应降至300毫秒的性能调优经验总结

上周四下午，我盯着屏幕上那个”正在执行…“的进度条，已经等了整整8秒多。那一刻，整个办公室的同事都转过头来看我——不是因为我敲代码的声音太大，而是因为我的表情一定很难看。

作为一个跟SQL Server打了五年交道的老家伙，这种画面我以前只在同事那里听过。但这次，它轮到了我头上。今天就想跟你们聊聊这8秒变成300毫秒的完整故事，从发现问题到最终解决，中间踩了不少坑，也学到不少东西。

事情的起因

先说说背景。我们有一个核心业务查询存储过程，主要是从一张近500万行的大表里，按多个条件筛选数据，然后关联三张维度表，最后返回一个分页结果给前端。听起来很常规对吧？

问题出在一次例行维护之后。那天DBA更新了统计信息，顺便调整了几个索引，以为万事大吉。结果第二天，业务方反馈说某个报表页面打开特别慢。我一开始也没往心里去，觉得可能只是网络抖动。

直到我自己在测试环境复现，打开SQL Server Management Studio，右键那个存储过程，选择”执行”，然后……8.2秒。整整8.2秒，就为了返回不到1000条数据。

我第一反应是：不可能吧？同样的数据量，同样的条件，之前明明只需要不到1秒的。

第一个嫌疑：临时表

排查的第一步，自然是看执行计划。点开执行计划一看，心里咯噔一下。

存储过程里用了一个临时表 #TempResult，结构大概是这样的：

CREATE PROCEDURE [dbo].[usp_GetBusinessReport]
    @StartDate DATE,
    @EndDate DATE,
    @PageSize INT,
    @PageNumber INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 先把符合条件的数据插到临时表
    SELECT 
        a.TransactionID,
        a.CustomerID,
        a.Amount,
        a.TransactionDate,
        b.CustomerName,
        c.CategoryName,
        d.RegionName
    INTO #TempResult
    FROM [dbo].[Transactions] a
    INNER JOIN [dbo].[Customers] b ON a.CustomerID = b.CustomerID
    INNER JOIN [dbo].[Categories] c ON a.CategoryID = c.CategoryID
    INNER JOIN [dbo].[Regions] d ON b.RegionID = d.RegionID
    WHERE a.TransactionDate BETWEEN @StartDate AND @EndDate;

    -- 再对临时表做分页
    SELECT *
    FROM #TempResult
    ORDER BY TransactionDate DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    DROP TABLE #TempResult;
END

乍一看，逻辑没问题。先过滤，再关联，再分页。很经典的写法，很多教科书上都是这么教的。

但执行计划显示，那个 INSERT INTO #TempResult 操作的花费占了整个查询的75%。更关键的是，临时表创建完后，SQL Server给它分配了一个聚集索引（自动生成的 TransactionID 主键），但这个索引对后面的 ORDER BY 和分页几乎没有帮助。

我盯着那个执行计划看了十分钟，突然想到一个问题：为什么要先把数据塞进临时表，然后再查？这个中间步骤到底在干嘛？

转折：CTE的想法

CTE（Common Table Expression，公用表表达式）在SQL Server 2005就引入了，理论上完全可以替代这个临时表。但之前一直没敢用，主要是担心性能问题——以前听说过CTE在某些场景下会重复执行，性能反而更差。

这次逼到这份上了，我决定试试看。

改造后的存储过程大概长这样：

CREATE PROCEDURE [dbo].[usp_GetBusinessReport]
    @StartDate DATE,
    @EndDate DATE,
    @PageSize INT,
    @PageNumber INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH CTE_Transactions AS
    (
        SELECT 
            a.TransactionID,
            a.CustomerID,
            a.Amount,
            a.TransactionDate,
            b.CustomerName,
            c.CategoryName,
            d.RegionName,
            ROW_NUMBER() OVER (ORDER BY a.TransactionDate DESC) AS RowNum
        FROM [dbo].[Transactions] a
        INNER JOIN [dbo].[Customers] b ON a.CustomerID = b.CustomerID
        INNER JOIN [dbo].[Categories] c ON a.CategoryID = c.CategoryID
        INNER JOIN [dbo].[Regions] d ON b.RegionID = d.RegionID
        WHERE a.TransactionDate BETWEEN @StartDate AND @EndDate
    )
    SELECT 
        TransactionID,
        CustomerID,
        Amount,
        TransactionDate,
        CustomerName,
        CategoryName,
        RegionName
    FROM CTE_Transactions
    WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
    ORDER BY RowNum;
END

关键点有几个：

第一，去掉了临时表。 数据直接在CTE里处理，不再需要中间存储。

第二，用 ROW_NUMBER() 替代了 OFFSET/FETCH。 这个改动其实很关键。虽然OFFSET/FETCH看起来更简洁，但在大数据量场景下，它的性能表现并不稳定，尤其是当排序列没有合适的索引时。

第三，把分页逻辑内嵌到了CTE里。 这样SQL Server在优化执行计划时，可以更灵活地决定如何评估和获取数据。

结果：从8秒到300毫秒

改完之后，我深吸一口气，点击”执行”。

307毫秒。

不是3.07秒，是307毫秒。整整快了27倍。

我反复测了几次，结果都在300~400毫秒之间波动，非常稳定。

打开执行计划对比，差异非常明显。临时表版本里，那个 INSERT INTO #TempResult 的花费极高，而且临时表的统计信息不准，导致后续查询的估计行数偏差巨大。而CTE版本里，SQL Server能够更好地推断数据量，选择合适的join策略，整个查询看起来干净多了。

为什么CTE这么快？

这个问题值得好好说说，因为很多人对CTE有误解，觉得它只是”语法糖”，其实不然。

临时表和CTE在执行机制上有本质区别。

临时表的执行过程是： 先把所有数据物化到tempdb，创建聚集索引，然后才能做后续的查询。这个过程是串行的，必须等第一步完成，第二步才能开始。而且，临时表的统计信息往往是过时的——如果你插入的数据分布和原始表差异很大，优化器就会做出错误的选择。

CTE的执行过程是： 它本质上是一个命名子查询，不会被物化（除非某些特殊情况）。SQL Server可以把CTE中的逻辑和其他部分合并优化，有时候甚至会内联展开，避免中间存储的开销。更重要的是，优化器可以更清楚地看到整个查询的全貌，从而做出更好的计划选择。

当然，CTE也不是万能的。如果CTE被引用多次，SQL Server确实会重复执行它。但在这个场景里，CTE只被引用了一次，所以不存在这个问题。

后续优化：索引的功劳

CTE替换临时表之后，查询已经从8秒降到了300毫秒，但我觉得还可以更好。

回去看了看 [Transactions] 表的索引情况，发现有一个问题：虽然 TransactionDate 字段经常被查询，但上面并没有合适的索引。现有的索引主要是围绕 CustomerID 和 CategoryID 建立的，这些在这个查询里是用在join条件上的，对WHERE条件的过滤帮助不大。

于是加了一个覆盖索引：

CREATE NONCLUSTERED INDEX IX_Transactions_Date_Customer_Category
ON [dbo].[Transactions] (TransactionDate, CustomerID, CategoryID)
INCLUDE (Amount);

这个索引的设计思路是：TransactionDate 在最前面，因为WHERE条件里对它做了范围查询；CustomerID 和 CategoryID 紧跟其后，方便后续的join操作；Amount 放在INCLUDE里，这样查询可以直接从索引中获取数据，不需要回表。

加上这个索引之后，查询响应时间进一步降到了150毫秒左右。

如果必须用临时表，该怎么办？

当然，不是所有场景都适合用CTE。有些情况下，临时表是更好的选择，比如数据需要分多次处理、中间结果需要被多个查询复用、或者数据量特别大需要分批处理的时候。

如果真的必须用临时表，这里有几个优化技巧：

第一，给临时表创建合适的索引。 不要依赖SQL Server自动生成的聚集索引，根据实际查询需要手动创建。

CREATE TABLE #TempResult (
    TransactionID INT NOT NULL,
    CustomerID INT NOT NULL,
    Amount DECIMAL(18, 2),
    TransactionDate DATETIME,
    CustomerName NVARCHAR(100),
    CategoryName NVARCHAR(100),
    RegionName NVARCHAR(100)
);

CREATE NONCLUSTERED INDEX IX_Temp_TransactionDate
ON #TempResult (TransactionDate DESC);

第二，考虑使用表变量。 表变量在内存中操作，开销比临时表小，适合数据量不大的场景。

第三，分批处理。 如果数据量确实很大，可以分成多个批次插入和处理，避免一次性操作占用太多资源。

一个经常被忽视的细节

最后分享一个容易被忽视的点。在优化过程中，我发现一个问题：优化后的查询在某些参数组合下，仍然会出现性能波动。

经过仔细分析，发现是参数嗅探（Parameter Sniffing）导致的。SQL Server会缓存执行计划，而第一次执行时使用的参数会影响缓存的计划。如果第一次执行的参数恰好筛选出大量数据，缓存的计划可能不适合后续筛选少量数据的查询。

解决这个问题的方法是使用 OPTIMIZE FOR 提示：

WITH CTE_Transactions AS
(
    SELECT 
        a.TransactionID,
        a.CustomerID,
        a.Amount,
        a.TransactionDate,
        b.CustomerName,
        c.CategoryName,
        d.RegionName,
        ROW_NUMBER() OVER (ORDER BY a.TransactionDate DESC) AS RowNum
    FROM [dbo].[Transactions] a
    INNER JOIN [dbo].[Customers] b ON a.CustomerID = b.CustomerID
    INNER JOIN [dbo].[Categories] c ON a.CategoryID = c.CategoryID
    INNER JOIN [dbo].[Regions] d ON b.RegionID = d.RegionID
    WHERE a.TransactionDate BETWEEN @StartDate AND @EndDate
)
SELECT 
    TransactionID,
    CustomerID,
    Amount,
    TransactionDate,
    CustomerName,
    CategoryName,
    RegionName
FROM CTE_Transactions
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY RowNum
OPTION (OPTIMIZE FOR (@StartDate = '2024-01-01', @EndDate = '2024-01-31'));

加上这个提示后，执行计划的稳定性明显提升，查询时间再也没有出现过大幅波动。

总结一下

这次优化经历让我深刻认识到几个要点：

不要盲目信任传统的写法。 临时表虽然常用，但不是最优选择。CTE在很多场景下能提供更好的性能，尤其是配合窗口函数做分页的时候。

执行计划是最好的老师。 遇到性能问题，不要靠猜，直接看执行计划，它会告诉你哪里花的时间最多，为什么。

索引设计要有的放矢。 好的索引可以改变查询的性能量级，但前提是你得知道查询会怎么跑。覆盖索引、包含列这些概念，真的值得深入理解。

参数嗅探是个隐形杀手。 很多查询平时跑得好好的，突然变慢，往往就是它搞的鬼。了解它、应对它，是SQL Server开发的必修课。

从8秒到300毫秒，看起来只是一个数字的变化，但背后是对查询机制、执行计划、索引设计的全面理解。这个过程虽然煎熬，但收获确实很大。希望我的经验能对你们有所帮助，如果你们也遇到过类似的问题，欢迎聊聊，也许我们能互相启发。

-- 展开阅读全文 --