3.2 SQL 튜닝의 실행 계획

1) 기본 실행 계획 수행
2) 기본 실행 계획 항목 분석
3) 좋고 나쁨을 판단하는 기준
4) 확장된 실행 계획 수행

 

1. 기본 실행 계획 수행

1) 실행계획 확인 명령어

EXPLAIN SQL문;
DESCRIBE SQL문;
DESC SQL문;

2) MySQL, MariaDB 에서 실행 계획

둘 다 EXPLAIN,DESCRIBE,DESC 사용 가능
MariaDB 10.05 버전 이상에서는 UPDATE,DELETE 문에서도 실행 계획 확인 가능

2. 기본 실행 계획 항목 분석

1) id

조인순서를 알 수 있음

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성, 급여.연봉,
       (SELECT MAX(부서번호) 
        FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
FROM 사원, 급여
WHERE 사원.사원번호 = 10001
AND 사원.사원번호 = 급여.사원번호;

* 결과
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY            | 사원  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL                         |
|  1 | PRIMARY            | 급여  | NULL       | ref   | PRIMARY       | PRIMARY | 4       | const |   17 |   100.00 | NULL                         |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Select tables optimized away |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
3 rows in set, 2 warnings (0.01 sec)

• ID 숫자가 작을수록 먼저 수행된것
• ID 숫자가 같으면 두 개 테이블에 조인이 이루어진 것
• 위 sql문을 보면, id 1인 첫번째와 두번째 행 조인 발생, id 2 인 세번째는 가장 마지막에 수행됨
• 위 sql문은 스칼라 서브쿼리 => 참고로 스칼라 서브쿼리는 가장 마지막에 수행됨

2) select_type

SELECT 문이 단순 FROM 절에 위치한것인지, 서브쿼리인지, UNION 절로 묶인 SELECT 문인지 등의 정보를 제공

1> SIMPLE

내부쿼리가 없는 SELECT 문

EXPLAIN 
SELECT * FROM 사원 WHERE 사원번호 = 100000;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | 사원  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

2> PRIMARY

서브쿼리를 감싸는 외부 쿼리에 표시

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성,
       (SELECT MAX(부서번호) 
        FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
FROM 사원
WHERE 사원.사원번호 = 100001;

* 결과
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY            | 사원  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL                         |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Select tables optimized away |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)

• 부서사원_매핑 테이블이 포함된 스칼라 서브쿼리를 감싸는 외부쿼리에 PRIMARY 출력
• 외부 쿼리의 사원 테이블에 우선적으로 접근한다는 의미

 

혹은, UNION 이 포함된 SQL문에서 첫번째로 SELECT 키워드가 작성된 구문에 표시

EXPLAIN
SELECT 사원1.사원번호, 사원1.이름, 사원1.성
FROM 사원 as 사원1
WHERE 사원1.사원번호 = 100001
UNION ALL
SELECT 사원2.사원번호, 사원2.이름, 사원2.성
FROM 사원 as 사원2
WHERE 사원2.사원번호 = 100002;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | 사원1 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | UNION       | 사원2 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

• UNION ALL 구문으로 통합된 SQL문에서 처음 SELECT 구문이 작성된 쿼리가 먼저 접근한다는 의미로 PRIMARY 출력

3> SUBQUERY

독립적으로 수행되는 서브쿼리를 의미.

EXPLAIN
SELECT (SELECT COUNT(*)
        FROM 부서사원_매핑 as 매핑
        ) as 카운트,
       (SELECT MAX(연봉)
        FROM 급여
        ) as 급여;

* 결과
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+----------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL  | NULL          | NULL       | NULL    | NULL |    NULL |     NULL | No tables used |
|  3 | SUBQUERY    | 급여  | NULL       | ALL   | NULL          | NULL       | NULL    | NULL | 2838731 |   100.00 | NULL           |
|  2 | SUBQUERY    | 매핑  | NULL       | index | NULL          | I_부서번호 | 12      | NULL |  331143 |   100.00 | Using index    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

• 스칼라쿼리 사용시
  외부쿼리는 PRIMARY 표시
  해당 스칼라쿼리는 SUBQUERY 표시
• 메인쿼리의 SELECT절에 정의된 부서사원_매핑 테이블과 급여 테이블이 서브쿼리

4> DERIVED

FROM 절에 작성된 서브쿼리라는 의미. FROM 절의 별도 임시테이블인 인라인 뷰

