실행 계획 분석

이 글에 포함된 모든 내용은 Real MySQL 8.0을 학습하며 작성된 내용입니다.

EXPLAIN

---

실행 계획 분석은 SQL이 어떻게 처리되면서 어떤 최적화를 수행하는지, 어떤 인덱스를 사용하는지 등의 정보를 통해 쿼리가 최상의 상태에서 수행되고 있는지 분석하는 단계이다.

---

id 칼럼

id 칼럼에는 SELECT 문장에서 1개 이사의 하위 SELECT 문자을 구분한 단위 쿼리에 부여되는 식별자 값이다. 이때 하나의 SELECT 쿼리의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다. 조인이 아닌 서로 다른 3개의 단위 SELECT 쿼리로 구성된 경우 각각을 식별할 수 있는 번호로 매겨지게 된다.

이때 주의할 점은 매겨진 번호가 테이블의 접근 순서를 의미하는 것이 아니기 때문에 테이블의 접근 순서를 알고싶다면 FORMAT=TREE 를 추가해 명확하게 알 수 있다.

---

select_type 칼럼

이 칼럼에서는 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

SIMPLE	UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우이다. 아무리 복잡한 실행 계획이라도 SIMPLE인 단위 쿼리는 하나만 존재하며 일반적으로 가장 바깥 SELECT 쿼리가 이에 해당한다.
PRIMARY	UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리에 해당한다. SIMPLE과 마찬가지로 PRIMARY인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PIMARY로 표시된다.
UNION	UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리를 의미한다. 이때 첫 번째 단위 SELECT는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)가 표시된다.
DEPENDENT UNION	UNION이나 UNION ALL로 집합을 결합하는 쿼리에 표시된다. 이때 DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.
UNION RESULT	UNION 결과를 담아두는 테이블을 의미. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값을 부여받지는 않는다.
SUBQUERY	FROM 절 이외에서 사용되는 서브쿼리만을 의미한다. (FROM 절에서 사용된 서브쿼리는 DERIVED로 표시)
DEPENDENT SUBQUERY	서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우 표시된다. 바깥쪽 SELECT 쿼리에 의존적(DEPENDENT)이기 때문에 나중에 실행되고 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.
DERIVED	단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미. 가능하다면 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
DEPENDENT DERIVED	FROM 절의 서브쿼리에서 외부 컬럼을 사용할 경우 표시. MySQL 8.0 버전부터는 래터럴 조인 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼의 참조가 가능해 졌다.
UNCHACHEABLE SUBQUERY	서브쿼리도 여러번 수행되는 동안 캐시된 값을 재활용하게 되는데 (DEPENDENT SUBQUERY의 경우 '외부 쿼리의 값' 단위로 캐시) 서브쿼리가 캐시를 사용할 수 없는 경우에 표시.  - 사용자 변수가 서브쿼리에 사용된 경우 - NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우 - 호출할 때마다 달라지는 함수(UUID(), RAND(), ...)가 서브쿼리에 사용된 경우
UNCACHEABLE UNION	UNION과 UNCHACHEABLE이란 두 개 키워드의 속성이 혼합된 의미
MATERIALIZED	FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용

---

table 칼럼

MySQL 서버의 실행 계획은 테이블 기준으로 표시된다. table 칼럼에서 "<>"로 둘러싸인 이름은 임시 테이블을 의미하며, "<>" 안에 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다. 즉 <derived2>는 단위 SELECT 쿼리의 id 값이 2인 실행 계획으로부터 만들어진 파생 테이블을 가리킨다.

---

partitions 칼럼

파티션을 참조하는 쿼리의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파이션들의 목록만 모아서 실행 계획이 partitions 칼럼에 표시해준다. 이때 type이 ALL로 표시되는데 이것은 모든 파티션이 아니라, 해당하는 파티션 테이블을 풀 스캔 실행했다는 의미이다.

---

type 칼럼

MySQL 서버가 각 테이블의 레코드를 인덱스를 사용했는지, 테이블을 전체 스캔(풀 테이블 스캔) 했는지 등을 나타낸다. 이 칼럼은 반드시 체크해야 할 중요한 정보다.

