[MySQL] Explain 실행 계획

explain 명령어

 select문을 어떠한 방식으로 수행하여 데이터를 가져올 것인지에 대한 실행 계획을 출력하는 명령어입니다. 사용 방법은 질의하고자 하는 select 쿼리 앞에 explain 키워드를 붙여서 사용합니다. 실행 계획은 아래와 같은 형식으로 표현됩니다.

explain select * from table

 만약 위와 같은 포맷이 아닌 json 포맷으로 출력하고 싶다면 아래와 같은 명령어를 사용하면 됩니다.

explain format = json
select * from table

 

 이제 각 컬럼별로 어떤 의미를 나타내는지 살펴보겠습니다.

 

id

 select 쿼리별로 부여되는 식별자 값입니다. 만약 하나의 select 문에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여됩니다. 하지만 쿼리 문장이 서로 다른 select 문(쿼리 안에 서브쿼리 구성 등..)으로 구성되어 있으면 각 레코드의 id 컬럼이 각기 다른 값을 부여받게 됩니다.

 

select_type

 select 문의 유형을 나타냅니다.

• SIMPLE
  • 서브쿼리나 UNION이 없는 가장 단순한 형태의 테이블을 말합니다.
• PRIMARY
  • 가장 바깥의 select문을 말합니다.
• DERIVED
  • from 절에 사용된 서브쿼리로부터 발생한 임시 테이블을 말합니다. 임시 테이블은 메모리에 저장될 수도 있고, 디스크에 저장될 수도 있습니다. 일반적으로 메모리에 저장하는 경우에는 성능에 큰 영향을 미치지 않지만, 데이터의 크기가 커서 임시 테이블을 디스크에 저장할 경우 성능이 떨어지게 됩니다.
• SUBQUERY
  • from 절 이외에서 사용되는 서브쿼리를 의미합니다. 서브쿼리는 사용되는 위치에 따라 각각 다른 이름을 가지고 있습니다.
    • 중첩된 쿼리 (Nested Query) : select 되는 컬럼에 사용된 서브쿼리를 말합니다.
    • 서브 쿼리 (Sub Query) : where 절에서 사용된 경우에는 일반적으로 그냥 서브 쿼리라고 말합니다.
    • 파생 테이블 (Derived) : from 절에서 사용된 서브 쿼리를 말합니다.
• DEPENDENT SUBQUERY
  • 서브 쿼리가 바깥쪽 select 쿼리에서 정의된 컬럼을 사용하는 경우를 말합니다. 이는 서브 쿼리가 먼저 실행되지 못하고 서브 쿼리가 외부 쿼리 결과에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만듭니다. 서브 쿼리가 외부의 쿼리의 값을 전달받고 있는지 검토해서, 가능하다면 외부 쿼리와의 의존도를 제거하는 것이 좋습니다.
• UNCACHEABLE SUBQUERY
  • 쿼리의 from 절 이외의 부분에서 사용되는 서브 쿼리는 가능하면 MySQL 옵티마이저가 캐싱하여 최대한 재사용 될 수 있게 유도합니다. 하지만 사용자 변수나 일부 함수가 사용된 경우에는 이러한 캐시 기능을 사용할 수 없게 만듭니다. 이런 실행 계획이 사용된다면 사용자 변수를 제거하거나 다른 함수로 대체해서 사용할 수 있을지 검토해보는 것이 좋습니다.
• UNION
  • union으로 결합하는 단위 select 쿼리 가운데 첫 번째를 제외한 두 번째 이후의 단위 select 쿼리의 select_type은 UNION으로 표시됩니다.
• DEPENDENT UNION
  • UNION select_type과 같지만 union으로 결합된 단위 쿼리가 바깥쪽 쿼리에 의존적이어서 외부의 영향을 받고 있는 경우를 말합니다.

 

