나의 첫 SQL최적화(?) - 27초를 1.7초로 줄이기

쿼리 수행시간이... 2.7초요? 아니 27초요?!!

회사에서 통계 화면을 테스트하고 있는데 네트워크 응답이 너무 안오길래 로그를 뒤져봤다. SQL 하나에 평균 27000ms가 찍혔다. 보자보자.. 0이 하나..둘.. 2.7초인가? 1000ms가 1초니까...

아니 27초?????
약 27초가 걸리는 것이다.
2.7초가 걸려도 느리다고 생각할 것 같은데 말이다.

"아니 도대체 데이터가 몇 개길래 27초가 걸리는거야!!!???" 라고 생각하며 봤지만 결과는 고작 4만건이 조금 넘었다.

4만건에 27초..? 이건 장비탓을 할 게 아니라 개발자의 잘못임이 틀림없었다.

문제 상황

이 문제는 도저히 못본 체 넘어갈 수가 없었다. 아니 어떤 백엔드 개발자라도 이걸 그냥 넘어가서는 안 된다..

문제가 발생한 DB는 MariaDB 였는데, 실행계획을 조회했을 때 별로 친절하게 알려주지 않았다. 어떻게든 인덱스를 만들어도 보고 쿼리도 바꿔보고 했는데 부동의 27초는 빈번히 나를 절망시켰다.

회사 코드이기 때문에 공개할 수는 없지만 대략적인 쿼리의 구조는 이랬다.

WITH RECURSIVE all_minute AS 
(         
  SELECT CAST('20240115' as datetime) AS DT
  UNION ALL         
  SELECT ADDTIME(all_minute.DT, '00:01:00')
  FROM all_minute         
  WHERE ADDTIME(all_minute.DT, '00:01:00') <= '20240215'
),
temp AS (
  SELECT
    SUM(a),
    SUM(b),
    DATE_FORMAT(t1.DT)
  FROM
    table1 t1
  WHERE
    ...
  GROUP BY
    t1.DT
) 
SELECT * 
FROM all_minute am
LEFT JOIN temp t ON am.DT = t.DT

거의 다 생략하고 대략적인 구조만 작성했다. 우선 all_minute은 재귀호출을 통해 한달을 분(Minute)으로 나눠 모든 분을 가져온다. 이후 GROUP BY한 실제 테이블과 조인하는데, 이 때 모든 데이터를 분단위로 가져오기 위해 all_minute를 선행테이블로 하여 LEFT 조인을 수행한다.

한 달은 40000분이 조금 넘기 때문에 조인되어 출력되는 레코드의 수 역시 40000개 정도 될 것이다.

무엇이 문제였나?

문제는 바로 데이터 타입 불일치였다.

MariaDB에서는 쿼리를 수행할 때, 쿼리가 어떻게 작동하는 지 자세하게 볼 수 있다.

쿼리 프로파일링이라는 것인데 자세한 내용은 https://mariadb.com/kb/en/show-profile/ 에서 참조하길 바란다.

확인 해보니 Creating sort index 라는 작업이 slow query의 원인이었다. 해당 작업만 25~26초가 걸렸다.
이 과정은 쿼리가 결과를 정렬할 필요가 있을 때, 임시 정렬 인덱스를 생성하는 것이다.
나는 도저히 쿼리에서 어떤 부분 때문에 이런 작업이 필요한 지 이해할 수가 없었다.

앞서 말했지만 문제는 데이터 타입 불일치이다. 정확히 말하면 JOIN 할 때 매핑되는 두 컬럼(DT)의 데이터 타입 불일치가 문제였다.
RECURSIVE할 때의 DT 컬럼은 CAST()를 사용하여 datetime형으로 변환해주었고, temp의 DT 컬럼은 DATE_FORMAT()으로 포맷만 날짜형식이 적용된 문자형이었다.

데이터 타입이 불일치 하기 때문에 조인할 때,많은 데이터에 대해 불필요한 타입 변환이 필요했기 때문에 성능이 매우 저하되었던 것이다.

WITH RECURSIVE all_minute AS 
(         
  /* 여기서 CAST를 해줄 것이 아니라 DATE_FORMAT()을 사용해주었어야 했다. */
  SELECT CAST('20240115' as datetime) AS DT 
  UNION ALL         
  SELECT ADDTIME(all_minute.DT, '00:01:00')
  FROM all_minute        
  WHERE ADDTIME(all_minute.DT, '00:01:00') <= '20

정리

정말 별 문제 아니었고, 이 정도로 쿼리 튜닝이라 할 것까진 아니지만 쿼리 시간을 27초에서 1초대로 줄였다.

사실 이 문제를 해결했을 때 개발자만 느낄 수 있는 그 희열을 잠시나마 느꼈지만 뭔가 씁쓸함도 느꼈다.

이게 '쿼리 튜닝이나 성능 최적화 가 맞는걸까?' 라는 생각도 들고 '그저 다른 개발자의 실수를 내가 바로잡은 것 아닐까?' 라는 생각에 휩싸였다. 실무에서 쿼리 성능최적화를 해보고싶었다. 실무에서 이런 경험을 한다는 것이 쉬운 것은 아니다. 개인적으로 공부할 때는 더욱 그렇다. 나의 첫 쿼리 최적화가 데이터 타입 이슈로 마무리 되어 상당히 아쉽다.

조금 더 기술적인 문제였더라면 더 좋은 경험이 되었지 않았을까라는 생각이 든다.

그래서 이번 기회로 좀 더 SQL에 대해 깊게 공부해보고, 더 좋은 쿼리가 무엇인 지도 공부해보려고 한다. 언젠가 마주할, 혹은 마주하지 않더라도 내가 무의식적으로 작성하는 SQL이 0.001초라도 최적화된 SQL이 될 수 있도록 노력해야겠다.