优化表匹配性能：百万级订单快速匹配Campaign Code方案求助

核心问题梳理 #

• 订单表：2700万唯一Service_Order_ID，1.72亿行明细
• 活动表：1万+Campaign Code，30万行明细
• 匹配规则：折扣码匹配计10分，服务码匹配计1分，取总分最高的活动码
• 当前痛点：现有窗口函数/Cross Apply方法处理100条需1分钟，全量处理无法达到秒级要求

一、数据预处理：缩小关联基数 #

直接基于1.72亿行明细做关联是性能瓶颈的核心，先对两张表做聚合预处理，把明细数据压缩到维度级：

1. 订单侧聚合（生成订单码特征表） #

将每个订单的服务码、折扣码做去重聚合，把1.72亿行压缩到2700万行：

SELECT 
    SERVICE_ORDER_ID,
    STRING_AGG(DISTINCT SERVICE_CODE, '|') AS service_code_set,
    STRING_AGG(DISTINCT DISCOUNT_CODE, '|') AS discount_code_set,
    COUNT(DISTINCT SERVICE_CODE) AS service_code_cnt,
    COUNT(DISTINCT DISCOUNT_CODE) AS discount_code_cnt
INTO dbo.order_code_summary
FROM dbo.CODES_ADDED_UNPACKED
GROUP BY SERVICE_ORDER_ID

-- 建主键与覆盖索引
ALTER TABLE dbo.order_code_summary ADD PRIMARY KEY CLUSTERED (SERVICE_ORDER_ID);
CREATE NONCLUSTERED INDEX IX_order_code_sets ON dbo.order_code_summary 
    (service_code_cnt, discount_code_cnt) INCLUDE (service_code_set, discount_code_set);

2. 活动侧聚合（生成活动码特征表） #

将每个活动的服务码、折扣码做去重聚合，把30万行压缩到1万+行：

SELECT 
    Campaign_Code,
    STRING_AGG(DISTINCT SERVICE_CODE, '|') AS camp_service_set,
    STRING_AGG(DISTINCT DISCOUNT_CODE, '|') AS camp_discount_set,
    COUNT(DISTINCT SERVICE_CODE) AS camp_service_cnt,
    COUNT(DISTINCT DISCOUNT_CODE) AS camp_discount_cnt
INTO dbo.campaign_code_summary
FROM dbo.all_campaigns_t_unpacked
GROUP BY Campaign_Code

-- 建覆盖索引
CREATE NONCLUSTERED INDEX IX_campaign_code_sets ON dbo.campaign_code_summary 
    (camp_service_cnt, camp_discount_cnt) INCLUDE (camp_service_set, camp_discount_set);

二、索引优化：消除回表与低效扫描 #

1. 原表索引调整 #

• 订单表：将现有IX_SERVICE_ORDER_ID_CAMPAIGN改为覆盖索引，确保聚合时无需回表：

  CREATE NONCLUSTERED INDEX IX_SERVICE_ORDER_ID_CODES ON dbo.CODES_ADDED_UNPACKED 
      (SERVICE_ORDER_ID) INCLUDE (SERVICE_CODE, DISCOUNT_CODE);
  

• 活动表：将现有IX_SERVICE_CODE_DISCOUNT_CODE调整为按活动聚合的覆盖索引：

  CREATE NONCLUSTERED INDEX IX_CAMPAIGN_CODE_CODES ON dbo.all_campaigns_t_unpacked 
      (Campaign_Code) INCLUDE (SERVICE_CODE, DISCOUNT_CODE);
  

2. 预处理表进阶优化 #

如果使用SQL Server，可将order_code_summary和campaign_code_summary改为内存优化表，直接在内存中完成匹配计算，避免磁盘IO：

CREATE TABLE dbo.order_code_summary_memory
(
    SERVICE_ORDER_ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 30000000),
    service_code_set NVARCHAR(MAX) NOT NULL,
    discount_code_set NVARCHAR(MAX) NOT NULL,
    service_code_cnt INT NOT NULL,
    discount_code_cnt INT NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

三、查询逻辑重构：替换逐行处理为批量匹配 #

放弃低效的CROSS APPLY/窗口函数逐行匹配，改用集合运算+哈希连接批量计算得分：