EXPLAIN
SELECT 사원.사원번호, 급여.연봉
FROM 사원,
       (SELECT 사원번호, MAX(연봉) as 연봉
        FROM 급여
        WHERE 사원번호 BETWEEN 10001 AND 20000 ) as 급여
WHERE 사원.사원번호 = 급여.사원번호;

* 결과
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |      1 |   100.00 | NULL        |
|  1 | PRIMARY     | 사원       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |      1 |   100.00 | Using index |
|  2 | DERIVED     | 급여       | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL  | 184756 |   100.00 | Using where |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
3 rows in set, 1 warning (0.05 sec)

• 메인쿼리의 FROM 절에서 급여 테이블의 데이터를 가져오는 인라인 뷰
• 해당 서브쿼리는 id 2인 두번째로 수행되고 있음
• <derived2> 는 뒤에 설명 예정

5> UNION

UNION, UNION ALL 구문으로 합쳐진 SELECT 문에서 첫번째 SELECT 구문을 제외한 이후의 SELECT 구문에 해당

EXPLAIN
SELECT 'M' as 성별, MAX(입사일자) as 입사일자
FROM 사원 as 사원1
WHERE 성별 = 'M'
UNION ALL
SELECT 'F' as 성별, MIN(입사일자) as 입사일자
FROM 사원 as 사원2
WHERE 성별 = 'F';

* 결과
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+-------+
|  1 | PRIMARY     | 사원1 | NULL       | ref  | I_성별_성     | I_성별_성 | 1       | const | 149578 |   100.00 | NULL  |
|  2 | UNION       | 사원2 | NULL       | ref  | I_성별_성     | I_성별_성 | 1       | const | 149578 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+--------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

• UNION 사용 시,
  첫번째 행은 PRIMARY
  두번쨰 행은 UNION

6> UNION RESULT

UNION ALL 이 아닌 UNION 구문으로 SELECT 절을 결합했을때 출력.
UNION은 출력결과에 중복이 없는 유일한 속성을 가지기때문에 각 SELECT 절에서 데이터를 가져와 정렬해 중복 체크하는 과정을 거침.
UNION RESULT 는 별도의 메모리 또는 디스크에 임시 테이블을 만들어 중복을 제거하겠다는 의미로 해석.

EXPLAIN
SELECT 사원_통합.* 
FROM ( 
      SELECT MAX(입사일자) as 입사일자
      FROM 사원 as 사원1
      WHERE 성별 = 'M' 
      
	  UNION 

      SELECT MIN(입사일자) as 입사일자
      FROM 사원 as 사원2
      WHERE 성별 = 'M' 
    ) as 사원_통합; 

* 결과
+------+--------------+------------+------------+------+---------------+-----------+---------+-------+--------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys | key       | key_len | ref   | rows   | filtered | Extra           |
+------+--------------+------------+------------+------+---------------+-----------+---------+-------+--------+----------+-----------------+
|  1   | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  |      2 |   100.00 | NULL            |
|  2   | DERIVED      | 사원1      | NULL       | ref  | I_성별_성     | I_성별_성 | 1       | const | 149578 |   100.00 | NULL            |
|  3   | UNION        | 사원2      | NULL       | ref  | I_성별_성     | I_성별_성 | 1       | const | 149578 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  |   NULL |     NULL | Using temporary |
+------+--------------+------------+------------+------+---------------+-----------+---------+-------+--------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

• 참고로, 튜닝포인트는 : UNION 을 사용하기 보다는,, 중복되지 않는 결과가 보장되는 SQL 문을 작성하고 UNION ALL 을 사용하기

7> DEPENDENT SUBQUERY

UNION, UNION ALL 을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우 중
UNION으로 연결된 단위 쿼리들 중 처음으로 작성한 단위 쿼리에 해당

EXPLAIN
SELECT 관리자.부서번호,
       ( SELECT 사원1.이름
         FROM 사원 AS 사원1
         WHERE 성별= 'F'
         AND 사원1.사원번호 = 관리자.사원번호

         UNION ALL
 
         SELECT 사원2.이름
         FROM 사원 AS 사원2
         WHERE 성별= 'M'
         AND 사원2.사원번호 = 관리자.사원번호
       ) AS 이름
FROM 부서관리자 AS 관리자;

