[MySQL] 실행계획(explain) 보는법 총정리

 

실행계획 쿼리수행

실행계획을 확인하고 싶은 SELECT 쿼리의 맨 앞에 'EXPLAIN'을 추가하여 쿼리를 수행한다.

EXPLAIN SELECT * FROM TABLE_NAME WHERE COLUMN1 = 1;

 

 

결과

1. id : 수행한 쿼리의 select문 순서
2. select_type : select문 유형
   • SIMPLE : 단순 select문 (union 또는 서브쿼리를 사용하지 않음)
   • PRIMARY : 가장 바깥쪽 select문
   • UNION : union문의 두번째 이후 select문
   • DEPENDENT UNION : 바깥 쿼리에 의존적인 union문의 두번째 이후 select문
   • UNION RESULT : union 결과물
   • SUBQUERY : 서브쿼리의 첫번째 select문
   • DEPENDENT SUBQUERY : SUBQUERY와 같은 유형이며 가장 바깥의 select문에 의존적
   • DERIVED : from 절의 서브쿼리
   • MATERIALIZED : 구체화된 서브쿼리
   • UNCACHEABLE SUBQUERY : 결과를 캐시할 수 없고 외부 쿼리의 각 행에 대해 재 평가해야하는 서브쿼리
   • UNCACHEABLE UNION : 캐시할 수 없는 union문의 두번째 이후 서브쿼리
3. table : 참조하고 있는 테이블 명
4. partitions : 쿼리와 일치하는 레코드가 있는 파티션
5. type : 조인 타입 (속도와 아주 밀접한 항목)
   • 최상의 유형 순 (속도 빠른 순)
     • system : 테이블에는 하나의 행만 존재(=시스템 테이블). const 조인의 특별한 형태
     • const : 하나의 매치되는 행만 존재하는 경우. 하나의 행만 있기 때문에 상수로 간주되며, 한번만 읽어들이기 때문에 무척 빠름
     • eq_ref : 조인 수행을 위해 각 테이블에서 하나의 행만이 읽혀지는 형태. system, const 타입 외에 가장 훌륭한 조인 타입
     • ref : ref 조인에서 키의 가장 왼쪽 접두사만 사용하거나 키가 PRIMARY KEY 또는 UNIQUE 인덱스 가 아닌 경우(즉, 조인이 키 값을 기반으로 단일 행을 선택할 수없는 경우)에 사용. 사용되는 키가 몇 개의 행과 만 일치하는 경우 이는 좋은 조인 유형
     • fulltext : 조인은 fulltext 인덱스를 사용하여 수행
     • ref_or_null : 이 조인 유형은 ref와 비슷하지만, MySQL이 NULL값 을 포함하는 행을 추가로 검색. 이 조인 유형 최적화는 하위 쿼리를 해결하는 데 가장 자주 사용
     • index_merge : 인덱스 병합 최적화가 적용되는 조인타입. 이 경우, key컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key명을 나타낸다.
     • unique_subquery : 오직 하나의 결과만을 반환하는 IN절이 포함된 서브쿼리
       index_subquery : unique_subquery와 비슷하지만 IN절에서 여러개의 결과를 반환함
     • range : 인덱스를 사용하여 주어진 범위 내의 행들만 추출됨. key 컬럼은 사용된 인덱스를 나타내고 key_len은 사용된 가장 긴 key부분을 나타냄. ref 컬럼은 이 타입의 조인에서 NULL. range 타입은 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE 또는 IN 연산자를 사용하여 키 컬럼을 상수와 비교할 때 사용
     • index : 인덱스가 스캔되는걸 제외하면 ALL과 동일. 보통 인덱스 파일이 데이터 파일보다 작기 때문에 ALL보다 빠름
     • ALL : 이전 테이블과의 조인을 위해 풀스캔. 만약 조인에 쓰인 첫 테이블이 고정이 아니라면 비효율적. 대부분의 경우 아주 느리며, 보통 상수값이나 상수인 컬럼값으로 row를 추출하도록 인덱스를 추가하여 ALL 타입을 피할 수 있다.
