본 글의 테이블명, 컬럼명, 쿼리 일부는 실제 서비스와는 다르게 임의로 수정, 각색되어 있습니다.
개요
실무에서 서비스를 유지보수하면서 경험한 쿼리 튜닝에 대해 정리해보았다. 실제 유저가 없는 개인 프로젝트와는 달리 실무에서는 많은 데이터를 고려하여 쿼리를 효율적으로 설계하는 것이 중요하다. 본 글에서는 부하가 큰 쿼리들을 추려내고, 해당 쿼리들을 개발비용을 고려하여 튜닝하는 방법에 대하여 다루려고 한다.
문제 배경
- 이벤트 당시 모니터링 결과, 데이터베이스에 부하가 크다라는 것을 알게 되었다.
(서버는 여러 개로 로드밸런싱이 되고 있었으나, DB는 1개로 DB 부하가 큰 상황이었다.)
- Performance Insight 확인 결과, 데이터베이스 부하의 원인은 비용이 큰 쿼리 때문임을 알게되었다.
- 점진적으로 적용 가능한 개선 방안을 찾다가, 시간과 개발 비용을 고려하여 쿼리튜닝을 적용해보기로 하였다.
데이터베이스를 scale up 해주는 방법도 있으나, 데이터베이스는 클라우드 중 비용이 매우 비싼 편이다. 돈으로 때우는 방법은 임시방편이다.
쿼리 튜닝 대상의 우선순위를 정하자
- 부하가 클 수록
부하가 큰 쿼리는 DB가 죽는 주요 원인이다.
- 자주 호출될 수록
자주 호출되는 쿼리를 개선해줄 수록 사용자 경험을 개선할 수 있다.
- 변경이 용이할 수록
개발비용을 고려하여 변경이 용이한 쿼리부터 시작하는 것이 좋다.
이러한 요소들을 잘 고려하여서 현실적으로 어떤 쿼리를 튜닝할지를 결정하자.
(팁) AWS RDS에서는 Performance Insights를 제공한다. 병목현상이 발생하는 쿼리를 확인하는 데 유용하다.
MySQL EXPLAIN(실행계획)을 보자
튜닝할 쿼리를 정했다면, 해당 쿼리에 대한 EXPLAIN을 살펴보아야 한다.
MySQL Workbench를 활용하면 쉽게 EXPLAIN을 활용할 수 있다.
우선적으로는 Scan 방식과 Query cost에 집중하여 살펴보자.
자세한 방법은 아래의 글을 참조하면 좋다.
튜닝하기 전 알아야 할 것들
B-Tree 인덱스
MySQL은 기본적으로 B-Tree 인덱스 구조이다. (엄밀하게는 B+ Tree이다.)
루트 노드, 브랜치 노드, 리프 노드, 데이터 파일로 나뉘어져 있는데, 루트 노드, 브랜치 노드에는 인덱스가 있고, 리프노드에는 인덱스, 데이터(주소)가 있다.
추가 시에는 적절한 위치를 검색 후 리프 노드에 저장되며(느림), 검색 시에는 트리 탐색 과정을 통해 루트 노드부터 탐색한다(빠름).
인덱스
쉽게 생각하면 메모리 계층 구조를 떠올리면 된다. 인덱스에 원하는 값이 있으면 빨리 찾고, 디스크 저장소까지 뒤져야하면 느려진다. 당연히 자주 검색하는 조건을 인덱스에 두면 더 빠를 것이다.
- SELECT의 개선을 인덱스를 추가해주는 것으로 해결할 수 있다.
- 물론, 인덱스를 무조건 추가하는 것이 답은 아니다. 인덱스의 추가는 command(insert, update, delete)의 성능 저하를 불러일으킨다. 데이터 양이 커질수록 인덱스의 영향은 커지므로 신중하게 판단하여 결정하여야 한다.
- 인덱스의 갯수
jojoldu님의 글에 따르면, 일반적으로 3~4개 정도가 적절하다. 인덱스가 PK 외에 특별히 걸려있지 않았다면 추가해줄 수 있다. 특별히 검색에 자주 사용하게 되는 컬럼이 있다면 인덱스로 걸어줄 수 있다. (예: 주문 테이블의 상품 아이디)
테이블 스캔
인덱스를 잘 설정했다고 해서 끝이 아니다. 결과적으로 쿼리의 테이블 스캔이 효과적으로 이루어져야한다.
일반적으로 다음 순서대로 빠르며, 가능하면 쿼리가 윗순서의 type으로 스캔하도록 개선하는 것이 더 효율적이다.
- index range scan
- const(UNIQUE INDEX SCAN)
- eq_ref, ref
- range
- index full scan
- index
- full table scan
- ALL
EXPLAIN의 type을 참고하면 이 외에도 많은 종류의 스캔 방식이 존재한다.
실전적인 쿼리 튜닝 팁이 더 궁금하다면 아래 아티클들을 참고하면 많은 도움이 될 것이다.
MySQL 공식 문서도 친절한 편이니 함께 보면 좋다.
![[mysql] 인덱스 정리 및 팁](https://tistory1.daumcdn.net/tistory/1826700/78816966c3ae46c68ac84d5b0e3cb4d6)
![[mysql] 인덱스 정리 및 팁](https://t1.daumcdn.net/cfile/tistory/2237674457FB7EA215)
사례
코드를 뜯어보니 인덱스에 대한 개념 없이 코드가 짜여진 코드가 많았고, PK 외에 특별히 인덱스가 존재하지 않는 테이블이 많았다. AWS RDS Performance Insights에서의 데이터를 토대로 부하가 크거나 자주 호출되는 쿼리들부터 튜닝을 시작했다.
사례1: 주문 별 적립금 정보 호출 기능 개선.
- 문제 상황
SELECT * FROM points WHERE order_no = ? AND user_id = ? AND point_status = 'N'points 테이블은 적립금 적립 내역과 함께 order_no(주문번호)를 함께 기록한다.마이페이지에서는
order_no를 기준으로 points 테이블에서 적립금 관련 정보를 출력해주고 있었고, 해당 쿼리의 부하가 매우 큰 상황이었다.- 해결책
공식 문서에 따르면 WHERE는 인덱스를 사용하는 가장 기본적인 곳이다.
MySQL uses indexes for these operations:
* To find the rows matching a WHERE clause quickly.
...points 테이블에서 검색 조건으로 가장 많이 사용되는 order_no를 Non-Unique Index로 추가해주었다.- 결과
Full Table Scan을 하던 쿼리가 Non-Unique Key Lookup으로, Query cost는 45262에서 1.2로 개선되었다.
사례2: 상품 옵션별 추가가격을 가져오는 기능 개선
- 문제상황
SELECT * FROM goods_info WHERE goods_option='선택1_WHITE:230'상품 옵션별 추가가격을 가져오는 작은 기능의 쿼리이다. 상품 정보를 불러올 때 추가 옵션이 있는 경우라면 반드시 불러와지고 있었다.
goods_no, goods_sub 별로 goods_option이 존재하는 구조인데 상품이 달라도 상품 옵션명이 같으면 그냥 가져와주세요~ 하는 쿼리이다. - 해결책
SELECT * FROM goods_info WHERE goods_no = 391196 AND goods_sub = 0 AND goods_option = '선택1_WHITE:230'쿼리의 오류를 수정해주었고,
goods_info 테이블에 goods_no, goods_sub, goods_option 으로 다중 칼럼 PRIMARY 키를 1개 걸어주었다. goods_no, goods_sub, goods_option 은 UNIQUE하므로, 해당하는 레코드는 1건만 저장되어 있으므로, 즉시 스캔이 가능하다. 다중 칼럼 키에서 주의해야할 것은 키의 순서이다. 가장 왼쪽 키부터 순서대로 인덱싱이 되기 때문에, 순서를 잘못 작성한다면 인덱스가 활용되지 못할 수 있다.
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
공식 문서의 예제를 참고하면, 인덱스가 (col1, col2, col3)일 때, 위의 쿼리는 인덱스를 타는 반면 아래 쿼리는 인덱스를 타지 않는다. (말로 설명이 어려운데, 예제로 보는 게 편하다!)
- 결과
Full Table Scan을 하던 쿼리가 Unique Index Scan(const)로 Query cost는 180258에서 1로 쉽고 빠르게 개선되었다.
사례3: ORDER BY 개선
- 문제 상황
SELECT * FROM board ORDER BY created_at DESC게시판에서 데이터를 시간 순으로 정렬하여 가져오고 있는데, ORDER BY의 조건으로
created_at을 이용하면서 Full Table Scan을 하고 있었다.이 때, 정렬은 filesort라는 과정을 통해 이루어지는데, 검색 이후 별도의 정렬 작업이 추가되므로 비효율적이다.
- 해결책
SELECT * FROM board ORDER BY id DESCorder by
id 로 수정하여 인덱스를 타게 해주는 것으로 개선하였다.- 결과
Query cost 상으로 큰 이득은 없었지만, filesort 과정이 사라지고 Index Scan이 가능해졌다.
사례4: 이메일 중복 검색 기능 개선
- 문제상황
SELECT count(*) FROM member WHERE email = ?회원 가입 시, 관리자 페이지 회원관리 기능에서 이메일 정보 중복을 검사하는 기능이 굉장히 느렸다.
특히 관리자 페이지에서는 이러한 코드가 회원 관련된 거의 매 기능마다 사용되고 있는 상황이었다.
- 해결책
회원 테이블에서 email을 인덱스로 추가해주었다. email이 인덱스로 쓰여질 만큼 중요한 컬럼인지는 고민이 있었다. 그러나 회원가입의 속도 개선도 중요하고, 관리자 서비스에서 정말 많이 이용되고 있었기 때문에 걷어 내기는 어려워서 기존의 불필요한 인덱스들을 여러개 지우고 email 인덱스를 추가하는 것으로 결정했다.
- 결과
Full Table Scan을 하던 쿼리가 Unique Index Scan(const)로 Query cost는 102358에서 1.2로 쉽고 빠르게 개선되었다.
초기에는 일부 관리자 기능 중 회원 정보 수정 기능에 timeout이 발생하였으나, 불필요한 인덱스 정리 이후에는 문제가 발생하지 않았다.
서비스가 더욱 커진다면 장기적으로는 큐를 이용하거나, 이메일 관련 테이블을 분리해주는 것으로 개선할 수 있을 것 같다.
마치면서
인덱스를 활용하여 매우 간단하게 병목 현상이 발생했던 몇몇 쿼리들을 개선하였다. 쿼리 개선 후 RDS t3.2xlarge db의 수명이 좀 더 연장되었다. 시간이 지날 수록 데이터가 많아지면서 사이즈 업에 대한 필요성이 느껴졌는데, 잠깐은 버틸 수 있게 되었다.
2편에서는 가장 사용 빈도가 높고, 가장 부하가 컸던 검색 쿼리를 개선하는 내용을 다룰 것이다.