카테고리 없음

MySQL 실행 계획

sanook 2023. 7. 16. 23:20

실행 계획

옵티마이저가 관리자나 사용자의 개입 없이 항상 좋은 실행 계획을 만드는 것은 아님으로 보완할 수 있도록 EXPLIAN 명령으로 옵티마이저가 수립한 실행 계획 확인 가능

10.1 통계 정보

인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해 저장하는 히스토그램 정보가 도입

10.1.1 테이블 및 인덱스 통계 정보

비용 기반 최적화에서 가장 중요한 정보
- 1억 건 레코드 저장된 테이블의 통계 정보가 갱신 되지 않아 10건 미만으로 되어 있으면 옵티마이저가 쿼리 실행시 인덱스 레인지 스캔이 아닌 풀 테이블 스캔하여 0.1초 걸리는 쿼리가 1시간 소요..
다른 DBMS보다 통계 정보의 정확도가 높지 않고 휘발성이 강해 실행 계획 수립시 실제 테이블 데이터를 일부 분석해 통계 정보 보완

10.1.1.1 MySQL 서버의 통계 정보

InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리 가능
- 이전에는 메모리에서 관리
- 특정 이벤트 발생시 자동으로 통계 정보가 갱신되며 인덱스 레인지 스캔으로 잘 처리하던 MySQL 서버가 어느 날 갑자기 풀 테이블 스캔으로 실행
각 테이블의 통계 정보를 테이블로 관리해 재 시작되어도 기존 통계 정보 유지 가능
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES LIKE '%_stats'; +---------------------------+ | Tables_in_mysql (%_stats) | +---------------------------+ | innodb_index_stats | | innodb_table_stats | +---------------------------+ 2 rows in set (0.01 sec)

테이블 생성시 STATS_PERSISTENT 옵션을 통해 테이블 단위 영구적 통계 정보 보관 여부 결정https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html

default
- innodb_stats_persistent 시스템 변수 값으로 결정
  - 이 값은 기본으로 ON(1)
0
- 메모리 저장
1
- 테이블 저장

//변경 가능
mysql> ALTER TABLE t1 STATS_PERSISTENT= 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM innodb_index_stats
    -> WHERE database_name = 'mysql' and table_name = 't1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| mysql         | t1         | DATE_IX    | 2023-07-06 23:35:59 | n_diff_pfx01 |          0 |           1 | date                              |
| mysql         | t1         | DATE_IX    | 2023-07-06 23:35:59 | n_diff_pfx02 |          0 |           1 | date,id                           |
| mysql         | t1         | DATE_IX    | 2023-07-06 23:35:59 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1         | DATE_IX    | 2023-07-06 23:35:59 | size         |          1 |        NULL | Number of pages in the index      |
| mysql         | t1         | PRIMARY    | 2023-07-06 23:35:59 | n_diff_pfx01 |          0 |           1 | id                                |
| mysql         | t1         | PRIMARY    | 2023-07-06 23:35:59 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| mysql         | t1         | PRIMARY    | 2023-07-06 23:35:59 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_table_stats WHERE database_name = 'mysql' and table_name = 't1';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| mysql         | t1         | 2023-07-06 23:35:59 |      0 |                    1 |                        1 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

n_diff_pfx*
- 인덱스가 유니크한 값의 개수
n_leaf_pages
- 인덱스의 리프 노드 페이지 개수
size
- 인덱스 트리의 전체 페이지 개수
n_rows
- 테이블의 전체 레코드 건수
clustered_index_size
- PK의 크기(InnoDB 페이지 개수)
sum_of_other_index_sizes
- PK 제외한 인덱스의 크기(InnoDB 페이지 개수)
- 0일 경우 ANALAZE TABLE {데이터베이스명}.{테이블명}

CREATE TABLE `t1` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime, PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT={DEFAULT | 0 | 1} //영구적인 통계 정보 사용일 경우에만 조회 mysql> select * from innodb_table_stats -> where table_name in ('t1'); +---------------+------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+------------+---------------------+--------+----------------------+--------------------------+ | mysql | t1 | 2023-07-06 23:35:59 | 0 | 1 | 1 | +---------------+------------+---------------------+--------+----------------------+--------------------------+ 1 row in set (0.01 sec)

통계 자동 계산되는지 여부를 제어

innodb_stats_auto_recalc 시스템 설정 변수 값을 OFF로 설정해 통계 정보 자동 갱신 막기 가능
- innodb_stats_auto_recalc가 비활성화되어 있는 경우 인덱싱된 열을 크게 변경한 후 ANALYZE TABLE 문을 실행하여 최적화 통계의 정확성을 보장할 수 있습니다.
STATS_AUTO_RECALC 옵션을 통해 테이블 단위로 통계 정보 자동 수집 여부 조정 가능
- default
  - innodb_stats_auto_recalc
- 1
  - 자동 수집
- 0
  - ANALYZE TABLE 문 수행시에만 테이블 통계 정보 수집

테이블 통계 정보 수집시 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정하는 옵션

innodb_stats_transient_sample_pages
- 통계 정보 수집시 이 값(default : 8)만 임의로 샘플링해서 분석 후 결과 통계 정보로 활용
innodb_stats_persistent_sample_pages
- ANALYZE TABLE 명령 실행시 이 값(default : 20)만 샘플링해서 영구 통계 정보 테이블에 저장하고 활용함
- 영구적 통계 정보 사용시 MySQL 점검이나 사용량이 많지 않은 시간을 이용해 더 정확한 통계 정보 수집 가능하고 이 통계 정보 정확성에 의해 쿼리의 성능이 결정됨으로 높은 값을 설정하면 더 정확한 통계 정보 수집 가능 (너무 높으면 정보 수집 시간 길어지니 주의)

