실행 계획

실행 계획(explain)이란?

클라이언트가 DBMS에 요청한 SQL문을 어떻게 데이터를 불러올 것인지에 관한 계획 및 경로
SQL 튜닝을 하기 위한 정보를 파악할 수 있다.
실제 실행 결과가 아닌 예측 결과를 반환한다.
실제 실행 결과를 보려면 옵션을 추가해야 한다.

사용 방법

EXPLAIN 실행할_SQL문;

EXPLAIN 실행할_SQL문\g; /* 세로로 출력된다. */

/* 8.0 버전부터 포맷을 지정할 수 있다. */
EXPLAIN FORMAT = TABLE 실행할_SQL문; /* 디폴트가 TABLE이라서 FORMAT = TABLE은 생략해도 된다. */
EXPLAIN FORMAT = TREE 실행할_SQL문; /* 트리 구조 */
EXPLAIN FORMAT = JSON 실행할_SQL문; /* JSON 구조 */

실행 계획 분석하기

실행 계획	의미
id	SELECT 쿼리 별 부여되는 식별자 값
select_type	각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 속성
table	접근하고 있는 테이블에 대한 표시
partitions	테이블에 파티셔닝이 되어 있는 경우 사용되는 필드
type	각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
possible_keys	“사용될 법했던 인덱스의 목록”이며 반드시 사용된 것은 아님
key	최종 선택된 실행 계획에서 사용되는 인덱스
key_len	선택된 인덱스의 길이를 의미
ref	접근 방법이 ref면 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시
rows	실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. (정확하지는 않음)
filtered	필터링되고 남은 레코드의 비율 (정확하지는 않음)
Extra	옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

id

SELECT 쿼리 별 부여되는 식별자 값
조인으로 엮인 테이블끼리는 같은 값을 나타낸다.
값이 다르면 다른 계층의 SELECT문이다.
id의 값이 테이블의 접근 순서를 나타내는 것은 아니다.

select_type

각 단위 SELECT문의 유형
종류
- SIMPLE
  - 단순한 SELECT문
- PRIMARY
  - 메인 쿼리
- SUBQUERY
  - FROM 절 이외에서 사용되는 서브쿼리
- UNION
  - UNION을 사용한 쿼리에서 2번째부터의 SELECT문
  - 1번쨰 테이블은 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)로 취급한다.
- DEPENDENT UNION
  - UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것
  - UNION의 결과가 서브 쿼리로 사용될 경우에 해당한다.
- UNION RESULT
  - UNION 결과를 담아두는 테이블
  - <union 2,3>처럼 id 값이 table 속성에 표시된다.
- DERIVED
  - 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것
  - select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 부른다.
  - MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 이전 버전과는 달리
    임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화되어 있다.

table

접근하고 있는 테이블을 나타낸다.
테이블의 이름에 별칭이 부여된 경우에는 별칭을 표시한다.

partitions

테이블에 파티셔닝이 적용되어 있는지를 나타낸다.
적용되는 파티션 목록을 쉼표로 묶어서 나타낸다.
적용된 파티션이 없을 경우에는 NULL로 나타낸다.
MySQL 5.7까지는 EXPLAIN PARTITION으로만 확인할 수 있었지만
MySQL 8.0부터는 EXPLAIN으로도 확인할 수 있다.

type

각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
쿼리 튜닝 시 반드시 체크해야 할 중요한 정보
ALL(테이블 풀 스캔)을 제외한 나머지는 모두 인덱스를 사용하는 접근 방식이다.
system → ALL 갈수록 성능이 느려진다.
MySQL 옵티마이저는 이러한 접근 방법과 비용을 함께 계산해서
최소의 비용이 필요한 접근 방법을 선택해 쿼리를 처리한다.
종류
- system
  - 테이블의 레코드가 최대 1건 존재하는 테이블을 참조하는 형태의 접근 방법
  - InnoDB 스토리지 엔진에서는 나타나지 않는다.
  - MyISAM/MEMORY 테이블에서만 사용되는 접근 방식이다.
  - 테이블에 레코드가 1건 이하인 경우에만 사용할 수 있기에 거의 보이지 않는다.
- const
  - 쿼리 결과가 반드시 1건을 반환하는 쿼리의 처리 방식
  - PK 및 Unique Key 속성을 이용하는 조건절이 해당된다.
  - 만약 PK가 2개 이상의 속성으로 이루어져 있을 때 1개의 속성만 사용하는 경우에는 해당하지 않는다.
    - 이런 경우에는 ref에 해당한다.
- eq_ref
  - 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
  - 조인에서 처음 읽은 테이블의 속성의 값을
    다음 읽을 테이블의 PK나 유니크 키 속성의 검색 조건에 사용할 때 나타난다.
  - 조인에서 두 번째 이후에 읽는 테이블에서
    반드시 1건만 존재한다는 보장이 있어야 사용할 수 있다.
- ref
  - 조인의 순서와 관계없이 동등(Equal) 조건으로 검색할 때 사용하는 접근 방식
  - ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로
    const나 eq_ref 보다는 느리지만 동등한 조건으로만 비교되기에
    매우 빠른 레코드 조회 방법 중 하나에 속한다.
