MySQL 실행계획 보는 방법

DB에서 쿼리를 작성하면 EXPLAIN 또는 DESC 명령어를 통해 실행계획을 확인합니다.

하지만 보다보면 가끔 잘 모르겠는 부분이 있어서..

Real MySQL을 보며 공부한 부분을 정리하려합니다.

 

통계정보와 히스토그램

실행계획을 보기 전에 옵티마이저가 어떤식으로 실행계획을 만드는지 간단하게 알아보려고합니다.

옵티마이저가 보다 효율적인 실행계획을 만들기 위해 필요한 두가지가 통계정보와 히스토그램입니다.

두 정보를 기반으로 어떤 인덱스를 선택할지 JOIN순서를 어떻게 정할지 결정하기 때문입니다.

 

통계정보는 디스크에 저장되며 테이블이 생성되거나 데이터가 변경될 때 통계정보가 추가 및 변경됩니다.

히스토그램은 자동으로 수집되지 않으며, 데이터 분포에 대한 통계정보를 제공합니다.

 

히스토그램은 아래와 같은 명령어로 생성 가능합니다.

ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 칼럼명;

 

출력 포멧

실행계획은 테이블, TREE, JSON 3가지 형태로 출력 할 수 있습니다.

 

테이블 출력

EXPLAIN SELECT 쿼리

 

TREE 출력

EXPLAIN FORMAT=TREE SELECT 쿼리

 

JSON 출력

EXPLAIN FORMAT=JSON SELECT 쿼리

 

TREE FORMAT에서 쿼리의 단계별 실행시간 및 실행계획 읽는법

EXPALIN ANALYZE 명령어로 단계별 쿼리 시간을 확인 할 수 있습니다.

이는 실제 실행계획은 아니며 MySQL이 가지고 있는 통계정보들을 가지고 예측한 결과를 보여줍니다.

EXPLAIN ANALYZE FORMAT=TREE SELECT o.order_id, u.name, p.name AS product_name, oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE u.name = 'Alice';

-> Nested loop inner join  (cost=1.8 rows=1) (actual time=0.0732..0.0857 rows=2 loops=1)
    -> Nested loop inner join  (cost=1.45 rows=1) (actual time=0.0648..0.0736 rows=2 loops=1)
        -> Nested loop inner join  (cost=1.1 rows=1) (actual time=0.0495..0.055 rows=1 loops=1)
            -> Filter: (u.`name` = 'Alice')  (cost=0.75 rows=1) (actual time=0.0336..0.0378 rows=1 loops=1)
                -> Table scan on u  (cost=0.75 rows=5) (actual time=0.0306..0.0334 rows=5 loops=1)
            -> Covering index lookup on o using user_id (user_id=u.user_id)  (cost=0.35 rows=1) (actual time=0.0142..0.0153 rows=1 loops=1)
        -> Filter: (oi.product_id is not null)  (cost=0.35 rows=1) (actual time=0.0146..0.0175 rows=2 loops=1)
            -> Index lookup on oi using order_id (order_id=o.order_id)  (cost=0.35 rows=1) (actual time=0.0142..0.0167 rows=2 loops=1)
    -> Single-row index lookup on p using PRIMARY (product_id=oi.product_id)  (cost=0.35 rows=1) (actual time=0.00508..0.00517 rows=1 loops=2)

 

TREE 형태의 실행계획에서 들여쓰기는 아래와 같은 의미를 가집니다.

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행된다.
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행된다.

즉 위와 같은 상황에서는 

 

1. Filter: (u.`name` = 'Alice')  (cost=0.75 rows=1) (actual time=0.0336..0.0378 rows=1 loops=1)

- actual time은 테이블에서 레코드를 가져오는데 걸리는 시간이며, 첫번째 숫자는 첫번째 레코드, 두번째는 마지막 레코드를 가져오는데 걸린 평균시간입니다.

- users 테이블에서 조건에 맞는 행을 찾습니다.

- 5개의 행을 스캔했고 조건에 맞는 actual rows=1 조건을 만족하는 행은 1개

 

2.Covering index lookup on o using user_id (user_id=u.user_id)  (cost=0.35 rows=1) (actual time=0.0142..0.0153 rows=1 loops=1)

- u.user_id를 기반으로 orders 테이블에서 조건에 맞는(u.name=Alice)데이터를 찾습니다.

- rows=1 매칭된 행 1개, 커버링 인덱스 사용

- loops=1 상위노드(users)에서 하위노드(orders)의 데이터를 읽어오는 작업이 한번 진행되었습니다.

 

3.Index lookup on oi using order_id (order_id=o.order_id)  (cost=0.35 rows=1) (actual time=0.0142..0.0167 rows=2 loops=1)

- o.order_id를 이용해 order_items 테이블에서 데이터를 찾습니다.

- rows=2 매칭된 행 1개

- loops=1 상위노드(order_items)에서 하위노드(orders)의 데이터를 읽어오는 작업이 한번 진행되었습니다.

 

4.Single-row index lookup on p using PRIMARY (product_id=oi.product_id)  (cost=0.35 rows=1) (actual time=0.00508..0.00517 rows=1 loops=2)

