[Mysql] 실행계획 (Explain)에 따른 쿼리튜닝 방법

 

Explain

Mysql에서 데이터를 조회해오는 시점에, 즉 내가 작성한 SQL문을 실행시키는 시점에

MySQL 내부에서 해당 쿼리문에 대한 예측값을 토대로 데이터를 어떤 경로로, 어떻게 불러오는 지에 대한 계획이다.

 

 

효율적인 데이터 탐색을 위해서는 내가 작성한 SQL문을 Explain을 통해 

내가 의도한 대로 실행되는지, 어디서 리소스를 많이 먹고 있는지에 대한 상세 로그를 이해하고 있어야 한다고 생각한다.

 

사용법은 다음과 같은 구문처럼, SQL문 제일 앞단에 Explain을 붙여주면 된다.

 

 

Explain SELECT *
	FROM user
        WHERE USER_NO > 20000
        AND DATE_FORMAT(CREATE_DATE,'%Y%m%d') > '20221130'
        AND DATE_FORMAT(CREATE_DATE,'%Y%m%d') < '20230101'

결과물

 

 

Explain의 각 항목 설명

 

다음 항목들은, 위 결과에서 볼 수 있듯이, 각 항목의 설명들인데 

쿼리 튜닝 시에, 참고한 항목들을 설명으로 풀어봄

---

select_type

       SIMPLE  |  위 예제 처럼, 기본 SELECT문의 타입
PRIMARY | SELECT 문이 여러 개 존재할 때, 첫 번째 SELECT문
UNION | UNION, UNION ALL로 SELECT 쿼리를 합침
DERIVED | FROM 절 SUBQUERY
SUBQUERY | SELECT, WHERE 절 SUBQUERY

 

table

테이블 명 출력

 

partitions

파티션 프루닝 (pruning) 
( WHERE 절에 사용된 조회 조건에 부합하는 파티션만 추출하는 기능)
을 사용했을 때, SQL문의 결과에 따라 파티션 정보를 보여줌

 

type (성능 좋은 순서대로 정리)

system | 테이블에 데이터가 0개 또는 1개만 존재 하는 경우 

const | (기본키 또는 인덱스 값으로) SQL문에서 데이터가 1건만 조회되는 경우 

eq_ref | JOIN 했을 때, 드라이빙 테이블에 접근하여 1건의 데이터를 조회하는 경우
( 조인 시, 성능이 가장 좋음 )

ref | JOIN 했을 때, 드라이빙 테이블의 데이터 접근이 2개 이상 조회하는 경우

indx | 인덱스 풀 스캔으로 인덱스 블록을 풀스캔한다.
all | 테이블 풀 스캔으로 모든 데이터를 풀스캔하는데, 전체 데이터를 다 가져올 필요 없이 소량의 데이터만 가져오는 경우, 인덱스 풀 스캔보다 성능면에서 더 좋을 수 있다.

 

 

rows

실행한 SQL문 내의 테이블에서 가져온 row 수

 

 

extra

Using where | WHERE 절로 필터링 된 경우에 볼 수 있는 문구

distinct | 중복이 제거된 경우 

Using index | 생성한 index만으로 SQL문을 처리한 경우에 볼 수 있음
해당 문구가 조회되면, 커버링 인덱스가 사용되고 있다라고 봄




커버링 인덱스란 ? 
SQL문에서 사용하는 구문 (SELECT, WHERE, GROUP BY )등에서 사용되는 모든 컬럼이 
Index로 선언되어 있고, 해당 인덱스를 이용해서 SQL문을 효율적으로 처리해주고 있다는 뜻

Using temporary | 임시 테이블을 생성한다.
해당 문구가 보이면, SQL문의 해당 테이블을 튜닝 시킬 수 있다.
임시 테이블을 생성할 때 소모되는 메모리나 메모리 영역을 초과해서 생성하는 경우에
성능이 떨어질 수 있기 때문

---

인덱스 추가 구문

CREATE INDEX [인덱스 이름] ON [테이블 이름] ( [컬럼 이름] )

 

위의 실행계획은 쿼리튜닝에 있어서 필수적이고, 또 기본적인 사항들이다

Explain을 통해 실행 계획을 확인한 이후에 SQL문의 문제가 되는 부분을 하나씩 처리해보자