10.1.2 히스토그램

5.7~ 인덱스된 칼럼의 유니크한 값의 개수 정도만 가지고 있어 인덱스의 일부 페이지를 랜덤으로 가져와 참조

8.0~ 데이터 분포도를 참조할 수 있는 히스토그램 정보 활용

10.1.2.1 히스토그램 정보 수집 및 삭제

칼럼 단위로 관리, ANALYZE TABLE … UPDATE HISTOGRAM 명령으로 실행해 수동으로 수집 및 관리

수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버 시작시 딕셔너리의 히스토그램 정보를information_schema.column_statistics 테이블로 로그하여 이 정보를 SELECT 해 참조 가능

mysql> analyze table mysql.t1
    -> update histogram on data, date;
+----------+-----------+----------+-------------------------------------------------+
| Table    | Op        | Msg_type | Msg_text                                        |
+----------+-----------+----------+-------------------------------------------------+
| mysql.t1 | histogram | status   | Histogram statistics created for column 'data'. |
| mysql.t1 | histogram | status   | Histogram statistics created for column 'date'. |
+----------+-----------+----------+-------------------------------------------------+
2 rows in set (0.01 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from column_statistics where schema_name = 'mysql' and table_name ='t1';
+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM                                                                                                                                                                                                              |
+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql       | t1         | data        | {
   "buckets":[
      
   ],
   "data-type":"string",
   "null-values":0.0,
   "collation-id":255,
   "last-updated":"2023-07-07 00:49:19.593879",
   "sampling-rate":1.0,
   "histogram-type":"singleton",
   "number-of-buckets-specified":100
} |
| mysql       | t1         | date        | {
   "buckets":[
      
   ],
   "data-type":"datetime",
   "null-values":0.0,
   "collation-id":8,
   "last-updated":"2023-07-07 00:49:19.594218",
   "sampling-rate":1.0,
   "histogram-type":"singleton",
   "number-of-buckets-specified":100
} |
+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

sampling-rate : 히스토그램 정보를 수집하기 위해 스캔한 페이지 비율로 높을 수록 정확하지만 부하가 높고 시스템 자원을 많이 소모 (histogram_generation_max_mem_size 메모리 크기에 맞게 샘플링)
histogram-type : 히스토그램의 종류를 저장
number-of-buckets-specified : 히스토그램 생성시 설정한 버킷 개수 저장, 일반적으로 default 면 충분

8.0 버전에서는 2종류의 히스토그램 타입이 지원됨

Singleton(싱글톤 히스토그램)
- 칼럼값 개별로 레코드 건수를 관리하는 히스토그램
- Value-Based 히스토그램, 도수 분포
Equi-Height(높이 균형 히스토그램)
- 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램
- Height-Balanced 히스토그램

버킷(Bucket)

히스토그램은 버킷(Bucket) 단위 구분되어 레코드 건수나 칼럼값의 범위가 관리
싱글톤 히스토그램
- 칼럼이 가지는 값별로 버킷이 할당
- 각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개의 값
높이 균형 히스토그램
- 개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당
- 버킷 범위의 시작 값, 마지막 값, 발생 빈도, 각 버킷에 포함된 유니크한 값의 개수

히스토그램 삭제

테이블의 데이터 참조가 아닌 딕셔너리 내용 참조임으로 다른 쿼리 처리 성능에 영향 없이 즉시 완료

쿼리의 실행 계획이 달라 질 수 있음

삭제하지 않고 옵티마이저가 히스토그램을 사용안하게 하려면 아래와 같이 시스템 변수 값 변경시 MySQL 모든 쿼리가 히스토그램을 사용하지 않고 condition_fanout_filter 옵션에 의해 영향받는 다른 최적화 기능이 사용되지 않음으로 주의

SET GLOBAL optimizer_switch='condition_fanout_filter=off';

//현재 커넥션에서 사용되는 쿼리만 히스토그램을 사용하지 않게 설정
SET SESSION optimizer_switch='condition_fanout_filter=off';

//현재 znjfl에서 사용되는 쿼리만 히스토그램을 사용하지 않게 설정
SELECT /*+SET_VAR(optimizer_switch='condition_fanout_filter=off') */ *
FROM ....

mysql> analyze table mysql.t1 -> drop histogram on data, date; +----------+-----------+----------+-------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+-----------+----------+-------------------------------------------------+ | mysql.t1 | histogram | status | Histogram statistics removed for column 'data'. | | mysql.t1 | histogram | status | Histogram statistics removed for column 'date'. | +----------+-----------+----------+-------------------------------------------------+ 2 rows in set (0.00 sec)

10.1.2.2. 히스토그램의 용도

BEFORE

히스토그램 이전 MySQL 서버가 가지는 통계 정보는 테이블의 전체 레코드건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정보로 예측
- 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않음으로 이를 극복 위해 히스토그램을 사용

AFTER

특정 칼럼이 가지는 모든 값에 대한 분포도 정보는 가지지 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 를 가지기 때문에 훨씬 정확한 예측을 할 수 있음
(예시 : 히스토그램 미사용/사용시 explain의 row(조건 일치 값)은 동일할수 있어도 filtered 를 통한 얼마나 일치할 것인가의 예측값은 확연히 달라질 수 있음)
히스토그램이 없으면 옵티마이저는 데이터가 균등하게 분포 되어 있을 것으로 예측하지만 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있음
- 쿼리 성능에 상당한 영향
  - 예: 테이블 두개를 조인하는 순서를 쿼리 힌트를 사용해서 바꾼 경우
    - 드라이빙 테이블에 따라 조인해야 하는 건수가 차이가 있음
    - 조인 조건이나 WHERE 조건의 칼럼이 인덱스 되지 않은 경우 히스토그램이 없다면 옵티마이저는 이 칼럼들의 데이터 분포를 전혀 알지 못하고 실행 계획 수립
    - 옵티마이저 힌트 제거시 옵티마이저는 테이블의 전체 레코드 건수나 크기 와 같은 단순한 정보로 조인의 드라이빙 테이블을 결정함으로 어느 테이블이라도 선택 가능
    ⇒ 쿼리 성능 10배 이상 차이 가능하며, InnoDB 버퍼 풀에 데이터가 존재하지 않아서 디스크 에서 데이터를 읽어야하는 경우라면 더 큰 차이
    - 각 칼럼에 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인 횟수를 줄일지 옵티마이저가 더 정확히 판단

10.1.2.3 히스토그램과 인덱스

MySQL 서버는 쿼리 실행 계획수립시 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악 하고 최종적으로 나은 선택을 한다.

인덱스 다이브(Index Dive)

조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 봄

인덱스 된 칼럼을 검색 조건으로 사용하는 경우 그 칼럼은 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 수집한 정보 활용
- 실제 검색 조건의 대상 값에 대한 샘플링을 실행한 것임으로 히스토그램보다 정확
어느 정도의 비용, (IN 절에 값이 많이 명시된 경우) 실행 계획 수립만으로 상당한 인덱스 다이브를 실행 하고 비용도 커짐

10.1.3 코스트 모델(Cost Model)

MySQL 서버의 쿼리 처리시 작업

디스크로부터 데이터 페이지 읽기
메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
인덱스 키 비교
레코드 평가
메모리 임시 테이블 작업
디스크 임시 테이블 작업

코스트 모델

MySQL 서버는 사용자의 쿼리에 대해 위의 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획 찾음
전체 쿼리의 비용을 계산하는데 필요한 단위 작업의 비용
8.0~ 칼럼의 데이터 분포를위한 히스토그램과 인덱스 별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획 수립에 사용됨
mysql DB에 존재하는 설정값을 사용
- server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
  - row_evaluate_cost
    - 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업으로 값이 증가할 수록 풀 테이블 스캔과 같은 많은 레코드를 처리하는 비용이 높아지고 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리 비용이 낮아짐
  - key_compare_cost
    - 키 값 비교 작업에 필요한 비용으로 증가할수록 레코드 정렬과 같이 키 값 비교 처리가 많은 경우 쿼리의 비용이 높아짐
- engine_cost : 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리
칼럼값 : https://dev.mysql.com/doc/refman/8.0/en/cost-model.html

MySQL 서버에서 각 실행 계획의 계산된 비용(Cost)는 다음과 같이 확인 가능

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    -> FROM t1 WHERE data = 'hello';
+---------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                           |
+---------------------------------------------------------------------------------------------------+
| -> Filter: (t1.`data` = 'hello')  (cost=0.35 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE data = 'hello';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "id",
        "data",
        "date"
      ],
      "attached_condition": "(`mysql`.`t1`.`data` = 'hello')"
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

코스트 모델 튜닝 방법

각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 저비용으로 바뀌는지 파악
(아래의 단위 작업은 모두 비용을 높이는 것 기준으로 설명)
key_compare_cost
- MySQL 서버 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택
row_evaluate_cost
- 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, MySQL 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아짐
disk_temptable_create_cost, disk_temptable_row_cost
- MySQL 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택
memory_temptable_create_cost, memory_temptable_row_cost
- MySQL 옵티마이저는 메모리에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택
io_block_read_cost
- MySQL 서버 옵티마이저는 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재되어 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아짐
memory_block_read_cost
- MySQL 서버는 InnoDB 버퍼 풀에 데이터 페이지가 상대적으로 적게 적재되어 있는 인덱스라도 그 인덱스를 사용할 가능성 높아짐

⇒ MySQL 서버에 적용된 기본 값만으로도 잘 처리 되니 전문적인 지식을 가지지 않으면 건들지 말자

10.2 실행 계획 확인

10.2.1 실행 게획 출력 포맷

8.0~ FORMAT 옵션을 사용해 실행 계획의 표시방법을 JSON, TREE, 단순 테이블 형태로 선택 가능

// 테이블 포맷 표시
mysql> EXPLAIN SELECT * FROM t1 WHERE data = 'hello';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

//트리 포맷 표시
mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    -> FROM t1 WHERE data = 'hello';
+---------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                           |
+---------------------------------------------------------------------------------------------------+
| -> Filter: (t1.`data` = 'hello')  (cost=0.35 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//JSON 포맷 표시
mysql> EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE data = 'hello';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ALL",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "id",
        "data",
        "date"
      ],
      "attached_condition": "(`mysql`.`t1`.`data` = 'hello')"
    }
  }
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