1. 基于预处理表的批量匹配 #

WITH service_match_scores AS (
    -- 计算服务码匹配得分
    SELECT 
        o.SERVICE_ORDER_ID,
        c.Campaign_Code,
        COUNT(DISTINCT s.value) AS service_score
    FROM dbo.order_code_summary o
    CROSS APPLY STRING_SPLIT(o.service_code_set, '|') s
    JOIN dbo.campaign_code_summary c 
        ON EXISTS (SELECT 1 FROM STRING_SPLIT(c.camp_service_set, '|') sc WHERE sc.value = s.value)
    GROUP BY o.SERVICE_ORDER_ID, c.Campaign_Code
),
discount_match_scores AS (
    -- 计算折扣码匹配得分
    SELECT 
        o.SERVICE_ORDER_ID,
        c.Campaign_Code,
        COUNT(DISTINCT d.value) * 10 AS discount_score
    FROM dbo.order_code_summary o
    CROSS APPLY STRING_SPLIT(o.discount_code_set, '|') d
    JOIN dbo.campaign_code_summary c 
        ON EXISTS (SELECT 1 FROM STRING_SPLIT(c.camp_discount_set, '|') dc WHERE dc.value = d.value)
    GROUP BY o.SERVICE_ORDER_ID, c.Campaign_Code
),
total_scores AS (
    -- 合并得分并去重
    SELECT SERVICE_ORDER_ID, Campaign_Code, service_score, ISNULL(discount_score, 0) AS discount_score
    FROM service_match_scores
    FULL OUTER JOIN discount_match_scores 
        ON service_match_scores.SERVICE_ORDER_ID = discount_match_scores.SERVICE_ORDER_ID
        AND service_match_scores.Campaign_Code = discount_match_scores.Campaign_Code
),
ranked_matches AS (
    -- 按订单取最高分活动
    SELECT 
        SERVICE_ORDER_ID,
        Campaign_Code,
        (ISNULL(service_score, 0) + discount_score) AS total_score,
        ROW_NUMBER() OVER (PARTITION BY SERVICE_ORDER_ID ORDER BY (ISNULL(service_score, 0) + discount_score) DESC) AS rn
    FROM total_scores
)
SELECT SERVICE_ORDER_ID, Campaign_Code
FROM ranked_matches
WHERE rn = 1
OPTION (HASH JOIN, MAXDOP 8); -- 强制哈希连接，启用并行查询

2. 倒排索引优化（进一步缩小候选范围） #

如果活动数量较多，可建立码-活动倒排表，快速定位每个订单对应的候选活动，避免全量关联：

-- 服务码-活动倒排表
SELECT SERVICE_CODE, STRING_AGG(DISTINCT Campaign_Code, '|') AS campaign_list
INTO dbo.service_code_campaign_map
FROM dbo.all_campaigns_t_unpacked
GROUP BY SERVICE_CODE

-- 折扣码-活动倒排表
SELECT DISCOUNT_CODE, STRING_AGG(DISTINCT Campaign_Code, '|') AS campaign_list
INTO dbo.discount_code_campaign_map
FROM dbo.all_campaigns_t_unpacked
GROUP BY DISCOUNT_CODE

查询时先通过倒排表获取订单的候选活动集合，再在小范围内计算得分，可大幅减少关联次数。

四、执行计划与统计信息调优 #

1. 强制哈希连接：大表关联时，哈希连接比嵌套循环效率高，通过OPTION (HASH JOIN)强制使用。
2. 更新统计信息：大表统计信息易过时，手动更新确保优化器生成最优计划：

   UPDATE STATISTICS dbo.CODES_ADDED_UNPACKED CODES_ADDED_STATS WITH FULLSCAN;
   UPDATE STATISTICS dbo.all_campaigns_t_unpacked all_campaigns_t_unpacked_STATS WITH FULLSCAN;
   

3. 启用并行查询：根据服务器CPU核心数设置MAXDOP（如OPTION (MAXDOP 8)），利用多核加速处理。
4. 分区表优化：若订单表按时间分区，可通过分区消除减少扫描范围；全量场景下分区可提升并行度。

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