- fulltext
  - MySQL의 전문 검색 인덱스(FULLTEXT INDEX)를 사용해 접근한다.
  - 전문 검색 인덱스는 전용 검색 쿼리를 사용해야 한다
    - MATCH(속성명) AGAINST('검색할_내용')
- ref_or_null
  - ref에서 NULL 비교만 추가된 형태
  - 많이 활용되지는 않으나 사용된다면 나쁘지 않은 정도에 속한다.
- unique_subquery
  - WHERE 조건절에서 사용될 수 있는 IN (Subquery) 형태의 쿼리를 위한 접근 방법
  - 서브 쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용한다.
- index_subquery
  - IN (Subquery)에서 서브 쿼리가 중복된 값을 반환할 수 있는데
    이 때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 사용한다.
- range
  - 인덱스 레인지 스캔 형태의 접근 방법
  - 인덱스를 하나의 값이 아닌 범위로 검색하는 경우에 해당한다.
  - 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스 검색할 때 사용한다.
  - 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법
  - 우선순위(성능)가 낮은 편이긴 하지만 range 접근 방법도 상당히 빠른 편에 속한다.
  - 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.
- index_merge
- 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후,
  그 결과를 병합해서 처리하는 방
- 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 보다 효율이 떨어진다.
- 전문 검색 인덱스(FULLTEXT INDEX)를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
- index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 된다.
  - 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.
- index
  - index 접근 방법은 많은 사람이 자주 오해하는 접근 방법으로 반드시 효율적이지는 않음
  - 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
  - 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 풀 테이블 스캔보다는 빠르게 처리된다.
  - 쿼리의 내용에 따라 정렬된 인덱스의 장점을 사용할 수 있기에 효율적이라 할 수는 있다.
  - 다음 조건을 만족할 떄 사용된다.
    - 필수
      - range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
    - 택 1
      - 인덱스에 포함된 속성만으로 처리할 수 있는 쿼리인 경우 (테이블 풀 스캔을 안해도 되는 경우)
      - 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (별도 정렬 작업을 피할 수 있는 경우)
- ALL
  - 풀 테이블 스캔을 의미한다.
  - 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환한다.
  - 앞선 방식으로 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법
  - 테이블이 매우 작지 않다면 실제 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해보고 적용하는 것이 좋다.

possible_keys

옵티마이저가 최적의 실행 계획을 만들기 위해
후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이다.
정확히는 “사용될 법했던 인덱스의 목록”이다.
- 반드시 사용됬다는 의미는 아니다.
실행 계획에서 특별한 경우를 제외하고는 무시해도 된다.

key

최종 선택된 실행 계획에서 사용되는 인덱스
쿼리 튜닝 시 key 속성에 의도했던 인덱스가 표시되는지 확인해야 한다.
인덱스를 전혀 사용하지 못하면 NULL로 표시된다.

key_len

선택된 인덱스의 길이를 표시한다.
쿼리를 처리하기 위해 다중 속성으로 구성된 인덱스에서 몇 개의 속성까지 사용했는지 표기
더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.
- 예시
  - INTEGER 속성의 인덱스 2개 사용 시 8로 표시된다.

ref

참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시한다.
- 상숫값으로 비교할 경우 const라고 표시된다.
- 속성으로 비교할 경우 해당 테이블명과 속성명이 표시된다.

rows

실행 계획의 효율성 판단을 위해 예측했던 레코드 건수
통계를 통한 예상 값이라서 실제 결과와 일치하지 않는 경우가 많다.

filtered

필터링되고 남은 레코드의 비율
통계를 통한 예상 값이라서 실제 결과와 일치하지 않는 경우가 많다.

Extra

옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값
쿼리 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다.
내부적인 처리 알고리즘에 대해 조금 더 깊이있는 내용을 보여주는 경우가 많다.
종류
- Using filesort
  - ORDER BY 처리가 인덱스를 사용하지 못할 때 나타난다.
    - 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해
      퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행한 것을 의미한다.
  - ORDER BY가 사용된 쿼리의 실행 계획에서만 나타날 수 있다.
  - 해당 값 노출 시 쿼리는 많은 부하를 일으키므로 가능하다면
    쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
- Using index
  - 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우에 나타난다.
  - 최악의 경우 인덱스를 통해 검색된 결과 레코드 한 건씩 디스크를 읽어야 할 수 있다.
  - MySQL 옵티마이저가 인덱스를 사용하는 것보다
    풀 테이블 스캔으로 처리하는 편이 더 효율적이라고 판단했을 때 나타난다.
  - 커버링 인덱스 (Covering Index)
    - 조건절에 사용하는 속성은 인덱스가 있는 속성만 사용되도록 하는 방법
- Using temporary
  - 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary Table)을 생성하여 표시된 것
- Using index for skip scan
  - MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용할 경우
- Using join buffer
  - 조인이 수행될 때 드리븐 테이블에 적절한 인덱스가 없는 경우
- Using where
  - 스토리지 엔진으로 부터 받은 데이터를 MySQL 엔진에서 별도의 가공을 해서 필터링 작업을 처리한 경우

실행 계획

실행 계획(explain)이란?

사용 방법

실행 계획 분석하기

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

관련된 글

테이블 생성

테이블 변경

테이블 삭제