[MYSQL] PLAN 확인 방법 (예측, 실제계획)

 

사용법

1. EXPLAIN SQL 구문 2. DESCRIBE SQL구문 3. DESC SQL 구문 -. 위 3구문 전부 동일한 결과가 나온다

mysql> explain
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tt    | NULL       | ALL  | test1_test_tab1_idx1 | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

mysql> 
mysql> describe
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tt    | NULL       | ALL  | test1_test_tab1_idx1 | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql> desc
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tt    | NULL       | ALL  | test1_test_tab1_idx1 | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+-------+------------+------+----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4. EXPLAIN Format option 

 

4.1 끝에 '\G' 를 붙이면 아래와 같이 출력된다.

mysql> explain
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt
   partitions: NULL
         type: ALL
possible_keys: test1_test_tab1_idx1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 

4.2 EXPLAIN FORMAT = TREE

mysql> EXPLAIN FORMAT = TREE
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                    |
+------------------------------------------------------------------------------------------------------------+
| -> Filter: (test1.tt.c1 between 1 and 10)  (cost=1.15 rows=9)
    -> Table scan on tt  (cost=1.15 rows=9)
 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

 

4.3 EXPLAIN FORMAT =JSON

mysql> EXPLAIN FORMAT = JSON
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.15"
    },
    "table": {
      "table_name": "tt",
      "access_type": "ALL",
      "possible_keys": [
        "test1_test_tab1_idx1"
      ],
      "rows_examined_per_scan": 9,
      "rows_produced_per_join": 9,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.90",
        "prefix_cost": "1.15",
        "data_read_per_join": "792"
      },
      "used_columns": [
        "c1",
        "c2"
      ],
      "attached_condition": "(`test1`.`tt`.`c1` between 1 and 10)"
    }
  }
} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

 

5. EXPLAIN ANALYZE

-. 위에서 본 실행계획은 실제 SQL문의 실행된 뒤 나온 계획이 아니라 MySQL 서버가 가지고 있는 통계 정보(히스토그램 등)을 활용한 예측된 결과이다. 만약 실제 실행된 소요 시간, 비용을 측정하여 실행 계획 정보를 출력하고 싶다면 ANALYZE 키워드를 사용한다.

mysql> EXPLAIN ANALYZE
    -> SELECT * FROM test1.test_tab1 tt 
    -> WHERE c1 BETWEEN 1 and 10;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (test1.tt.c1 between 1 and 10)  (cost=1.15 rows=9) (actual time=0.013..0.019 rows=9 loops=1)
    -> Table scan on tt  (cost=1.15 rows=9) (actual time=0.012..0.017 rows=9 loops=1)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

 

#참고

https://kukim.tistory.com/128

MySQL 실행 계획과 결과 컬럼 설명 (MySQL EXPLAIN Output Format)

 

구분	설명
(1) id	select 아이디로 SELECT를 구분하는 번호
(2) table	참조하는 테이블
(3) select_type	select에 대한 타입
(4) type	조인 혹은 조회 타입
(5) possible_keys	데이터를 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
(6) key	실제로 사용할 인덱스
(7) key_len	실제로 사용할 인덱스의 길이
(8) ref	Key 안의 인덱스와 비교하는 컬럼(상수)
(9) rows	쿼리 실행 시 조사하는 행 수립
(10) extra	추가 정보

 

(1) id

행이 어떤 SELECT 구문을 나타내는 지를 알려주는 것으로 구문

 

(2) table 

행이 어떤 테이블에 접근하는 지를 보여주는 것으로 대부분의 경우 테이블 이름이나 SQL에서 지정된 별명 같은 값을 나타낸다.

 

(3) select_type

구분	설명
SIMPLE	단순 SELECT (Union 이나 Sub Query 가 없는 SELECT 문)
PRIMARY	Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리
UNION	UNION 쿼리에서 Primary를 제외한 나머지 SELECT
DEPENDENT_UNION	UNION 과 동일하나, 외부쿼리에 의존적임 (값을 공급 받음)
UNION_RESULT	UNION 쿼리의 결과물
SUBQUERY	Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문
DEPENDENT_SUBQUERY	Sub Query 와 동일하나, 외곽쿼리에 의존적임 (값을 공급 받음)
DERIVED	SELECT로 추출된 테이블 (FROM 절 에서의 서브쿼리 또는 Inline View)
UNCACHEABLE SUBQUERY	Sub Query와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리. 외부쿼리에서 공급되는 값이 동이라더라도 Cache된 결과를 사용할 수 없음
UNCACHEABLE UNION	UNION 과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리

 

(4) type

구분	설명
system	테이블에 단 한개의 데이터만 있는 경우
const	SELECT에서 Primary Key 혹은 Unique Key를 살수로 조회하는 경우로 많아야 한 건의 데이터만 있음
eq_ref	조인을 할 때 Primary Key
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우
index_merge	두 개의 인덱스가 병합되어 검색이 이루어지는 경우
unique_subquery	다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01);
index_subquery	unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02);
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음
index	인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함
all	테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함

 

(5) possible_keys

쿼리에서 접근하는 컬럼 들과 사용된 비교 연산자들을 바탕으로 어떤 인덱스를 사용할 수 있는 지를 표시해준다.

 

(6) key

테이블에 접근하는 방법을 최적화 하기 위해 어떤 인덱스를 사용하기로 결정했는 지를 나타낸다.

 

(7) key_len

MySQL이 인덱스에 얼마나 많은 바이트를 사용하고 있는 지를 보여준다. MySQL에서 인덱스에 있는 컬럼들 중 일부만 사용한다면 이 값을 통해 어떤 컬럼들이 사용되는 지를 계산할 수 있다.

 

(8) ref

키 컬럼에 나와 있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 사용되었는 지를 나타낸다.

 

(9) rows

원하는 행을 찾기 위해 얼마나 많은 행을 읽어야 할 지에 대한 예측값을 의미한다.

 

(10) extra

구분	설명
using index	커버링 인덱스라고 하며 인덱스 자료 구조를 이용해서 데이터를 추출
using where	where 조건으로 데이터를 추출. type이 ALL 혹은 Indx 타입과 함께 표현되면 성능이 좋지 않다는 의미
using filesort	데이터 정렬이 필요한 경우로 메모리 혹은 디스크상에서의 정렬을 모두 포함. 결과 데이터가 많은 경우 성능에 직접적인 영향을 줌
using temporary	쿼리 처리 시 내부적으로 temporary table이 사용되는 경우를 의미함