MySQL 실행 계획 톺아보기

MySQL 실행 계획 분석

💡 실행 계획에서 표시되는 각 컬럼이 어떤 것을 의미하는지 알아보자

메뉴얼한 작업이나 가끔 쿼리 성능을 확인하고 싶을 때 EXPLAIN 키워드를 통해 MySQL 실행 계획을 살펴보게 되는데,
각 칼럼의 의미에 대해서는 명확하게 정리하거나 알아본 경험이 없는 것 같아서 이번 기회에 이를 정리하고 상기합니다.

각 칼럼의 구성은 다음과 같습니다.

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

 

id 칼럼

💡 ID 컬럼은 단순히 SELECT 쿼리 수를 의미하고 해당 SELECT 쿼리를 식별하기 위한 용도로 사용됨.

• SELECT 쿼리 수를 의미하므로 같은 ID가 여러개 보일 수도 있음
• *쿼리 순서는 아님
  • EXPLAIN FORMAT=TREE SELECT 명령으로 쿼리 순서 확인 가능

 

select_type 칼럼

💡 SELECT 쿼리가 어떤 타입인지 표시되는 칼럼

다음과 같은 값들이 표시될 수 있음

SIMPLE

• UNINON이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우
• 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE

PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 가장 바깥쪽에 있는 단위 쿼리는 select_type이 PRIMARY로 표시됨
• SIMPLE과 마찬가지로 하나만 존재

UNION

• UNION으로 결합될 쿼리에 표시

DEPENDENT

• 내부 쿼리가 외부의 값을 참조해서 처리될 때 표기
• e.g. DEPENDENT UNION, DEPENDENT SUBQUERY
• 아래 예시는 UNION이 서브쿼리에 영향을 주므로 DEPENDENT가 표시된 걸 확인할 수 있음

UNION RESULT

• UNION 결과를 담아둘 임시 테이블
• 실제 SELECT 쿼리가 아니기 때문에 별도의 ID가 표시되지 않음

SUBQUERY

• FROM 절 이외에서 사용되는 서브쿼리
• FROM 절에 사용되는 서브쿼리는 파생 테이블(DERIVED)로 표기됨

👊 파생 테이블은 일반적으로 RDBMS에서는 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부름.

 

DERIVED

• FROM 절에 사용되는 서브쿼리. 즉, 파생 테이블에 표기
• MySQL 5.6 부터 임시 테이블에 인덱스를 추가하는 등 성능 최적화가 많이 진행되었지만 한계가 있다고 함.
  • 결국 최적화된 쿼리를 작성하는 것이 중요

UNCACHEABLE

• 캐싱할 수 없을 때 표기되는 prefix
• 서브 쿼리 결과 등을 재사용하기 위해 내부적인 캐시 공간에 담아두게 되는데, UNCACHEABLE은 이게 불가능한 경우를 뜻함
• e.g. UNCACHEABLE SUBQUERY, UNCACHEABLE UNION
• 캐시를 사용할 수 없는 경우는 변수, RAND 등 호출할 때마다 변동되는 값인 경우임
• 아래는 캐시된 데이터를 재활용하는 구조 예시

MATERIALIZED

• MySQL 5.7버전부터 서브쿼리의 결과를 임시 테이블로 최적화하는데, 이 경우에 표기되는 키워드.

 

table 칼럼

💡 사용되는 테이블명 표기

• <> 로 둘러싸인 이름은 임시 테이블
  • <> 안에 항상 표시되는 숫자는 SELECT 쿼리의 id 값을 지칭
  • e.g. <derived2> 인 경우 id 2를 가리킴

partitions 칼럼

💡 접근할 파티션 목록 표기

다음 예시는 joined_date 칼럼 기준으로 p_2000, p_2001_2010 파티션을 조회하는 실행 계획을 조회한 모습

💡 파티션 대상으로 하는 쿼리에서 type 칼럼이 ALL인 경우 물리적으로 나뉜 파티션만 풀 스캔하게 되니 실행 계획 판단에 주의
type 칼럼은 아래에서 알아봅니다.

 

type 칼럼

💡 각 테이블의 레코드를 어떤 방식으로 읽었는지 표기 e.g. 풀 테이블 스캔으로 데이터를 읽었는지 등