system	레코드가 1건 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법. MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다.
const	건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고, 항상 1건을 반환하는 쿼리의 처리 방식을 의미. 다른 DBMS에서는 유니크 인덱스 스캔이라고도 표현된다. 다중 칼럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 칼럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다. (실제 레코드가 1건이라는 확신이 없기 때문) const 타입의 경우 옵티마이저가 쿼리를 먼저 실행해서 통째로 상수화한다.
eq_ref	여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시. 처음 읽은 테이블의 칼럼값을 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때를 의미한다. 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법
ref	이 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없고 프라이머리 키나 유니크 키등의 제약 조건도 없다. 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없기 때문에 앞의 eq_ref와 const보단 느리지만 동등 조건으로만 비교되므로 매우 빠른 조회 방법 중 하나다.
fulltext	MySQL 서버의 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미
ref_or_null	ref 접근 방법에서 NULL 비교가 추가된 형태. 잘 활용되지 않지만 나쁘지 않은 접근 방법이다.
unique_subquery	WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법. 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.
index_subquery	IN 연산자의 특성(괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되야 한다)에 따라 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 사용되는 접근 방법.
range	인덱스 레인지 스캔 형태의 접근 방법. 하나의 값이 아니라 범위로 검색하는 경우를 의미한다. 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
index_merge	2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식. 이를 만큼 효율적이지는 않다. - 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어짐 - 전문 검색 인덱스를 사용하는 쿼리에서 적용되지 않음 - 이 접근 방법으로 처리된 결과는 항상 2개의 집합이 되기 때문에 부가적인 작업이 필요
index	이 방식은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미. 효율적인 방법이 아니라는 것을 명심하자. 풀 테이블 스캔 방식과 비교해 비교하는 레코드 건수를 같지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 풀 테이블 스캔보단 빠르게 처리된다. 이 접근 방식은 다음 조건에서 1+2 조건 또는 1+3 조건을 충복하는 쿼리에서 사용되는 방식이다. - range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우 - 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우 - 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
All	풀 테이블 스캔을 의미하는 접근 방법. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 경우)하고 반환한다. 가장 마지막으로 선택되는 가장 비효율적인 방법이다.

const, eq_ref, ref 이 세가지 접근 방법은 모두 WHERE 조건절에 사용하는 비교 연산자는 동등 비교 연산자여야 한다는 공통점이 있다. 또한 이 세 가지 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제을 일으키지 않는 접근 방법이다.

---

possible_keys 칼럼

이 칼럼은 MySQL 옵티마이저가 실행 계획을 수립하기 앞서 후보로 선정한 인덱스 목록을 나타낸다. 즉, '사용될 법했던 인덱스 목록'이다. 이 항목은 참고사항으로 크게 도움이 되지는 않아 특별한 경우를 제외하곤 무시해도 된다. 절대 이 칼럼에 표시됐다고 그 인덱스를 사용한다고 판단하지 않도록 주의하자.

---

key 칼럼

이 칼럼에 표시되는 인덱스는 최종 선택되어 실행 계획에서 사용하는 인덱스를 의미한다. 쿼리를 튜닝할 때 이 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. 인덱스를 전혀 사용하지 못하면 NULL을 표시한다.

---

key_len 칼럼

이 칼럼은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려주는 칼럼으로 매우 중요한 정보이다. 추가적으로 MySQL에서 NOT NULL이 아닌 칼럼에 대해서 NULL인지 확인하기 위한 1바이트가 추가된다는 점을 고려하자.

---

ref 칼럼

접근 방법(type 칼럼)이 ref면 어떤 값이 제공됐는지 보여준다.

---

rows 칼럼

실행 계획의 효율성을 판단하기 위해 예측했던 레코드 건수를 보여준다. 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값으로 정확하지 않다.

---

filtered 칼럼

이 칼럼의 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미한다. MySQL 옵티마이저가 실행 계획을 수립하는 동안 읽어온 데이터를 저장해둘 메모리 사용량을 낮추기 위해 대상 건수가 적은 테이블을 선행 테이블로 선택할 가능성이 높다. 따라서 filtered 칼럼이 얼마나 정확히 예측될 수 있느냐에 따라 조인 성능이 달라진다.

---

Extra 칼럼

쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 자주 표시된다.

Using where	가장 흔하게 표시되는 내용으로 MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 표시. filtered 칼럼과 함께 참고하여 성능상의 이슈가 있는지 없는지 알아낼 수 있다.
Using temporary	MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용할 경우 표시. 인덱스를 사용하지 못하는 GROUP BY 쿼리는 Using temporary 메시지가 표시되는 가장 대표적인 쿼리
Using index(커버링 인덱스)	데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 경우 표시.
Using filesort	ORDER BY를 처리하기 위해 적절한 인덱스를 사용하지 못할 때 표시. 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사 후 퀵 소트 또는 힙 소트 알고리즘 정렬을 수행한다는 의미이다.