10.2.2 쿼리의 실행 시간 확인

EXPLAIN ANALYZE 로 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인

SHOW PROFILE 로 어느 부분에서 시간이 많이 소요되는지 확인 가능하지만 단계별 시간 정보 확인 불가
✅ EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 다르게 실제 쿼리를 실행하고 사용된 실행 계획과 소요된 시간을 보여줌으로 실행 계획이 나쁜 경우 EXPLAIN 명령으로 선 확인후 튜닝하고 EXPLAIN ANALYZE 사용하자

mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE data = 'hello';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t1.`data` = 'hello')  (cost=0.35 rows=1) (actual time=0.056..0.056 rows=0 loops=1)
    -> Table scan on t1  (cost=0.35 rows=1) (actual time=0.054..0.054 rows=0 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

들여쓰기가 같은 레벨에서는 상단 라인 먼저 실행, 다른 레벨에서는 안쪽 라인이 먼저 실행
actual time={첫번째 레코드를 가져오는데 걸린 평균 시간(밀리초)}…{마지막 레코드를 가져오는데 걸린 평균 시간(밀리초)}
- employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에 일치하는 레코드를 검색하는데 걸린 시간
rows = 10
- employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블의 평균 레코드 건수
loops = 233
- employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블의 레코드를 찾는 작업이 반복된 횟수

10.3 실행 계획 분석

EXPLAIN 명령 실행시 표시되는 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브쿼리로 임시 테이블을 생성한 경우 그 또한 포함)의 개수 만큼 표시됨
UNION과 상관 서브쿼리를 제외하고 실행 순서는 위에서 아래로 표시되고 id 칼럼이 작은 위쪽에 출력된 결과 일수록 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 안쪽(Inner) 또는 나중에 접근한 테이블에 해당

