SQL优化：关联表时仅显示消息对应最新成就的高效查询方案

问题背景 #

现有两张数据表：

• Messages表：包含P_ID（用户ID）、Message_ID（消息ID）、Message_sent_date（消息发送日期）字段
• People表：包含P_ID（用户ID）、P_Achievement（用户成就）、Achievement_date（成就获取日期）字段

需求是按P_ID关联两张表，每条消息仅展示该消息发送日期之前对应用户的最新成就。原查询使用嵌套子查询在小数据量下可行，但大数据量下因重复计算导致性能极差，甚至无法完成查询。

优化方案 #

方案1：使用窗口函数标记最新成就 #

利用ROW_NUMBER()窗口函数预先为每个用户的成就按日期倒序排序，再与消息表关联筛选符合条件的最新成就，避免子查询的重复计算：

WITH ranked_achievements AS (
    SELECT 
        P_ID,
        P_Achievement,
        Achievement_date,
        -- 按用户分组，成就日期倒序排名，最新成就排第1
        ROW_NUMBER() OVER (PARTITION BY P_ID ORDER BY Achievement_date DESC) AS rn
    FROM People
)
SELECT 
    m.Message_ID,
    m.P_ID,
    m.Message_sent_date,
    ra.P_Achievement,
    ra.Achievement_date AS latest_achievement_date
FROM Messages m
LEFT JOIN ranked_achievements ra
    ON m.P_ID = ra.P_ID
    AND ra.Achievement_date <= m.Message_sent_date
    AND ra.rn = 1;

方案2：使用LATERAL JOIN（PostgreSQL）或CROSS APPLY（SQL Server） #

这种方式针对每条消息直接查询对应用户的最新前置成就，执行计划更高效，适配单条消息匹配单条成就的场景：

PostgreSQL版本

SELECT 
    m.Message_ID,
    m.P_ID,
    m.Message_sent_date,
    p.P_Achievement,
    p.Achievement_date AS latest_achievement_date
FROM Messages m
LEFT JOIN LATERAL (
    SELECT P_Achievement, Achievement_date
    FROM People p
    WHERE p.P_ID = m.P_ID
      AND p.Achievement_date <= m.Message_sent_date
    ORDER BY Achievement_date DESC
    LIMIT 1
) p ON true;

SQL Server版本

SELECT 
    m.Message_ID,
    m.P_ID,
    m.Message_sent_date,
    p.P_Achievement,
    p.Achievement_date AS latest_achievement_date
FROM Messages m
OUTER APPLY (
    SELECT TOP 1 P_Achievement, Achievement_date
    FROM People p
    WHERE p.P_ID = m.P_ID
      AND p.Achievement_date <= m.Message_sent_date
    ORDER BY Achievement_date DESC
) p;

方案3：预计算成就快照（适用于频繁查询场景） #

如果这类查询业务频次高，可预先创建中间表存储用户最新成就快照，定期刷新后直接关联查询：

-- 创建中间表
CREATE TABLE user_latest_achievement_snapshot (
    P_ID INT,
    latest_achievement_date DATE,
    latest_achievement VARCHAR(255),
    PRIMARY KEY (P_ID)
);

-- 定期刷新数据（例如每日凌晨执行）
TRUNCATE TABLE user_latest_achievement_snapshot;
INSERT INTO user_latest_achievement_snapshot
SELECT 
    P_ID,
    MAX(Achievement_date) AS latest_achievement_date,
    FIRST_VALUE(P_Achievement) OVER (PARTITION BY P_ID ORDER BY Achievement_date DESC) AS latest_achievement
FROM People
GROUP BY P_ID;

-- 查询时直接关联
SELECT 
    m.Message_ID,
    m.P_ID,
    m.Message_sent_date,
    ulas.latest_achievement,
    ulas.latest_achievement_date
FROM Messages m
LEFT JOIN user_latest_achievement_snapshot ulas
    ON m.P_ID = ulas.P_ID
    AND ulas.latest_achievement_date <= m.Message_sent_date;

关键索引优化 #

无论采用哪种方案，添加以下复合索引可大幅提升性能：

• People表索引：CREATE INDEX idx_people_pid_date ON People(P_ID, Achievement_date DESC);
• Messages表索引：CREATE INDEX idx_messages_pid_date ON Messages(P_ID, Message_sent_date);

这些索引能让数据库快速定位目标记录，避免全表扫描。

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