데이터베이스 질문과 답변
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 안녕하세요.쿼리 튜닝 도움 요청드립니다.(MySQL)현재 쿼리SELECT T.ITEM , T.COUNT , ROUND((T.A_SU * 1.0) / T.COUNT, 1) , ROUND(((T.B_TIME * 1.0) / T.COUNT) / 3600.0, 1) , ROUND((((T.A_TIME + T.B_TIME) * 1.0) / T.CNT) / 3600.0, 1) , ROUND(T.A_TIME / 3600.0, 1) , ROUND((T.SUM_C_TIME / 3600), 1 , ROUND(((T.A_TIME * 1.0) / T.COUNT) / 3600.0, 1) , T.D , ROUND((T.D * 1.0) / T.COUNT) , ROUND((((T.B_TIME + T.C_TIME) * 1.0) / T.COUNT) / T.A_TIME_AVG, 1)FROM( SELECT ITEM , COUNT(*) AS COUNT , AVG(A_TIME / (CASE WEHN A_SU = 0 THEN 1 ELSE A_SU END)) AS A_TIME_AVG , SUM(A_TIME) AS A_TIME , SUM(CASE WEHN A_SU = 0 THEN 1 ELSE A_SU ENS) AS A_SU , SUM(B_TIME) AS B_TIME , SUM(C_TIME) AS C_TIME , SUM(C_TIME*A_SU) AS SUM_C_TIME , SUM(D) AS D FROM TABLE WHERE DATE >= STR_TO_DATE('202101010000', '%Y%m%d%H%i%s') AND DATE <= STR_TO_DATE('202201012359', '%Y%m%d%H%i%s') GROUP BY ITEM ) T |
특정 기간동안의 사용량 통계를 내는 쿼리로 사용중이고
3개월을 기준으로 잡으면 DATE 컬럼에 index를 타서 속도가 5초 이내로 돕니다만, 1년 치 이상 조회를 요구하셔서 현재 쿼리로 진행시 30여 초 정도 시간이 소요됩니다.
데이터는 약 1700만 건 정도인데 실행 계획상 사용 가능한 index가 있지만 풀스캔을 돌고 있습니다.
FROM 절 서브 쿼리 부분을 보완할 수 있는 방법이 없는지 도움 부탁드립니다.
by 마농
[2023.03.30 09:07:53]
인덱스가 무조건 좋은게 아니기 때문입니다.
적정량을 넘어서면 풀스캔이 차라리 좋습니다.
해결방안은
1. 파티션 테이블을 이용하는 방안
2. 커버링인덱스를 이용하는 방안 : 사용되는 모든 항목을 결합인덱스로
3. 월단위 집계 테이블을 따로 만들어 두고 사용하는 방안
- 전달까지는 집계테이블 이용하고
- 현재달은 실제 테이블을 이용하여
- 최종 UNION ALL ~ SUM 하는 방법
- 단, 중간에 평균(AVG) 부분이 있어서 적용 가능할지는 미지수네요.
기타 사항으로 1.0 으로 나누는 것은
- MSSQL 에서 소수점 이하 계산을 하기 위해 사용되는 방식입니다.
- MySQL 에서는 굳이 사용할 필요가 없습니다.
by 구경꾼
[2023.03.30 17:00:38]
일자별 데이터 분포가 어떤지 모르겠지만 풀스캔이 유리하다고 옵티마이저가 판단한 것 같은데..통계정보가 갱신되지 않아 그렇게 판단했을 수도 있을 듯요.
) 버튼을 클릭하여 작성 하시면 됩니다.