[SQL 튜닝] 3장 SQL 실행 계획 파헤치기

JUN·2024년 8월 19일

mysql 튜닝 업무에 바로 쓰는 SQL 튜닝

sql

목록 보기

6/6

서론

왜 2장을 건너뛰고 3장을 정리하는가... 그것은 내가 휴가를 다녀왔기 때문이다.
스터디에 뒤처지지 않도록 2장도 조만간 정리해서 올릴 예정.

오래된 책이라 다른 부분이 있는 곳이 있다.
조만간 정오표 사이트에 올려와봐야겠다.

1. 실행 계획

MySQL 의 실행 계획을 확인하는 방법 - `Explain`

MySQL의 옵티마이저는 SQL 쿼리를 실행하기 전에 최적화된 실행 계획을 수립한다.

그 실행 계획을 조회하는 명령어가 아래 명령어다.

EXPLAIN sql문;
DESCRIBE sql문;
DESC sql문;

출력 결과

해당 결과를 보면 확인할 수 있는 속성은

id
select_type
table
type
key

등의 정보가 출력된다.

이러한 정보는 이후 SQL 쿼리의 적합성을 판단하는데 도움이 되므로 하나하나 확인해보자.

`id` 속성

SQL 문이 수행되는 차례를 ID 로 표기한 것. 조인할때에는 똑같은 id 가 출력된다.

여기서 알 수 있는 정보는 두가지다.

ID의 숫자가 작을 수록 먼저 수행된 것이다.
ID가 같은 값이라면 두개의 테이블의 조인이 이루어졌다는 것이다.

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

`select_type` 속성

SQL문을 구성하는 SELECT문의 유형을 출력하는 것.

유형을 출력한다는게 무슨 뜻이냐? 그것은 해당 SELECT 문이

단순히 FROM 절에 위치한 것인지 → SIMPLE

서브쿼리인지 → PRIMARY & SUBQUERY

UNION 절로 묶인 SELECT 문인지 → UNION

등의 정보를 제공한다.

SIMPLE: 서브쿼리나 UNION 없이 단순한 SELECT 문

mysql> 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, 1 warning (0.00 sec)

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성, 급여.연봉
    FROM 사원,
        (SELECT 사원번호, 연봉
            FROM 급여
            WHERE 연봉 > 80000) AS 급여
    WHERE 사원.사원번호 = 급여.사원번호
    AND 사원.사원번호 BETWEEN 10001 AND 10010;
+----+-------------+--------+------------+-------+---------------+---------+---------+----------------------------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+----------------------------+------+----------+-------------+
|  1 | SIMPLE      | 사원   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL                       |   10 |   100.00 | Using where |
|  1 | SIMPLE      | 급여   | NULL       | ref   | PRIMARY       | PRIMARY | 4       | tuning.사원.사원번호       |    9 |    33.33 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+----------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

PRIMARY: 가장 바깥쪽의 SELECT 문을 의미하며, 서브쿼리를 포함할 수 있음

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 절로 묶인 상태에서는 첫번째로 온 쿼리가 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: SELECT 문 안에 포함된 서브쿼리를 의미합니다.

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 | 2838398 |   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 절에 포함된 서브쿼리로부터 파생된 SELECT 문 ( = 인라인 뷰)

EXPLAIN
SELECT 사원.사원번호, 급여.연봉
    FROM 사원,
        (SELECT 사원번호, MAX(연봉) as 연봉
            FROM 급여
            WHERE 사원번호 BETWEEN 10001 AND 20000 GROUP BY 사원번호) as 급여
    WHERE 사원.사원번호 = 급여.사원번호;
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key     | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                   | NULL    | NULL    | NULL                | 184756 |   100.00 | NULL        |
|  1 | PRIMARY     | 사원       | NULL       | eq_ref | PRIMARY                | PRIMARY | 4       | 급여.사원번호       |      1 |   100.00 | Using index |
|  2 | DERIVED     | 급여       | NULL       | range  | PRIMARY,I_사용여부     | PRIMARY | 4       | NULL                | 184756 |   100.00 | Using where |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