다음과 같은 값이 표기될 수 있고 위에서부터 성능이 빠른 순임

system

• 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법

const

• 반드시 1건을 반환하는 쿼리의 처리 방식
• 유니크 인덱스 스캔이라고도 표현함
• e.g. 프라이머리 키나 유니크 키 칼럼을 이용하는 where 조건의 경우
• 단, 다중 칼럼으로 구성된 유니크 키의 일부 칼럼을 사용하는 경우는 const 타입의 접근 방식을 사용할 수 없음

eq_ref

• 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법
• 아래는 PK를 조인에 사용하는 예시

ref

• 동등(Equal) 조건으로 검색할 때 ref 접근 방법이 활용됨.
• 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않지만 동등 비교이므로 매우 빠른 레코드 조회 방법

💡 동등 비교 연산자는 `=` 또는 `<=>` 을 의미함 `<=>` 는 NULL에 대한 비교 방식만 조금 다를 뿐 `=` 와 같은 연산자

• • 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 방법

fulltext

• MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미
• 전문 검색 인덱스가 테이블에 정의되어 있는 경우 + 전문 검색 쿼리를 사용한 경우(MATCH (...) AGINST (...))

💡 fulltext보다 일반 인덱스를 사용하는 range 접근 방법이 더 빨리 처리되는 경우가 많았다고 함 따라서 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인해 보는 편이 좋다.

ref_or_null

• ref 접근 방법에 NULL 비교가 추가된 형태

unique_subquery

• WHERE 조건절에서 IN(subquery) 형태의 쿼리를 위한 접근 방법
• 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용

index_subquery

• IN(subquery) 형태에서 서브쿼리가 중복된 값을 반환할 수 있지만 인덱스를 이용해 중복을 제거할 수 있는 경우 사용되는 접근 방법

range

• 인덱스의 일정 범위를 읽는 인덱스 레인지 스캔 형태의 접근 방법

💡 주로 `<`, `>`, `IS NULL`, `BETWEEN`, `IN`, `LIKE` 등의 연산자를 이용해 인덱스를 검색할 때 사용됨

• 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법

💡 우리가 보통 “인덱스를 효율적으로 사용한다”라고 말할 때는 const, ref, range 세 가지 접근 방법을 이야기함

• 레코드 수에 따라 성능 차이가 있지만 range 접근 방법도 상당히 빠른 편

index_merge

• 2개 이상의 인덱스를 이용해 각각 검색 결과를 만들고 그 결과를 병합(merge)하는 방식
• index_merge 접근 방법이 사용되는 경우를 보면 이름만큼 그렇게 효율적으로 작동하진 않음
  • 여러 인덱스를 읽어야 하므로 range 접근 방법보다 효율성 낮음
  • 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않음
  • 항상 2개 이상의 집합이 결과로 나오므로 중복 제거와 같은 부가적인 작업이 더 필요
• index_merge 접근 방법이 사용될 때는 Extra 칼럼에 좀 더 부가적인 설명이 포함됨

index

• 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 형태의 접근 방법

💡 “효율적으로 인덱스를 사용하겠다”싶은 이름이지만 사실 인덱스 풀 스캔을 의미하니 주의

ALL

• 풀 테이블 스캔
• 옵티마이저가 가장 마지막에 선택하는 가장 비효율적인 방법

💡 InnoDB도 다른 DBMS와 같이 대량의 디스크 I/O가 필요한 작업에서 한꺼번에 많은 페이지를 읽어오는 리드 어헤드(Read Ahead) 기능을 제공함.
리드 어헤드(Read Ahead) : 인접한 페이지가 연속해서 몇 번 읽히면 백그라운드로 작동하는 읽기 쓰레드가 최대 64개의 페이지씩 한꺼번에 디스크로부터 읽어 들이는 작동 방식

일반적인 온라인 트랜잭션 처리 환경이 아니라 데이터 웨어하우스나 배치 프로그램처럼 대용량의 레코드를 처리하는 작업에서는 억지로 인덱스를 사용하게 하는 등의 잘못 튜닝된 쿼리보다 상황에 따라 풀 테이블 스캔이 더 나은 접근 방법일 수도 있음

쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니라는 점을 기억하자

 

possible_keys 칼럼

💡 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 인덱스의 목록

• = 사용될뻔한 인덱스 목록.
• 실제 사용된 인덱스가 아닐 수 있으니 주의

 

key 칼럼

💡 최종 선택된 실행 계획에서 사용하는 인덱스

 

key_len 칼럼

💡 인덱스에서 몇 바이트까지 사용했는지 표기.
다중 칼럼 인덱스의 경우 key_len 칼럼 값을 통해 몇 개의 칼럼까지 사용했는지 알 수 있음!

 

ref 칼럼

💡 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 표기

• 상수값인 경우 const
• 다른 테이블의 칼럼값인 경우 그 테이블 명과 칼럼 명이 표기됨
• 타입을 변환해야 하는 등 MySQL 서버가 내부적으로 값을 변환해야 할 때는 func가 표기 됨

아래 사진은 ref 예시

 

rows 칼럼

💡 옵티마이저가 실행 계획에서 예측했던 `읽어야할 레코드 건수`를 표기

• 레코드 건수 예측치가 아니라 해당 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미하니 주의
• 옵티마이저가 산출해 낸 예상 값으로 정확하지 않을 수 있음

 

filtered 칼럼

💡 옵티마이저가 예측한 값으로, rows 칼럼에 표기되는 레코드 수에서 필터링되어 실제 사용될 걸로 예측되는 레코드 건수의 비율 표기

• e.g. rows 233에 filtered 값이 16.03이면, 37(=233 * 0.1603) 건 정도 사용될 걸로 예측했다는 의미
• 옵티마이저는 메모리 사용량을 낮추기 위해 대상 건수가 적은 테이블을 선택할 가능성이 높음
  그래서 filtered 칼럼에 표시되는 값이 얼마나 정확히 예측될 수 있느냐도 중요.

 

Extra 칼럼

💡 이름과 달리 실행 계획에서 성능과 관련된 중요한 내용이 Extra 칼럼에 자주 표기되고 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여주는 경우가 많음

다양한 내용이 표기되므로 매뉴얼을 참고하고 몇 개만 알아보자

Using filesort

• ORDER BY 처리가 인덱스를 사용하지 못할 때 표기됨
• 조회된 레코드를 정렬용 메모리 버퍼에 복사해 정렬을 수행한다는 의미
• Using filesort는 많은 부하를 일으키므로 가능하다면 쿼리 튜닝 혹은 인덱스 생성이 필요

Using index

• 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표기
  • = 커버링 인덱스를 사용하는 경우

Using index condition

• 옵티마이저가 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화를 사용하면 표기

💡 인덱스를 비교하는 건 InnoDB 스토리지 엔진에서 수행하지만 테이블의 레코드를 비교하는 건 MySQL 엔진이 수행하는 작업으로 수행 주체가 다름.

인덱스에 대한 정보는 MySQL 엔진은 알 수 없으므로 인덱스에 조회하는 칼럼이 있어서 커버링 인덱스가 가능한 상황에서도 테이블 레코드를 한 번 더 조회하는 경우가 생길 수 있음하지만 인덱스 조건을 InnoDB 스토리지 엔진에서 MySQL 엔진으로 pushdown 해주면 인덱스 정보를 알 수 있으므로 테이블 레코드를 조회하지 않아도 됨
이를 인덱스 컨디션 푸시 다운(Index condition pushdown)이라고 함.
인덱스 컨디션 푸시 다운은 MySQL 5.6 부터 지원함

아키텍처 구조는 아래 이미지 참고.

 

Using where

• MySQL 엔진에서 스토리지 엔진에서 전달된 데이터를 별도의 가공 처리하고 필터링한 경우

💡 Using where가 성능상 문제를 일으킬지 아닐지를 적절하게 선별하는 것 이 중요한데, MySQL 8.0부터는 filtered 칼럼 값을 확인 가능하고 filterd 값이 작은 경우(=filter되어 남는 데이터의 비율이 적은 경우)를 확인하면 판단이 가능함

filtered 칼럼 값이 꽤 작다고 판단되면 인덱스를 추가해 최적화를 진행해볼 수 있음

 

 

Reference

- Real MySQL 8.0