* 결과
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
| id | select_type        | table  | partitions | type   | possible_keys     | key        | key_len | ref                    | rows | filtered | Extra       |
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
|  1 | PRIMARY            | 관리자 | NULL       | index  | NULL              | I_부서번호 | 12      | NULL                   |   24 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | 사원1  | NULL       | eq_ref | PRIMARY,I_성별_성 | PRIMARY    | 4       | tuning.관리자.사원번호 |    1 |    50.00 | Using where |
|  3 | DEPENDENT UNION    | 사원2  | NULL       | eq_ref | PRIMARY,I_성별_성 | PRIMARY    | 4       | tuning.관리자.사원번호 |    1 |    50.00 | Using where |
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
3 rows in set, 3 warnings (0.01 sec)

• 튜닝포인트는 : UNION으로 연결되는 첫번째 단위 쿼리가 독립적으로 수행되지 못하고 메인 테이블로부터 값을 하나씩 공급받음 ( AND 사원1.사원번호 = 관리자.사원번호 )

8> DEPENDENT UNION

UNION, UNION ALL 을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우 중
UNION으로 연결된 단위 쿼리들 중 두번째로 작성한 단위 쿼리에 해당

## 참고로 위 쿼리와 동일!
EXPLAIN
SELECT 관리자.부서번호,
       ( SELECT 사원1.이름
         FROM 사원 AS 사원1
         WHERE 성별= 'F'
         AND 사원1.사원번호 = 관리자.사원번호

         UNION ALL
 
         SELECT 사원2.이름
         FROM 사원 AS 사원2
         WHERE 성별= 'M'
         AND 사원2.사원번호 = 관리자.사원번호
       ) AS 이름
FROM 부서관리자 AS 관리자;

* 결과
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
| id | select_type        | table  | partitions | type   | possible_keys     | key        | key_len | ref                    | rows | filtered | Extra       |
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
|  1 | PRIMARY            | 관리자 | NULL       | index  | NULL              | I_부서번호 | 12      | NULL                   |   24 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | 사원1  | NULL       | eq_ref | PRIMARY,I_성별_성 | PRIMARY    | 4       | tuning.관리자.사원번호 |    1 |    50.00 | Using where |
|  3 | DEPENDENT UNION    | 사원2  | NULL       | eq_ref | PRIMARY,I_성별_성 | PRIMARY    | 4       | tuning.관리자.사원번호 |    1 |    50.00 | Using where |
+----+--------------------+--------+------------+--------+-------------------+------------+---------+------------------------+------+----------+-------------+
3 rows in set, 3 warnings (0.01 sec)

• 튜닝포인트는 : UNION으로 연결되는 첫번째 단위 쿼리가 독립적으로 수행되지 못하고 메인 테이블로부터 값을 하나씩 공급받음 ( AND 사원2.사원번호 = 관리자.사원번호 )

9> UNCACHEABLE SUBQUERY

메모리에 상주해서 재활용되어야할 서브쿼리가 재사용되지 못할때 출력.
해당 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함되거나, RAND()/UUID() 함수 등을 사용해 매번 조회 시마다 결과가 달라지는 경우

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 = (SELECT ROUND(RAND()*1000000));

==> 출력값은 없거나 매번 변경됨

* 결과
+----+----------------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+----------------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY              | 사원  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299157 |   100.00 | Using where    |
|  2 | UNCACHEABLE SUBQUERY | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL |   NULL |     NULL | No tables used |
+----+----------------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)

• RAND() : 0~1 사이의 소수점 기준으로 17자리 숫자를 출력하는 함수
• 매번 달라지는 함수이기때문에 서브쿼리(SELECT ROUND(RAND()*1000000))는 메모리에 캐시되지 못함

10> MATERIALIZED

IN절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤 조인이나 가공작업을 수행할 때 출력.
즉, IN절의 서브쿼리를 임시 테이블로 만들어서 조인 작업 수행

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 IN (SELECT 사원번호 FROM 급여 WHERE 시작일자>'2020-01-01' );

* 결과
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+---------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys       |key                 | key_len | ref                  | rows    | filtered | Extra                    |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+---------+----------+--------------------------+
|  1 | SIMPLE       | 사원        | NULL       | ALL    | PRIMARY             |NULL                | NULL    | NULL                 |  299157 |   100.00 | Using where              |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> |<auto_distinct_key> | 4       | tuning.사원.사원번호 |       1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | 급여        | NULL       | index  | PRIMARY             |I_사용여부          | 4       | NULL                 | 2838731 |    33.33 | Using where; Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

