제발 쿼리 이렇게 짜지 좀 마요🤬

onlyJoon·2024년 7월 26일
post-thumbnail

📚 이게 쿼리야, 소설이야?

레거시 프로젝트를 진행하면서 접한 쿼리 중 눈에 띄는 것이 있었습니다.

바로 이 쿼리인데요:

SELECT CAL.*
FROM (
    SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
           SUM(COUNT) AS COUNT, SUM(COST) AS COST 
    FROM (
        SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
               COUNT * COUNT(*) AS COUNT, COST * COUNT(*) AS COST 
        FROM USER.DAILY_CALC 
        WHERE CALC_ID = :calId AND TYPE IS NOT NULL 
        GROUP BY CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, COUNT, COST
    ) A 
    GROUP BY A.CALC_DAY, A.PRD_POLICY_ID, A.TYPE 
    UNION ALL 
    SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
           SUM(COUNT) AS COUNT, SUM(COST) AS COST 
    FROM (
        SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
               COUNT * COUNT(*) AS COUNT, COST * COUNT(*) AS COST 
        FROM USER.DAILY_CALC 
        WHERE CALC_ID = :calId AND TYPE IS NULL 
        GROUP BY CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, COUNT, COST
    ) B 
    GROUP BY B.CALC_DAY, B.PRD_POLICY_ID, B.TYPE 
) CAL 
INNER JOIN USER.PRD_POLICY PP 
ON CAL.PRD_POLICY_ID = PP.ID 
ORDER BY CAL.CALC_DAY DESC, PP.ORDR ASC, CAL.COST DESC;

이 쿼리, 잠깐만 살펴봐도 고칠 게 많아 보이지 않나요? 😅

  1. 서브 쿼리 A 및 집계
  2. 서브 쿼리 B 및 집계
  3. 두 서브 쿼리를 UNION ALL
  4. 다른 테이블과 조인
  5. 정렬

크게 5단계로 나눌 수 있습니다. 서브 쿼리와 집계 과정이 많은 것을 보면 성능이 안좋을 것임을 예상할 수 있습니다.

📊 현재 성능 분석

실행 계획

우선 실행 계획부터 살펴봤어요. (MariaDB 10.4 기준)
MariaDB에서 실행 계획을 확인하기 위해 EXPLAIN 명령어를 사용합니다.

쿼리의 실행 계획을 분석하면 쿼리가 어떻게 실행되는지, 어떤 인덱스가 사용되는지 등을 파악할 수 있어요.


참고로 DAILY_CALC 테이블의 레코드는 약 450만 건, PRD_POLICY 테이블은 40건입니다.

1. PRIMARY (PP 테이블)

type: ALL: 테이블 전체를 스캔하고 있습니다.
rows: 40: 약 40개의 행을 읽고 있습니다.
Extra: Using temporary, Using filesort: 임시 테이블과 파일 정렬을 사용하고 있습니다.

2. PRIMARY (derived2)

type: ref: 참조 접근 방식을 사용하고 있으며, key0 인덱스를 사용하고 있습니다.
rows: 94: 약 94개의 행을 읽고 있습니다.
Extra: Using temporary, Using filesort: 임시 테이블과 파일 정렬을 사용하고 있습니다.

3. 서브쿼리 (DAILY_CALC 테이블)

type: ALL: 테이블 전체를 스캔하고 있습니다.
rows: 2: 약 2개의 행을 읽고 있습니다.
Extra: Using index condition, Using temporary, Using filesort: 인덱스 조건을 사용하지만 임시 테이블과 파일 정렬도 사용하고 있습니다.

4. 서브쿼리 (DAILY_CALC 테이블)

type: range: 범위 스캔을 사용하고 있습니다.
rows: 307: 약 307개의 행을 읽고 있습니다.
Extra: Using index condition, Using temporary, Using filesort: 인덱스 조건을 사용하지만 임시 테이블과 파일 정렬도 사용하고 있습니다.

5. UNION (derived5)

type: ALL: 테이블 전체를 스캔하고 있습니다.
rows: 9472: 약 9472개의 행을 읽고 있습니다.
Extra: Using temporary, Using filesort: 임시 테이블과 파일 정렬을 사용하고 있습니다.

6. 서브쿼리 (DAILY_CALC 테이블)

type: ref: 참조 접근 방식을 사용하고 있으며, IDX_CALC_ID 인덱스를 사용하고 있습니다.
rows: 9472: 약 9472개의 행을 읽고 있습니다.
Extra: Using where, Using temporary, Using filesort: WHERE 조건을 사용하며, 임시 테이블과 파일 정렬도 사용하고 있습니다.

