优化高耗时OUTER APPLY：转换为LEFT JOIN的正确方法

一、修正ROW_NUMBER的分区逻辑 #

你当前的ROW_NUMBER分区字段完全错误，导致分组逻辑和原OUTER APPLY的语义不匹配，这是结果不正确的核心原因。

原OUTER APPLY的逻辑是：针对main_table的每一行（对应mt.one_id、mt.another_date、mt.one_date的组合），在sub_table中筛选符合关联条件的记录，然后取TOP1。而你用PARTITION BY st.another_id, st.one_date, st.another_date是按sub_table的字段分组，完全偏离了原逻辑。

正确的实现方式 #

1. 先将两张表按原条件关联，保留main_table的关联键作为分区依据
2. ROW_NUMBER的分区字段要对应main_table的行标识（如果main_table有唯一主键，比如mt.id，用主键分区更高效准确；没有的话用mt.one_id, mt.another_date, mt.one_date的组合）
3. 筛选序号=1的记录，得到每个main_table行对应的唯一sub_table记录

示例代码：

-- 创建临时表，保留main_table的关联字段用于后续关联
SELECT 
    mt.one_id,
    mt.another_date,
    mt.one_date,
    st.*,
    -- 按main_table的行标识分区，保证每个mt行对应一组st记录
    ROW_NUMBER() OVER (
        PARTITION BY mt.one_id, mt.another_date, mt.one_date 
        ORDER BY st.yet_another_date DESC -- 保持和原逻辑一致，虽排序无实际意义
    ) AS rn
INTO #temp_sub_top1
FROM main_table mt
JOIN sub_table st 
    ON mt.one_id = st.another_id
    AND st.one_date > mt.another_date
    AND st.another_date < mt.one_date;

-- 主查询LEFT JOIN临时表，仅取每组的TOP1
SELECT mt.*, ts.*
FROM main_table mt
LEFT JOIN #temp_sub_top1 ts
    ON mt.one_id = ts.one_id
    AND mt.another_date = ts.another_date
    AND mt.one_date = ts.one_date
    AND ts.rn = 1;

如果main_table有唯一主键（比如mt.main_id），可以直接用主键分区，避免多字段的冗余：

ROW_NUMBER() OVER (PARTITION BY mt.main_id ORDER BY st.yet_another_date DESC) AS rn

二、其他优化OUTER APPLY的方案 #

1. 最大化利用现有聚集索引 #

你的sub_table已经有包含another_id, one_date, another_date的聚集索引，可以尝试优化原OUTER APPLY的查询写法，确保SQL Server能走索引seek：

SELECT * 
FROM main_table mt
OUTER APPLY (
    SELECT TOP 1 st.* 
    FROM sub_table st 
    WHERE st.another_id = mt.one_id
        AND st.one_date > mt.another_date
        AND st.another_date < mt.one_date
    ORDER BY st.yet_another_date DESC
) st

如果执行计划没走聚集索引，可以尝试强制指定索引：

SELECT * 
FROM main_table mt
OUTER APPLY (
    SELECT TOP 1 st.* 
    FROM sub_table st WITH(INDEX(你的聚集索引名称))
    WHERE st.another_id = mt.one_id
        AND st.one_date > mt.another_date
        AND st.another_date < mt.one_date
    ORDER BY st.yet_another_date DESC
) st

2. 创建覆盖索引减少IO #

如果聚集索引没有包含查询所需的所有字段，会触发书签查找，拖慢性能。可以创建覆盖索引，包含所有需要返回的字段：

CREATE NONCLUSTERED INDEX IX_sub_table_cover 
ON sub_table(another_id, one_date, another_date)
INCLUDE (yet_another_date, 字段1, 字段2); -- 替换为你实际需要的sub_table字段

3. 分批处理降低单次查询压力 #

由于两张表各有1亿条数据，全量查询的资源消耗极大，可以按main_table的主键或日期分段，分批执行后合并结果：

DECLARE @start_id BIGINT = 1;
DECLARE @batch_size BIGINT = 1000000;
DECLARE @max_id BIGINT = (SELECT MAX(one_id) FROM main_table);

WHILE @start_id <= @max_id
BEGIN
    SELECT * 
    FROM main_table mt
    OUTER APPLY (
        SELECT TOP 1 st.* 
        FROM sub_table st 
        WHERE st.another_id = mt.one_id
            AND st.one_date > mt.another_date
            AND st.another_date < mt.one_date
        ORDER BY st.yet_another_date DESC
    ) st
    WHERE mt.one_id BETWEEN @start_id AND @start_id + @batch_size - 1;

    SET @start_id = @start_id + @batch_size;
END

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