• 서브쿼리(SELECT 사원번호 FROM 급여 WHERE 시작일자>'2020-01-01')가 임시 테이블 생성하고 사원테이블과 조인 수행

3) table

• table 명을 표시하는 항목.
• 서브쿼리나 임시테이블을 만들어서 별도의 작업을 수행할때에는 <subquery#>, <derived#>라고 출력

EXPLAIN
SELECT 사원.사원번호, 급여.연봉
FROM 사원,
      (SELECT 사원번호, MAX(연봉) as 연봉
       FROM 급여
       WHERE 사원번호 BETWEEN 10001 AND 20000 ) as 급여
WHERE 사원.사원번호 = 급여.사원번호;

* 결과
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |      1 |   100.00 | NULL        |
|  1 | PRIMARY     | 사원       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |      1 |   100.00 | Using index |
|  2 | DERIVED     | 급여       | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL  | 184756 |   100.00 | Using where |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+--------+----------+-------------+
3 rows in set, 1 warning (0.03 sec)

• 첫번째 행 : PRIMARY + <derived2>(ID가2인테이블) + ID=1
• 두번째 행 : PRIMARY + ID=1
• 세번째 행 : DERIVED + ID=2
• ID=2인 급여테이블 과 두번째행의 사원테이블이 조인
  =
• FROM 절의 서브쿼리 구문으로 작성된 급여테이블 과 사원테이블이 조인

4) partitions

• 특정 파티션에 선택적으로 접근하는것이 성능 측면에서 유리
• 너무 많은 영역의 파티션에 접근한다면 튜닝 필요

5) type

테이블의 데이터를 어떻게 찾을지에 관한 정보 제공
( 풀스캔,인덱스 스캔 등을 확인 )

1> system

• 테이블에 데이터가 없거나 한개만 있는 경우
  (성능상 최상의 type)

EXPLAIN
SELECT * FROM myisam_테이블;

* 결과
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | myisam_테이블 | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2> const

• 조회되는 데이터가 단 1건일때 출력
  (성능상 매우 유리)
• 고유 인덱스나 기본키 사용해서 1건의 데이터에만 접근

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 = 10001;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | 사원  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3> eq_ref

• 조인이 수행될때 드리븐 테이블(후행테이블)의 데이터에 접근하며 고유 인덱스 또는 기본키로 단 1건의 데이터를 조회
• 드라이빙 테이블 + 드라이븐 테이블(조인키가 드라이븐 테이블에 유일) = (성능상 가장 유리)

EXPLAIN
SELECT 매핑.사원번호, 부서.부서번호, 부서.부서명
FROM 부서사원_매핑 as 매핑,
     부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.사원번호 BETWEEN 100001 AND 100010;

* 결과
+----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys      | key     | key_len | ref                  | rows | filtered | Extra                    |
+----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | 매핑  | NULL       | range  | PRIMARY,I_부서번호 | PRIMARY | 4       | NULL                 |   12 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | 부서  | NULL       | eq_ref | PRIMARY            | PRIMARY | 12      | tuning.매핑.부서번호 |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.01 sec)

• 위 예제 참고
• 매핑 테이블과 부서 테이블 조인
• 첫번째행의 매핑 테이블이 드라이빙 테이블
• 두번째행의 type이 eq_ref 이므로 조인 수행시, 기본키나 고유인덱스를 활용해 1건의 데이터씩만 검색 (성능 good)

4> ref

• 조인을 수행할때 드리븐 테이블(후행 테이블)의 데이터 접근 범위가 2개 이상인 경우
• <,> 사용해 인덱스로 생성된 열을 비교하는 경우
• 기본키나 고유 인덱스를 활용하면 드리븐 테이블에서 2개 이상의 데이터가 검색되거나 유일성이 없는 비고유 인덱스 사용
• 데이터양이 많을때는 접근해야할 데이터 범위가 넓어져 성능 저하의 원인이 될수 있음

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 10001 AND 100000;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 사원  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149578 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

EXPLAIN
SELECT *
FROM 사원
WHERE 입사일자 = '1985-11-21';

* 결과
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | 사원  | NULL       | ref  | I_입사일자    | I_입사일자 | 3       | const |  119 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 드라이빙 테이블인 사원테이블의 사원번호를 조인키로 삼아 직급 테이블의 데이터에 접근
• 여러개의 직급 데이터가 조회됨

