mysql Explain 설명

Explain 정보보는법

인덱스가 적절히 사용되고 있는지 검토
나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력

EXPLAIN 의 각 행 설명

A.         id :

     쿼리 처리 순서

B.         select_type:

     SELECT의 타입(종류)

    - SIMPLE:

    단순 SELECT (UNION 이나 SUB-QUERY가 없는 SELECT 문)

    - PRIMARY: 

    SUB-QUERY를 사용할 경우 SUB-QUERY 의 외부에 있는 쿼리(첫번째 쿼리)

    UNION 을 사용할 경우 UNION의 첫번째 SELECT 쿼리.

    - UNION:

    UNION 쿼리에서 PRIMARY를 제외한 나머지 SELECT

    - DEPENDENT UNION:

    UNION 과 동일하나, 외부쿼리에 의존적임 (값을 공급 받음)

    - UNION RESULT

    UNION 쿼리의 결과물

    - SUBQUERY:

    SUB-QUERY 또는 SUB-QUERY를 구성하는 여러 쿼리 중 첫번째 SELECT문

    - DEPENDENT SUBQUERY:

    SUB-QUERY와 동일하나, 외곽쿼리에 의존적임 (값을 공급 받음)

    - DERIVED:

    SELECT 로 추출된 테이블 (FROM 절 에서의 서브쿼리 또는 INLINE VIEW)

    - UNCACHEABLE SUBQUERY:

    SUB-QUERY와 동일하지만, 공급되는 모든 값에 대해 SUB-QUERY를 재처리. 외부쿼리에서 공급되는 값이 동일하더라도 CACHE된 결과를 사용할수 없음.

    - UNCACHEABLE UNION:

    UNION 과 동일하지만, 공급되는 모든 값에 대하여 UNION 쿼리를 재처리.

C.         table:

    table명 혹은 Alias

D.         type:

     data access 타입, 우수한 순서대로... 뒤로갈수록 나쁜 조인형태

    - system

    테이블에 오직 하나의 row만 있어 테이블에매칭되는 row가 무조건 1건인 경우, Const 타입의 특별한 케이스

    - const

    매칭되는 row가 오직 1건인 경우

    가장 빠른 경우이며, 옵티마이저가 row을 찾기 위해 Unique/Primary Key 사용

    각 컬럼 값은 나머지 연산에서 상수로 간주, 처음한번만 읽어 들이면 되므로 매우 빠름

    - eq_ref

    이전 테이블에서 공급받은 값으로 조인 처리시 단 하나의row만이 조인되는 테이블에 존재하는 경우

    조인 타입 중 거의 최상의 경우이며, 조인되는테이블의 Unique/Primary Key를 사용 (1:1 관계)

    - ref

    이전 테이블에서 공급받은 값으로 조인 처리시 하나 이상의row가 조인되는 테이블에 존재하는 경우

    조인을 처리할 때 Unique/Primary Key를 100% 사용하지 못하거나 Non-Unique 인덱스를 사용 (1:n 관계)

    단일 쿼리인 경우 WHERE 조건 처리를 위해 Non-Unique 인덱스를 사용

    인덱스를 사용하지만 제공되는 값에 매칭되는 row가많지 않은 경우 나쁘지 않은 타입임

    - ref_or_null

     ref 와 같지만 IS NULL 최적화가 수행 됨

     - fulltext

     Full Text 인덱스를 사용하여 데이터 Access (Only MyISAM)

     - index_merge

     동일한테이블에서 두 개 이상의 인덱스가 동시에 사용되는 경우 (fulltext 인덱스 제외)

     인덱스병합 최적화가 적용되는 조인 타입

     이경우, key 컬럼은 사용된 인덱스의 리스트를 나타낸다.

     - unique_subquery

     IN sub-query 에서 Unique 한 결과가 만들어 지는 경우

     index lookup function이 사용됨 (서브쿼리 최적화)

     - index_subquery

     unique_subquery와 유사

     IN sub-query 값이 Unique 하지 않음

     - range

     주어진범위 내의 row를 Scan하며 Access 해야 할 데이터의 범위에 크게 영향을 받음

     키컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL,<=>, BETWEEN 또는 IN 연산에 사용될 때 적용됨

     - index

     전체인덱스 Block을 스캔 하는 경우, 인덱스 Block을 스캔 한다는 것을 제외하면 Full Table Sacn(ALL)과같음

    일반적인 경우 인덱스가 테이블보다 사이즈가 작기 때문에,ALL보다는 빠를 가능성이 높음

     MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할 때 이 조인타입을 적용함

     - ALL

     젂체데이터 Block을 스캔 하는 경우 (Full Table Scan)

     이전테이블과의 조인을 위해 풀스캔

     (조인에 쓰인) 첫번 째 테이블이 고정이 아니라면 비효율적

     대부분의경우에 아주 느린 성능을 보임

