미스터 역마살

My SQL 실행 계획 (1/2) 본문

Database/MYSQL

My SQL 실행 계획 (1/2)

Mr. YeokMaSsal 2022. 8. 8. 20:41
728x90
반응형

실행 계획 확인하는 방법

실행계획을 확인 하는 키워드로는 EXPLAIN, DESCRIBE, DESC가 있다. 3가지 중 어떤 키워드를 사용해도 실행계획의 결과는 같다.

EXPLAIN [SQL문];
DESCRIBE [SQL문];
DESC [SQL문];

위와 같은 실행 계획에서 이를 해석하는 방법에 대해서 알아보자

실행 계획 각 항목별 해석 방법은 아래와 같다.

 

id

  • 실행 계획을 순서를 표시하는 숫자 id 값이 같은 경우 위에서 아래의 순서로 테이블을 읽으며 조인이 이루어짐
  • id순서가 따라서 테이블 접근을 의미하지는 않음( 예를 들어 id 1,2,3,4 가 있을 경우 1,2,3,4 순서로 테이블 접근은 아니라는 의미)
  • 아래의 경우 첫번째 행과 두번째 행의 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)

 

select_type

  • SQL을 구성하는 SELECT 문의 유형을 출력하는 항목
  • SELECT 문이 단순히 FROM절에 위치한것인지, 서브쿼리인지, UNION절로 묶인 SELECT인지의 정보를 제공함
  • 자주 출력되는 select_type은 아래와 같음

SIMPLE

UNION이나 내부쿼리가 없는 SELECT문이라는걸 의미하는 유형으로 단순한 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)

 

PRIMARY

  • 서브쿼리가 포함된 SQL이 있을때 첫번째 SELECT문에 해당하는 구문에 표시되는 유형
  • 서브쿼리를 감싸는 외부 쿼리이거나 UNION이 포함된 SQL문에서 가장 바깥쪽에 있는 단위 쿼리
  • 예를 들어 다음과 같이 부서사원_매핑 테이블이 포함된 스칼라 서브쿼리가 있을때 외부 쿼리의 사원 테이블에 우선적으로 접근한다는 의미로 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)

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

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)

 

SUBQUERY

  • 독립적으로 수행되는 서브쿼리를 의미함
  • 스칼라서브쿼리인 경우나 where 절에 중첩 서브쿼리 인경우 해당함
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)

 

DERIVED

  • FROM절에 작성된 인라인뷰를 의미함
  • DERIVED는 단위 SELECT 쿼리의 실행결과로 메모리나 디스크에 임시테이블을 생성하는 것을 의미함
  • 쿼리 튜닝시 실행계획에 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.05 sec)

 

UNION

  • UNION 및 UNION ALL 구문으로 합쳐진 SELECT문에서 첫번째 SELECT 구문을 제외한 이후ㅡ이 SELECT 구문에 해당함
  • 첫번째 SELECT는 PRIMARY로 나타냄
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 RESULT

  • UNION ALL이 아닌 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)

 

DEFENDENT SUBQUERY

  • UNION또는 UNION ALL을 사용하는 서브쿼리가 메인 쿼리 테이블에 영향을 받는 경우로 UNION 으로 연결된 단위 쿼리들 중에서처음으로 작성한 단위 쿼리에 해당되는 경우임
  • UNION으로 연결되는 첫번째 단위 쿼리가 독립적으로 수행하지 못하고 메인 테이블로 부터 값을 하나씩 공급받는 구조이므로 성능적으로 불리하여 SQL문의 튜닝 대상이 됨
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)

 

DEFENDENT UNION

  • UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우로 UNION으로 연결된 단위 쿼리 중 첫번째 단위쿼리를 제외하고 두번째 단위 쿼리에 해당하는 경우임
  • DEFENDENT SUBQUERY와 같이 독립적으로 수행하지 못하고 메인 테이블로 부터 값을 하나씩 공급받는 구조이므로 성능적으로 불리하여 SQL문의 튜닝 대상이 됨
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.00 sec)

 

UNCACEABLE SUBQUERY

  • 메모리에 상주하여 재활용되어야 할 서브쿼리가 재사용되지 못할때 출력되는 유형임
  • 이러한 유형은
  1. 해당 서브쿼리 안에 사용자 정의 함수나 사용자 변수가 포함되어 있거나
  2. RAND, UUID() 함수 등을 사용하여 매번 조회시 마다 결과가 달라지는 경우에 해당됨
  • 만약 자주 호출되는 SQL이라면 메모리에 서브쿼리 결과가 상주할 수 있도록 변경하는 방향으로 SQL튜닝을 검토해 볼 수 있음
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)

 

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)

 

 