- oi.product_id를 이용해 products 테이블에서 데이터를 찾습니다.

- roops=2 상위노드(product)에서 하위노드(order_items)의 데이터를 읽어오는 작업이 한번 진행되었습니다.

 

EXPLAIN 컬럼들

id (SELECT의 단위)

SELECT 쿼리별로 부여되는 식별자값 입니다.

하나의 SELECT 문장은 하나의 ID값을 가지며, JOIN된 테이블들은 하나의 ID값을 가집니다.

그렇기에 아래와 같이 서브쿼리를 사용하면 다른 ID값을 가진 실행계획을 찾을 수 있습니다.

select_type

SELECT 쿼리에 대한 타입입니다.

 

SIMPLE 

UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT쿼리입니다.

일반적으로 가장 바깥의 문장이 SIMPLE입니다.

 

PRIMARY

UNION이나 서브쿼리를 가지는 실행계획에서 가장 바깥쪽 문장의 타입입니다.

 

DERIVED

쿼리의 실행결과로 메모리와 디스크에 임시테이블을 만드는 작업을 말합니다.

 

UNION

UNION으로 결합되는 쿼리 중 첫번 째 이후 쿼리에 대한 타입입니다. (첫번째는 DERIVED)

첫째 테이블은 4개의 서브쿼리로 조회된 결과를 UNION ALL로 결합해 임시테이블을 이용해 사용하고 있기에 DERIVED 타입을 가지게됩니다.

 

추가로 UNION이나 UNION ALL로 결합된 쿼리가 외부쿼리에 의해 영향을 받으면 DEPENDENT 타입(DEPENDENT SUBQUERY, DEPENDENT UNION)이 됩니다.

DEPENDENT의 경우에는 외부쿼리가 먼저 실행되고 내부쿼리가 실행되어야 하기에 일반 서브쿼리보다는 느립니다.

 

SUBQUERY

SELECT TYPE에서 서브쿼리는 FROM절 이외에서 사용되는 서브쿼리만을 의미합니다.

FROM절에서 사용되는 서브쿼리는 DERIVED로 표시됩니다.

 

 

MATERIALZED

주로 FROM절이나 IN형태의 서브쿼리에 사용된 서브쿼리의 최적화를 위해 사용합니다

서브쿼리의 내용을 임시로 구체화 한 후 임시테이블을 다른 테이블과 조인하는 형태로 최적화합니다.

TABLE 컬럼

사용된 TABLE을 표시합니다.

<derived숫자>, <union M,N>과 같은 컬럼들은 임시테이블을 의미합니다. 

 

아래 예시와 같은 쿼리에서 <derived3>이라고 되어있는데 여기서 3은 SELECT쿼리의 id값이 3인 실행계획으로 부터 만들어진 파상태이블임을 의미합니다.

즉 id:1 번은 실행되기 위해 id:3번인 users 테이블을 읽어서 만들어진 임시테이블이라는 의미입니다.

PARTITIONS 컬럼

SELECT에서 조회할 때 파티션을 통해 조회했다면 해당 파티션을 확인 할 수 있습니다.

TYPE 컬럼 (JOIN TYPE)

각 테이블의 레코드를 어떤 방식으로 읽었는지 확인합니다.

 

CONST

pk나 유니크 키를 사용하는 WHERE 조건절을 가지고있으며, 반드시 1건을 반환하는 방식입니다.

유니크 인덱스 스캔이라고도 합니다.

 

옵티마이저가 쿼리를 최적화 하는 단계에서 해당 쿼리를 먼저 실행시켜 통채로 상수화시킵니다.

그래서 const타입이라고 합니다.

pk조건 중 일부만 사용할때는 const가 아닌 ref방식으로 읽습니다.

 

eq_ref

join에서 처음 읽은 테이블의 컬럼값을 다른 테이블의 pk나 유니크 키 조건에 사용 할 때를 eq_ref라고합니다.

이 때 처음 읽은 테이블이 아닌 두번째 테이블의 type에 eq_ref라고 표시됩니다.

첫번째 테이블의 조건을 통해 읽는 두번째 테이블의 데이터가 반드시 유니크해야 eq_ref방식을 사용합니다.

 

ref, ref_or_null

인덱스의 종류와 상관없이 동등(Equal)조건으로 검색할 때에는 ref방식이 사용됩니다.

eq_ref와 다르게 유니크한 값일 필요가 없으므로 const나 eq_ref보다는 느리지만 여전히 빠른 접근방식입니다.

null조건도 함께 검색한다면 ref_or_null 방식이 사용됩니다.

 

unique_subquery, index_subquery

모두 where조건절에서 사용되는 IN 형태의 쿼리를 위한 접근방식입니다.

unique_subquery는 서브쿼리에서 중복되지 않는 유니크한 값만 반환 할 때 사용되는 방식입니다.

index_subquery는 서브쿼리에서 중복된 결과를 인덱스를 통해 제거 할 수 있을 떄 사용되는 방법입니다.

 

range