E.         possible_keys:

    MySQL 옵티마이저가 쿼리처리를 위해 고려한 인텍스 후보

    possible_keys 에 나타난 인덱스들이결과에 나타난 테이블 순서에서 실제 사용할 수 없을 수도 있음

F.          key:

    MySQL 옵티마이저가 실제사용한 key(index)

G.         key_len:

    MySQL 이 사용한 인덱스의 길이, key 컬럼 값이 NULL 이면 이 값도 NULL

    key_len 값으로 MySQL 이 실제 복수 컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음

H.         ref:

     행을 추출하는데 키와 함께 사용된 컬럼이나 상수 값

I.          rows:

     쿼리를 수행하기 위해 검색해야 할 Row의 개수 (옵티마이저연산에 따른 추정치)

J.          Extra:

    MySQL 옵티마이저가 쿼리를 해석한 추가적인 정보를나타냄

    - const row not found

    대상 테이블이 empty 테이블인경우

    - Distinct

     Distinct 쿼리를 수행하는 경우, 이미 처리한 값과 동일한 값을가짂 Row는 처리하지 않음

     - Full scan on NULL key

     Index lookup function이 사용되는 “IN (Subquery)” 쿼리에서 Outer Query에서 NULL 값이 공급되는 경우 발생

    Index lookup 실패로 Full Table Scan이 발생할 수 있음

    - Impossible HAVING

     HAVING 절이 항상 False 인 경우, SELECT 처리하지 않음

     - Impossible WHERE

     WHERE 절이 항상 False 인 경우, SELECT 처리하지 않음

     - Impossible WHERE noticedafter reading const tables

     const/system 타입의 테이블을 읽은 후 WHERE 조건이 항상 False인 것을 확인

     - No tables used

     FROM 절에 테이블이 명시되지 않음, 혹은 FROM DUAL 구문을 사용

     - Not exists

     LEFT JOIN 형태의 Anti Join 쿼리를 Not Exists 형태로 최적화 하는 경우

     (조건에 맞는 결과를 찾으면 추가 Join 처리하지 않음)

     - range checked for eachrecord (index map: N)

     조인처리시 적절한 인덱스가 없는 상황에서, 선행 테이블에서 공급되는 값에 따라 인덱스 사용을 검토할 수있는 경우

     공급되는각각의 Row에 대해 range / index merge를검토

     N 값은 어떤 인덱스가 검토되었는지 표시 (0x11011, 19  1, 4,5번 인덱스가 검토)

     - Select tables optimizedaway

     쿼리가 Aggregate(예: MAX) 함수만 포함하고 있는 경우

     옵티마이저는인덱스 Lookup 후 1개의 결과만 리턴

     - unique row not found

     const row not found와 거의 유사

     SELECT … FROM TABLE 쿼리에서 만족하는 row를 찾지못한 경우

     - Using filesort

     MySQL 이 정렬을 위해 추가적인 과정을 필요로 함(물리적인 정렬작업 수행) 

     - Using index

     실제데이터 Block을 읽지 않고 인덱스 Block 만으로 결과를생성할 수 있는 경우 (Covering Index)

     쿼리에서단일 인덱스된 컬럼들만을 사용하는 경우

     - Using index for group-by

     Using index와 유사하며, 데이터 Block을 읽지 않고 인덱스 Block 만으로 Group-By / Distinct 처리 (Covering Index)

     Loose Index Scan 처리

     - Using join buffer

     Join 처리시 Join Buffer가 사용되었음을 의미

     Join Buffer는 Join 처리를 위한 인덱스가 없을 때 사용됨

     - Using sort_union(...),Using union(...), Using intersect(...)

     Index Merge 수행, 두 개 이상의 인덱스를 동시에 사용하여처리

     - Using temporary

     쿼리처리를 위해 임시 테이블을 생성

     group by, order by 컬럼이 각각 컬럼을 사용할 때 발생.group by 처리에 인덱스를 사용하지 못하는 경우 발생

     - Using where
WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우

l extra 정리: 

쿼리를 가능한 한 빠르게 하려면, Extra 값의Using filesort 나 Using temporary 에 주의해야 함

EXPLAIN 의 출력 내용 중 rows 컬럼값들을 곱해봄으로써 얼마나 효과적인 join 을 실행하고 있는지 알 수 있다

출처 : http://laydios.egloos.com/1542611