10.3.1 id 칼럼

SELECT 키워드 단위로 구분한 것을 단위(SELECT 쿼리)라 표현

SELECT
FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
WHERE tb1.id = tb2.id;

//단위로 분리
SELECT ... FROM tb_test1;
SELECT FROM tb1, tb_test2 tb2
WHERE tb1.id = tb2.id;

실행 계획 왼쪽에 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값
조인되는 테이블 개수만큼 실행 계획 레코드가 출력되지만 값은 id 값이 부여 됨
✅ id 칼럼이 테이블의 접근 순서를 의미하진 않음으로 EXPLAIN FORMAT=TREE 명령어로 들여쓰기 확인

10.3.2 select_type 칼럼

10.3.2.1 SIMPLE

UNION, 서브쿼리 사용하지 않은 경우
실행계획에서 하나만 존재하며 일반적으로 가장 바깥쪽(OUTER) 쿼리

10.3.2.2 PRIMARY

UNION, 서브쿼리의 가장 바깥쪽(OUTER) 쿼리

10.3.2.3 UNION

UNION으로 결합하는 단위 SELECT 쿼리 중 두 번째 이후 단위 SELECT 쿼리
- 첫번째 단위 SELECT는 UNION 쿼리 결과를 모아 저장하는 임시 테이블(DERIVED)

10.3.2.4 DEPENDENT UNION

UNION, UNION ALL로 집합을 결합하는 쿼리에서 DEPENDENT는 UNION, UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받았음을 의미
- 외부의 employees를 먼저 읽고 서브 쿼리를 실행하여 employees의 칼럼값이 서브 쿼리에 영향
  - 자동으로 UNION에 사용된 WHERE 조건에 e2.emp_no = e1.emp_no 과 e3.emp_no = e1.emp_no 조건이 자동으로 추가됨