table

  • 말그대로 테이블명을 표시하는 항목
  • 실행계획 정보에서 테이블 명이나 테이블 별칭을 출력하여 서브쿼리나 임시 테이블을 만들어서 별도의 작업을 수행할때 <subquery#>나 <derived#>라고 출력됨
  • 아래는 첫번째 행의 table 열에는 <derived2>라고 출력되었고 ID값은 1임
  • 두번째 행의 ID값 역시 마찬가지로 1인거승로 보아 첫번째 행의 <derived2>테이블과 두번째 행의 사원 테이블이 조인 했으리라 해석할 수 있음
  • 여기서 <derived2>는 id가 2인 테이블이라는 뜻으로 사실상 급여 테이블을 의미함
  • 즉 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.03 sec)

 

 

partition

  • 실행계획의 부가정보로 데이터가 저장된 논리적인 영역을 표시하는 항목

 

type

  • 테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목
  • 테이블을 처음부터 끝까지 전부 확인할지 아니면 인덱스를 활용하여 바로데이터를 찾아갈지 등을 해석할 수 있음

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)

 

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)

 

eq_ref

  • 조인이 수행될 때 드리븐 테이블의 데이터에 접근하며 고유 인덱스 또는 기본키로 단 1건의 데이터를 조회하는 방식
  • 드라이빙 테이블과의 조인키가 드리븐 테이블에 유일하므로 조인이 수핼될 때 성능상 가장 유리함
  • 아래의 실행 계획은 첫번째 와 두번째 id가 1로 동일한 것으로 보아 부서사원_매핑 테이블과 부서 테이블의 조인을 수행한다는걸 알수있음
  • 먼저 작성된 첫번째 행의 부서사원_매핑 테이블이 드라이빙 테이블이고 이후 출력되는 두번째 행의 부서테이블이 드리븐 테이블인것으로 해석되며 여기서 type 유형이 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)

 

ref

  • 앞에서 설명한 eq_ref 유형과 유사한 방식으로 조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개이상일 경우를 의미함
  • 즉 드라이빙 테이블과 드리븐 테이블이 조인을 수행하면 일대다 관계가 되므로 드라이빙 테이블의 1개값이 드리븐 테이블에서는 2개이상의 데이터로 존재함
  • 이때 기본키나 고유 인덱스를 활용하면 2개의상의 데이터가 검색되거나 유일성이 없는 비고유 인덱스를 사용하게 됨
  • 드리븐 테이블의 데이터 양이 많지 않을때는 성능 저하를 크게 우려하지 않아도 되지만 데이터 양이 많다면 접근해야 할 데이터 범위가 넓어져 성능 저하의 원인이 되는지 확인해야 함
  • 한편으로는 =, <, > 등의 연산자를 인덱스를 사용해 비교할때도 출력됨
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)

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)

 

ref_or_null

  • ref 유형과 유사하지만 IS NULL 구문에 대해 인덱스를 활용하도록 최적화된 방식임
  • Mysql은 NULL에 대해서도 인덱스를 활용하여 검색할 수 있으며 이때 NULL은 가장 앞쪽에 정렬됨
  • 테이블에서 검색할 때 NULL 데이터 양이 적다면 ref_of_null 방식을 활용했을때 효율적인 SQL문이 될것이나, 검색할 NULL 데이터 양이 많다면 SQL 튜닝의 대상이 될 것
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)

 

range

  • 테이블 내의 연속된 데이터 범위를 조회하는 유형으로 =, <, >, ≤, ≥ , IS NULL, ≤>, BETWEEN, IN 연산을 통해 범위 스캔을 수행하는 방식
  • 주어진 데이터 범위 내에서 행단위로 스캔하지만 스캔할 범위가 넓으면 성능 저하의 요인이 될 수 있으므로 SQL 튜닝 검토 대상이 됨
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)

 

fulltext

텍스트 검색을 빠르게 처리하기 위해 전문 인덱스(full texxt index)를 사용하여 데이터에 접근하는 방식

 

index_merge

  • 결합된 인덱스들이 동시에 사용되는 유형
  • 특정 테이블에 생성된 두개이상의 인덱스가 병합되어 동시에 적용됨(전문 인덱스는 제외)
  • 아래의 옞제는 사원 테이블의 입사일자 열에 대한 인덱스_입사일자 인덱스와 사원번호열로 구성된 기본키 모두를 통합해서 사용하리라 예측됨
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)

 