인덱스 레인지 스캔을 의미합니다.

일반적인 어플리케이션 쿼리가 가장 많이 사용하게되는 방법입니다.

 

index_merge

2개 이상의 인덱스를 이용하여 각각 검색결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식입니다.

pk컬럼과 인덱스 컬럼을 or 조건처럼 각각 조회하게되는 쿼리일때 

MySQL은 pk를 통해 데이터를 찾고, 인덱스를 통해 데이터를 찾은 후 두 결과를 병합하는 형태로 처리합니다.

 

index

인덱스 풀 스캔을 의미합니다.

아래 방법 중 1+2, 1+3과 같은 상황일 때 사용됩니다.

 

1. range나 const, ref와 같은 접근방법으로 인덱스를 사용하지 못하는 경우

2. 인덱스에 포함된 컬럼만으로 처리 할 수 있는 컬럼일 경우 

3. 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우

 

ALL

테이블 풀 스캔을 의미합니다.

possible_keys 컬럼

옵티마이저가 실행계획을 만들 때 후보로 선정했던 즉 실제 사용되지 않았던 인덱스를 의미합니다.

key 컬럼

옵티마이저가 실제 사용했던 인덱스를 의미합니다.

key의 값이 primary라면 pk를 사용한다는 의미이고, 이 외의 값은 테이블이나 인덱스를 만들었을 때의 그 고유이름이다.

실행계획이 ALL이라면 key컬럼은 NULL로 표시됩니다.

key_lens 컬럼

쿼리를 처리하기 위해 사용했던 인덱스에서 몇개의 인덱스까지 사용되었는지 byte의 길이로 알려줍니다.

name은 varchar(50)이기에 한 문자당 3바이트, 즉 200바이트에 문자열의 길이를 저장하기 위한 1바이트, NULL 허용하기에 1바이트,

key_lens컬럼에서 바이트를 정수로 표현하기 위한 1바이트 총 203바이트가 표시됩니다.

ref 컬럼

참조조건으로 어떤값이 제공되었는지 알려줍니다.

상수값이라면 const, 다른 테이블의 컬럼값이라면 그 테이블과 컬럼명이 표시됩니다.

rows 컬럼

옵티마이저의 실행계획에서 이 처리방식이 얼마나 많은 레코드를 읽고 비교해야하는지 예측해서 산정한 비용입니다.

인덱스를 사용한 조건에만 일치하는 레코드의 예상치이고, 정확한 수치는 아닙니다.

filtered 컬럼

스토리지 엔진에서 가져온 데이터 중 filterd% 만큼이 쿼리조건에 만족한다는 의미입니다. (MySQL엔진에서 처리)

아래와 같은 상황에서는 ix_firstname 인덱스 조건에 일치하는 레코드는 233건이고 그 중 쿼리조건에 맞는 데이터는 16.03%라는 의미입니다.

 

EXTRA 컬럼

성능 관련하여 중요한 내용들이 표시되는 컬럼입니다.

 

const row not found

const조건으로 읽었지만 해당 레코드가 1건도 없을 때 나타납니다.

 

Distinct

distinct, union등이 사용된 쿼리에서 나타납니다.

아래는 두 테이블 전체에서 유니크한 user_id를 가져려는 쿼리입니다.

EXTRA 컬럼에서 users테이블에서는 distinct가 표시되었는데 해당 테이블에서 중복이 제거되어 필요한 유니크한 값들만을 찾았다는 의미입니다.

 

Full scan on NULL key

쿼리에서 컬럼의 값이 NULL인 경우를 찾을 때,

예를 들어 col1 in (SELECT name FROM USER)와 같은 상황에서 나타날 수 있는 내용입니다.

만약 col1이 NULL이게 된다면 그때는 서브쿼리테이블에 대해서 풀 테이블 스캔을 한다는 내용입니다.

 

impossible having, impossible where

작성한 having, where조건이 무조건 FALSE일 때 나타나는 내용입니다.

쿼리 재작성이 필요한건 아닌지 확인할 필요가 있습니다.

 

impossible having, impossible where

작성한 having, where조건이 무조건 FALSE일 때 나타나는 내용입니다.

 

No matching min/max row

집합함수에 일치하는 레코드가 없다는 내용입니다.

 

No matching row in const table

조인에 사용된 테이블에서 const방법으로 접근할때 일치하는 레코드가 없을 때 나타납니다.

 

Using filesort

Order by로 처리되지 못하고 filesort방식으로 정렬했을때 나타납니다.

 

Using index (커버링인덱스)

인덱스만 읽어서 모든 쿼리를 처리 할 수 있을 때 사용됩니다. (SELECT에서 사용하는 컬럼이 모두 인덱스의 컬럼일 때)

 

Using temporary

임시테이블을 사용했을때 나타납니다.

 

Using where

스토리지에서 가져온 데이터를 MySQL 엔진에서 별도의 작업을 했을 때 나타납니다.

스토리지 엔진에서 100건의 데이터를 가져왔다면 filtere처리로 데이터가 MySQL엔진에서 필터링되었다면 Using where조건이 나타납니다.