mysql> EXPLAIN SELECT * FROM employees e1 WHERE e1.emp_no IN ( //**DEPENDENT SUBQUERY** SELECT e2.emp_no FROM employees e2 WHERE e2.first_name = 'Matt' UNION //**DEPENDENT UNION** SELECT e3.emp_no FROM employees e3 WHERE e3.first_name = 'Matt' );

10.3.5 UNION_RESULT

UNION 결과를 담아 놓는 테이블(임시 테이블로 버퍼링)
- 실제 쿼리에서 단위 쿼리가 아니라 id 존재 안함
- UNION ALL의 경우 임시테이블 사용 안함
- EXPLAIN > TABLE 확인시 <union{id},{id}> 와 같이 UNION한 단위 쿼리의 id 값이 들어감

10.3.2.6 SUBQUERY

select_type 에서 SUBQUERY는 FROM 절 이외에 사용되는 서브 쿼리만 의미

10.3.2.7 DEPENDENT SUBQUERY

서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우(의존적)
외부 쿼리가 먼저 수행후 내부 쿼리(서브쿼리)가 실행되어야 함으로 일반 서브쿼리보다 느릴 경우많음

10.3.2.8 DERIVED

5.6~ 옵티마이저 옵션(optimizer_switch)에 따라 FROM 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화 수행
DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성
- 이 임시 테이블을 파생테이블이라고도 함
- 5.6~ 부터 임시 테이블에도 인덱스 추가 가능
8.0~ 부터 불필요한 서브쿼리는 조인으로 재 작성하지만 ✳️ 옵티마이저는 한계가 있음으로 여전히 최적화된 쿼리를 작성하는 것은 중요하다.

10.3.2.9 DEPENDENT DERIVED

8.0~ 래터럴 조인(LATERAL JOIN) 기능 추가로 FROM 절의 서브쿼리에서도 외부 칼럼 참조 가능
- JOIN 옆에 LATERAL 키워드 추가 필요

10.3.2.10 UNCACHEABLE SUBQUERY

하나의 쿼리 문장에 서브쿼리가 하나만 있어서 한 번만 실행되는 것이 아니므로 조건이 같은 서브쿼리 실행시 이전 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담음
쿼리 캐시나 파생 테이블과는 무관한 기능
SUBQUERY와 캐시 사용 차이
- SUBQUERY는 바깥쪽(Outer) 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요시 캐시된 결과를 이용
- DEPENDENT SUBQUERY 는 의존하는 바깥쪽(Outer) 쿼리의 칼럼 값 단위로 캐시해 사용
서브쿼리에 포함된 요소에 의해 캐시 불가능할 경우 UNCACHEABLE SUBQUERY
- 사용자 변수가 서브쿼리에 사용
- NON-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리내 사용
- UUID()와 RAND() 같이 결과 값이 호출시 마자 달라지는 경우

10.3.2.11 UNCACHEABLE UNION

10.3.2.12 MATERIALIZED

FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화
- 서브쿼리 내용을 임시테이블로 구체화(MATERIALIZED)후 이를 Outer와 조인

10.3.3 table 칼럼

MySQL 서버의 실행 계획은 SELECT 쿼리 기준이 아닌 테이블 기준
- select now() 같이 테이블 없을 경우 EXPLAIN > table은 null
- <>로 둘러싸인 이름은 임시테이블
  - 예: <derived N>의 경우 id 값이 N인 실행 계획으로 부터 만들어진 파생 테이블
  - id가 N이 더 높아도 N(select type : DERIVED)으로 인해 파생됨으로 N이 먼저 실행

10.3.4 partitions 칼럼

8.0~ EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인 가능
PK를 포함하는 모든 유니크 인덱스의 일부인 파티션 키를 사용하는 파티션 테이블을 생성후 테스트
파티션 프루닝(Partition pruning)
- 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정
파티션을 참조하는 쿼리(파티션 키 칼럼을 WHERE 조건으로 가진) 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록을 모아 partitions 칼럼에 표시
- 이때, type은 ALL(풀 테이블 스캔)으로 처리되는데 이는 파티션은 물리적으로 개별 테이블처럼 별도 저장 공간을 가지기 때문이로 partitions에 표시된 파티션만 풀 스캔을 진행한다.

10.3.5 type 칼럼

MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지 나타냄
- 인덱스를 이용해 테이블을 읽었는지 풀 테이블 스캔을 했는지 등등
- ✅ 쿼리 튜닝시 인덱스를 효율적으로 사용하는지 확인하는 것이 중요함으로 반드시 확인
MySQL 매뉴얼에선 type 칼럼을 “조인 타입”으로 소개하고 하나의 테이블로 부터 레코드를 읽는 작업도 조인처럼 처리
- SELECT 쿼리의 테이블 개수와 관계없이 실행 계획 type 칼럼을 “조인 타입”으로 명시
- 각 테이블의 접근 방법으로 해석하자
ALL을 제외하고는 모두 인덱스를 사용하는 접근 방법
- ALL은 풀 테이블 스캔
아래에 접근 방법은 성능이 빠른 순서대로 설명한다

10.3.5.1 system

레코드가 1건만 존재하는 테이블 / 한 건도 존재하지 않는 테이블 참조하는 형태의 접근 방법으로 InnoDB가 아닌 엔진에서 나타남

10.3.5.2 const

테이블의 레코드 건수와 관계 없이 PK나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 방법
- 다른 DBMS에서는 유니크 인덱스 스캔으로 부름
✅ 다중 칼럼으로 구성된 PK나 유니크 키중에서 인덱스의 일부 칼럼 만 조건으로 사용시에는 사용 불가
옵티마이저가 쿼리 최적화시 쿼리를 먼저 실행해 통째로 상수화되어 쿼리 실행기로 전달
- 예를 들어 WHERE first_name = (select …. PK = 1) 인 값을 조회하면 실제 쿼리실행은 WHERE first_name = ‘도토리’

10.3.5.3 eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획으로 처음 읽은 테이블의 컬럼 값을 그 다음에 읽어야할 테이블 의 PK나 유니크 키 칼럼의 검색 조건에 사용시
- 두 번째 이후에 읽은 테이블의 type 칼럼에 eq_ref가 표시
두 번째 이후에 읽히는 테이블을 유니크 키로 검색시 그 유니크 인덱스는 NOT NULL 이여야 하고,
다중 칼럼으로 만들어진 PK나 유니크 인덱스라면 인덱스의 모든 칼럼이 비교 조건에 사용되어야 함
- 조인에서 두 번째 이후 읽은 테이블에서 반드시 1건 이여야 함

10.3.5.4 ref

조인 순서와 관계 없이 사용되고, PK나 유니크 키 등의 제약 조건도 없음
인덱스 종류와 관계 없이 동등(Equal) 조건으로 검색시 사용
ref 타입으로 반환시 반드시 1건이라는 보장이 없어 const나 eq_ref 보다 느리지만 동등 조건으로 비교됨으로 매우 빠름
동등 비교 연산자
- =, <=> (NULL비교 방식만 다를 뿐 = 와 동일)

10.3.5.5 fulltext

MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법

전문 검색 인덱스는 통계 정보가 관리되지 않음
- MATCH (…) AGAINST(…) 구문을 사용하며, 반드시 해당 테이블에 전문 검색용 인덱스(FULLTEXT KEY) 준비되어 있어야 함
전문 검색 조건은 우선 순위가 높음
- 전문 인덱스를 사용하는 조건과 일반 인덱스를 사용하는 조건이 공존시 일반 인덱스의 접근 방법이 const, eq_ref, ref가 아니면 일반적으로 MySQL은 전문 인덱스를 사용하는 조건을 선택
- 경험상 fulltext보다 일반 인덱스를 사용하는 range 접근 방법이 더 빨리 처리됨으로 조건별 성능 확인

10.3.5.6 ref_or_null

ref와 동일 하지만 NULL 비교(IS NULL) 접근 방법

10.3.5.7 unique_subquery

WHERE 조건절에 사용될 수 있는 IN(subquery) 형태를 위한 접근 방법
서브쿼리에서 중복되지 않는 유니크한 값만 반환시 사용
- 중복되지 않음으로 index_subquery와 같이 중복 제거 필요 없음
8.0~ 세미 조인 최적화를 보통 사용

10.3.5.8 index_subquery

IN(subquery | 상수 나열) 연산자 특성상 () 안에 있는 값의 목록 중 중복된 값 먼저 제거 필요
- subquey 의 반환 값에 중복된 값이 존재할 수 있고 이를 인덱스로 중복된 값 제거 가능

10.3.5.9 range

인덱스 레인지 스캔 형태
인덱스를 범위로 검색
- <, >, IS NULL, BETWEEN, IN, LIKE
레코드 양에 따라 차이가 있지만 상당히 빠르며 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능 보장

10.3.5.10 index_merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어 낸 후 그 결과를 병합해서 처리하는 방식
효율적으로 작동 안함
- 여러 인덱스를 읽어야 함으로 range 보다 효율성 떨어짐
- 전문 검색 인덱스 사용 쿼리에서는 적용 안됨
- 2개 이상 집합됨으로 교집합, 합집합, 중복 제거와 같은 부가작업 필요

10.3.5.11 index

인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔
- 풀 테이블 스캔과 비교하는 레코드 건수는 같지만 파일 전체 크기가 작음으로 효율적
사용 조건
- 인덱스 레인지 스캔 불가
- 인덱스에 포함된 칼럼만으로 처리 가능
  - 데이터 파일 읽지 않아도 됨
- 인덱스를 이용해 정렬이나 그루핑 작업 가능
  - 별도의 정렬 작업 피할수 있는 경우

10.3.5.12 ALL

풀 테이블 스캔
리드 어헤드(Read Ahead)
- 풀 테이블 스캔과 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한 번에 많은 페이지를 읽어 드림
- 쿼리를 튜닝한다는 것이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는건 아님
웹 서비스와 같은 온라인 트랜잭션 처리 환경에서는 빠른 응답 불가능 함으로 부적합

10.3.6 possible_keys 칼럼

사용될 법 했던 인덱스 목록임으로 무시

10.3.7 key 칼럼

최종 선택된 실행 계획에서 사용되는 인덱스
PRIMARY의 경우 PK를 사용한다는 의미로 그 외에는 테이블이나 인덱스 생성시 부여한 고유 이름
type이 ALL 일 때와 같이 인덱스 사용 불가시 key 칼럼은 null

10.3.8 key_len 칼럼

다중 칼럼으로 만들어진 인덱스가 실무보다 많음 key_len의 경우 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줌으로 중요

단일 칼럼에서도 같은 지표

dept_no 칼럼이 CHAR(4)임으로 PK의 앞쪽 16 바이트만 유효하게 사용
- 칼럼이 uft8mb4 사용시 1~4바이트 까지 사용됨으로 key_len이 16일 경우 해당 컬럼의 타입이 CHAR(4) 이면 앞쪽 16바이트만 유효하다는 것

mysql> EXPLAIN SELECT * FROM dept_emp WHERE dept_no = 'd005' and emp_no = 10001;
+----+-------------+----------+------------+-------+---------------------------+---------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys             | key     | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------------------+---------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | dept_emp | NULL       | const | PRIMARY,ix_empno_fromdate | PRIMARY | 20      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------------------+---------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

emp_no는 INTEGER 타입으로 4바이트를 차지해 dept_no와 emp_no 모두 적절히 사용됨
DATE 와 같이 3바이트 사용하는 칼럼이 4바이트 일 수 있는데 이는 NULL이 저장 될 수 있는(NULLABLE) 칼럼으로 저장되 이를 판단하는 1바이트를 추가로 더 사용하기 때문

10.3.9 ref 칼럼

참조 조건(Equal 비교 조건)으로 어떤 값이 제공되었는지 보여줌

상수값 : const
다른 테이블의 칼럼 값 : 그 테이블명과 칼럼명
EXPLAIN SELECT * FROM employees e, dept_emp de WHERE e.emp_no = de.emp_no; +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------+--------+----------+-------+ | 1 | SIMPLE | de | NULL | ALL | ix_empno_fromdate | NULL | NULL | NULL | 329534 | 100.00 | NULL | | 1 | SIMPLE | e | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.de.emp_no | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+-------------------+---------+---------+----------------+--------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)