index

  • type항목의 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)

 

 

ALL

  • 테이블 풀 스캔 방식
  • 활용할 수 있는 인덱스가 없거나 인덱스를 활용하는게 오히려 비효율적이라고 옵티마이저가 판단했을때 선택 됨
  • ALL 유형일때는 인덱스를 새로 추가하거나 기존 인덱스를 변경하여 인덱스를 활용하는 방식으로 SQL튜닝을 할 수 있으나 전체 테이블 중 10~20% 이상의 분량의 데이터를 조회할때는 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)

 

 

 

possible_keys

옵티마이저가 SQL을 최적화 하고자 사용할 수 있는 인덱스 목록을 출력하게 되는데 이 경우 실제 사용한 인덱스가 아닌 사용할 수 있는 후보군의 기본키와 인덱스 목록만 보여주므로 SQL 튜닝의 효용성은 없음

 

key

  • 옵티 마이저가 SQL을 최적화 하고자 사용한 기본키(PK)똔느 인덱스 명을 의미함
  • 어느 인덱스로 데이터를 검색했는지 확인 할 수 있으므로 비효율적인 인덱스를 사용했거나 인덱스 자체를 사용하지 않았다면 SQL 튜닝의 대상이 됨
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)

아래의 SQL의 key열을 확인해보면 그 값이 null으로 확인되며 이는 기본키와 인덱스를 전혀 사용하지 않았다는걸 의미함

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)

 

key_len

  • 인덱스를 사용한 바이트(byte)수를 의미함
  • UTF-8 기준으로
    • INTEGER : 4yte
    • VARCHAR : 단위당 3byte
    • CHAR : 단위당 4byte
    • DATE : 3byte
  • NOT NULL이 아닌 컬럼에서는 컬럼의 값이 NULL인지 아닌지 저장하기 위해 1바이트를 추가로 더 사용함
  • 아래의 SQL은 직급 테이블의 데이터에 접근할 때 사원번호와 직급명, 시작일자 열을 결합한 기본키를 사용함
  • key열 확인 결과 PRIMARY로 확인 되며 key_lent은 159byte로 확인됨
  • PK는 사원번호 + 직급명 + 시작일자 로 구성된 결합 인덱스이며 사원번호는 INT로 4byte 이고 직급명은 VARCHAR(50) 으로 (50+1) x 3byte = 155byte
  • 즉 PK에서 사원번호의 4byte와 직급명의 155byte만 사용해서 key_len이 159byte로 출력됨을 알수있음
desc 직급;

* 결과
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 사원번호 | int         | NO   | PRI | NULL    |       |
| 직급명   | varchar(50) | NO   | PRI | NULL    |       |
| 시작일자 | date        | NO   | PRI | NULL    |       |
| 종료일자 | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


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)

 

 

ref

  • reference의 약자로 테이블 조인을 수핼할때 어떤 조건으로 해당테이블에 액세스 되었는지 알려줌
  • 아래 SQL의 결과 2개의 행의 ID가 똑같이 1로 출력되어으므로 사원테이블과 직급테이블의 조인을 수행했음을 알수있음
  • 드리븐 테이블인 직급 테이블의 데이터에 접근하면 사원번호로 데이터를 검색한다는걸 확인 할 수 있음
  • WHERE 절의 사원.사원번호 = 직급.사원번호를 통해서도 알수있는 부분임
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)

 

rows

  • SQL을 수행하고자 정ㅂ근하는 데이터의 모든 행수를 예측하는 항목
  • 디스크에서 데이터 파일을 읽고 메모리에서 처리해야 할 행수를 예상하는 값이고 수시로 변동되는 mysql 통계정보를 참고하여 산출하는 값이므로 수치가 정확하진 않음
  • 최종 출력될 행 수가 아니라는 점에 유의해야함
  • SQL문의 최종 결과 건수와 비교해 row수가 크게 차이 날때는 불필요하게 mysql엔진까지 데이터를 많이 가져왔다는 뜻이므로 SQL 튜닝의 대상이 될 수 있음

 

filtered

  • SQL을 통해 DB엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느정도의 비율로 데이터를 제거했는지를 의미하는 항목
  • 예를 들어 DB엔진으로 100건의 데이터를 가져왔다고 가졍한다면 이후 WHERE 절의 사원번호 BETWEEN 1 AND 10 조건으로 100건의 데이터가 10건으로 필터링 됨
  • 100건 10건으로 필터링 되었으므로 filtered에는 10이라는 정보가 출력됨, 단위는 % 임

 