6. possible_keys : 테이블 검색에 사용할 수 있는 인덱스
7. key : 테이블 검색에 실제로 사용된 인덱스
8. key_len : 사용된 인덱스 길이 (key 컬럼 값이 NULL이면 이 컬럼의 값도 NULL)
9. ref : 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값
10. rows : 결과 산출에 있어서 접근하는 record의 추정치 (조인문이나 서브쿼리 최적화에 있어서 중요한 항목)
11. filtered : 테이블 조건으로 필터링된 행의 백분율 (rows는 검사된 행 수, rows x filtered / 100 = 이전 테이블과 조인 될 행 수)
12. Extra : 쿼리를 해석하는 방법에 대한 추가 정보
    • Child of 'table' pushed join@1 : 이 테이블은 tableNDB 커널로 푸시다운될 수 있는 조인에서 자식으로 참조됨. 푸시다운 조인이 활성화된 경우 NDB 클러스터에만 적용됨
    • const row not found : 테이블이 비어있음
    • Deleting all rows : DELETE의 경우 일부 스토리지 엔진(예: MyISAM)은 핸들러를 지원
    • Distinct : MySQL은 고유한 값을 찾고 있으므로 일치하는 첫 번째 행을 찾은 후 추가 행을 검색하지 않음
    • FirstMatch : semijoin FirstMatch 조인 단축 전략은 tbl_name에 사용
    • Full scan on NULL key : 옵티마이저가 인덱스 조회 액세스 방법을 사용할 수 없을 때 폴백 전략으로 하위 쿼리 최적화에 대해 발생
    • Impossible HAVING : 이 HAVING절은 항상 false이며 행을 선택할 수 없음
    • Impossible WHERE : 이 WHERE절은 항상 false이며 행을 선택할 수 없음
    • Impossible WHERE noticed after reading const tables : WHERE절에 대한 쿼리의 결과가 없어 실행 계획을 볼 수 없는 상태
    • LooseScan : semijoin LooseScan 전략 사용
    • No matching min/max row : SELECT MIN(...) FROM ... WHERE 조건과 같은 쿼리의 경우 조건을 만족하는 행이 없음
    • no matching row in const table : 조인이 있는 쿼리의 경우 빈 테이블이 있거나 고유한 인덱스 조건을 충족하는 행이 없는 테이블이 있음
    • No matching rows after partition pruning : DELETE또는 의 경우 UPDATE옵티마이저가 파티션 정리 후 삭제하거나 업데이트할 항목을 찾지 못함 (Impossible WHERE for SELECT문과 의미가 비슷)
    • No tables used : 쿼리에 FROM 절이 없거나 FROM DUAL절이 있음
    • Not exists : LEFT JOIN 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않음
    • Range checked for each record : 최적의 인덱스를 찾지 못하여 차선의 인덱스를 사용함
    • Scanned N databases : 테이블에 대한 쿼리를 처리할 때 서버가 수행하는 디렉터리 스캔 수
    • Select tables optimized away : 옵티마이저는 1) 최대 하나의 행이 반환되어야 하고 2) 이 행을 생성하려면 결정론적 행 집합을 읽어야 한다고 결정. 읽을 행을 최적화 단계에서 읽을 수 있는 경우(예: 인덱스 행 읽기) 쿼리 실행 중에 테이블을 읽을 필요가 없음
    • Skip_open_table, Open_frm_only, Open_full_table : 쿼리에 적용되는 파일 열기 최적화를 나타냄
    • Start temporary, End temporary : semijoin Duplicate Weedout 전략에 대한 임시 테이블 사용을 나타냄
    • unique row not found : 인덱스 또는 테이블에 대한 조건을 충족하는 행이 없음
    • Using filesort : MySQL이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
    • Using index : 실제 행을 읽기 위해 추가 검색을 수행할 필요 없이 인덱스 트리의 정보만을 사용
    • Using index condition : 전체 테이블 행을 읽을지 여부를 결정하기 위해 먼저 인덱스 튜플을 테스트하여 테이블을 읽음
    • Using index for group-by : Using index와 유사하게 MySQL 이 실제 테이블에 대한 추가 디스크 액세스 없이 a 또는 쿼리의 모든 열을 검색하는 데 사용할 수 있는 인덱스를 찾았음을 나타냄
    • Using join buffer : 이전 조인의 테이블을 조인 버퍼로 부분적으로 읽은 다음 해당 행을 버퍼에서 사용하여 현재 테이블과의 조인을 수행
    • Using MRR : 다중 범위 읽기 최적화 전략을 사용
    • Using sort_union(...), Using union(...), Using intersect(...) : index_merge 조인 유형 에 대해 인덱스 스캔이 병합되는 방법을 보여주는 특정 알고리즘을 나타냄
    • Using temporary : 임시 테이블을 사용. order by 나 group by 할때 주로 사용
    • Using where : 조건을 사용한다는 의미
    • Using where with pushed condition : 이 항목은 NDB 테이블에만 적용. NDB 클러스터가 조건 푸시다운 최적화를 사용하여 비인덱싱된 열과 상수 간의 직접 비교 효율성을 개선하고 있음을 의미

 

 

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