func

참조용으로 사용되는 값을 그대로 사용한 것이 아닌 콜레이션 변환이나 값 자체의 연산을 거쳐 참조

mysql> EXPLAIN SELECT * FROM employees e, dept_emp de WHERE e.emp_no = (de.emp_no -1);
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | de    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 329534 |   100.00 | NULL        |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

사용자가 값을 변환할 때 뿐만아니라 타입이 달라 변환해 조인하는 경우 등 MySQL 서버가 내부적으로 값을 변환시에도 func 출력

10.3.10 rows 칼럼

옵티마이저는 각 조건에 대해 가능한 처리 방식 나열 후 비용을 비교해 최종적으로 하나의 실행 계획 수립

각 처리 방식이 얼마나 많은 레코드를 읽고 비교 하는지 예측해서 비용 산정
대상 테이블에 얼마나 많은 레코드 포함, 각 인덱스 값의 분포도가 어떤지 통계 정보 기준

rows 칼럼 값

실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여주는데 통계 정보를 참조해 옵티마이저가 산출해 낸 예상값이라 부정확
반환하는 레코드의 예측치가 아닌 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크하는지
- 실제 쿼리 결과 반환된 레코드 건수와 일치하지 않을 수 있음

mysql> EXPLAIN SELECT * FROM dept_emp WHERE from_date >= '1985-01-01';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | dept_emp | NULL       | ALL  | ix_fromdate   | NULL | NULL    | NULL | 329534 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT count(*) FROM dept_emp; 
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM dept_emp WHERE from_date >= '2002-07-01';
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | dept_emp | NULL       | range | ix_fromdate   | ix_fromdate | 3       | NULL |  292 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

테이블의 거의 모든 레코드를 비교해야 하는 경우 ix_fromdate 인덱스를 이용한 인덱스 레인지 스캔이 아닌 풀 테이블 스캔을 선택한 것을 알 수 있음

