MySQL EXPLAIN 실행 계획 보기 (쿼리 최적화 하기)

사용이유

MySQL 쿼리를 실행할 때 최적화 계획을 세운다.

이러한 계획은 실행계획이라고 하는데 Explain 키워드를 사용해 실행계획에 대한 정보를 확인할 수 있다. 

실행계획을 통해 문제가 발생하는 쿼리문을 이해하고 어떻게 최적화할지에 대한 인사이트를 제공하는 도구가 될 수 있다. 

 

 

 

사용법

select문 앞에 explain 을 붙여서 사용한다. 

EXPLAIN SELECT * FROM table_name
EXPLAIN SELECT * FROM table_name WHERE table_no = 1

 

explain을 사용해 쿼리문을 실행하면 아래 이미지와 같은 컬럼을 확인할 수 있다. 

 

 

컬럼 설명

• id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자이다. 만약 하나의 select 문에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여된다. 하지만 쿼리 문장이 서로 다른 select 문(서브쿼리 구성 등..)으로 구성되어 있으면 각 레코드의 id 컬럼이 각기 다른 값을 부여받게 된다.

 

• select_type : select 문의 유형을 말한다. 각 유형은 아래와 같다
• SIMPLE : 서브쿼리나 union이 없는 가장 단순한 select문을 말한다
• PRIMARY : 가장 바깥에 있는 select 문을 말한다
• DERIVED : from 문 안에있는 서브쿼리의 select 문이다.
• SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리이다.
• DEPENDENT SUBQUERY : 기본적으로 서브쿼리와 같은 유형이며, 가장 바깥의 select문에 의존성을 가진 서브쿼리의 select문이다.
• UNCACHEABLE SUBQUERY : 쿼리의 from 절 이외의 부분에서 사용되는 서브 쿼리는 가능하면 MySQL 옵티마이저가 캐싱하여 최대한 재사용 될 수 있게 유도한다.
• UNION : union 문의 두번째 select 문을 말한다
• DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문을 말한다

 

• table : 참조되는 테이블을 말한다

 

• type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목이다. 이는 매우 중요한데, 이유는 이 타입을 분석함으로써 어떤 인덱스가 사용되고 사용되지 않았는지를 알 수 있고, 이를통해 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문이다. 각 유형은 아래와 같다
• system : 0개 또는 하나의 row를 가진 테이블이다.
• const : 테이블의 레코드 수에 관계없이 Primary key나 Unique key 컬럼을 이용하는 where절 조건을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식이다.
• eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우이다. const 방식 다음으로 빠른 방법이다.
• ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
• index_merge : 2개 이상의 인덱스를 이용하여 각각의 검색 결과를 만들어낸 후에 그 결과를 병합하는 처리 방식이다.
• unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
• index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
• range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
• all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.

 

• possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.  

 

• key : 실제적으로 쿼리 실행에 사용된 key의 목록이다. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.

 

• ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.

 

• rows : 결과 산출에 있어서 접근되는 record의 숫자이다. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목이다.

 

• Extra : 실행계획에 있어서 부가적인 정보를 보여준다.
  • distinct : 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음.
  • not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다.
  • range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미.
  • using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
  • using index : select 할때 인덱스 파일만 사용
  • using temporary : 임시 테이블을 사용한다. order by 나 group by 할때 주로 사용
  • using where : 조건을 사용한다는 의미.

 

 

참고

https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information