쿼리가 느린가요? 당황하지 말고 일단 인덱스 살펴봅시다.

시작하기 전에 결론부터

0. 6초에서 264ms 로 줄임
1. 역시나 쿼리는 데이터 빠방하게 넣고 테스트하자.
2. 일단은 인덱스!
3. explain 만 잘 읽을 줄 알아도 떡이 나온다.
4. 정보 공유의 중요성은 아무리 강조해도 지나치지 않는다.

이상한 걸 발견하는 능력

이번 프로젝트를 하며 동작은 하는데 절차가 꼬인 것, 데이터가 잘못되거나 타입이 이상한 것 등을 엄청나게 발견하고 있다. 동료에게 '왜이리 이상한 걸 잘 찾냐?'는 이야기를 수차례 들었다.

생각을 해보면 개발을 하기 이전부터도 규칙에 어긋나는 걸 발견하는 능력이 꽤 있었던 것 같다. 그리고 최근에 느낀건데, 이건 개발하는 데 상당히 도움이 된다. 일머리 차원이랄까?

이 능력이 발휘되어 느린 쿼리를 또 발견했다.

쿼리는 같은데?????

앞서 말했듯이 이번 프로젝트는 기존 CS 프로그램을 변환하는 작업이다.

기존 쿼리를 ORM으로 열심히 변환을 해놨는데, CS에선 금세 결과가 떨어지는 게 내가 짠 건 6초가 넘는 게 아닌가?!

수 백줄이 넘는 기존 쿼리와 ORM이 생성한 쿼리를 눈알 빠지도록 하나하나 비교를 해봐도 똑같은데 속도 차이가 이만큼이나 나니 환장할 노릇.

쿼리를 쪼개서 DBeaver에서 테스트해보니 일단 서브쿼리가 원인인 건 금방 찾았다.

이 쿼리에 실행되는 서브쿼리는 7개. explain을 보면

SubPlan 1
  -> GroupAggregate  (cost=175789.73..175789.76 rows=1 width=12)
      Group Key: u0.serial
        ->  Sort  (cost=175789.73..175789.74 rows=1 width=4)
           Sort Key: u0.serial
           ->  Index Scan using uix_ on D_table u0  (cost=0.56..175789.72 rows=1 width=4)
              Index Cond: (((ㄴ) AND (ㄷ))
              Filter: (((ㄹ) AND (ㅁ) AND (ㅅ) AND (ㅇ)

역시나 서브쿼리 쪽의 cost가 굉장히 컸다.

인덱스가 원인일 거 같긴한데, 근데 쿼리는 같은데????

한참 이것저것 만져보다가 where(filter)에 보편적인 조건을 하나 넣었더니...?!!!

6초가 걸리던 게 264ms로 2000% 이상 빨라지는 거 아닌가?

프로젝트의 정보를 노출하지 않기 위해 프랜차이즈에 비유해 설명하자면, 기존 CS는 본점만 굴렸다면 새로운 버전에선 프랜차이즈로 확대하려고 하는데, 프랜차이즈 1호점, 2호점 코드를 DB의 모든 테이블에 추가를 했고, 그걸 모든 테이블의 유니크 인덱스(uix) 모음의 첫 번째 조건 으로 등록이 되어 있던 것. 그리고 다른 인덱스가 조건에 들어가더라도, 첫 번째가 빠지면 인덱스를 타지 않고 풀스캔이 되어 그렇게 시간이 오래 걸린 것이라고 한다.

후자의 explain을 살펴보면

SubPlan 1
  -> GroupAggregate  (cost=12.16..12.18 rows=1 width=12)
      Group Key: u0.serial
        ->  Sort  (cost=12.16..12.17 rows=1 width=4)
           Sort Key: u0.serial
           ->  Index Scan using uix_ on D_table u0  (cost=0.56..12.15 rows=1 width=4)
              Index Cond: (((ㄱ) AND (ㄴ) AND (ㄷ))
              Filter: (((ㄹ) AND (ㅁ) AND (ㅅ) AND (ㅇ)

Index Cond에 (ㄱ) 하나가 추가된 것만으 cost가 175789.73..175789.76에서 12.16..12.18로 엄청나게 줄었고, 덕분에 응답속도도 2000% 이상 빨라졌음을 알 수 있다.

후속 조치

원인 파악을 하자마자 드는 생각은 나 뿐 아니라 다른 팀원들이 짠 쿼리에서도 프랜차이즈 컬럼이 쿼리 조건에서 빠진 경우가 분명 있을 것 같다는 것이었다.

DBA에게 전체 공지를 부탁하면서 혹시나 모르니 백엔드 개발자에게도 해당 사항을 따로 공유한 뒤, 내가 짜거나 검토했던 쿼리를 다 살피고 수정했다.

참고로 이전 글의 D-480도 프랜차이즈 컬럼이 빠진 상황이었는데 추가를 했더니 870ms였던 쿼리가 600ms 초반으로 줄어드는 걸 확인했다.

회고

쿼리 속도 관련해 연속으로 좋은 경험을 했다. 그리고 이번 경험을 통해 explain을 해석하는 법을 약간이나마 더 익힌지라 추후에 다른 문제를 발견하더라도 원인 파악 및 문제 해결을 원활하게 할 수 있을 것 같다...(고 기대해본다.)

참고로 향로 님의 mysql 관련 글들이 정리가 무척 잘 되어 있다. 공부하자!