집약 쿼리의 프로시저 표시 또는 저장

집약 쿼리의 프로시저 표시 또는 저장

• 저는 현재 600,000개의 행이 있는 테이블을 가지고 있습니다.
• Group By 절을 사용하여 데이터에 대해 Daily Average를 실행하여 보고서 응용 프로그램의 행 수를 줄이고 싶습니다.

리포트 애플리케이션의 데이터 서브셋(99% 삭감)이 사용됩니다.

이 도구는 매일 '작성'되기 때문에 저장 프로시저, 보기 또는 기타 도구 중 가장 좋은 도구는 무엇입니까?

[ Summary ]테이블을 빌드 및 유지보수합니다.처음에는 대규모로 운영해야 합니다.GROUP BY모든 오래된 데이터를 수집합니다.그 후 야간 작업이 계산됩니다.COUNT(*),SUM(...)전날의 , 등입니다.

그러면 '보고서'가 이 새로운 테이블에 대해 훨씬 더 빨리 실행될 것입니다.

이 테이블의 키에는 요일(날짜+시간이 아님)과 보고서에 필요한 몇 개의 열이 포함됩니다.

자세한 것은 블로그를 참조해 주세요.

일반적인 속도 향상은 10배이며, 100배(99% 감소)가 될 수 있습니다.

최적의 도구는 cron(또는 MySQL)을 통해 실행하는 스크립트입니다.EVENT)는, 간단하게 다음과 같은 기능을 합니다.

INSERT INTO SummaryTable (dy, ..., ct, tot, ...)
SELECT DATE(datetime), ...,   -- key
       COUNT(*), SUM(..), ...   -- data
   FROM FactTable
   WHERE datetime >= CURDATE() - INTERVAL 1 DAY
     AND datetime  < CURDATE();

SQL 문 하나만 있으면 됩니다.예, 저장 프로시저에 있을 수 있지만 야간 스크립트에 직접 있는 것과 크게 다르지 않습니다.

경우에 따라 사용하는 것이 더 나을 수 있습니다.INSERT ... ON DUPLICATE KEY UPDATE ... SELECT ...(하지만 그건 엉망진창이야)

「평균」에 대해서는, 다음의 점에 주의해 주세요.

• 매일 밤 일평균을 계산할 수 있습니다.AVG(...),그렇지만
• 월평균은 일평균이 아니라 월평균으로 계산해야 합니다.SUM(daily_sums) / SUM(daily_counts)즉, 요약 테이블은 다음과 같습니다.COUNT(*)그리고.SUM(...).

처음에 이 요약 표를 작성하기 위해 600M 행을 한 번에 하나씩 천천히 이동하는 원타임 스크립트를 작성합니다.물론 한 번에 할 수 있지만 다른 모든 것에 대한 간섭은 '나쁜' 것일 수 있습니다.

야간 스크립트에 "중지된 부분부터 다시 시작"하는 코드를 포함하는 것이 더 좋습니다.이렇게 하면 스크립트가 어느 날 밤에 실행되지 않으면 다음날 밤 누락된 내용을 복구합니다.또는 문제가 발생했을 때 수동으로 실행할 수도 있습니다.그리고 한 번 더 달려도 아무 문제 없을 거야

그 동안 필요한 다른 요약 테이블에 대해 생각해 보십시오.일반적으로 데이터 웨어하우스 애플리케이션에는 3-7개의 요약 테이블이 필요합니다.한편, 주간 및 월간 요약은 일별 요약 표에서 충분히 효율적으로 도출할 수 있다는 점에 유의하십시오.몇 가지 경우에는 한 가지에 대한 시간별 요약 표와 여러 가지에 대한 일별 표를 가지고 있었습니다.

6억 줄 크네요.'오래된' 데이터는 삭제됩니까?필요한 요약 표를 얻으면 이전 데이터는 더 이상 필요하지 않습니까?이러한 경우 파티셔닝 사용에 대한 블로그.

언급URL : https://stackoverflow.com/questions/41634190/view-or-stored-procedure-for-an-aggregate-query