날짜별로 정렬된 통합 CTE를 기반으로 이전 행의 값을 반환하는 속도가 느립니다.

Question

从以下的体育比赛表格中：

id_	p1_id	p2_id	match_date	p1_stat	p2_stat
852666	1	2	01/01/1997	1301	249
852842	1	2	13/01/1997	2837	2441
853471	2	1	05/05/1997	1474	952
4760	2	1	25/05/1998	1190	1486
6713	2	1	18/01/1999	2084	885
9365	2	1	01/11/1999	2894	2040
11456	1	2	15/05/2000	2358	1491
13022	1	2	14/08/2000	2722	2401
29159	1	2	26/08/2002	431	2769
44915	1	2	07/10/2002	1904	482

对于选定的比赛id_，我希望返回两位选手在他们各自上一场比赛中的统计数据，无论该选手在上一场比赛中是p1还是p2。请参考下面的预期输出，其中id_ = 11456：

id_	p1_id	p2_id	match_date	p1_stat	p2_stat	p1_prev_stat	p2_prev_stat
11456	1	2	15/05/2000	2358	1491	2040	2894

在这个大小的表上，以下的SQL语句完全正常运行：

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

然而，实际的表格有130万行，大约需要12秒的时间。根据这个回答，问题似乎是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
  );

Answer 1

먼저 데이터베이스에 올바른 인덱스가 있는지 확인하는 것이 중요합니다. id_, p1_id, p2_id 및 match_date 필드가 쿼리에 사용될 때 색인이 생성되었는지 확인해야 합니다.

둘째, 두 개의 CTE(공통 표현식)를 사용하는 대신 기본 쿼리에서 직접 LAG() 함수를 사용하세요. 이렇게 하면 중복 행이 필요하지 않으므로 쿼리 속도가 빨라집니다. 사용할 수 있는 방법은 다음과 같습니다.

으아아아

이 쿼리는 다음을 수행합니다.

• 행 수가 두 배로 늘어나는 것을 방지하기 위해 결합 연산을 제거했습니다.
• 원래 테이블을 두 번 조인하는 단계를 건너뜁니다.
• 이전 통계를 얻으려면 메인 쿼리에 직접 LAG() 함수를 적용하세요.

그러나 이 접근 방식이 성능 요구 사항을 충족하지 못하는 경우 요약 테이블 생성을 고려해 보세요. 테이블은 각 게임에 대한 각 플레이어의 통계를 추적합니다. 새로운 경기 결과가 추가될 때마다 요약 테이블이 업데이트됩니다. 이는 더 많은 저장 공간을 차지하고 쓰기 성능에 영향을 주지만 읽기 성능은 크게 향상됩니다.