UNION: UNION 및 UNION ALL 구문으로 합쳐진 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 | 149534 |   100.00 | NULL  |
|  2 | UNION       | 사원2   | NULL       | ref  | I_성별_성     | I_성별_성    | 1       | const | 149534 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

UNION RESULT: UNION ALL 이 아닌 UNION 으로 SELECT 절을 결합했을 때 출력.

UNION은 출력 결과에 중복이 없는 유일한 속성ㅇ르 가지므로 SELECT 문에서 중복을 체크하는 과정을 과정을 거침

UNION 이 아니라 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 | 149534 |   100.00 | NULL            |
|  3 | UNION        | 사원2       | NULL       | ref  | I_성별_성     | I_성별_성    | 1       | const | 149534 |   100.00 | NULL            |
|  4 | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL         | NULL    | NULL  |   NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+--------------+---------+-------+--------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

DEPENDENT 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.00 sec)

DEPENDENT SUBQUERY: UNION 또는 UNION ALL 로 연결된 서브쿼리의 첫번째 쿼리
DEPENDENT UNION UNION 또는 UNION ALL 로 연결된 서브쿼리의 두번째 쿼리

`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 | SIMPLE      | 사원   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299069 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

MATERIALIZED: 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       | index  | PRIMARY       | PRIMARY | 7       | NULL                       | 2838398 |     3.56 | Using where; Using index; LooseScan |
|  1 | SIMPLE      | 사원   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | tuning.급여.사원번호       |       1 |   100.00 | NULL                                |
+----+-------------+--------+------------+--------+---------------+---------+---------+----------------------------+---------+----------+-------------------------------------+
2 rows in set, 1 warning (0.00 sec)

`table` 속성

테이블을 표시하는 항목
테이블명이나 테이블 별칭을 출력
서브쿼리나 임시 테이블을 작업할 때는 <subquery#> , <derived#> 로 출력됨. (#은 ID)

EXPLAIN
SELECT 사원.사원번호, 급여.연봉
FROM 사원,
    (SELECT 사원번호, MAX(연봉) as 연봉
     FROM 급여
     WHERE 사원번호 BETWEEN 10001 AND 20000 GROUP BY 사원번호) as 급여
WHERE 사원.사원번호 = 급여.사원번호;
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key     | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                   | NULL    | NULL    | NULL                | 184756 |   100.00 | NULL        |
|  1 | PRIMARY     | 사원       | NULL       | eq_ref | PRIMARY                | PRIMARY | 4       | 급여.사원번호       |      1 |   100.00 | Using index |
|  2 | DERIVED     | 급여       | NULL       | range  | PRIMARY,I_사용여부     | PRIMARY | 4       | NULL                | 184756 |   100.00 | Using where |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

해석 : ID 가 동일한 derived2 와 사원 테이블이 JOIN 했음. 여기서 는 ID 가 2인ㅇ 급여 테이블이라는 것을 알 수 있다.

`partitions` 속성

데이터가 저장된 논리적인 영역을 표시하는 항목.
사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 유리하다.
만약 너무 많은 영역의 파티션에 접근하는 것을 출력된다면 파티션 정의를 튜닝해봐야한다.

`type` 속성

테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목
index를 이용할지, 테이블을 처음부터 끝까지 전부 확인할지 등을 해석 가능.

System : 테이블에 데이터가 한개만 있거나 없는 경우. (성능상 최상의 type 이긴 하다.)

mysql> CREATE TABLE myisam_테이블 (
    ->     col1 INT(11) NULL DEFAULT NULL
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO myisam_테이블 VALUES(1);
Query OK, 1 row affected (0.00 sec)

-- 이후
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건일 때.

System 처럼 성능상 매우 유리하다.

인덱스나 기본키로 단 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건의 데이터를 조회하는 방식.

조인이 수행될 때 성능상 가장 유리한 경우임.

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.00 sec

ref : eq_ref 와 유사.. 1대 다 관계로 드라이빙 테이블과 드리븐 테이블의 조인이 수행될때

이때 검색에 드라이빙 테이블의 기본키나 고유 인덱스를 활용하여 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                       |  151 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | 사원   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | tuning.직급.사원번호       |    1 |   100.00 | Using index              |
+----+-------------+--------+------------+--------+---------------+---------+---------+----------------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

해당 부분은 책이 잘못 된 것 같다.

mysql> desc 사원;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| 사원번호     | int           | NO   | PRI | NULL    |       |
| 생년월일     | date          | NO   |     | NULL    |       |
| 이름         | varchar(14)   | NO   |     | NULL    |       |
| 성           | varchar(16)   | NO   |     | NULL    |       |
| 성별         | enum('M','F') | NO   | MUL | NULL    |       |
| 입사일자     | date          | NO   | MUL | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> 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.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.01 sec)

ref_or_null

ref 와 비슷하지만 ISNULL 구문에 인덱스를 활용하도록 최적화한 방식.
MySQL, MariaDB는 null도 인덱스를 활용하여 검색 가능하며 제일 앞쪽에 정렬됨.

이때 테이블에서 검색할 null 의 양이 많다면 ref_or_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.00 sec)

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 | 149534 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

fulltext
- 텍스트 검색을 빠르게 하기 위해 전문 인덱스를 사용하여 데이터에 접근하는 방식

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 |    93.75 | Using intersect(I_입사일자,PRIMARY); Using where     |
+----+-------------+--------+------------+-------------+------------------------+------------------------+---------+------+------+----------+------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

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 | 442486 |    10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

All

테이블을 처음부터 끝까지 읽는 풀스캔 유형

전체 테이블 중 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 | 299069 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

`possible_keys` 속성

옵티마이저가 SQL문을 최적화할때 사용할 수 있는 인덱스 목록 출력.

`key` 속성

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 | 442486 |    10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

`key_len` 속성

사용한 인덱스의 Byte 수를 의미
key_len 의 계산
- INT - 4byte
- VARCHAR - 단위당 UTF-8 의 경우 3byte + 가변 길이일 경우 2byte 추가
- Date - 3byte

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 | 442486 |    10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 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)

해당 테이블을 보면

varchar(50) * 3 + 2 = 152byte

date = 3byte

int = 4byte 로

→ 152 + 3 + 2 = 159 byte 이다.

`ref` 속성

테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 엑세스되는지 알려주는 속성

EXPLAIN
SELECT 사원.사원번호, 직급.직급명
FROM 사원, 직급
WHERE 사원.사원번호 = 직급.사원번호
AND 사원.사원번호 BETWEEN 10001 AND 10180;
+----+-------------+--------+------------+--------+---------------+---------+---------+----------------------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key     | key_len | ref                        | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+---------------+---------+---------+----------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | 직급   | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL                       |  266 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | 사원   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | tuning.직급.사원번호       |    1 |   100.00 | Using index              |
+----+-------------+--------+------------+--------+---------------+---------+---------+----------------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

해당 테이블을 보면 id가 동일함으로 join 이 일어났고 드리븐 테이블인 직급 테이블의 데이터에 접근할 때 직급.사원번호 로 접근한다는 것을 알 수 있음.

`rows` 속성

SQL 문을 수행하고자 접근하는 데이터의 모든 행(row)의 수를 나타내는 예측 항목.
SQL 최종 결과 건 수와 비교해서 rows 의 수가 크게 차이날때는 불필요하게 MySQL 엔진까지 데이터를 많이 가져왓다는 뜻. → SQL 튜닝 필요.

`filtered` 속성

SQL 문을 통해 DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지 의미.
단위는 %

`extra` 속성

SQL 수행에 따른 추가 정보를 보여주는 항목
30여가지 항목이 있으나 자주 만나는 정보만 설명.
Distinct: 중복이 제거되어 유일한 값을 찾을 때 출력되는 정보이다. 중복 제거가 포함되는 distinct 키워드나 union 구문이 포함된 경우 출력된다.
Using where: 실행 계획에서 자주 볼 수 있는 extra 정보이다. WHERE 절의 필터 조건을 사용해 MySQL 엔진으로 가져온 데이터를 추출한다는 의미로 이해할 수 있다.
Using temporary: 데이터의 중간 결과를 저장하고자 임시 테이블을 생성한다는 의미이다. 데이터를 가져와 저장한 뒤에 정렬 작업을 수행하거나 중복을 제거하는 작업 등을 수행한다. 보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 Using temporary 정보가 출력된다.

Using index: 물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 SQL 문의 요청사항을 처리할 수 있는 경우를 의미한다. 커버링 인덱스라고 부르며, 인덱스로 구성된 열만 SQL 문에서 사용할 경우 이 방식을 활용한다.

EXPLAIN
SELECT 직급명
FROM 직급
WHERE 사원번호 = 100000;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | 직급   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Using filesort: 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미이다. 인덱스를 사용하지 못할 때는 정렬을 위해 메모리 영역에 데이터를 올리게 된다.
Using join buffer: 조인을 수행하기 위해 중간 데이터 결과를 저장하는 조인 버퍼를 사용한다는 의미이다.
Using union/Using intersect/Using sort_union: 인덱스를 병합하여 데이터를 접근하는 방식이다. Using union은 OR 구문, Using intersect는 AND 구문, Using sort_union은 OR 구문이 동등 조건이 아닐 때 사용된다.
Using index condition: 인덱스 조건을 스토리지 엔진에서 필터링하여 MySQL 엔진의 부하를 줄이는 방식이다.
Using index condition(BKA): 배치 키 액세스를 사용하는 방식이다.
Using index for group-by: Group by 구문이나 Distinct 구문이 포함될 때 인덱스로 정렬 작업을 수행하는 인덱스 루스 스캔일 때 출력된다.
Not exists: 하나의 일치하는 행을 찾으면 추가로 행을 더 검색하지 않아도 될 때 출력되는 유형이다.

확장된 EXPLAIN

실행계획에 대한 추가정보를 확인하고자 한다면 확장된 실행 계획 명령어로 확인한다.

MySQL

예측된 실행 계획 정보
- EXPLAIN FORMAT = TRADITIONAL Default 실행계획 정보.
- EXPLAIN FORMAT = TREE 트리형태로 추가된 실행 계획 황목 확인
- EXPLAIN FORMAT = JSON JSON 형태로 추가된 실행계획 항목 확인

실행된 실행 계획 정보

EXPLAIN ANALYZE 실제 수행된 소요 시간과 비용을 출력

mysql> EXPLAIN ANALYZE
    -> SELECT *
    -> FROM 사원
    -> WHERE 사원번호 BETWEEN 100001 AND 200000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (`사원`.`사원번호` between 100001 and 200000)  (cost=4021 rows=20080) (actual time=0.0407..8.52 rows=10025 loops=1)
    -> Index range scan on 사원 using PRIMARY over (100001 <= 사원번호 <= 200000)  (cost=4021 rows=20080) (actual time=0.0388..7.49 rows=10025 loops=1)
             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

EXPLAIN PARTITIONS 파티션으로 설정된 테이블에 대해 접근 대상인 파티션 정보를 출력.

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

select_type, type, extra 항목을 기준을 참조하여 튜닝할 쿼리를 찾을 수 있따.

프로파일링

접속한 세션에 한해서만 적용되는 프로파일링 옵션 on

-- 켜졌는지 확인
show variables like 'profiling%';
-- 프로파일링 활성화
set profiling = 'ON';

쿼리문 실행

SELECT 사원번호
FROM 사원
WHERE 사원번호 = 100000;
+--------------+
| 사원번호     |
+--------------+
|       100000 |
+--------------+
1 row in set (0.01 sec)

프로파일링 확인

show profiles;
+----------+------------+-------------------------------------------------------------+
| Query_ID | Duration   | Query                                                       |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00068300 | SELECT 사원번호
FROM 사원
WHERE 사원번호 = 100000           |
|        2 | 0.00043300 | how profiles                                                |
+----------+------------+-------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

프로파일링 상세 번호 확인

show profile for query 2
+---------------+----------+
| Status        | Duration |
+---------------+----------+
| starting      | 0.000350 |
| freeing items | 0.000050 |
| cleaning up   | 0.000033 |
+---------------+----------+
3 rows in set, 1 warning (0.00 sec)

만약 특정 Status에 Duration 값이 높게 나타난다면 문제의 소지가 될 수 있음.

프로파일링 결과 해석하기

선택 가능한 출력 정보
ALL 모든 정보를 표시
BLOCK IO 블록 입력 출력 작업의 횟수를 표시
CONTEXT SWITCHES 자발적 및 비자발적인 컨텍스트 스위치 수를 표시

컨텍스트 스위치란?

컨텍스트 스위치는 CPU가 현재 실행 중인 프로세스의 상태를 저장하고, 다른 프로세스의 상태를 불러와 실행하는 작업입니다. 이는 멀티태스킹을 지원하기 위해 필수적인 과정입니다.
CPU 사용자 및 시스템 CPU 사용 기간 표시
IPC : 보내고 받은 메시지의 수를 표시
PAGE FAULTS 주 페이지 오류 및 부 페이지 오류 수
SOURCE 함수가 발생하는 파일 이름과 행 번호와 함께 소스코드의 함수 이름 표시
SWAPS 스왑 카운트 표시

스왑 카운트란?

스왑 카운트는 운영 체제에서 메모리가 부족할 때, 사용 중인 메모리 페이지를 디스크에 저장하고 다시 불러오는 횟수

추가 정보

MySQL 8.0 버전 기준: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/explain-output.html
MariaDB: https://mariadb.com/kb/en/explain/

[스터디 질문]

서적의 EXPLAIN으로 옵티마이저의 실행 계획을 조회하는 부분에서 예제 쿼리에 대한 결과가 달라 질문드립니다.!

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

해당 쿼리의 결과가 책에서는 ref 로 나오나

이렇게 나와있으나 제 실행 결과에서는 eq_ref 로 나옵니다.

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

그래서 각 테이블을 조회해보니 두 테이블 모두 사원, 직급 테이블의 사원번호가 기본키더군요 (직급 테이블에서는 사원번호가 복합 기본 키의 한 속성입니다..)
그렇다면 1대 다가 되야할텐데 왜 책과 달리 eq_ref 가 나왔을까요
각 테이블 정보도 함께 보여드립니다.

mysql> desc 사원;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| 사원번호     | int           | NO   | PRI | NULL    |       |
| 생년월일     | date          | NO   |     | NULL    |       |
| 이름         | varchar(14)   | NO   |     | NULL    |       |
| 성           | varchar(16)   | NO   |     | NULL    |       |
| 성별         | enum('M','F') | NO   | MUL | NULL    |       |
| 입사일자     | date          | NO   | MUL | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> 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.00 sec)

답변 :
eq_ref가 발생하는 이유:
• 직급 테이블의 사원번호는 복합 기본 키의 일부이므로, 사원 테이블과 조인할 때 각 사원번호에 대해 직급 테이블에서 고유한 행을 찾을 수 있습니다.
• 이런 구조에서 MySQL은 eq_ref로 판단할 가능성이 높습니다. 이는 사원 테이블의 사원번호가 기본 키로, 조인 시 직급 테이블에서 고유한 한 행과 연결된다고 옵티마이저가 판단하기 때문입니다.

-> 아무래도 해당 책이 나온지 조금 됐고 버전 차이도 있어 이러한 결과가 나오나보다.

정오표 관련.

당신이 발견한 내용을 바탕으로, 문제를 좀 더 명확하게 정리해보겠습니다.

문제 상황

MySQL에서 EXPLAIN 명령어를 사용하여 쿼리 실행 계획을 확인할 때, key_len 속성은 사용된 인덱스의 바이트 수를 나타냅니다. 아래는 문제의 예시입니다:

mysql> 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 | 442486 |    10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

이 쿼리에서 key_len이 159로 나타나지만, 실제 테이블 구조를 보면 다음과 같습니다:

mysql> desc 직급;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 사원번호     | int         | NO   | PRI | NULL    |       |
| 직급명       | varchar(50) | NO   | PRI | NULL    |       |
| 시작일자     | date        | NO   | PRI | NULL    |       |
| 종료일자     | date        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

여기서, key_len 값인 159를 계산할 때 책에서는 다음과 같이 설명하고 있습니다.

하지만 직급명에 쓰인 varchar (50+1) * 3 = 153 에 사원번호와 시작일자를 더해도 159byte 가 되지 않습니다. (또한 해당 계산 부분이 조금 이상합니다.)

문제 분석

추가적으로 알아본 결과, varchar 필드의 길이를 계산할 때 가변 길이 문자열에 대한 추가적인 2바이트가 필요하다는 사실을 발견했습니다. 이를 통해 다음과 같은 실험을 수행하였습니다:

mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| test  | varchar(30) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> explain select * from temp where test = '1234';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | temp  | NULL       | const | PRIMARY       | PRIMARY | 92      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

여기서 varchar(30)의 경우, UTF-8 인코딩으로 인한 30 * 3 = 90 바이트에, 추가적인 2바이트의 길이 정보가 더해져 총 92 바이트가 나온다는 것을 확인했습니다.

결론

key_len 속성의 계산에서 varchar 타입 필드의 경우, 가변 길이 정보를 포함하기 위해 추가적인 2바이트가 필요하다.

아무래도 버전과 여러 부분의 차이로 책에 약간의 오류가 있는 것 같다. 스터디때 논의한 해당 부분을 잘 정리해서 책을 쓴 분께 보내야겠다.

JUN

순간은 기록하고 반복은 단순화하자 🚀

이전 포스트

[SQL 튜닝] 3장 SQL 실행 계획 파헤치기

sql

서론

1. 실행 계획

MySQL 의 실행 계획을 확인하는 방법 - `Explain`

`id` 속성

`select_type` 속성

`table` 속성

`partitions` 속성

`type` 속성

`possible_keys` 속성

`key` 속성

`key_len` 속성

`ref` 속성

`rows` 속성

`filtered` 속성

`extra` 속성

확장된 EXPLAIN

MySQL

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

프로파일링

프로파일링 결과 해석하기

서적의 EXPLAIN으로 옵티마이저의 실행 계획을 조회하는 부분에서 예제 쿼리에 대한 결과가 달라 질문드립니다.!

문제 상황

문제 분석

결론

[SQL 튜닝] 1장. MySQL 과 MariaDB 개요

0개의 댓글

[SQL 튜닝] 3장 SQL 실행 계획 파헤치기

sql

서론

1. 실행 계획

MySQL 의 실행 계획을 확인하는 방법 - Explain

id 속성

select_type 속성

table 속성

partitions 속성

type 속성

possible_keys 속성

key 속성

key_len 속성

ref 속성

rows 속성

filtered 속성

extra 속성

확장된 EXPLAIN

MySQL

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

프로파일링

프로파일링 결과 해석하기

서적의 EXPLAIN으로 옵티마이저의 실행 계획을 조회하는 부분에서 예제 쿼리에 대한 결과가 달라 질문드립니다.!

문제 상황

문제 분석

결론

[SQL 튜닝] 1장. MySQL 과 MariaDB 개요

0개의 댓글

MySQL 의 실행 계획을 확인하는 방법 - `Explain`

`id` 속성

`select_type` 속성

`table` 속성

`partitions` 속성

`type` 속성

`possible_keys` 속성

`key` 속성

`key_len` 속성

`ref` 속성

`rows` 속성

`filtered` 속성

`extra` 속성