含WHERE条件的玩家历史比赛统计SQL性能优化请求

问题背景 #

需从match_table中返回每场比赛双方玩家的上一场比赛统计数据（无论玩家在上一场中是p1还是p2）。当前基于CTE+LAG窗口函数的查询在130万行全表上耗时35秒，即使添加WHERE m.p1_id=1 OR m.p2_id=1过滤至约800行，仍耗时约10秒，需优化查询性能。

1. 创建针对性复合索引 #

窗口函数LAG依赖玩家维度的比赛时间排序，必须创建覆盖玩家ID、排序字段及查询所需统计字段的复合索引，避免回表IO：

-- 覆盖玩家作为p1的场景，包含排序与统计字段
CREATE INDEX idx_match_p1_time ON match_table (p1_id, match_time DESC) INCLUDE (p1_score, p2_score, match_id);
-- 覆盖玩家作为p2的场景
CREATE INDEX idx_match_p2_time ON match_table (p2_id, match_time DESC) INCLUDE (p1_score, p2_score, match_id);

若查询需额外字段，将其加入INCLUDE列表；部分数据库（如MySQL）不支持INCLUDE，可将字段直接加入索引列（注意索引长度限制）。

2. 提前过滤数据，避免CTE全表扫描 #

多数数据库中CTE为惰性执行，即使外层加过滤条件，CTE仍会扫描全表。可先过滤目标玩家的比赛数据，再进行窗口函数计算：

-- 先过滤目标玩家的所有比赛，再处理玩家维度的统计
WITH target_matches AS (
    SELECT * FROM match_table WHERE p1_id = 1 OR p2_id = 1
),
player_stats AS (
    SELECT 
        match_id,
        p1_id AS player_id,
        match_time,
        p1_score AS player_score,
        p2_score AS opponent_score,
        LAG(match_time) OVER (PARTITION BY p1_id ORDER BY match_time) AS last_match_time,
        LAG(p1_score) OVER (PARTITION BY p1_id ORDER BY match_time) AS last_player_score
    FROM target_matches
    UNION ALL
    SELECT 
        match_id,
        p2_id AS player_id,
        match_time,
        p2_score AS player_score,
        p1_score AS opponent_score,
        LAG(match_time) OVER (PARTITION BY p2_id ORDER BY match_time) AS last_match_time,
        LAG(p2_score) OVER (PARTITION BY p2_id ORDER BY match_time) AS last_player_score
    FROM target_matches
)
SELECT * FROM player_stats WHERE player_id = 1;

此方式将处理数据集从130万行压缩至800行，大幅降低计算量。

3. 用横向连接替代UNION ALL，减少扫描次数 #

通过LATERAL JOIN（PostgreSQL）或CROSS APPLY（SQL Server）将单条比赛记录拆分为两个玩家维度的数据，替代两次UNION扫描，提升效率：

SELECT 
    m.match_id,
    p.player_id,
    m.match_time,
    p.player_score,
    p.opponent_score,
    LAG(m.match_time) OVER (PARTITION BY p.player_id ORDER BY m.match_time) AS last_match_time,
    LAG(p.player_score) OVER (PARTITION BY p.player_id ORDER BY m.match_time) AS last_player_score
FROM match_table m
CROSS JOIN LATERAL (
    VALUES 
        (m.p1_id, m.p1_score, m.p2_score),
        (m.p2_id, m.p2_score, m.p1_score)
) AS p(player_id, player_score, opponent_score)
WHERE p.player_id = 1
ORDER BY m.match_time;

该逻辑仅扫描一次过滤后的数据集，结合索引可快速定位目标玩家的所有比赛。

4. 验证执行计划，确保索引生效 #

用数据库自带的执行计划工具确认索引是否被使用：

• PostgreSQL：执行EXPLAIN ANALYZE <你的查询>
• MySQL：执行EXPLAIN <你的查询>
  若出现全表扫描（Seq Scan/ALL），需更新表统计信息：

-- PostgreSQL
ANALYZE match_table;
-- MySQL
ANALYZE TABLE match_table;

5. 大表场景：考虑按时间分区 #

若表数据持续增长，可按match_time（如月/季度）创建分区表，查询时仅扫描包含目标玩家比赛的分区，进一步缩小扫描范围。

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