优化查询执行：处理重复Customer ID的交易匹配方案

问题背景 #

需要匹配CUSTOMER与TRANSACTION表的交易记录，原仅通过CUSTOMER_ID匹配，但TRANSACTION表存在大量同CUSTOMER_ID的重复记录，需按以下规则完成精准匹配：

• TRANSACTION.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
• SUBSTR(TRANSACTION.ACCOUNT_NUMBER, INSTR(TRANSACTION.ACCOUNT_NUMBER, '.') + 1) = CUSTOMER.ACCOUNT_NUMBER
• 若ADDRESS字段存在，TRANSACTION.ADDRESS = CUSTOMER.ADDRESS（可选匹配条件）
• 多记录匹配时，选择满足CUSTOMER.CREATED_AT <= TRANSACTION.ISSUE_DATE的最早ISSUE_DATE交易记录

现有方案改为按单个客户全字段查询，抵消了批量查询的性能优势，需更优处理方式。

最优方案一：SQL层面直接完成匹配与筛选 #

利用数据库窗口函数一次性完成所有规则的匹配和最优记录筛选，避免循环单查的性能损耗。以下是示例SQL（以MySQL为例）：

WITH ranked_transactions AS (
    SELECT 
        t.*,
        -- 按客户唯一标识分组，按规则排序后取第一条
        ROW_NUMBER() OVER (
            PARTITION BY t.CUSTOMER_ID, SUBSTR(t.ACCOUNT_NUMBER, INSTR(t.ACCOUNT_NUMBER, '.') + 1)
            ORDER BY 
                -- 优先匹配地址（如果客户地址存在）
                CASE WHEN c.ADDRESS IS NOT NULL AND t.ADDRESS = c.ADDRESS THEN 0 ELSE 1 END,
                -- 再按交易日期升序，取最早符合条件的记录
                t.ISSUE_DATE ASC
        ) AS rn
    FROM TRANSACTION t
    JOIN CUSTOMER c 
        ON t.CUSTOMER_ID = c.CUSTOMER_ID
        AND SUBSTR(t.ACCOUNT_NUMBER, INSTR(t.ACCOUNT_NUMBER, '.') + 1) = c.ACCOUNT_NUMBER
        -- 处理地址可选条件：客户地址为空则跳过匹配，否则必须相等
        AND (c.ADDRESS IS NULL OR t.ADDRESS = c.ADDRESS)
    WHERE c.CREATED_AT <= t.ISSUE_DATE
)
-- 取每组排序后的第一条，即最优匹配记录
SELECT * FROM ranked_transactions WHERE rn = 1;

关键优化点： #

• 给TRANSACTION表建立联合索引：(CUSTOMER_ID, ACCOUNT_NUMBER, ISSUE_DATE)，加速分组和排序
• 给CUSTOMER表建立联合索引：(CUSTOMER_ID, ACCOUNT_NUMBER, CREATED_AT, ADDRESS)，加速关联查询

备选方案二：批量拉取数据后内存处理 #

若数据库不支持窗口函数或规则需频繁调整，可采用两次批量查询+内存处理的方式：

1. 批量获取目标客户数据：查询所有需要匹配的CUSTOMER记录，提取CUSTOMER_ID、ACCOUNT_NUMBER、ADDRESS、CREATED_AT字段
2. 批量获取候选交易数据：查询TRANSACTION表中CUSTOMER_ID在目标客户列表内，且满足以下条件的所有记录：
   • SUBSTR(ACCOUNT_NUMBER, INSTR(ACCOUNT_NUMBER, '.') + 1)匹配对应客户的ACCOUNT_NUMBER
   • CREATED_AT <= ISSUE_DATE
3. 内存中筛选最优记录：
   • 将交易数据按CUSTOMER_ID + ACCOUNT_NUMBER分组
   • 对每组数据：若客户有ADDRESS，先过滤出地址匹配的记录；再筛选出ISSUE_DATE最早的那条记录

方案对比 #

• 方案一优先：数据库对大数据量的分组、排序优化更成熟，性能更稳定，减少应用层内存消耗
• 方案二灵活：适合规则频繁变动或老版本数据库场景，内存处理逻辑调整更便捷

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