基于日期排序的Union CTE查询性能优化求助（130万行表）

现有一张体育比赛数据表match_table，结构及数据如下：

需求说明 #

针对指定的比赛id_，返回该场比赛两名选手各自上一场比赛的统计数据，无论该选手在上一场比赛中担任p1还是p2角色。例如当id_ = 11456时，预期输出如下：

当前实现与性能问题 #

当前使用的SQL在小数据量表上运行正常，但生产表有130万行数据，该查询耗时约12秒：

WITH cte_1 AS (
  (
    SELECT 
      id_, 
      match_date, 
      p1_id AS player_id, 
      p1_stat AS stat 
    FROM 
      test.match_table 
    UNION ALL 
    SELECT 
      id_, 
      match_date, 
      p2_id AS player_id, 
      p2_stat AS stat 
    FROM 
      test.match_table
  )
), 
cte_2 AS (
  SELECT 
    id_, 
    player_id, 
    LAG(stat) OVER (
      PARTITION BY player_id 
      ORDER BY 
        match_date, 
        id_
    ) AS prev_stat 
  FROM 
    cte_1
) 
SELECT 
  m.*, 
  cte_p1.prev_stat AS p1_prev_stat, 
  cte_p2.prev_stat AS p2_prev_stat 
FROM 
  test.match_table AS m 
  JOIN cte_2 AS cte_p1 ON cte_p1.id_ = m.id_ 
  AND cte_p1.player_id = m.p1_id 
  JOIN cte_2 AS cte_p2 ON cte_p2.id_ = m.id_ 
  AND cte_p2.player_id = m.p2_id
WHERE m.id_ = 11456
ORDER BY m.match_date

问题根源在于CTE加载了全表数据，而非仅查询所需的相关数据，需要优化性能。

测试表创建SQL：

CREATE TABLE `match_table` (
  `id_` int NOT NULL AUTO_INCREMENT, 
  `p1_id` int NOT NULL, 
  `p2_id` int NOT NULL, 
  `match_date` date NOT NULL, 
  `p1_stat` int DEFAULT NULL, 
  `p2_stat` int DEFAULT NULL, 
  PRIMARY KEY (`id_`), 
  KEY `ix__p1_id` (`p1_id`), 
  KEY `ix__p2_id` (`p2_id`), 
  KEY `ix__match_date` (`match_date`), 
  KEY `ix__comp` (`p1_id`, `p2_id`, `match_date`)
);
INSERT INTO `match_table` 
VALUES 
  (4760, 2, 1, '1998-05-25', 1190, 1486), 
  (6713, 2, 1, '1999-01-18', 2084, 885), 
  (9365, 2, 1, '1999-11-01', 2894, 2040), 
  (11456, 1, 2, '2000-05-15', 2358, 1491), 
  (13022, 1, 2, '2000-08-14', 2722, 2401), 
  (29159, 1, 2, '2002-08-26', 431, 2769), 
  (44915, 1, 2, '2002-10-07', 1904, 482), 
  (852666, 1, 2, '1997-01-01', 1301, 249), 
  (852842, 1, 2, '1997-01-13', 2837, 2441), 
  (853471, 2, 1, '1997-05-05', 1474, 952);

性能优化建议 #

1. 先定位目标比赛，再针对性查询历史数据 #

避免全表扫描，先获取目标比赛的选手ID和比赛日期，再分别查询两名选手在该日期之前的最后一场比赛统计：

-- 先获取目标比赛的核心信息
WITH target_match AS (
    SELECT id_, p1_id, p2_id, match_date, p1_stat, p2_stat
    FROM test.match_table
    WHERE id_ = 11456
)
SELECT 
    tm.*,
    -- 查询p1的上一场统计
    (SELECT 
        CASE WHEN p1_id = tm.p1_id THEN p1_stat ELSE p2_stat END
     FROM test.match_table
     WHERE (p1_id = tm.p1_id OR p2_id = tm.p1_id)
       AND match_date < tm.match_date
     ORDER BY match_date DESC, id_ DESC
     LIMIT 1) AS p1_prev_stat,
    -- 查询p2的上一场统计
    (SELECT 
        CASE WHEN p1_id = tm.p2_id THEN p1_stat ELSE p2_stat END
     FROM test.match_table
     WHERE (p1_id = tm.p2_id OR p2_id = tm.p2_id)
       AND match_date < tm.match_date
     ORDER BY match_date DESC, id_ DESC
     LIMIT 1) AS p2_prev_stat
FROM target_match tm;

2. 创建覆盖索引加速查询 #

现有索引仅包含单一字段，建议创建覆盖索引，让子查询直接从索引中获取数据，无需回表：

-- 为p1方向创建覆盖索引
CREATE INDEX ix__p1_date_stat ON match_table(p1_id, match_date DESC, id_ DESC, p1_stat, p2_stat);
-- 为p2方向创建覆盖索引
CREATE INDEX ix__p2_date_stat ON match_table(p2_id, match_date DESC, id_ DESC, p1_stat, p2_stat);

3. 缩小窗口函数的处理范围 #

如果坚持使用窗口函数，先筛选出目标两名选手的所有比赛数据，再应用LAG函数，避免全表处理：

WITH target_players AS (
    SELECT p1_id AS player_id FROM test.match_table WHERE id_ = 11456
    UNION
    SELECT p2_id AS player_id FROM test.match_table WHERE id_ = 11456
),
player_matches AS (
    SELECT 
        id_,
        match_date,
        CASE WHEN p1_id = tp.player_id THEN p1_id ELSE p2_id END AS player_id,
        CASE WHEN p1_id = tp.player_id THEN p1_stat ELSE p2_stat END AS stat
    FROM test.match_table mt
    JOIN target_players tp ON mt.p1_id = tp.player_id OR mt.p2_id = tp.player_id
),
player_prev_stats AS (
    SELECT 
        id_,
        player_id,
        LAG(stat) OVER (PARTITION BY player_id ORDER BY match_date, id_) AS prev_stat
    FROM player_matches
)
SELECT 
    mt.*,
    pps1.prev_stat AS p1_prev_stat,
    pps2.prev_stat AS p2_prev_stat
FROM test.match_table mt
JOIN player_prev_stats pps1 ON mt.id_ = pps1.id_ AND mt.p1_id = pps1.player_id
JOIN player_prev_stats pps2 ON mt.id_ = pps2.id_ AND mt.p2_id = pps2.player_id
WHERE mt.id_ = 11456;

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