데카르트 제품을 피하기 위해 SQL View 최적화

데카르트 제품을 피하기 위해 SQL View 최적화

나는 주기적으로 실행되고 그 결과를 mariadb 데이터베이스의 테이블(script_history)에 쓰는 일련의 테스트 스크립트(약 4,000개의 고유 스크립트)를 가지고 있습니다.그 테이블에는 현재 60,000개의 행이 있습니다.4,000개의 스크립트 중 가장 최근의 기록을 보기 위해 다음과 같이 기록했습니다.

SELECT
   t1.pk ASK pk,
   t1.script_name AS script_name, 
   t1.test_points_passed AS test_points_passed,
   t1.test_points_failed AS test_points_failed,
   t1.execution_time AS execution_time,
   t1.tester_name AS tester_name,
   t1.execution_date as execution_date,
   t1.test_notes AS test_notes,
   t1.script_in_execution AS script_in_execution,
   t1.hostname AS hostname
FROM
   (script_db.script_history t1 LEFT JOIN script_db.script_history t2 ON
    (t2.script_name = t1.script_name and t2.execution_date > t1.execution_date))
WHERE t2.execution_date IS NULL group by t1.script_name

이것은 각각의 4,000개의 스크립트 중 가장 최근에 실행된 것에 대한 기록을 제공합니다.안타깝게도 뷰를 로드하려고 할 때 성능이 심각하게 저하되는 데카르트 제품을 생산하고 있습니다(로드하는 데 거의 5분 소요됨).

보기를 위해 다음을 시도해 봤습니다.

SELECT
   script_history.*
FROM
   (SELECT
      pk, script_name, test_points_passed, test_points_failed, execution_time, tester_name, MAX(execution_date)
      as execution_date, test_notes, script_in_execution, hostname
   FROM script_history
   GROUP BY script_name) AS A
INNER JOIN
   script_history
   ON
     script_history.script_name = A.script_name AND
     script_history.execution_date = A.execution_date;

이 보기 정의는 매우 빠르게 로드되지만 안타깝게도 원하는 결과를 얻지 못하는 것 같습니다.4000개의 고유 스크립트 각각의 마지막 실행 데이터 대신 동일한 스크립트가 같은 날 실행된 중복(약 400개의 레코드)을 도입하여 이 보기에서 약 4,400개의 레코드를 생성합니다.스크립트 그룹의 마지막 실행 행 데이터로 보기를 얻을 수 있도록 도와주시면 감사하겠습니다.

샘플 데이터: (pk, script_name, 테스트 포인트 통과, 테스트 포인트 실패, 실행 시간, 테스터, 실행 날짜, 테스트 노트, 실행 중 스크립트, 호스트 이름)

1    script1     5    7    10:30   j_doe     2021-05-01    NULL    0    main_server
2    script1     8    4    10:29   j_doe     2021-05-03    NUll    0    backup_server
3    script2    44    0    2:40    j_doe     2021-05-04    NULL    0    backup_server
4    script3     3    2    1:39    j_doe     2021-05-05    NULL    0    main_server
5    script2    43    1    2:40    j_doe     2021-05-05    NULL    0    main_server
6    script3     5    0    1:38    j_doe     2021-06-01    NULL    0    backup_server
7    script4    15    0    0:50    j_doe     2021-07-05    NULL    0    main_server
8    script4    15    0    0:50    j_doe     2021-07-05    NULL    0    main_server

원하는 결과:

2    script1     8    4    10:29   j_doe     2021-05-03    NUll    0    backup_server
5    script2    43    1    2:40    j_doe     2021-05-05    NULL    0    main_server
6    script3     5    0    1:38    j_doe     2021-06-01    NULL    0    backup_server
8    script4    15    0    0:50    j_doe     2021-07-05    NULL    0    main_server

제가 생각하는 코드는 이렇게 보여야 하고, 최대 실행 날짜를 얻고, 중복이 있으면 최대 pk를 받습니다.

SELECT ScriptHistory.*
FROM ScriptHistory

INNER JOIN (
  SELECT ScriptHistory.Script_name, ScriptHistory.execution_date, MAX(pk) AS MaxPK
  FROM ScriptHistory

  INNER JOIN (
    SELECT Script_name, Max(execution_date) AS MaxDate
    FROM ScriptHistory
    GROUP BY Script_name
  ) AS A on A.Script_name = ScriptHistory.Script_name
  AND A.MaxDate = ScriptHistory.execution_date

  GROUP BY ScriptHistory.Script_name, ScriptHistory.execution_date

) AS B on B.Script_name = ScriptHistory.Script_name
AND B.execution_date = ScriptHistory.execution_date
AND B.MaxPK = ScriptHistory.pk

언급URL : https://stackoverflow.com/questions/68287951/optimizing-sql-view-to-avoid-cartesian-product