Home MySQL EXPLAIN Output Format
Post
Cancel
Preview Image

MySQL EXPLAIN Output Format

개요

  • 업무를 하던 도중 쿼리를 최적화 하기 위해서 EXPLAIN 구문을 사용하게 되면서 알게 된 점을 정리해본다.

EXPLAIN에 대해서

  • EXPLAINSELECT, DELETE, INSERT, REPLACE, UPDATE 구문에서 사용할 수 있다.
  • EXPLAINSELECT 문에 사용된 각 테이블에 대한 정보 행을 반환한다.
  • MySQL이 명령문을 처리하는 동안 테이블을 읽는 순서대로 출력에 테이블을 나열해준다.
    • MySQL이 첫 테이블의 로우들을 읽고, 거기에 매칭되는 로우를 두번째 테이블에서 찾고, 거기에 매칭되는 로우를 세번째 테이블에서 찾고하는 식으로 계속…
  • 모든 테이블이 처리되면 MySQL은 선택한 열을 출력하고 더 많은 일치하는 행이 있는 테이블을 찾을 때까지 테이블 목록을 역추적한다.
  • 이 테이블에서 다음 행을 읽고 프로세스는 다음 테이블에서 계속된다.
  • EXPLAIN 구문에 더 대해서 알고 싶다면 아래 링크를 참고하면 된다.

EXPLAIN 결과 테이블의 열들 (EXPLAIN Output Columns)

ColumnJSON NameMeaning
idselect_idSELECT 식별자
select_typeNoneSELECT 타입
tabletable_name결과 행의 테이블
partitionspartitions매칭된 파티션들
typeaccess_typejoin 타입
possible_keyspossible_keys선택할 수 있는 인덱스들
keykey실제로 선택된 인덱스
key_lenkey_length선택된 키의 길이
refref인덱스와 비교한 열들
rowsrows검사할 행들의 추정치
filteredfiltered테이블 컨디션에 의해 필터된 행들의 퍼센티지
ExtraNone추가적인 정보

id (JSON name: select_id)

  • SELECT 식별자
  • 쿼리 내에서 SELECT의 순차 번호
  • 만약 행이 다른 행들의 결과들의 합이면 NULL

select_type (JSON name: none)

  • SELECT의 타입
  • UNION이나 서브쿼리를 사용하지 않았다면, SIMPLE이다.
select_type ValueJSON NameMeaning
SIMPLENoneSimple SELECT (not using UNION or subqueries)
PRIMARYNoneOutermost SELECT
UNIONNoneSecond or later SELECT statement in a UNION
DEPENDENT UNIONdependent (true)Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULTunion_resultResult of a UNION.
SUBQUERYNoneFirst SELECT in subquery
DEPENDENT SUBQUERYdependent (true)First SELECT in subquery, dependent on outer query
DERIVEDNoneDerived table
DEPENDENT DERIVEDdependent (true)Derived table dependent on another table
MATERIALIZEDmaterialized_from_subqueryMaterialized subquery
UNCACHEABLE SUBQUERYcacheable (false)A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNIONcacheable (false)The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

table (JSON name: table_name)

  • 테이블 명
  • <unionM,N>, <derivedN>, <subqueryN> 의 경우, M 혹은 N 은 쿼리의 id

partitions (JSON name: partitions)

  • 쿼리와 일치하는 레코드가 있는 파티션
  • 파티션이 없는 테이블이면 NULL이다.

type (JSON name: access_type)

  • join type
속성값내용
system테이블에 row가 1건인 경우 (= system table)
const 의 특별한 케이스
const행이 하나만 있기 때문에 이 행의 열 값은 옵티마이저에서 상수로 간주할 수 있다
const 테이블은 한번만 읽기 때문에 빠르다
PRIMARY KEY 나 UNIQUE index 와 비교하는 경우 사용됨
eq_refsystem, const와 달리 join하는 경우 최적의 타입
인덱스의 모든 부분이 조인에 의해 사용되고 인덱스가 PRIMARY KEY 또는 UNIQUE NOT NULL 인덱스일 때 사용됨
index된 컬럼에 = 연산을 사용하는 경우에도 사용
refref는 조인이 키의 가장 왼쪽 접두사만 사용하거나 키가 PRIMARY KEY 또는 UNIQUE 인덱스가 아닌 경우(즉, 조인이 키 값을 기반으로 단일 행을 선택할 수 없는 경우) 사용
사용된 키가 몇 개의 행과만 일치하는 경우 좋은 조인 유형임
fulltextFULLTEXT 인덱스를 사용하여 join이 수행된 경우
ref_or_nullref와 동일하나 null 값(IS NULL)에 대한 최적화가 되어있음
index_merge이 조인 유형은 인덱스 병합 최적화가 사용됨을 나타냄
이 경우 출력 행의 키 열에는 사용된 인덱스 목록이 포함되고 key_len에는 사용된 인덱스에 대해 가장 긴 키 부분 목록이 포함됨
unique_subqueryprimary_key 를 조회하는 일부 IN 서브쿼리에 대해 eq_ref를 대체
unique_subquery는 더 나은 효율성을 위해 하위 쿼리를 완전히 대체하는 인덱스 조회 기능
index_subqueryunique_subquery와 비슷하나 IN 서브쿼리 결과값이 unique하지 않은 경우
range인덱스를 사용하여 행을 선택하여 지정된 범위에 있는 행만 검색
출력 행의 키 열은 사용되는 인덱스를 나타냄
key_len은 사용된 가장 긴 키 부분을 포함
이 유형의 참조 열은 NULL
키 열이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE 또는 IN 연산에 사용될 때 적용됨
index인덱스 트리가 스캔된다는 점을 제외하고 인덱스 조인 유형은 ALL과 동일합니다. 이는 두 가지 방식으로 발생합니다.
1. 인덱스가 쿼리에 대한 커버링 인덱스이고 테이블에서 필요한 모든 데이터를 충족하는 데 사용할 수 있는 경우 인덱스 트리만 스캔. 이 경우 Extra 열에 Using index라고 표시. 인덱스의 크기가 일반적으로 테이블 데이터보다 작기 때문에 인덱스 전용 스캔은 일반적으로 ALL보다 빠름.
2. 전체 테이블 스캔은 인덱스에서 데이터 행을 인덱스 순서로 조회하는 읽기를 사용하여 수행. 추가 열에 인덱스 사용이 표시되지 않음.
all테이블이 const로 표시되지 않은 첫 번째 테이블인 경우 일반적으로 좋지 않으며 다른 모든 경우에는 일반적으로 매우 나쁨.
일반적으로 이전 테이블의 상수 값 또는 열 값을 기반으로 테이블에서 행 검색을 활성화하는 인덱스를 추가하여 ALL을 방지 가능.