문제점 분석

실행 계획을 통해 확인한 문제점들은 다음과 같아요:

테이블 전체 스캔

여러 단계에서 type: ALL이 나타나고 있으며, 이는 테이블 전체 스캔을 의미합니다. 인덱스를 사용하지 않기 때문에 성능이 매우 낮습니다.

임시 테이블과 파일 정렬

Using temporary, Using filesort가 여러 단계에서 나타나고 있으며, 이 문제는 데이터 양이 많을 때 특히 심각해질 수 있습니다.

인덱스 사용 부족

특정 단계에서는 인덱스를 제대로 사용하지 않고 있습니다

따라서 Using temporary, Using filesort, ALL의 사용을 줄일 수 있도록 변경해야합니다.
또한 필요하다면 적절한 인덱스를 추가해야 합니다.

DERIVED

DERIVED는 실행 계획에서 사용되는 용어로, 서브쿼리를 나타냅니다. DERIVED 테이블은 쿼리의 서브쿼리로부터 생성된 임시 테이블을 의미합니다.
DERIVED 테이블의 사용을 최적화하여 성능을 향상시킬 수 있는 방법은 다음과 같습니다:

  1. 서브쿼리 대신 조인 사용: 가능하면 서브쿼리를 제거하고 조인을 사용합니다.
  2. 서브쿼리 단순화: 서브쿼리가 복잡한 경우, 이를 단순화합니다.
  3. 적절한 인덱스 사용: 서브쿼리에서 사용되는 테이블에 적절한 인덱스를 생성합니다.
  4. 필요한 데이터만 선택: 서브쿼리에서 필요한 데이터만 선택합니다.

Duration

✨ 쿼리 최적화

우선 Using temporary, Using filesort를 줄이기 위해 서브쿼리와 집계 과정을 최적화했어요.

서브쿼리 구조를 보면 TYPE의 NULL 여부만 다를 뿐 같은 구조로 되어 있어요. 집계 과정까지 동일하기 때문에 TYPE의 NULL 여부 조건을 삭제하고 두 서브쿼리를 합칠 수 있어요. 그와 동시에 같이 붙어있는 조건절을 밖으로 빼면 불필요한 집계 과정과 서브쿼리를 모두 줄일 수 있어요. 

SELECT CAL.*
FROM (
    SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
           SUM(COUNT) AS COUNT, SUM(COST) AS COST 
    FROM USER.DAILY_CALC
    WHERE CALC_ID = :calId
    GROUP BY CALC_DAY, PRD_POLICY_ID, TYPE 
) CAL 
INNER JOIN USER.PRD_POLICY PP 
ON CAL.PRD_POLICY_ID = PP.ID 
ORDER BY CAL.CALC_DAY DESC, PP.ORDR ASC, CAL.COST DESC;

이렇게만 해도 쿼리가 매우 단순해졌어요. 😊 다시 실행 계획을 살펴보겠습니다.

쿼리구조가 단순해지면서 여러 번 수행해야 했던 Using temporary, Using filesort가 확 줄어들었습니다. 이는 디스크 I/O를 줄이고, 쿼리 실행 시간을 단축시킵니다.
뿐만 아니라 기존 쿼리는 약 2만개의 행을 처리해야 하는 경우가 존재했지만
최적화한 쿼리에서는 절반의 행으로 원하는 데이터를 가져올 수 있습니다.

여전히 Using temporary, Using filesort가 존재하지만 필수적인 부분이라 더 이상 최적화는 어렵다고 판단하였습니다.

Duration

무려 약 100%의 실행시간 개선을 확인할 수 있어요. 😍

🤔 이대로 끝?

이 쿼리의 결과를 백엔드에서 사용하고 있어요.
하지만 결과를 그대로 사용하는 것이 아니라 여러 조건들로 데이터를 필터링하여 사용하고 있어요.
만약 쿼리 결과를 여러 곳에서 사용하고 있다면 괜찮을 수 있지만,
현재는 필터링 결과를 그대로 반환하고 있어요.

result = result.stream().filter("DAILY_CALC 조건 1");
result = result.stream().filter("PRD_POLICY 조건 1");
result = result.stream().filter("PRD_POLICY 조건 2");
result = result.stream().filter("PRD_POLICY 조건 3");
result = result.stream().filter("PRD_POLICY 조건 4");  

return result;