table

 참조되는 테이블을 말합니다. 테이블 이름에 별칭(Alias)가 부여된 경우에는 별칭이 표시됩니다. table 컬럼이 "<derived>" 또는 "<union>"과 같이 "<>"로 둘러싸인 이름이 명시되는 경우는 임시 테이블이라는 의미입니다. 또한 "<>" 안에 항상 표시되는 숫자는 단위 select 쿼리의 id를 지칭합니다. (ex: <union1, 2>)

 

partitions

  테이블이 파티셔닝이 되어있는 경우에 사용되는 필드입니다. 파티셔닝이 되어있지 않으면 null이 출력됩니다.

 

type

 테이블의 레코드를 어떤 방식으로 접근했는지에 대한 정보를 나타냅니다. 접근 방식은 대상 테이블로의 접근 방식이 효율적일지 여부를 판단하는데 중요한 항목입니다.

• system
  • 레코드가 한 건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 말합니다. (InnoDB 테이블에서는 나타나지 않습니다.)
• const
  • 테이블의 레코드 수에 관계없이 Primary key나 Unique key 컬럼을 이용하는 where절 조건을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리방식입니다.
• eq_ref
  • 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됩니다. Primary key나 Unique key를 사용하여 조인하는 경우를 말합니다. 조인되는 테이블에서 레코드가 반드시 한 건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방식입니다.
• ref
  • eq_ref와 달리 인덱스의 종류와 상관없이 동등 조건으로 검색할 때 사용되는 접근 방식입니다. 반환되는 레코드가 반드시 한 건이라는 보장이 없으므로 eq_ref 보다는 빠르지 않습니다.
• ref_or_null
  • ref와 같으면서 null 비교가 추가된 형태의 접근 방식입니다.
• fulltext
  • 전문검색(FullText) 인덱스를 사용하여 레코드를 읽는 방법을 의미합니다.
• index_merge
  • 2개 이상의 인덱스를 이용하여 각각의 검색 결과를 만들어낸 후에 그 결과를 병합하는 처리 방식입니다. (FullText index 제외)
• unique_subquery
  • where 조건절에서 사용할 수 있는 in(subquery) 형태의 쿼리를 위한 접근 방법입니다. 서브 쿼리에서 중복되지 않는 Unique한 결과 값을 반환할 때 이 방법을 사용합니다.
• index_subquery
  • in(subquery) 형태에서 subquery가 중복된 값을 반환할 수는 있지만 인덱스를 이용하여 중복된 값을 제거할 수 있을 때 사용하는 접근 방법입니다.
• range
  • 주어진 범위 내의 레코드를 스캔하는 형태의 접근 방법입니다. 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용하여 인덱스를 검색할 때 사용됩니다.
• index
  • 전체 인덱스를 처음부터 끝까지 풀 스캔하는 것을 의미합니다. 일반적인 경우 인덱스가 테이블보다 사이즈가 훨씬 작기 때문에 테이블 풀 스캔과 비교했을 때, 읽는 레코드 건 수는 같지만 테이블 풀 스캔보다 빠를 확률이 큽니다.
• ALL
  • 테이블을 처음부터 끝까지 읽는 테이블 풀 스캔을 의미합니다. 테이블 풀 스캔은 위에서 설명된 타입으로 처리할 수 없을 때 가장 마지막에 선택되며, 대부분의 경우 아주 느린 성능을 보입니다.

 

possible_keys

 MySQL의 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스들을 나타냅니다.

 

key

 possible_keys에서 MySQL 옵티마이저가 실제로 사용한 인덱스를 나타냅니다.

 

key_len

 선택된 인덱스의 길이를 의미합니다. key 컬럼이 null이면 key_len 컬럼도 null이 출력됩니다.

 