possible_keys (JSON name: possible_keys)

  • MySQL이 쿼리 처리를 위해 고려한 인덱스 후보, 즉 사용가능한 인덱스들의 리스트
  • 이 컬럼이 NULL이면, 관련된 인덱스가 없다는 뜻
    • 이 경우 WHERE 절을 검사하여 인덱싱에 적합한 열을 참조하는지 여부를 확인하여 쿼리 성능을 향상시킬 수 있다
  • 순서도 독립적이라 무관

key (JSON name: key)

  • MySQL이 쿼리 처리를 위해 실제로 사용하기로 결정한 인덱스
  • possible_keys 의 인덱스 중에서 하나를 사용하기로 결정
    • possible_keys 에 없을 수도 있다
    • 이는 possible_keys 인덱스 중 어느 것도 행 조회에 적합하지 않지만 쿼리에 의해 선택된 모든 열이 다른 인덱스의 열인 경우에 발생 가능
    • 즉, 명명된 인덱스는 선택한 열을 포함하므로 검색할 행을 결정하는 데 사용되지는 않지만 인덱스 스캔이 데이터 행 스캔보다 효율적
  • InnoDB의 경우 InnoDB가 각 보조 인덱스와 함께 기본 키 값을 저장하기 때문에 쿼리가 기본 키를 선택하더라도 보조 인덱스가 선택한 열을 포함할 수 있음
    • 키가 NULL이면 MySQL은 쿼리를 보다 효율적으로 실행하는 데 사용할 인덱스를 찾지 못함
  • MySQLpossible_keys 열에 나열된 인덱스를 사용하거나 무시하도록 하려면 쿼리에 FORCE INDEX, USE INDEX 또는 IGNORE INDEX를 사용

key_len (JSON name: key_length)

  • key_len 열은 MySQL이 사용하기로 결정한 키의 길이
  • key_len의 값을 사용하면 MySQL이 실제로 사용하는 다중 부분 키의 부분을 결정 가능
  • 키 열이 NULL이라고 말하면 key_len 열도 NULL
  • 키 저장 형식으로 인해 키 길이는 NOT NULL 열보다 NULL일 수 있는 열에 대해 하나 더 큽니다.

ref (JSON name: ref)

  • ref 열은 테이블에서 행을 선택하기 위해 키 열에 명명된 인덱스와 비교되는 열 또는 상수를 보여줌
  • 값이 func이면 사용된 값은 일부 함수의 결과
    • 어떤 함수를 보려면 EXPLAIN 다음에 SHOW WARNINGS를 사용하여 확장된 EXPLAIN 출력을 확인
    • 함수는 실제로 산술 연산자와 같은 연산자일 수 있음

rows (JSON name: rows)

  • 행 열은 MySQL이 쿼리를 실행하기 위해 검사해야 한다고 생각하는 행의 수
  • InnoDB 테이블의 경우 이 수치는 추정치이며 항상 정확한 것은 아님

filtered (JSON name: filtered)

  • filtered 열은 테이블 조건으로 필터링된 테이블 행의 예상 백분율을 의미
    • 보통은 where절에 해당
  • 최대값은 100이며 행 필터링이 발생하지 않았음을 의미
  • 100에서 감소하는 값은 필터링 양이 증가함을 의미
  • 행은 검사된 행의 예상 수를 표시하고 행 × 필터링된 행은 다음 표와 조인된 행 수를 표시
    • 예를 들어, 행이 1000이고 필터링된 행이 50.00(50%)이면 다음 테이블과 결합할 행 수는 1000 × 50% = 500

Extra (JSON name: none)

References

This post is licensed under CC BY 4.0 by the author.

macOS 어디에서든 단축키로 터미널 실행하기

[Kotlin] 소수점 이하 자릿수 구하는 방법