5> ref_or_null

• ref 와 유사하지만 IS NULL 구문에 대해 인덱스를 활용하도록 최적화됨
• (특징) MySQL & MariaDB 는 NULL 에 대해서도 인덱스를 활용해서 검색 가능, 이 때 NULL은 가장 앞쪽에 정렬됨
• NULL 데이터양이 적다면, ref_or_null 방식을 활용했을때 효율적임

EXPLAIN
SELECT *
FROM 사원출입기록
WHERE 출입문 IS NULL
OR 출입문 = 'A';

* 결과
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+--------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key      | key_len | ref   | rows   | filtered | Extra                 |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+--------+----------+-----------------------+
|  1 | SIMPLE      | 사원출입기록 | NULL       | ref_or_null | I_출입문      | I_출입문 | 4       | const | 329468 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+----------+---------+-------+--------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

• 위 예제 참고
• 출입문 열에 대해 NULL 또는 A라는 문자 데이터 있는지 검색
• 출입문 열에 대해 인덱스_출입문이라는 이름의 인덱스 존재
  

  

• 출입문 IS NULL 조건문에 대해서도 인덱스를 활용해 데이터를 검색 가능

6> range

• 테이블 내의 연속된 데이터 범위를 조회
• =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN 으로 범위 스캔
• 주어진 데이터 범위 내에서 행 단위로 스캔. 하지만 스캔범위 넓으면 성능 저하

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 10001 AND 100000;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 사원  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149578 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

7> fulltext

• 텍스트 검색을 빠르게 처리하기 위해 full text index 사용해서 데이터 접근

8> index_merge

• 결합된 인덱스들이 동시에 사용되는 유형
• 즉, 특정 테이블에 생성된 2개 이상의 인덱스가 병합돼 동시에 적용( 이때 full text index 는 제외 )

EXPLAIN
SELECT * 
FROM 사원 
WHERE 사원번호 BETWEEN 10001 AND 100000 
AND 입사일자 = '1985-11-21'; 

* 결과
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys      | key                | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | 사원  | NULL       | index_merge | PRIMARY,I_입사일자 | I_입사일자,PRIMARY | 7,4     | NULL |   15 |   100.00 | Using intersect(I_입사일자,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+--------------------+--------------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

• 입사일자 인덱스, 기본키 사원번호 모두 통합해서 사용

9> index

• 인덱스 풀 스캔 (= 물리적인 인덱스 블록을 처음부터 끝까지 훑음 )
• 전체 데이터 스캔이 아닌 인덱스를 전체 스캔! 따라서 테이블 풀 스캔방식 보다는 빠름

EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager';

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | 직급  | NULL       | index | PRIMARY       | PRIMARY | 159     | NULL | 442961 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• Question : 직급명은 결합인덱스 두번째 컬럼인데,, 인덱스를 탈 수 있나,,?
  => Answer : 인덱스 풀스캔은 선두컬럼이 조건절에 없으면 자동선택 실행된다.
• Question : PRIMARY KEY (사원번호,직급명,시작일자) 인데, 인덱스를 사용할 수 있나,,?
  => yes 사용 가능! 인덱스 선두 컬럼인 사원번호가 조건절에 없으므로 Index Range Scan 은 불가능! 하지만 조건절에 직급명이 있으므로 Index Full Scan 을 통해 데이터 검색
  => 최적의 인덱스가 없을 때 차선으로 선택
  => 인덱스 선두 컬럼이 조건절에 없으면 옵티마이저는 먼저 Table Full Scan 방식을 고려합니다. 그런데 대용량 테이블인 경우에 Table Full Scan 방식이 부담이 있다면, 옵티마이저는 인덱스를 활용해서 I/O를 줄이거나 정렬된 결과를 쉽게 얻을 수 있는 방안을 고려할 수 밖에 없습니다.
  => 인덱스가 차지하는 면적은 테이블이 차지하는 면적보다 훨씬 적습니다. 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블 액세스할 수 있다면, Table Full Scan보다 Index Full Scan 방식이 유리합니다. 그래서 옵티마이저는 이런 상황에서 Index Full Scan 방식을 선택합니다.

10> ALL

• 테이블 풀 스캔
• 인덱스가 없거나, 인덱스 활용이 비효율이라고 반단 시 출력됨
• 튜닝포인트 : 인덱스 추가, 기존 인덱스 변경

EXPLAIN 
SELECT * FROM 사원;

* 결과
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | 사원  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299157 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

6) possible_keys

