Where 쉽게보지마라. 쿼리 튜닝하기

40s 쿼리를 4s로 줄이는 쉽고 간단한 방법

2019-12-04

프로젝트를 진행하며 백엔드개발을 하다보면 쿼리작성하는일이 많이 있습니다.
그 중에 오늘 쿼리 튜닝한 내용에 대해 기록&보관 해보고자 합니다.

우선 상황을 간단히 요약 설명하자면,
본 테이블에 쌓이는 데이터는 통계 데이터로, 하루 약 100만건 정도 쌓이고 있습니다.

이 데이터들을 RDB에 쌓는것부터가 일단 문제죠. (처음에 이정도로 사용자가 있을거라 생각 못한 잘못도.. 급하게 만든 잘못도..)

나중에 큐와 NoSQL을 도입하여 개선해보고자 합니다. (그 때 한번 더 정리해두도록 하겠습니다)

해당 데이터들은 완전 row데이터로 이 데이터들을 통계 목적으로 제공하기위해 cron을 실행하여
사용자별 요약데이터를 생성합니다.

이 쿼리의 튜닝을 해보았습니다.

우선 기본 튜닝 골격은 아래와 같습니다.

select sum(a) a, sum(b) b, sum(c) c, avg(d) d
from xxxx
where date(convert_tz(created_at, 'GMT', 'Asia/Seoul')) = '2019-12-04'

위와 같은 쿼리에서 table row가 별로 없다면 문제가 되지 않았을 것입니다.
(성능의 큰 차이가 안보였겠죠)

하지만 위 테이블은 하루 약 100만건.. 현재 5천만건 가까이 쌓이고 있습니다.

위 쿼리 실행 시 약 40~45초 정도가 걸렸습니다.

위 쿼리의 문제점이 무엇을까요?

mysql에는 쿼리문 앞에 explain를 붙여 실행하면 쿼리의 실행계획을 확인할 수 있습니다.

위 쿼리를 실행해보면 아래와같이 확인할 수 있습니다.

위 사진에서 중요한건 type을 보면 All이라 적혀있고, 그것은 풀스캔으로 실행한다는 의미입니다.
풀스캔의 경우 대부분에 아주 느린 성능이 나오게 됩니다.

어떻게 해결을 할 수 있을까요.

40s 쿼리를 4s로 개선하기

우선 위 쿼리에서 문제가 되는 date()를 수정합니다.

date()는 풀스캔을 유발하는 함수로, index도 불가능합니다.

그래서 where 조건을 아래와 같이 수정해봅니다.

where created_at >= convert_tz('2019-12-02', 'Asia/Seoul', 'GMT')  
  and created_at < convert_tz('2019-12-03', 'Asia/Seoul', 'GMT')

변환 조건을 앞이 아닌 뒤에 줍니다. 이렇게 변환한다면 created_at에 index를 설정하여 속도를 개선할 수 있고, full scan을 돌지않아 속도도 향상됩니다.
explain을 다시 확인해보겠습니다.

type을 보면 ref로 나옵니다.

그리고 created_at에 index도 추가해줍니다.

ALTER TABLE `xxx` ADD INDEX (`created_at`);

오래걸립니다. 실서비스라면 반드시 none lock을 사용해야합니다. 관련 문서도 첨부해봅니다.. 문서 보기

4초로 줄었습니다. 와우!

40초 쿼리가 4초로.. 1/10 수준으로 줄었습니다.

해당 쿼리는 위에도 설명했지만, 매일 한번 배치로 실행되며 전날의 데이를 요약하여 통계데이터를 만들어주는 쿼리입니다.
즉, 쿼리 실행 중에 새로운 데이터가 들어오는것에 대해 신경쓸 필요가 없습니다.

(새로 들어오는 데이터는 오늘 데이터이므로 전날 요약하는 쿼리에는 영향이 없습니다)

따라서 우리는 set session transaction isolation level repeatable read;를 이용하여 실행 중 새로운 데이터가 있더라도 무시할 수 있습니다.

40s -> 4s로 줄였지만 더 개선한다면 더 빠르게 실행할 수 있을 것 같습니다.
복잡한 or 실행시간이 오래걸리는 쿼리를 작성할 때에는 explain을 활용하는게 좋을 것 같습니다.