[DB] MySQL explain의 요소

MySQL의 실행계획을 살펴보면 정확히 알 수는 없지만 대략적으로 짐작이 가능한 요소들이 존재한다.

이번에는 이러한 짐작 가능한 요소들에 대해서 확실히 알아보도록 하겠다.

확실한 실행계획에 대한 이해가 성능이 최적화된 쿼리를 작성할 수 있게 해준다.

EXPLAIN
select * from tableA; 

 위와 같은 쿼리를 실행하면

id    select_type   table         type      possible_keys                       key                   key_len       ref             rows               Extra
1       SIMPLE        tableA         ALL           (null)                              (null)                   (null)       (null)          361269                      

다음과 같은 실행계획이 출력된다.

이렇게 나타난 항목들의 상세 정보를 알아보자.

* ID

 id는 실행계획의 순서를 나타낸 것이다.  

 

* SELECT_TYPE
 
select 수행시의 select type을 표현하는 정보이다.  여러가지가 존재하며 이 중 하나의 값으로 정해진다.

SIMPLE - 단순 select, union이나 subquery를 사용하지 않는다.

PRIMARY - 가장 바깥쪽의 select

UNION - union에서 두번째 혹은 이후의 select

DEPENDENT UNION - union에서 두번째 혹은 이후의 select, outer 쿼리에 의존적

UNION RESULT - union의 결과

SUBQUERY - subquery의 첫번째 select

DEPENDENT SUBQUERY - subquery의 첫번째 select, outer 쿼리에 의존적

DERIVED - select로 추출된 테이블(from 절의 subquery)

 

* TABLE

해당행의 대상이 되는 table  

* TYPE

join의 타입 

SYSTEM - 테이블에 단 하나의 행만 존재한다. const join 타입의 특별한 경우.

CONST - 매칭되는 행이 하나만 존재하기 때문에, 옵티마이저에 의해 상수로 인식될 수 있다.
               PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값과 비교할 때 사용된다.

SELECT * FROM tbl_name WHERE primery_key=1;

EQ_REF - system과 const를 제외한 가장 좋은 조인의 타입이다. 
                PRIMARY KEY 혹은 UNIQUE NOT NULL 인덱스를 이용하며 조인을 수행하는 경우이다.

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

REF - eq_ref와 다른 점은 PRIMARY KEY 혹은 UNIQUE 인덱스가 아니라면 사용된다.
          (위와같은 키값에 기반하였으나 단일 행을 select 할 수 없는 경우)

SELECT * FROM ref_table WHERE key_column=expr;

REF_OR_NULL - ref와 유사하지만 null 값을 가지는 행에 대해서도 검색을 한다.

SELECT * FROM ref_table

  WHERE key_column=expr OR key_column IS NULL;

INDEX_MERGE - 인덱스 병합의 최적화가 사용된다.

UNIQUE_SUBQUERY - IN 서브쿼리에 대해서 ref를 대체한다.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

INDEX_SUBQUERY - IN 서브쿼리를 대체하지만, non-unique 인덱스에 대해서도 동작을 한다.

value IN (SELECT key_column FROM single_table WHERE some_expr)

RANGE - 주어진 범위에 들어 있는 행만을 추출, 행 선택은 인덱스를 사용한다.

                (=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN,IN을 사용하는 상수와 비교할 때 사용한다.)

SELECT * FROM tbl_name

  WHERE key_column BETWEEN 10 and 20; 

INDEX - ALL과 동일하지만 인덱스 트리만을 스캔한다는 점이 차이이다. 단일 인덱스의 일부분인 컬럼만을 사용할 때 사용된다.

ALL - 풀스캔을 의미한다. 아주 느린 성능.

 가장 좋은 SYSTEM 부터 가장 나쁜 ALL까지 순서대로 나열되어 있다.

* POSSIBLE_KEYS

 해당 테이블에서 데이터를 찾기 위해 MySQL이 선택한 인덱스를 가리킨다.

이 중 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을수도 있음을 의미한다.

* KEY

MySQL이 실제로 사용할 예정인 키(인덱스)를 가리킨다. 

* KEY_LEN

MySQL이 사용하기로 결정한 키의 길이를 나타낸다. 

* REF

 테이블에서 행을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 혹은 상수와 비교하는지 보여준다.

* ROWS

 쿼리를 실행하기 위해 조사해야 하는 행의 숫자

* EXTRA

 쿼리에 관한 추가적인 정보

Distinct - 매칭되는 행을 찾게되면 검색을 중단한다.

Not exists - LEFT JOIN을 수행시 매치되는 행을 찾으면 검색을 중단한다.

range checked for each record - 사용할 인덱스는 찾지 못했으나, 이전 테이블에서 찾은 컬럼으로 range 또는 index_merge 접근방식이
                                                       가능한지 검사한다. 그리 빠른 방법은 아니지만 인덱스를 전혀사용하지 않는것보다는 빠르다.

Using filesort - 정렬은 조인타입과 정렬 키 및 where 구문과 매치가 되는 모든 행에 대한 행 포인터를 사용해서 모든 행에 걸쳐 진행된다.
                         후에 그 키는 저장이되고 행은 저장 순서에 따라서 추출된다.

Using index - 인덱스 트리의 정보만 가지고 컬럼정보를 추출한다.

Using temporary - 쿼리를 해석하기 위해, 결과를 저장할 임시 테이블을 생성한다.(group by 혹은 order by 사용시)

Using where - where 구문은 다음 테이블에 대한 행 매치 또는 클라이언트에 보내지는 행을 제한하기 위해 사용된다.

Using sort_union, Using union, Using intersect - 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합되는지 표현한다.

Using index for group-by - 실제 테이블을 추가적으로 검색하지 않고서도, group by 또는 distinct 쿼리의 모든 컬럼을
                                             추출하기위해 사용될 수 있는 인덱스를 찾았음을 가리킨다.

아직 전부이해가 되는 것은 아니지만 반복적인 쿼리작성을 통해 익혀야겠다.


 

* 참고자료 : MySQL 매뉴얼