DBA와 함께 한 쿼리튜닝 - 그룹화

※ 이 글은 몇 달 전 작성한 글입니다.

한 달 전 런칭한 프로젝트가 있습니다. 주문/결제와 관련 있는 프로젝트로 처음에는 크게 대수롭지 않게 생각하였습니다. 비록 메인 업무 도메인은 아니라도 서브는 되었고, 이 프로젝트에서는 로직에 깊게 관여할 부분도 없다고 여겼습니다. 하지만 프로젝트를 진행해보니 예상과 달랐던 부분이 생겼습니다. 빈번하지는 않아도 예상을 빗나가는게 개발이 가진 묘미죠.

프로젝트를 진행해보니 새로운 기능을 기존 데이터 기반으로 만들 경우 유지보수가 힘들어 보였습니다. 데이터를 역으로 추출해야 하는데, 간단하게 추출할 수가 없었습니다. 심지어 이 방식으로 진행할 경우 관련 로직이 바뀔 때마다 하드코딩이 필요해보였습니다. 진행 중인 프로젝트가 끝나기 전에 신규 프로젝트에 대한 리뷰를 받는게 빈번한 상황에서 데이터를 늘리더라도 유지보수를 줄이는게 정말 중요하였습니다. 결국 팀원과 논의 후 프로토타입 코드를 전부 지운 뒤 다시 작업하였습니다. 부랴부랴 신규 테이블 스키마를 작성하여 DBA에게 요청하고, 이 테이블을 활용하는 방식으로 재설계하였습니다. 다행스럽게도 빠르게 선행한 덕분인지 일정에 지장이 생기지 않았습니다. 무사히 런칭할 수 있었죠. 신규 테이블을 생성할 때 앞으로 다양하게 활용할 수 있는 데이터 구조로 만들어 만족하였습니다. 불가 며칠 전까지 말이죠.

사실 그 프로젝트와 관련된 쿼리 중 하나가 불완전하다는 것을 알고 있었습니다. 쿼리튜닝이 필요해보였으나, 런칭에 초점을 맞추었습니다. 일정 수준 버퍼를 개발 기간에 포함하였지만, 코드를 재설계하면서 시간을 전부 소비하였기 때문이죠. 비록 마음에 드는 쿼리는 아니었어도, 쿼리 실행 계획을 살펴보니 슬로우쿼리가 될 것 같지는 않았습니다. 전부 인덱스를 타고 있었고, WHERE 절을 최대한 안 쪽에 배치하여 불필요한 데이터 로드를 최대한 배제하고자 했습니다.

EXPLAIN을 활용하면 쿼리 실행 계획을 미리 살펴볼 수 있다.

그럼에도 불구하고 실서버 모니터링에 해당 쿼리가 캐치 되었습니다. DBA가 슬로우쿼리는 아니지만, load에 해당 쿼리가 계속 잡혀 쿼리튜닝이 필요하다고 전하였습니다. 어느정도 예상 되던 이야기였습니다. 개발자로서 가진 감은 대부분 정확하였거든요. 쿼리를 작성한 스스로도 불안하다고 느꼈으니 모니터링에 그대로 잡혔던겁니다. 해당 쿼리는 UNION ALL을 사용하고 테이블 10개 이상 불러오는 쿼리였거든요.

네, 이상한 쿼리였습니다. 아무리 백앤드 개발자라도 이처럼 복잡한 정책을 가진 리스트 쿼리를 직접 만들 일은 없었거든요. 일반적으로 테이블 스키마를 설계할 때 리스트를 뽑아내는데 무리 없도록 설계하지만, 이번에 추가된 리스트 정책은 한 두 테이블로 간단하게 처리할 수 없었습니다. 그나마 다행인지 불행인지 쿼리튜닝을 준비하던 도중 다른 개발자가 신규 프로젝트를 위해 만든 특이 데이터도 기존 쿼리로 호환할 수 없다는 사실을 발견하여 이번 쿼리튜닝에 같이 고치도록 하였습니다.

DBA에게 정책을 설명하고, 기존 쿼리를 정리하여 주었습니다. 이를 바탕으로 기본적인 뼈대는 DBA가 만들었습니다. GROUP BY를 제거하기 위해 다음과 같은 구조로 주었습니다.

// as-is
SELECT *
FROM sample_data
GROUP BY part_cd
ORDER BY idx DESC

// to-be
SELECT base.*
FROM ( SELECT *,
                      @seq := CASE WHEN @part_chk = part_cd THEN @seq + 1 ELSE 1 END AS seq,
                      @part_chk := part_cd AS part_chk
	      FROM sample_data
) AS base, (SELECT @part_chk := 0, @seq := 0) AS tmp
WHERE base.seq = 1
ORDER BY base.idx DESC

수정된 쿼리를 보면 GROUP BY 대신 매개변수와 CASE를 활용한 부분을 찾을 수 있습니다. GROUP BY로 묶는 것보다 매개변수를 이용한 그룹화가 성능 상 더 나은 결과를 보였습니다. 캐싱 때문에 여러 차례 테스트 하지는 못 했으나, 이전보다 2.5배 정도 속도가 개선되었습니다. (쿼리도 늘어ㅆ..!)

이후에도 정책에 맞는 리스트 쿼리를 만들기 위해 두 차례 더 주고 받았습니다. 그 때 DBA가 추가적인 조언을 해주었는데, ‘매개변수를 기반으로 처리할 때 동일선상에 ORDER BY가 있으면 쿼리 실행 계획이 틀어질 수 있어 서로 분리해야 한다.’는 것이었습니다. 자세한 내용은 더 묻지 않아 Mysql에 한정된 이야기인지는 모르겠습니다. DBA 이야기를 들어 안 좋을건 없으니 잊지 않기 위해 기록합니다.

그래서 최종 쿼리는 어떠냐고요? SELECT 17개, FROM, JOIN 합쳐서 24개, EXISTS 4개, UNION ALL 1개, ORDER BY 3개, CASE 3개, 매개변수 5개인 120줄인 리스트 쿼리가 되었습니다. 누..누군가가 유지보수 할 수 있겠죠? (단일 쿼리에 친절하게 주석이 4개입니다.)

몇 차례 쿼리튜닝을 요청하며 배운걸 정리하면 다음과 같습니다.

1. 다수의 GROUP BY, ORDER BY는 지양해야 한다.
2. 가독성은 둘 째 치고, GROUP BY보다 매개변수와 CASE를 활용한 쿼리가 성능에 우위를 보인다.
3. 매개변수와 CASE를 활용하여 그룹화할 때 ORDER BY와 분리해야 쿼리 실행 계획이 틀어지지 않는다.