MySQL] MySQL 퍼포먼스 최적화 (쿼리 성능 최적화)

2. 쿼리 성능 진단은 최적화의 기초

 

SQL을 실행하도록 DB에 명령을 내리면 DB는 SQL 파싱, 옵티마이징을 거친 후 데이터를 찾는다.

쿼리 실행 계획

실행할 쿼리 앞에 EXPLAIN [EXTENDED]을 붙여준다.
DB가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 보여준다.
성능 최적화가 가능하다.

EXTENDED는 실제 DB에서 처리되는 최종 SQL 형태를 보여주는 명령어다.
'EXPLAIN EXTENDED'로 출력하면, warning이 하나 떠 있다. 'SHOW WARNINGS\G'를 질의하면 DB가 내부적으로 이해한 최종 형태의 쿼리가 출력된다.

구분	설명
ID	SELECT 아이디로 SELECT를 구분하는 번호
SELECT_TYPE	SELECT에 대한 타입 EX) SIMPLE - UNION이나 서브쿼리가 없는 단순 SELECT PRIMARY - 서브쿼리가 있을 때 가장 바깥에 있는 SELECT DERIVED - FROM절 안의 서브쿼리 DEPENDENT SUBQUERY - 외부 쿼리와 상호 연관된 서브쿼리다.
TABLE	참조하는 테이블
TYPE	조인 혹은 조회 타입 EX) system - 테이블에 단 한 개의 데이터만 있는 경우 const - SELECT에서 PK 혹은 UK를 상수로 조회하는 경우로, 많아야 한 건의 데이터만 있음 eq_ref - 조인할 때 PK 혹은 UK로 매칭하는 경우 ref - 조인할 때 PK 혹은 UK가 아닌 Key로 매칭하는 경우 ref_or_null - ref와 같지만 NULL이 추가되어 검색되는 경우 index_merge - 두 개의 인덱스가 병합되어 검색이 이루어지는 경우 unique_subquery - 다음과 같이 IN절 안의 서브쿼리에서 PK가 오는 특수한 경우 index_subquery - unique_subquery와 비슷하나 PK가 아닌 일반 인덱스인 경우 range - 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출. index - 인덱스 풀스캔 all - 테이블 풀스캔
POSSIBLE_KEYS	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
KEY	실제로 사용할 인덱스
KEY_LEN	실제로 사용할 인덱스의 길이
REF	KEY 안의 인덱스와 비교하는 칼럼(상수)
ROWS	쿼리 실행 시 조사하는 행 수
EXTRA	추가 정보 Using Index - 커버링 인덱스, 인덱스 자료 구조를 이용해서 데이터를 추출 Using Where - WHERE 조건으로 데이터를 추출, Type이 all과 index와 함께라면 좋지 않다. Using Filesort - 데이터 정렬이 필요한 경우 Using Temporary - 쿼리 처리 시 내부적으로 Temporary Table이 사용되는 경우

 

쿼리 프로파일링

실제 쿼리 실행 시 병목이 되는 부분을 찾아낼 수 있다.

SET PROFILING = 1;
SQL
SHOW PROFILE;

리스트 중 확인하고 싶은 쿼리 번호를 실행하면 특정 쿼리에 대한 프로파일링 정보를 확인할 수 있다.
SHOW PROFILE FOR QUERY 3;

 

📖 출처

성동찬 지음, 『MySQL 퍼포먼스 최적화』, 한빛미디어(2013)