MySQL 쿼리 성능 진단

SQL 작성 해서 DB에 명령을 내리면 DB는 내부적으로 SQL 파싱(SQL 문법체크 미 DB를 실행 할 수 있는 형태로 변경) 그리고 옵티마이징을 거친후 데이터를 찾는다. 실제로는 SQL로 모든 조작이 일어나는게 아니라 DB 내부적으로도 빠르게 찾기위해 여러 방법을 사용한다.

 

쿼리 실행 계획

DB가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과셋으로 보여주는 것이다.

(기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화에 대한 전박적인 업무를 처리)

EXPLAIN [EXTENDED]
SELECT .. FROM .. WHERE ..

이는 실제 DB에서 처리되는 최종 SQL 형태를 보여주기 위한 명령어 인데. warning이 하나 떠있다.

 

이때 SHOW WARNINGS\G 사용하면 쿼리가 출력되는데. 이것이 바로 DB가 내부적으로 이해한 최종적인 형태입니다.

 

EXPLAIN EXTENDED는 실해한 쿼리가 실제 DB 내부적으로 어떻게 처리되는지를 알고자 할 때 유용한 명령어이다.

 

- 동일한 ID 내부에서 내려가다가 Table의 <derived2>를 만나면 해당하는 ID로 이동하여 실행하게 된다.

 

구분	설명
ID	Select 아이디로 Select를 구분하는 번호
Select_type	Select에 대한 타입
Table	참조하는 테이블
Type	조인 혹은 조회 타입
Possible_keys	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
Key	실제로 사용할 인덱스
Key_len	실제로 사용할 인덱스의 길이
Ref	Key 안의 인덱스와 비교하는 칼럼(상수)
Rows	쿼리 실행 시 조사하는 행수
Extra	추가 정보

 

Select_type의 의미

구분	설명
SIMPLE	UNION이나 서브쿼리가 없는 단순 SELECT를 의미한다.
PRIMARY	서브쿼리가 있을 때 가장 바깥쪽에 있는 SELECT다. ex) SELECT * FROM (         SELECT * FROM tab01       )sub
DERIVED	FROM절 안의 서브쿼리다. ex) SELECT * FROM (        SELECT * FROM tab01        )sub
DEPENDENT SUBQUERY	외부 쿼리와 상호 연관된 서브쿼리다. ex) SELECT * FROM tab01 t1       WHERE EXISTS (             SELECT 1 FROM tab02 t2             WHERE t2.t1_id = t1.id       )

 

Type의 의미

구분	설명
system	테이블에 단 한 갠의 데이터만있는 경우
const	SELECT에서 Primary Key 혹은 Unique Key를 상수로 조회하는 경우로, 많아야 한 건의 데이터만 있음
eq_ref	조인을 할 때 Primary Key 혹은 Unique Key로 매칭하는 경우
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 key로 매칭하는 경우
ref_or_null	ref와 같지만 NULL이 추가되어 검색되는 경우
index_merge	두 개의 인덱스가 병합되어 검색이 이루어지는 경우
unique_subquery	다음과 같이 IN절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 ex) SELECT * FROM tab01       WHERE col01 IN (             SELECT Primary_Key FROM tab01       )
index_subquery	unique_subquery와 비슷하나 Primary Key가 아닌 일반 인덱스인 경우 ex) SELECT * FROM tab01       WHERE col01 IN (             SELECT Key01 FROM tab01       )
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT에서는 나쁘지 않다.
index	인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 한다.
all	테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 한다.

성능은 위에서 아래로 내려올수록 좋지않습니다. 단, range 에서 조회 데이터 건수가 많지 않다면 성능이 나쁘다고 볼수는 없음.

Extra의 의미

MySQL의 쿼리 실행에 대한 추가적인 정보를 보여준다.

구분	설명
Using Index	커버링 인덱스라고도 하며 인덱스 자료 구조를 이용해서 데이터를 추출한다.
Using Where	Where 조건으로 데이터를 추출한다. Type이 ALL 혹은 Index
Using Filesort	데이터 정렬이 필요한 경우로, 메모리 혹은 디스크상에서의 정렬을 모두 포함한다. 결과 데이터가 많은 경우 성능에 직접적인 영향을 미친다.
Using Temporay	쿼리 처리 시 내부적으로 Temporay Table 사용되는 경우를 의미한다.

TEMPORARY TABLE은 하나의 세션 동안이나 트랜젝션(COMMIT 하기 전) 동안 임시적으로 데이터를 보관하기 위해 제공된다.

출처: https://recollectionis.tistory.com/157 [커피와 개발자]

 

일반적으로는 데이터가 많은 경우 Using Filesort와 Using TEmporay 상태는 좋지 않다.

 

쿼리 프로파일링

쿼리를 처리할 때 DB 내부적으로는 Open Table/Close Table, Optimizing, Sending Data 등을 비롯해 여러 단계를 거치며 최종적으로 데이터를 찾아낸다. (이 방법으로 어느 부분이 느린지에 대한 병목이 있는지 확인 할 수 있따.)

 

SET PROFILING = 1;
SELECT * FROM tab;
SHOW PROFILE;

프로파일링 관련 세션 변수를 활성화한 후 쿼리를 실행하면 프로파일된 정보를 확인할 수 있다.

 

 

특정 쿼리에 대한 프로파일링 정보를 확인할 수 있다.

 

SHOW PROFILES;

SHOW PROFILE FOR QUERY 2;

이러한 현상을 확인하면서 sending data부분이 오래 걸린다면 (스토리지 엔진에서 DB 엔진으로 쿼리를 보내느 단계) 이것을 튜닝하면된다.