MySQL 쿼리 튜닝 경험기 (1/2)

개요

문제 배경

• 서버는 여러 개로 로드밸런싱이 되고 있었으나, DB는 1개로 DB 부하가 큰 상황이었다.

• 데이터베이스를 scale up 해주는 방법도 있으나, 데이터베이스는 클라우드 중 비용이 매우 비싼 편이다. 돈으로 때우면 때울 수는 있지만 임시방편이다.

• 서비스가 갑자기 터지면 대부분은 DB 관련 문제였다.

• 점진적으로 적용 가능한 개선 방안을 찾다가, 시간과 개발 비용을 고려하여 쿼리튜닝을 적용해보기로 하였다.

쿼리 튜닝 대상의 우선순위를 정하자

• 부하가 클 수록
부하가 큰 쿼리는 DB가 죽는 주요 원인이다.

• 자주 호출될 수록
자주 호출되는 쿼리를 개선해줄 수록 사용자 경험을 개선할 수 있다.

• 변경이 용이할 수록
개발비용이 적은 튜닝이 좋다.

MySQL EXPLAIN(실행계획)을 보자

MySQL Workbench의 VISUAL EXPLAIN으로 인덱스 동작 확인하기 - LINE ENGINEERING

This post is also available in the following language. 일어 By | 2018.10.31 LINE 포인트 개발을 담당하고 있습니다. 주로 서버쪽을 맡고 있습니다. 안녕하세요. 저는 LINE 포인트 서버쪽 개발을 담당하고 있는 Ohara(@kory1202)입니다. 얼마 전 특정 테이블에서 데이터를 추출하는 코드를 작성했는데요. 함께 일하는 동료가 그 코드를 보더니 '이런 인덱스가 필요하겠다'고 조언해주었습니다.

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index/

튜닝하기 전 알아야 할 것들

B-Tree 인덱스

인덱스

• SELECT의 개선을 인덱스를 추가해주는 것으로 해결할 수 있다.

• 물론, 인덱스를 무조건 추가하는 것이 답은 아니다. 인덱스의 추가는 command(insert, update, delete)의 성능 저하를 불러일으킨다. 데이터 양이 커질수록 인덱스의 영향은 커지므로 신중하게 판단하여 결정하여야 한다.

• 인덱스의 갯수
jojoldu님의 글에 따르면, 일반적으로 3~4개 정도가 적절하다. 인덱스가 PK 외에 특별히 걸려있지 않았다면 추가해줄 수 있다. 특별히 검색에 자주 사용하게 되는 컬럼이 있다면 인덱스로 걸어줄 수 있다. (예: 주문 테이블의 상품 아이디)

테이블 스캔

• index range scan
• const(UNIQUE INDEX SCAN)
• eq_ref, ref
• range

• index full scan
• index

• full table scan
• ALL

[mysql] 인덱스 정리 및 팁

MySQL 인덱스에 관해 정리를 하였습니다. MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 싶어서 정리한 것이라 오류가 있을수도 있습니다. 인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다. insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.

https://jojoldu.tistory.com/243

✅ 쿼리 최적화 첫걸음 - 보다 빠른 쿼리를 위한 7가지 체크 리스트

별점 5.5억 개, 유저당 월평균 시청 시간 24시간 이상 등, 왓챠피디아 & 왓챠의 DB에는 방대한 데이터가 있으며, 서비스의 성장과 함께 날이 갈수록 더 많은 양의 데이터가 쌓이고 있습니다. 그러다 보니 때때로 DB에 쿼리를 날릴 때에는 쿼리의 최적화 여부에 따라 성능이 눈에 띄게 변하는 것을 자주 느낍니다. 그래서 이번 글에서는 SQL을 최적화할 수...

https://medium.com/watcha/%EC%BF%BC%EB%A6%AC-%EC%B5%9C%EC%A0%81%ED%99%94-%EC%B2%AB%EA%B1%B8%EC%9D%8C-%EB%B3%B4%EB%8B%A4-%EB%B9%A0%EB%A5%B8-%EC%BF%BC%EB%A6%AC%EB%A5%BC-%EC%9C%84%ED%95%9C-7%EA%B0%80%EC%A7%80-%EC%B2%B4%ED%81%AC-%EB%A6%AC%EC%8A%A4%ED%8A%B8-bafec9d2c073

사례

사례1: 주문 별 적립금 정보 호출 기능 개선.

• 문제 상황

SELECT * FROM points WHERE order_no = ? AND user_id = ? AND point_status = 'N'

points 테이블은 적립금 적립 내역과 함께 order_no(주문번호)를 함께 기록한다.
마이페이지에서는 order_no를 기준으로 points 테이블에서 적립금 관련 정보를 출력해주고 있었고, 해당 쿼리의 부하가 매우 큰 상황이었다.

• 해결책
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 으로 다중 칼럼 PRIMARY 키를 걸어주었다. goods_no, goods_sub 은 UNIQUE하므로, 해당하는 레코드는 1건만 저장되어 있으므로, 즉시 스캔이 가능하다.

• 결과
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을 하고 있었다.

• 해결책

SELECT * FROM board ORDER BY id DESC

order by id 로 수정하여 인덱스를 타게 해주었다.

• 결과
Query cost 상으로 큰 이득은 없었지만, 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이 발생하였으나, 불필요한 인덱스 정리 이후에는 문제가 발생하지 않았다.
서비스가 더욱 커진다면 장기적으로는 큐를 이용하거나, 이메일 관련 테이블을 분리해주는 것으로 개선할 수 있을 것 같다.

마치면서

이미지 출처