10.3.11 filtered 칼럼

rows 칼럼은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것으로 대부분의 쿼리에서 WHERE 절에 사용되는 모두 인덱스를 사용할 수 있는 것은 아님
조인 이 사용되는 경우 WHERE 절 인덱스 사용 조건 뿐만아닌 사용하지 못하는 조건에 일치하는 레코드 건수를 파악하는 것도 매우 중요

mysql> EXPLAIN
    -> SELECT *
    -> FROM employees e,
    -> salaries s
			//인덱스 사용
    -> WHERE e.first_name = 'Matt'
    -> AND e.hire_date BETWEEN '1990-01-01' AND '1991-01-01'
    -> AND s.emp_no = e.emp_no
    -> AND s.from_date BETWEEN '1990-01-01' AND '1991-01-01'
			//인덱스 사용 	    
		-> AND s.salary BETWEEN 50000 AND 60000;
+----+-------------+-------+------------+------+----------------------------------+--------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                    | key          | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------------+--------------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ref  | PRIMARY,ix_hiredate,ix_firstname | ix_firstname | 58      | const         |  233 |    16.65 | Using where |
|  1 | SIMPLE      | s     | NULL       | ref  | PRIMARY,ix_salary                | PRIMARY      | 4       | test.e.emp_no |    9 |     4.77 | Using where |
+----+-------------+-------+------------+------+----------------------------------+--------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

인덱스 조건에 일치하는 레코드는 233건이며 이 중에서 16.65%만 인덱스를 사용하지 못하는 ’e.hire_date BETWEEN '1990-01-01' AND '1991-01-01' 조건에 일치함
- filtered 칼럼의 값은 필터링되고 남은 레코드의 비율
employees 테이블에서 salaries 테이블로 조인을 수행한 레코드 건수
- 37(233 * 0.1603)건

조인을 반대로 수행시

mysql> EXPLAIN 
SELECT /*+ join_order(s, e) */ * 
FROM employees e, salaries s 
WHERE e.first_name = 'Matt' 
AND e.hire_date BETWEEN '1990-01-01' 
AND '1991-01-01' 
AND s.emp_no = e.emp_no 
AND s.from_date BETWEEN '1990-01-01' 
AND '1991-01-01' AND s.salary BETWEEN 50000 AND 60000;
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+---------------+---------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                    | key     | key_len | ref           | rows    | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+---------------+---------+----------+-------------+
|  1 | SIMPLE      | s     | NULL       | ALL    | PRIMARY,ix_salary                | NULL    | NULL    | NULL          | 2838426 |     4.77 | Using where |
|  1 | SIMPLE      | e     | NULL       | eq_ref | PRIMARY,ix_hiredate,ix_firstname | PRIMARY | 4       | test.s.emp_no |       1 |     5.00 | Using where |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+---------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

→✳️ 옵티마이저는 레코드 건수뿐만 아니라 다른 요소도 감안하지만 조인의 횟수를 줄이고 그과정에서 읽어온 데이터를 저장해둘 메모리 사용량을 줄이기 위해 대상 건수가 적은 테이블을 선행 테이블로 선택할 가능성이 높다

⇒ filter 칼럼에 표시되는 값이 얼마나 정확히 예측될 수 있느냐에 따라 조인 성능이 달라짐

10.3.12 Extra 칼럼

주로 내부적인 처리 알고리즘에 대해 더 깊이 있는 내용 보여줌

10.3.12.1 const row not found

const 접근 방법으로 읽었지만 실제 해당 테이블에 1건도 레코드가 존재하지 않음

10.3.12.2 Deleting all rows

스토리지 엔진이 핸들러 함수 호출로 레코드 건수 만큼이 아닌 한 번에 모든 테이블을 지움

→ deprecated 되었음으로 DELETE WHERE이 아닌 TRUNCATE TABLE

10.3.12.3 Distinct

SELECT COUNT(DISTINCT emp_no) FROM employees; SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;

mysql> EXPLAIN
    -> SELECT DISTINCT d.dept_no
    -> FROM departments d, dept_emp de WHERE de.dept_no = d.dept_no;
+----+-------------+-------+------------+-------+---------------------+-------------+---------+----------------+-------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key         | key_len | ref            | rows  | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+----------------+-------+----------+------------------------------+
|  1 | SIMPLE      | d     | NULL       | index | PRIMARY,ux_deptname | ux_deptname | 162     | NULL           |     9 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | de    | NULL       | ref   | PRIMARY             | PRIMARY     | 16      | test.d.dept_no | 41191 |   100.00 | Using index; Distinct        |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+----------------+-------+----------+------------------------------+
2 rows in set, 1 warning (0.01 sec)

DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 무시하고 꼭 필요한 것만 조인, dept_emp 테이블 에서는 꼭 필요한 레코드만 읽음

10.3.12.4 FirstMatch

9장-2::RealMysql

mysql> EXPLAIN
    -> SELECT *
    -> FROM employees e
    -> WHERE e.first_name = 'Matt'
    -> AND e.emp_no IN (
    -> SELECT t.emp_no FROM titles t
    -> WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
    -> );
+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type | possible_keys        | key          | key_len | ref           | rows | filtered | Extra                                   |
+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ref  | PRIMARY,ix_firstname | ix_firstname | 58      | const         |  233 |   100.00 | NULL                                    |
|  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY              | PRIMARY      | 4       | test.e.emp_no |    1 |    11.11 | Using where; Using index; FirstMatch(e) |
+----+-------------+-------+------------+------+----------------------+--------------+---------+---------------+------+----------+-----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