레거시 코드이기 때문에 코드 퀄리티는 차치하고서라도 이렇게 사용하면 많은 문제점이 있습니다.
조건에 맞지 않는 데이터까지 전부 조회하기 때문에 자원의 낭비가 심하고,
데이터가 많아질 경우, 성능에 큰 영향을 미칠 수 있고 최악의 경우 OOM이 발생할 수도 있습니다.

이러한 문제점을 해결하기 위해 필터링을 백엔드 로직에서가 아닌 쿼리문에서 직접 진행하도록 수정을 진행하였습니다.

😃 쿼리에 조건 추가

모든 조건을 JOIN 이후에 적용해보면 다음과 같습니다.

SELECT CAL.*
FROM (
    SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
           SUM(COUNT) AS COUNT, SUM(COST) AS COST 
    FROM USER.DAILY_CALC
    WHERE CALC_ID = :calId
    GROUP BY CALC_DAY, PRD_POLICY_ID, TYPE 
) CAL 
INNER JOIN USER.PRD_POLICY PP 
ON CAL.PRD_POLICY_ID = PP.ID 
WHERE ("DAILY_CALC 조건 1")
AND ("PRD_POLICY 조건 1")
AND ("PRD_POLICY 조건 2")
AND ("PRD_POLICY 조건 3")
AND ("PRD_POLICY 조건 4")
ORDER BY CAL.CALC_DAY DESC, PP.ORDR ASC, CAL.COST DESC;


실행계획을 살펴보면 PRIMARY에서 Using where 조건 평가가 발생합니다. 추가적인 필터링 작업이 필요하다는 뜻입니다.

이 때 DAILY_CALC 조건 1 를 서브쿼리 내에 적용하여 이를 없앨 수 있습니다.

서브쿼리 단계에서 더 많은 조건을 평가하므로, 서브쿼리 자체의 결과 집합이 작아질 가능성이 있습니다.
이는 서브쿼리 결과를 메모리에 적재하는 양을 줄여서 성능을 향상시킬 수 있습니다.

SELECT CAL.*
FROM (
    SELECT ID, CALC_ID, CALC_DAY, PRD_POLICY_ID, TYPE, PRICE, 
           SUM(COUNT) AS COUNT, SUM(COST) AS COST 
    FROM USER.DAILY_CALC
    WHERE CALC_ID = :calId
    AND ("DAILY_CALC 조건 1")
    GROUP BY CALC_DAY, PRD_POLICY_ID, TYPE 
) CAL 
INNER JOIN USER.PRD_POLICY PP 
ON CAL.PRD_POLICY_ID = PP.ID 
WHERE ("PRD_POLICY 조건 1")
AND ("PRD_POLICY 조건 2")
AND ("PRD_POLICY 조건 3")
AND ("PRD_POLICY 조건 4")
ORDER BY CAL.CALC_DAY DESC, PP.ORDR ASC, CAL.COST DESC;


불필요한 Using where 조건 평가가 없기 때문에 쿼리 성능이 약간 더 향상될 수 있음을 확인할 수 있습니다.

🎉 결론

이번 최적화 작업을 통해 성능이 낮았던 레거시 쿼리와 로직을 크게 개선할 수 있었습니다.

  • 서브쿼리 단순화: TYPE의 NULL 여부에 따라 나눠져 있던 두 서브쿼리를 하나로 통합하여 쿼리 구조를 단순화했습니다. 이를 통해 불필요한 집계 과정과 서브쿼리 수행을 줄였습니다.

  • 임시 테이블과 파일 정렬 줄이기: 서브쿼리와 집계 과정이 단순화되면서 여러 번 수행되었던 Using temporary와 Using filesort를 줄여 쿼리 성능을 향상시켰습니다.

  • 필터링 로직 쿼리로 이동: 백엔드에서 수행하던 필터링 작업을 쿼리문 내에서 직접 수행하도록 수정함으로써 자원 낭비를 줄이고 성능을 개선했습니다.

최적화 작업을 통해 기존에 비해 성능이 크게 개선되었으며, 불필요한 데이터 조회와 자원 낭비를 줄여 전체 시스템의 효율성을 높일 수 있었습니다. 이 과정을 통해 쿼리 최적화의 중요성과 효과를 다시 한 번 확인할 수 있었습니다.

profile
onlyJoon
A smooth sea never made a skilled sailor
이전 포스트

Docker로 MySQL 설정 및 데이터베이스 생성하기

다음 포스트

@Transactional 알고 쓰자 (2)

1개의 댓글

comment-user-thumbnail
DevSeoRex
2024년 8월 5일

세세한 분석 잘 배우고 갑니다 👍

답글 달기