extra

  • SQL문을 어떻게 수행할 것인지에 관한 추가정볼르 보여주는 항목
  • 30여가지의 항목으로 나타날 수 있음

Distinct

중복이 제거되어 유일한 값을 찾을때 출력되는 정보로써 중복제거가 포함된느 distinct 키워드나 union 구문이 포함된 경우 출력됨

Using where

실행계획에서 자주 볼 수 있는 extra 정보로써 WHERE 절의 필터 조건을 사용해 Mysql 엔진으로 가져온 데이터를 추출할 것이라는 의미임

Using temporary

  • 데이터의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미로써 데이터를 가져와 저장한뒤에 정렬작업을 수행하거나 중복을 제거하는 작업등을 수행함
  • 보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 Using temporary 정보가 출력됨
  • 임시 테이블을 메모리에 생성하거나 메모리 영역을 초과하여 디스크에 임시테이블을 생성하면 Using temporary는 성능 저하의 원인이 될 수 있음
  • 따라서 이 항목의 정보가 출력되면 SQL 튜닝 대상이 될 수 있음

Using index

  • 물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 SQL문의 요청사항을 처리할 수 있는 경우를 의미함
  • 일명 커버링 인덱스 방식이라고 부르며 인덱스로 구성된 열만 SQL에서 사용할 경우 이 방식을 이용하게됨
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 filesort

  • 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미
  • 이미 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요없지만 인덱스를 사용하지 못할때는 정렬을 위해 메모리 영역에 데이터를 올리게 되는데 Using fileosrt는 추가적인 정렬 작업이므로 인덱스를 활용하도록 SQL 튜닝 대상이 될 수 있음

 

Using join buffer

조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인버퍼를 사용한다는 의미

 

Using union / Using intersect / Using sort_union

2개 이상의 인덱스가 병합되어 사용 될 경우 index_merge로 나타나며 extra 정보에 어떻게 병합되었는지 나타남

  • Using union
    인덱스를 합집합 처럼 모두 결합하여 데이터에 접근한다는 뜻으로써 보통 SQL문에 OR 구문으로 작성된 경우 해당됨
  • Using intersect
    인덱스들을 교집합 처럼 추출하는 방식으로 SQL문이 AND 구문으로 작성된 경우 확인 할 수 있음
  • Using sort_union
    Using union과 비슷하지만 WHERE 절의 OR 구문이 동등 조건이 아닐때 확인 할 수 있는 정보

 

Using index condition

  • Mysql엔진에서 인덱스로 생성된 열의 필터조건에 따라 요청된 데이터만 필터링하는 Using where 방식과 달리 필터조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 Mysql엔진의 부하를 줄이는 방식
  • 이는 스토리지 엔진의 데이터 결과를 Mysql엔진으로 전송하는 데이터 양을 줄여 성능 효율을 높일 수 있는 옵티마이저의 최적화 방식임

 

Using index condition(BKA)

type 정보의 Using index condition 유형과 비슷하나 데이터를 검색하기 위해 배치 키 엑세스를 사용하는 방식

 

Using index for group by

SQL에서 group by 구문이나 DISTINCT 구문이 포함될때는 인덱스를 정렬 작업을 수행하여 최적화 하는데 이때 Using index for group by는 인덱스로 정렬 작업을 수행하는 인덱스 루스 스캔일때 출력되는 정보임

 

Not exists

  • 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될때 출려되는 유형
  • 해당 매커니즘은 left outer join이나 righter outer join에서 테이블에 존재하지 않는 데이터를 명시적으로 검색할 때 발생 함

 

extra 정보에 관한 추가항목

https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/explain-output.html

 

8.8.2 EXPLAIN Output Format

EXPLAIN Extra Information The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column. Each item also indicates for JSON-formatted output which

docs.oracle.com

 

 

좋고 나쁨을 판단 하는 기준

select_type

type

extra

 

 

 

728x90
저작자표시

'Database > MYSQL' 카테고리의 다른 글

Mysql 프로파일링  (0) 2022.08.08
My sql 실행 계획 (2/2)  (0) 2022.08.08
Mysql Audit Log  (0) 2022.08.03
Mysql 모니터링 스크립트  (0) 2022.08.01
Mysql 파티셔닝  (0) 2022.08.01
'Database/MYSQL' Related Articles more
Comments