사용할 수 있는 인덱스 목록 출력 (실제 사용한 인덱스 X)

7) key

• 사용한 기본키(PK) 또는 인덱스명
• 어느 인덱스로 데이터 검색했는지

EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager';

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | 직급  | NULL       | index | PRIMARY       | PRIMARY | 159     | NULL | 442961 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• using index : 커버링인덱스(인덱스만으로 조회가능) 사용

8) key_len

• 사용한 인덱스의 바이트 수 의미
• 복합 인덱스라면 인덱스를 모두 더한 값이 나옴
• INT : 단위당 4바이트
• VARCHAR : 단위당 3바이트

9) ref

• 어떤 조건으로 해당 테이블에 액세스 되었는지 알려줌

EXPLAIN
SELECT 사원.사원번호, 직급.직급명
FROM 사원, 직급
WHERE 사원.사원번호 = 직급.사원번호
AND 사원.사원번호 BETWEEN 10001 AND 10100;

* 결과
+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref                  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | 사원  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL                 |  100 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | 직급  | NULL       | ref   | PRIMARY       | PRIMARY | 4       | tuning.사원.사원번호 |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+-------+---------------+---------+---------+----------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

10) rows

• 데이터의 모든 행 수를 나타내는 예측 항목
• 디스크에서 데이터 파일을 읽고 메모리에서 처리해야할 행 수를 예상
• 정확하진 않음! 최종 행 수는 아님!

11) filtered

• 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지를 의미

12) extra

1> Distinct

• 중복이 제거돼 유일한 값 찾을 때 출력
• distinct -> 성능저하
• 튜닝 필요

2> Using where

• where 절의 필터조건 사용해 데이터 추출

3> Using temporary

• 임시테이블을 생성
• 데이터 가져와서 저장 > 정렬 작업 or 중복제거
• Distinct, GROUP BY, ORDER BY 포함 시
• 임시테이블 생성 시 성능 저하
• 튜닝 필요

4> Using index

• 물리적인 데이터 파일 읽지 않고 인덱스만 읽음
• 커버링 인덱스 방식 (인덱스로 구성된 열만 사용할 경우 이 방식 활용)
• 적은 양의 데이터에 접근할 때 효율적

5> Using filesort

• 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업 수행
• 인덱스 사용하지 못할때 정렬을 위해 메모리 영역에 데이터 올리는 것
• 튜닝 필요

6> Using join buffer

• 조인 수행을 위해 중간 데이터 결과를 저장하는 조인 버퍼 사용
• 드라이빙 테이블 데이터 접근 > 결과 조인 버퍼에 담기 > 조인 버퍼와 드라이븐 테이블 사이 일치하는 조인 키값 찾음

7> Using union/Using intersect/Using sort_union

• 인덱스를 어떻게 병합했는지 출력
• Using union : 모두 결합하여 데이터에 접근 (OR 구문 작성 시)
• Using intersect : 인덱스들을 교집합처럼 추출 (AND 구문 작성 시)

8> Using index condition

• 필터 조건을 스토리지 엔진으로 전달해 필터링 작업에 대한 mysql 엔진의 부하를 줄이는 방식

9> Using index for group-by

• group-by, distinct 구문이 포함될때는 정렬될때 출력되는 내용

10> Not exists

• 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력

3. 좋고 나쁨을 판단하는 기준

4. 확장된 실행 계획 수행

MySQL

1> EXPLAIN FORMAT = TRADITIONAL
= EXPLAIN
2> EXPLㅋㅋAIN FORMAT = TREE
트리 형태로 출력
3> EXPLAIN FORMAT = JSON
JSON 형태로 출력
4> EXPLAIN ANALYZE
실제 수행된 소요시간 및 비용을 측정해서 예측 계획 확인

MariaDB

1> EXPLAIN PARTITIONS
파티션으로 설정된 테이블에 대해 파티션 정보 출력
2> EXPLAIN EXTENDED
스토리지 엔진에서 가져온 데이터를 다시 MySQL 엔진에서 추출한 비율인 filtered 열의 값 추가로 출력
3> ANALYZE
실제 수행된 소요시간 및 비용을 측정해서 예측 계획 확인