[MySQL] 쿼리 성능 개선을 하며

얼마전 운영중인 서비스에 특정 쿼리 조회 속도가 5초 이상을 넘어가는 것을 보고 DB에 데이터가 그렇게 많이 쌓였나.. 하는 생각에 들여다 보여 문제를 해결했던 내용을 기록해본다..(DB야 미안해..)

---

MySQL같은 RDBMS는 튜닝을 위해서는 쿼리와 스키마 최적화가 가장 중요하다.

스키마는 설계가 끝나고 적용되면 변경이 어렵지만 쿼리 수정은 비교적 변경하기 쉽기 때문에 성능을 개선하기 위해선 보통 쿼리 튜닝을 진행한다.

 

먼저 쿼리 성능을 향상시키기 위해서 쿼리 튜닝을 검토해야 하는데 이때 주로 쓰는 명령어가 EXPLAIN 실행계획 명령어 이다.

 

EXPLAIN 명령어는 실행한 SQL이 데이베이스에서 어떻게 처리되는지 보여준다. 

다시 말해 EXPLAIN은 데이터베이스가 데이터를 찾아가는 과정을 알아보기 쉽게 테이블 형태로 보여주는 것이다. 이 정보를 활용해 기존 쿼리를 튜닝할 수 있을 뿐만 아니라, 성능분석, 익덱스 전략 수집 등 여러가지 성능 최적화에 활용할 수 있다.

 

사용 예시

EXPLAIN
SELECT
	*
FROM 테이블명
WHERE 조건

결과

 

위에 조회된 데이터의 각 컬럼을 살펴보면 아래와 같다

 

• select_type: select 문 유형
  • SIMPLE: 단순 select(union, 서브쿼리 사용 x)
  • PRIMARY: Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리
  • UNION: UNION쿼리에서 PRIMARY를 제외한 나머지 SELECT
  • DEPENDENT_UNION: UNION과 동일하나 외부쿼리에 의존하여 값을 공급 받음
  • UNION_RESULT: UNION 쿼리 결과
  • SUBQUERY: 서브쿼리 또는 서브쿼리를 구성하는 여러 쿼리
  • DEPENDENT SUBQUERY: 서브쿼리 첫번째 SELECT
  • DERIVED: SELECT로 추출된 테이블(FROM절에서의 서브쿼리 또는 Inline View)
• table: 참조하고 있는 테이블
• type: 조인 형태
  • system: 1개 이하 row를 가진 테이블
  • const: 테이블 조건을 만족하는 row가 1개
  • eq_ref: primary key나 unique key 로 검색
  • ref: 인덱스로 지정된 컬럼끼리 비교 연산자(=, <=)와 같은 조건문 검색
  • unique_subquery: 오직 하나의 결과만 반환하는 IN 이 포함된 sub query
  • index_subquery: unique subquery와 성격은 동일, 하지만 여러개 결과 반환
  • range: 특정한 범위의 키를 매친할때(BETWEEN IN)
  • all: 모든 row 스캔
• possible_keys: 테이블에서 row를 매핑시키기 위한 key 목록
• key: 실제적으로 쿼치 실행에 사용된 key
• ref: key 컬럼에 지정된 인덱스와 비교되는 컬럼
• rows: 결과 산출에 접근되는 record 수(예상치이기 때문에 정확하지 않음)
• extra: 실행계획에 있어 SQL이 해석되는 부가적인 정보
  • using index: 커버링 인덱스라고 하며 인덱스 자료 구조를 이용해 데이터 추출
  • using where: where 조건으로 데이터를 추출 (* type이 ALL 또는 Index타입과 함께 표현되면 성능이 좋지 않다)
  • using filesort: 데이터 정령이 필요할 때 메모리 또는 디스크상에서 정렬을 모두 포함(결과 데이터가 많은 경우 성능에 직접적인 영향을 줌)
  • using temporary: 쿼리 처리 시 내부적으로 temporary table이 사용되는 경우

---

위 내용들만 알고 있어도 어떻게 쿼리 성능을 개선해 나가고, 어떤 컬럼에 커버링 인덱스를 설정하고, 어떤 조인 테이블이 성능을 낮추는지 파악하기 쉽다.

 

필자는 실제로 편의상 만들어둔 View를 너무 남용하여 Inline View가 2~3개가 넘어가고 커버링 인덱스가 잘못 설정되었거나 엉뚱한 컬럼에 설정되어 있어서 Inline view를 raw 쿼리로 풀어쓰고 인덱싱을 정리하다보니 5초이상 걸리던 쿼리를 0.1초로 개선했다.