含字符串比较的Outer Apply查询性能优化求助

首先，咱们得先搞清楚为什么加了Keyword比较后速度暴跌：Keyword是nvarchar(2000)的长字符串，直接做相等比较开销极大，而且如果没有合适的索引，数据库得做全表扫描/逐行匹配，再加上OUTER APPLY是逐行处理的逻辑，300万行的情况下性能自然雪崩。下面给你几个针对性的优化方案，都能保留业务逻辑：

1. 修正排序逻辑（最容易忽略的点） #

先看你原查询里的order by a.Date_ID desc——这完全是多余的！对每一行a来说，a.Date_ID是固定值，排序根本起不到筛选top 1的作用，数据库可能因此做了无意义的排序操作。必须改成按b.Date_ID desc排序，这样才能拿到b中符合条件的最新行：

order by b.Date_ID desc -- 替换原有的order by a.Date_ID desc

这个小改动说不定能先提升一部分性能。

2. 创建覆盖索引（核心优化） #

因为你的CTE本质是基于基础表的查询，所以要给基础表（假设叫YourTable）创建合适的覆盖索引，把过滤、排序和需要返回的列都包含进去。注意：nvarchar(2000)的长度超过了非聚集索引键的900字节限制，所以不能把Keyword直接放到索引键里，得用哈希计算列绕开这个限制：

步骤1：添加持久化哈希计算列 #

给基础表添加一个基于Keyword的哈希值列，哈希值是固定长度的（SHA2_256是32字节），适合做索引键：

ALTER TABLE dbo.YourTable
ADD KeywordHash AS HASHBYTES('SHA2_256', Keyword) PERSISTED;

步骤2：创建覆盖索引 #

索引键包含用于过滤和排序的列，包含列放需要查询返回的字段，避免后续的键查找：

CREATE NONCLUSTERED INDEX IX_YourTable_ProjectSEKeywordHashDate
ON dbo.YourTable (Project_Id, SE_Id, KeywordHash, Date_ID DESC)
INCLUDE (Keyword, Load_Date, position, traffic, tags, url, Domain, trend_date);

步骤3：修改查询条件 #

先通过哈希值快速过滤，再比较原Keyword避免哈希冲突（概率极低，但业务严谨性需要）：

with cte as (
    -- 原CTE查询，记得把KeywordHash也包含进来
    select ..., KeywordHash from YourTable ...
)
select -- 你的查询列
from cte a
outer apply (
    select top 1 position,traffic,tags,url,Domain,Load_Date,trend_date
    from cte b
    where b.KeywordHash = a.KeywordHash -- 先比哈希，快速缩小范围
      and b.Keyword = a.Keyword -- 再比原字符串，确保完全匹配
      and b.Date_ID<=a.Date_ID 
      and b.Load_Date is not null 
      and a.Domain is null 
      and a.Project_Id=b.Project_Id 
      and a.SE_Id=b.SE_Id 
    order by b.Date_ID desc 
)x

3. 用窗口函数重写查询（替代Outer Apply） #

OUTER APPLY是逐行关联，换成LEFT JOIN+ROW_NUMBER()的窗口函数写法，数据库可以用更高效的批量连接算法，性能往往更好：

WITH cte AS (
    -- 原CTE查询
    select ... from YourTable ...
),
ranked_data AS (
    SELECT 
        a.*, -- 你需要的a表列
        b.position, b.traffic, b.tags, b.url, b.Domain, b.Load_Date, b.trend_date,
        -- 按a的分组字段给b行排名，取最新的一行
        ROW_NUMBER() OVER (
            PARTITION BY a.Project_Id, a.SE_Id, a.Keyword, a.Date_ID 
            ORDER BY b.Date_ID DESC
        ) AS rn
    FROM cte a
    LEFT JOIN cte b 
        ON b.Project_Id = a.Project_Id 
        AND b.SE_Id = a.SE_Id 
        AND b.Keyword = a.Keyword
        AND b.Date_ID <= a.Date_ID 
        AND b.Load_Date IS NOT NULL 
        AND a.Domain IS NULL
)
SELECT 
    -- 这里选你需要的列，注意排除rn
    position, traffic, tags, url, Domain, Load_Date, trend_date,
    -- 其他a表的列
FROM ranked_data
WHERE rn = 1; -- 只取每个分组的第一行（最新的b记录）

4. 用临时表缓存CTE结果 #

如果你的CTE本身比较复杂，数据库可能会重复扫描CTE两次（一次作为a，一次作为b），把CTE结果插入临时表并建索引，能避免重复计算：

-- 创建临时表，包含CTE所有需要的列
CREATE TABLE #TempCTE (
    Date_ID INT, -- 替换成你的实际类型
    Load_Date DATETIME,
    Domain NVARCHAR(255),
    Project_Id INT,
    SE_Id INT,
    Keyword NVARCHAR(2000),
    position INT,
    traffic INT,
    tags NVARCHAR(MAX),
    url NVARCHAR(MAX),
    trend_date DATETIME
);

-- 插入CTE数据
INSERT INTO #TempCTE
SELECT -- 原CTE的查询列
FROM YourTable ...;

-- 给临时表建索引（用之前的哈希列方案或者如果允许的话直接包含Keyword）
CREATE NONCLUSTERED INDEX IX_TempCTE_ProjectSEKeywordDate
ON #TempCTE (Project_Id, SE_Id, Keyword, Date_ID DESC)
INCLUDE (Load_Date, position, traffic, tags, url, Domain, trend_date);

-- 用临时表执行查询
SELECT -- 你的查询列
FROM #TempCTE a
OUTER APPLY (
    SELECT TOP 1 position,traffic,tags,url,Domain,Load_Date,trend_date
    FROM #TempCTE b
    WHERE b.Date_ID<=a.Date_ID 
      AND b.Load_Date IS NOT NULL 
      AND a.Domain IS NULL 
      AND b.Project_Id=a.Project_Id 
      AND b.SE_Id=a.SE_Id 
      AND b.Keyword=a.Keyword
    ORDER BY b.Date_ID DESC
)x;

-- 清理临时表
DROP TABLE #TempCTE;

建议你先从修正排序逻辑和创建覆盖索引开始，这两个是见效最快的。如果还是不够，再尝试窗口函数或者临时表的方案。

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