ref

 key 컬럼에 지정된 인덱스가 참조 조건으로 어떤 컬럼이 제공되었는지 보여줍니다. 참조 대상으로 상수를 지정했다면 const로 표시되고, 다른 테이블의 컬럼 값이면 그 테이블 명과 컬럼 명이 표시됩니다. func은 "Function"의 줄임말로, 참조하는 컬럼의 값이 그대로 사용된 것이 아니라 콜레이션 변환이나 값 자체 연산을 거쳐서 참조됐다는 것을 의미합니다.

 

rows

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

 

filtered

 가져온 레코드에서 where절의 조건 검색이 적용되면 몇 개의 레코드가 남는지에 대한 추정치입니다. 이 값도 통계 정보를 바탕으로 계산된 값이므로 현실의 값과 반드시 일치하지 않습니다.

 

extra

 실행 계획에서 쿼리를 어떻게 해석하는지에 관한 추가적인 정보가 출력됩니다.

• const row not found
  • type 컬럼이 const인데 해당 테이블에 1건도 존재하지 않으면 이 내용이 표시됩니다.
• Distinct
  • 조건을 만족하는 레코드를 찾았을 때, 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않습니다.
• Full scan on NULL key
  • column1 IN (subquery) 조건을 가진 쿼리에서 자주 발생하는 형태로, 만약 column1 값이 NULL이라면 테이블을 풀 스캔 하겠다는 뜻입니다. column1에 NOT NULL이 정의되어 있다면 이러한 키워드는 발생하지 않습니다.
• Impossible HAVING
  • 쿼리에 HAVING 절의 조건을 만족하는 레코드가 없을 때 나타나는 키워드입니다.
• Impossible WHERE
  • 테이블 구조상 WHERE 조건이 항상 false가 될 수 밖에 없는 경우에 이 키워드가 표시됩니다.
• Impossible WHERE noticed after reading const table
  • 데이터를 스캔해서 실제로 데이터를 읽어보고 WHERE 조건에 일치하는 데이터가 존재하지 않는다는 것을 확인했을때 이 키워드가 표시됩니다.
• No matching min/max row
  • MAX(), MIN()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을때 나타나는 키워드입니다.
• No matching row in const table
  • 조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없으면 이 키워드가 나타납니다.
• No tables used
  • FROM 절이 없는 쿼리 문장을 실행할 때 나타납니다.
• Not exists
  • A 테이블에는 존재하지만 B 테이블에는 존재하지 않는 값을 조회해야 할 때 주로 "NOT IN (subquery)", "NOT EXISTS 연산자" 를 주로 사용합니다. 그러나 이러한 형태의 조인을 안티 조인이라고 합니다. 이와 동일한 처리를 아우터 조인을 이용해서도 구현할 수 있습니다. 레코드가 많을 경우에는 안티 조인보다는 아우터 조인을 이용하면 더 빠른 성능을 낼 수 있습니다. 아우터 조인을 이용해서 안티조인과 동일한 수행을 하는 쿼리에는 이 키워드가 나타납니다.
•  Range checked for each record
  • 조인 처리시에 적절한 인덱스가 없는 상황에서 선행되는 테이블에서 공급되는 값에 따라 인덱스 사용을 검토할 수 있는 경우에 이 키워드가 나타납니다. 공급되는 각각의 레코드에 대해 range / index_merge를 검토합니다.
• Select tables optimized away
  • Min() 또는 MAX() 만 select 절에서 사용되거나 group by로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 이 키워드가 나타납니다.
• unique row not found
  • 두 개의 테이블이 각각 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 이 키워드가 표시됩니다.
• Using filesort
  • order by를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 찾지 못하였을 때는 MySQL 서버가 조회된 레코드를 다시 한 번 정렬해야 합니다. order by 처리가 인덱스를 활용하지 못할 때만 이 키워드가 표시되며, 조회된 레코드를 정렬용 메모리 버퍼에 복사하여 퀵 소트 알고리즘을 수행합니다.
• Using index (커버링 인덱스)
  • 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 나타나는 키워드입니다.