employees 테이블을 기준으로 titles 테이블에서 첫 번째로 일치하는 한 건만 검색한다.

10.3.12.5 Full scan on NULL key

col1 In (SELECT col2 FROM …) 같은 조건에서 col1이 NULL이면 NULL In (SELECT col2 FROM …) 으로 바뀜
이때, 서브쿼리에 사용된 테이블에 대해 풀 테이블(Full scan)을 해야만 결과를 알아냄

NULL 연산 수행을 위한 조건 비교

서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
서브쿼리가 1건이라도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE

mysql> EXPLAIN SELECT d1.dept_no , NULL IN (SELECT d2.dept_name FROM departments d2) FROM departments d1;
+----+-------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------------------------------------------+
| id | select_type | table | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                           |
+----+-------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------------------------------------------+
|  1 | PRIMARY     | d1    | NULL       | index          | NULL          | ux_deptname | 162     | NULL |    9 |   100.00 | Using index                                     |
|  2 | SUBQUERY    | d2    | NULL       | index_subquery | ux_deptname   | ux_deptname | 162     | func |    1 |   100.00 | Using where; Using index; Full scan on NULL key |
+----+-------------+-------+------------+----------------+---------------+-------------+---------+------+------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

칼럼이 NOT NULL로 정의되지 않았지만 NULL 비교 규칙을 무시해도 되면 col1 is NOT NULL을 추가시 col1이 NULL이면 col1 is NOT NULL이 FALSE가 되어 후속 조건인 col1 IN (SELECT col2 FROM tb_test2) 조건이 실행되지 않음

mysql> SELECT *
    -> FROM tb_test1
    -> WHERE col1 IS NOT NULL
    -> AND col1 IN (SELECT col2 FROM tb_test2);

실제 Full scan on NULL key이 표시되도 IN이나 NOT IN의 왼쪽의 값이 NULL이 없다면 tb_test2 에 대한 풀스캔은 발생하지 않지만, NULL이고 서브쿼리에 WHERE 조건있으면 상당한 성능 이슈

10.3.12.5 Impossible HAVING

HAVING 조건을 만족하는 레코드가 없을 경우

mysql> EXPLAIN
    -> SELECT e.emp_no, COUNT(*) AS cnt
    -> FROM employees e
    -> WHERE e.emp_no = 10001
    -> GROUP BY e.emp_no
    -> HAVING e.emp_no IS NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible HAVING |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
1 row in set, 1 warning (0.00 sec)

쿼리 잘못 작성된 경우가 대부분임으로 쿼리 재 점검

10.3.12.7 Impossible WHERE

WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우

EXPLAIN
    -> SELECT * FROM employees WHERE emp_no IS NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

emp_no 칼럼은 NOT NULL

10.3.12.8 LooseScan

SET optimizer_switch='FirstMatch=off';
EXPLAIN
SELECT *
  FROM departments d    -- 9건
 WHERE d.dept_no IN (SELECT de.dept_no
                       FROM dept_emp de    -- 33만건, (dept_no + emp_no) PK인덱스 
                     )
;
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows   | Extra                  |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+
|    1 | PRIMARY     | de    | index  | PRIMARY       | PRIMARY | 16      | NULL                 | 331143 | Using index; LooseScan |
|    1 | PRIMARY     | d     | eq_ref | PRIMARY       | PRIMARY | 12      | employees.de.dept_no |      1 |                        |
+------+-------------+-------+--------+---------------+---------+---------+----------------------+--------+------------------------+

10.3.13.9 No matching min/max row

MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 존재하지 않음

10.3.2.10 no matching row in const table

조인이 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없음

mysql> EXPLAIN
    -> SELECT *
    -> FROM dept_emp de,
    -> (SELECT emp_no FROM employees WHERE emp_no = 0) tb1
    -> WHERE tb1.emp_no = de.emp_no AND de.dept_no = 'd005';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec

10.3.2.10 no matching row after partition pruning

파티션된 테이블에 대한 UPDATE / DELETE 명령의 실행 계획 표시가 가능한데 이에 대한 대상 레코드가 존재하지 않는 즉, 상대파티션이 존재하지 않을 때

10.3.12.12 No tables used

FROM 절이 없는 쿼리나 FROM DUAL(상수 테이블, 칼럼과 레코드를 각각 1개씩만 가지는 가상의 상수 테이블) 형태의 쿼리 실행

mysql> EXPLAIN SELECT 1 FROM dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

10.3.12.13 Not exists

아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서 표시

안티-조인(Anti-JOIN)

A테이블에는 존재하지만 B테이블에는 없는 값을 조회할 때 NOT IN(subquery)나 NOT EXISTS 연산자 사용

→ 레코드 건수가 많을때 아우터 조인을 사용하면 동일한 처리가 되고 빠른 성능

mysql> EXPLAIN
    -> SELECT *
    -> FROM dept_emp de
    -> LEFT JOIN departments d ON de.dept_no = d.dept_no
    -> WHERE d.dept_no IS NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                                  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | de    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 329534 |   100.00 | NULL                                                   |
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |      9 |    11.11 | Using where; Not exists; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

옵티마이저가 dept_emp 테이블의 레코드를 이용해 departments 테이블 조인시 departments 테이블의 레코드가 존재하는지 아닌지만 판단함
- departments 테이블에 조인 조건에 일치하는 레코드가 여러건이여도 딱 1건만 보고 처리

10.3.12.14 Plan isn’t ready yet

8.0~ 다른 커넥션에서 실행중인 쿼리의 실행 계획 보기

출저 : Real MySQL 8.0 (1권) 개발자와 DBA를 위한 MySQL 실전 가이드 [ 전면개정판 ] 백은빈, 이성욱저 , 위키북스