MySQL EXPLAIN 명령어는 뭘까?

왜 글을 쓰나요?

RealMySQL 8.0에서 쿼리에 대한 실행계획을 설명할때 항상 EXPLAIN 명령어를 근거로 설명합니다. 이를테면 책에서는 다음과 같이 설명합니다.

Extra 칼럼에 “Using index for skip scan”이면 인덱스 스킵 스캔을 활용해 데이터를 조회했다는 것을 의미한다

좋은 설명이지만, EXPLAIN에 대해 잘 알지 못하다보니

흠 그렇군... 그런데 왜 Extra 컬럼에 저런 정보가 들어가지? type, partition, … 등 다른 컬럼은 어떤 정보를 설명하는거지?

라는 궁금증이 항상 있었습니다. 또 앞서 언급한 정보를 모르다보니, 실제 EXPLAIN을 실행시켜도 어떤 실행계획인지 잘 파악하지 못하는 문제가 있었습니다. 그래서 EXPLAIN 명령어에 대해 학습하기로 결정했습니다.

그렇군요! 그러면 EXPLAIN 명령어에 대해 간단히 설명해주세요!

우선 옵티마이저는 쿼리를 최적화하는 역할을 합니다. 여러가지 실행계획중 작업의 비용정보와 데이터베이스의 통계정보를 활용해 가장 낮은 비용으로 예측되는 실행계획을 선택하게됩니다. 이렇게 옵티마이저가 결정한 실행계획에 대한 정보를 EXPLAIN을 통해 알 수 있습니다.

 

좋습니다!! 이제 EXPLAIN의 각 컬럼들을 설명해주세요

예시

Id

• 쿼리를 구성하는 각 select문의 고유 번호입니다.

 

Q. 위의 예시에서 모든 id가 1인 이유가 있나요? 출력에 행이 4개인데…

 

A. id는 각 select문의 고유번호입니다. select문이 UNION으로 결합되어 있거나, 서브쿼리가 포함된 경우 id값을 볼 수 있습니다. 예시의 경우 단일 select이기 때문에 모든 행의 고유번호는 같습니다.

각 행은 쿼리에서 참조하는 테이블 중 하나를 나타냅니다. 그리고 해당 테이블에 대한 실행계획을 나열하게 됩니다.

 

select_type

• 쿼리의 유형을 나타냅니다.

Q. 어떤 쿼리유형들이 있나요?

 

A.

SIMPLE → 서브쿼리나 UNION이 없는 단순 SELECT 쿼리

PRIMARY → 서브쿼리나 UNION 쿼리가 있는 경우 바깥쪽 쿼리

SUBQUERY → SELECT문 내에 포함된 서브쿼리

DERIVED → FROM절 내에 있는 서브쿼리

등등

table

• 현재 행에서 접근하고 있는 테이블의 이름(별칭)입니다.

partitions

• 현재 행에서 접근하고 있는 테이블의 파티션이 있는 경우 해당 파티션을 나타냅니다.

Q. 파티션이 뭔가요?? 또 예시에서는 왜 NULL인가요?

 

A. 파티션은 대규모의 테이블이나 인덱스를 관리하기 용이하도록 더 작은 단위로 세분화 하는 데이터베이스 관리기술입니다. 예시가 NULL인 이유는 파티션을 적용하지 않아 NULL입니다.

type

• 조인 유형을 나타냅니다.

Q. 예시에서의 조인 유형을 알려주세요

 

A.

ALL → 풀 테이블 스캔을 나타냅니다.

eq_ref → 유니크 인덱스 혹은 기본키를 사용해 단일 행을 찾는 조인입니다

ref → 비 유니크 인덱스나 기본키가 아닌 컬럼을 사용해 여러행이 매칭될 수 있는 조인입니다.

possiple_keys

• 쿼리실행에 사용될 수 있는 인덱스를 나타냅니다.

Q. 쿼리 실행에 사용될 수 있는 모든인덱스를 알려주는 건가요? 왜 실제 사용한 인덱스가 아닌 모든 인덱스를 알려주는건가요?

 

A. 네. 사용가능한 모든 인덱스를 알려줍니다. 모든 인덱스를 알려주는 이유는 DB 관리자가 필요시 인덱스 구조를 수정할 수 있게끔 하기 위해서입니다.

key

• 실제로 쿼리 실행에 사용된 인덱스입니다.

key_len

• 사용된 인덱스의 길이를 나타냅니다.

Q. 인덱스의 길이는 실행계획에서 왜 알려주는 거에요?

 

A. 인덱스의 길이정보는 쿼리의 효율과 관련있습니다. 인덱스가 길면 인덱스가 짧은 경우보다 더 많은 비교를 해야 합니다.

 

ref

• 인덱스 키와 비교된 컬럼이나 상수값을 나타냅니다.

 

Q. 예시는 어떻게 해석해야 할까요?

 

A. 조인할때 한 테이블의 컬럼이 다른 테이블의 인덱스와 비교되어 조인됩니다. 이때의 값입니다.

row

• 쿼리 실행 계획에서 접근해야 할 것으로 예상되는 행의 수입니다.

Q. 이 정보는 어떻게 추정하나요?

 

A. 옵티마이저는 테이블의 통계정보로 데이터 분포, 인덱스의 카디널리티를 가지고 있고 쿼리조건을 분석해 추정합니다. 이때 통계정보는 정기적으로 업데이트하게 됩니다.

filtered

• 쿼리 조건에 의해 필터링된 데이터의 비율을 나타냅니다

Q. 이 정보도 row처럼 추정치인가요?

 

A. 네 추정치입니다.

Extra

• 쿼리 실행에 관련된 추가정보를 제공합니다.

Q. 추가 정보라는게 어떤 것들이 있을까요?

 

A. 쿼리 실행과 관련된 추가적인 정보를 제공합니다. 예를들면

Using index → 쿼리가 인덱스를 사용해 데이터를 검색하고 테이블에 접근하지 않는 경우입니다(커버링 인덱스)

Using where → 행 필터링 작업이 필요할때 나타납니다.

Using temporary → 쿼리실행을 위해 임시테이블을 사용하는 경우입니다.

Using filesort → 정렬을 요구하는 쿼리에서, 인덱스를 이용한 정렬이 불가능할때 사용됩니다.

 

 

Q. 예시에서 where가 없는데 왜 Using where가 등장하나요?

 

A. 내부적으로 조인 조건을 처리하는 과정에서 데이터 필터링이 일어나기 때문입니다. order 테이블은 명시적인 where조건은 없지만 조인 과정에서 값을 비교해 조인하게 됩니다.