• Using index for group-by
  • group by 처리가 인덱스를 이용할 때 나타나는 키워드입니다. 보통의 group by 절은 그룹핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그룹핑하는 형태의 부하 작업을 필요로 합니다. 하지만 group by 처리가 인덱스를 이용하면 정렬된 인덱스 컬럼을 순서대로 읽으면서 그룹핑 작업만 수행하면 됩니다. 이렇게 인덱스를 이용하면 레코드의 정렬이 필요로 하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르게 처리됩니다. group by 처리를 위해 인덱스를 읽는 방법을 "루스 인덱스 스캔"이라고 합니다.
• Using join buffer
  • 일반적으로 빠른 쿼리 실행을 위해 조인되는 컬럼은 인덱스를 생성합니다. 실제로 조인에 필요한 인덱스는 조인되는 양쪽 테이블 컬럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 컬럼만 필요로 합니다. MySQL 옵티마이저도 조인되는 두 테이블에 있는 각 컬럼에서 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행합니다. 뒤에 읽는 테이블은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문입니다.
  • 조인이 수행될 때 드리븐 테이블의 조인 컬럼에 적절한 인덱스가 없다면 드라이빙 테이블로부터 읽은 레코드 건수만큼 매번 드리븐 테이블을 테이블 풀 스캔이나 인덱스 풀 스캔해야 할 것입니다. 이때 드리븐 테이블의 비효율적인 검색을 보완하기 위해 MySQL 서버는 드라이빙 테이블에서 읽은 레코드를 임시 공간에 보관하여 필요할 때 재사용 할 수 있도록 합니다. 읽은 레코드를 임시로 보관해두는 메모리 공간을 "조인 버퍼"라고 하며, 조인 버퍼가 사용되는 실행 계획에 이 키워드가 표시됩니다.
    • 참고) 두 테이블이 조인될 때, 먼저 읽는 테이블을 드라이빙(Driving) 테이블이라고 하며, 뒤에 읽히는 테이블을 드리븐(Driven) 테이블이라고 합니다.
• Using sort_union, Using union, Using intersect
  • 쿼리가 index_merge 접근 방식(실행 계획의 type 컬럼 값)으로 실행되는 경우 2개 이상의 인덱스가 사용될 수 있습니다. 이때 두 실행 계획의 Extra 컬럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하게 설명하기 위해 다음 3개 중에서 하나의 메시지를 선택적으로 출력합니다.
  • Using intersect
    • 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미입니다.
  • Using union
    • 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미입니다.
  • Using sort_union
    • Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로 대량의 range 조건들) 이 방식으로 처리됩니다. 레코드 건 수에 거의 관계없이 각 WHERE 조건에 사용된 비교 조건이 모두 동등 조건이면 Using union이 사용되고, 그렇지 않으면 Using sort_union이 사용됩니다.
• Using temporary
  • 쿼리를 처리하는 동안 중간 결과를 담아두기 위해 임시 테이블(Temporary table)을 사용하면 이 키워드가 표시됩니다. 임시 테이블은 메모리 상에 생성될 수도 있고, 디스크 상에 생성될 수도 있습니다. 이때 사용된 임시 테이블이 메모리에 생성되었는지, 디스크에 생성되었는지는 실행 계획만으로는 알 수 없습니다.
• Using where
  • MySQL은 내부적으로 크게 MySQL 엔진과 스토리지 엔진 두 개의 레이어로 나눠서 볼 수 있습니다. 스토리지 엔진은 디스크나 메모리 상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행합니다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 이 키워드가 나타납니다.

 

---

참고자료

https://12bme.tistory.com/160

[MySQL] MySQL 실행 계획

https://denodo1.tistory.com/306

[Mysql] Explain 실행계획 보는법

https://12bme.tistory.com/168

[MySQL] 실행 계획 분석 시 주의사항

https://idea-sketch.tistory.com/48

[MySQL]MySQL 벼락치기(7) - 실행계획(1)