실행계획

진성대·2025년 3월 13일

mysql

SQL

목록 보기

10/10

RealMySQL 책을 읽고 노션에 정리한 글을 옮긴 것입니다.

1. 통계 정보

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

비용기반 최적화에서는 통계정보가 중요하다.
MySQL 은 통계 정보의 정확도가 높지 않고 휘발성이기 때문에 쿼리의 실행계획을 수립할때 실제 테이블의 데이터를 분석해서 통계정보를 보완해서 사용했다.
1.1.1 MySQL 서버의 통계 정보
- MySQL 5.6 버전부터는 통계정보를 휘발성이 아닌 영구적으로 보관할 수 있도록 개선되었다.
  - innodb_index_status테이블과 innodb_table_stats 테이블로 관리할 수 있다.
- 테이블을 생성할 때는 STATS_PERSISTENT 옵션을 통해서 테이블 단위의 영구적인 통계 정보를 보관할 지 말지를 결정할 수 있다.
```
mysql> CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20), PRIMARY KEY(fd1)) 
ENGINE = InnoDB STATS_PERSISTENT={ DEFAULT | 0 | 1 }
```
- STATS_PERSISTENT=0 : 테이블 통계 정보를 5.5 이번방식대로 관리함, 메모리로 관리
- STATS_PERSISTENT=1 : 테이블 통계 정보를 innodb_index_status, innodb_table_stats 테이블로 관리함
- STATS_PERSISTNET=DEFAULT : innodb_stats_persistent 시스템 변수에 의존한다.
- MySQL 5.5 이전 버전에서는 이벤트가 발생하면 자동으로 통계 정보가 갱신되었다.
  - 테이블이 새로 오픈되는 경우
  - 테이블의 레코드가 대량으로 변경되는 경우 (테이블의 전체 레코드 중에서 1/16 정도의 UPDATE 또는 INSERT 나 DELETE 가 실행되는 경우)
  - ANALYZE TABLE 명령이 실행되는 경우
  - SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
  - InnoDB 모니터가 활성화 되는 경우
  - innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우
- 메모리에 있는 통계정보가 자동으로 업데이트 되는 것을 막기위해 innodb_stats_auto_recalc 시스템 변수를 통해 조정할 수 있다.
  - STATS_AUTO_RECALC=1 : 테이블의 통계 정보를 MySQL 5.5 이전의 방식대로 자동 수집한다.
  - STATS_AUTO_RECALC=0 : 테이블의 통계 정보는 ANALYZE TABLE 명령을 실행할 때만 수집한다.
  - STATS_AUTO_RECALC=DEFAULT : 테이블을 생성할 때 별도로 STATS_AUTO_RECALC 옵션을 사용하지 않고 innodb_stats_auto_recalc 시스템 변수의 값으로 설정한다
- innodb_stats_transient_sample_pages
  - 이 시스템 변수의 기본값은 8인데, 이는 자동으로 통계 정보 수집이 실행될 때 8개 페이지만 임의로 실행해서 분석하고 그 결과를 통계 정보로 활용한다.
- innodb_stats_persistent_sample_pages
  - 기본값은 20이며, ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함을 의미한다.

1.2 히스토그램

SQL 5.7 버전 까지 = 인덱스된 칼럼의 유니크한 값의 개수를 통해 옵티마이저가 실행계획 수립 + 인덱스의 일부 페이지를 랜덤으로 가져와 참조
8.0 이후 부터는 칼럼의 데이터 분포도를 참조할 수 있는 히스토그램 사용
1.2.1 히스토그램 정보 수집 및 삭제
- SQL 8.0 버전에서 히스토그램은 자동으로 수집되지 않고 수동으로 ANALYZIE TABLE ... UPDATE HISTOGRAM 명령어를 통해서 수집된다.
- MySQL 8.0 버전에서는 2종류의 히스토그램 타입이 지원된다.
  - Singletom(싱글톤 히스토그램) : 칼럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
  - 싱글톤 히스토그램은유니크한 값이 상대적으로 적을 경우 사용한다.
  - Equi-Height(높이 균형 히스토그램) : 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로, Height-Balanced 히스토그램이라고도 불린다.
  - 레코드 건수 비율이 누적되어 표시된다. 그래프 기울기가 비슷하게 증가하므로 비슷한 값을 가지는것을 알 수 있다.
- information_schema.column_statistics 테이블의 HISTOGRAM 칼럼이 가진 필드들의 의미
  - sampling-rate : 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장한다. 샘플링 비율이 0.35라면 전체 데이터 페이지의 35%를 스캔해서 이 정보가 수집됐다는 것을 의미한다.
  - histogram-type : 히스토그램의 종류를 저장한다.
  - number-of-buckets-specified : 히스토그램을 생성할 때 설정했던 버킷의 개수를 저장한다. 히스토그램을 생성할 때 별도의 버킷의 개수를 지정하지 않았다면 기본으로 100개의 버킷이 사용된다.
```
mysql> ANALYZE TABLE employees.employees DROP HISTOGRAM ON gender, hire_date;
```
- 히스토그램 삭제시 테이블을 참조하지 않고 딕셔너리 내용만 삭제하지만, 히스토그램을 삭제하면 실행계획이 변경될 수 있다.
- 히스토그램을 삭제하지 않고 히스토그램을 사용하지 않게 하는 방법
```
mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=off'; 
```
  1.2.2 히스토그램의 용도
- 히스토그램이 사용되기 이전에는 인덱스의 분포에 상관없이 동일하다고 생각을 하고 실행계획을 작성했다.
- 예를 들어, 테이블 레코드가 1000건이고 어떤 칼럼의 유니크한 값의 개수가 100개 라면 히스토그램을 사용하지 않았을때 동등 비교 검색으로 대략 10개의 레코드가 일치할 것이라 예상한다.
- 하지만 데이터는 특정값을 골고루 분포하지 않는다.
  
  1.2.3 히스토그램과 인덱스
- 인덱스 다이브(Index Dive)
  - 쿼리 옵티마이저가 인덱스를 조사(스캔) 하면서 쿼리 실행 계획을 최적화하는 과정에서 사용되는 기법이다.
  - 옵티마이저는 특정 인덱스를 사용할때 해당 인덱스의 일부만 샘플링(Sampling)하여 데이터 분포를 추정하고, 이를 기반으로 실행비용을 계산한다.
```
mysql> SELECT * FROM employees WHERE first_name='Tonny' AND birth_date 
BETWEEN '1954-01-01' AND '1955-01-01'
```
- 옵티마이저는 이 쿼리를 테이블 풀 스캔을 사용할지 first_name 의 인덱스를 사용할지 고민한다.
- fisrt_name 에 히스토그램이 있다고 하더라도 SQL 8.0 서버에서는 인덱스 다이브를 선호한다.
- 실제 인덱스를 샘플링해서 진행하므로 히스토그램보다 더 정확한 통계를 가지고 있다는 이유때문이다.

1.3 코스트 모델(Cost Model)

SQL 의 코스트를 뽑아내는 과정을 고정된 규칙으로 비용측정하는것이 이제는 비효율적이다. 따라서 능동적으로 상황에 맞게 비용측정을 해야한다.
SQL 5.7버전 부터는 상수로 되어있던 작업 비용을 DBMS 관리자가 조정할 수 있게 개선이 되었다. 하지만 인덱스 되지 않은 칼럼의 히스토그램이나 메모리에 상주중인 페이지의 비용등 비용 계산과 연관된 부분의 정보가 부족한 상태다
SQL 8.0 버전부터는 칼럼의 히스토그램과 각 인덱스별 메모리에 들어갈 페이지 비율이 관리되면서 옵티마이저의 실행 계획 수립에 사용되기 시작했다.
server_cost : 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리 (테이블)
engine_cost : 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리 (테이블)
server_cost 와 engine_cost의 공통 칼럼
- cost_name : 코스트 모델의 각 단위 작업
- default_value : 각 단위 작업의 비용(기본값이며, 이 값은 MySQL 서버 소스코드에 설정된 값)
- cost_value : DBMS 관리자가 설정한 값(이 값이 NULL 이면 MySQL 서버는 default_value 칼럼의 비용 사용)
- last_updated : 단위 작업의 비용이 변경된 시점
- comment : 비용에 대한 추가 설명
engine_cost 의 추가 2개의 칼럼
- engine_name : 비용이 적용된 스토리지 엔진
- device_type : 디스크 타입

2. 실행 계획 확인

MySQL 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

2.1 실행 계획 출력 포맷

2.2 쿼리의 실행 시간 확인

시간정보 확인 EXPLAIN ANALYZE
TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미한다
- 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
- 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
단계별로 실행 시간(actual time), 처리한 레코드 건수(rows), 반복 횟수(loops) 가 표시된다.

Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.98 rows=10)
(actual time=0.007..0.009 rows=10 loops=233)

actual time=0.007 ..0.009: 첫번째 숫자 값은 첫 번째 레코드를 가져오는데 걸린 평균 시간(밀리초). 두 번쨰 숫자 값은 마지막 레코드를 가져오는데 걸린 평균 시간(밀리초)
rows=10 : employees 테이블에서 읽은 emp_no 와 일치하는 salaries 테이블의 평균 레코드 갯수
loops=233 : employees 테이블에 일치한 값을 찾기위해 반복된 갯수 , employees 테이블에서 읽은 emp_no 갯수
EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 다르게 실제 쿼리를 실행해서 실행 결과값을 토대로 보여준다.

3. 실행 계획 분석

3.1 id 칼럼

SELECT 별로 id 값이 부여된다. 여러개의 테이블을 조인하더라도 SELECT 문이 1개이면 id 식별자는 1개 밖에 없다.
id 칼럼이 테이블의 접근 순서를 의미하지 않는다.

mysql> EXPLAIN FORMAT=TREE
			SELECT * FROM dept_emp de
			WHERE de.emp_no = (SELECT e.emp_no FROM employees e WHERE e.first_name='Georgi'
			AND e.last_name='Facello' LIMIT 1);
			
+----+-------------+--------+---------+-------------------+------------+-------------+
| id | select_type | table  | type    | key               | rows       | Extra       |
+----+-------------+--------+---------+-------------------+------------+-------------+
| 1  | PRIMARY     | de     | ref     | ix_empno_fromdate | 1          | Using where |
| 2  | SUBQUERY    | e      | ref     | ix_firstname      | 253        | Using where |
+----+-------------+--------+---------+-------------------+------------+-------------+

-> Filter : (de.emp_no = (select #2)) (cost=1.10 rows=1)
     -> Index lookup on de using ix_empno_fromdate (emp_no=(select #2)) (cost=1.10 rows=1)
     -> Select #2 (subquery in condition; run only once)
          -> Limit: 1 row(s)
              -> Filter: (e.last_name = 'Facello')  (cost=70.49 rows=25)
                   -> Index lookup on e using ix_firstname (first_name='Georgi') (cost=70.49 rows=253)

table 의 id 를 보면 PRIMARY 진행 후 SUBQUERY 가 진행되는거 같지만 FORMAT=TREE 로 보면 e table 을 먼저 읽고 그다음에 de 랑 emp_no 를 찾는 걸 확인할 수 있다.

3.2 select_type 칼럼

3.2.1 SIMPLE

UNION 이나 서브쿼리를 사용하지 않는 단순 SELECT 쿼리일 경우
쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type 이 SIMPLE 인 단위 쿼리는 하나만 존재한다.

3.2.2 PRIMARY

UNION 이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(OUTER) 에 있는 단위 쿼리를 select_type 에서 PRIMARY 로 표시된다.
SIMPLE 과 마찬가지로 select_type 이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재한다.

3.2.3 UNION

UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type 은 UNION 으로 표시된다.
첫 번째는 DERIVED(임시 테이블) 로 select_type 에 표시된다.

mysql> EXPLAIN 
			SELECT * FROM (
				(SELECT emp_no FROM employees e1 LIMIT 10) UNION ALL
				(SELECT emp_no FROM employees e2 LIMIT 10) UNION ALL
				(SELECT emp_no FROM employees e3 LIMIT 10) ) tb;
				
				
+----+-------------+------------+-------+-------------+------+--------+-------------+
| id | select_type | table      | type  | key         | ref  | rows   | Extra       |
+----+-------------+------------+-------+-------------+------+--------+-------------+
| 1  | PRIMARY     | <derived2> | ALL   | NULL        | NULL | 30     | NULL        |
| 2  | DERIVED     | e1         | index | ix_hiredate | NULL | 300252 | Using index |
| 3  | UNION       | e2         | index | ix_hiredate | NULL | 300252 | Using index |
| 4  | UNION       | e3         | index | ix_hiredate | NULL | 300252 | Using index |
+----+-------------+------------+-------+-------------+------+--------+-------------+

3.2.4 DEPENDENT UNION

UNION 이나 UNION ALL 로 결합된 단위 쿼리가 외부 쿼리에 의해 영방을 받을때 DEPENDENT UNION으로 표시한다.

3.2.5 UNION RESULT

UNION 결과를 담아두는 테이블을 의미한다.
UNION ALL 은 SQL 8.0 부터는 임시 테이블을 사용하지 않는다. UNION 은 여전히 임시 테이블 사용

3.2.6 SUBQUERY

FROM 절 이외에서 사용되는 서브쿼리를 의미한다.

💡

중첩된 쿼리 (Nested Query) : SELECT되는 칼럼에 사용된 서브쿼리를 네스티드 쿼리라고 한다.
서브쿼리 (Subquery) : WHERE 절에 사용된 경우에는 일반적으로 그냥 서브쿼리라고 한다.
파생 테이블(Derived Table) : FROM 절에 사용된 서브쿼리를 MySQL에서는 파생 테이블이라고 하며, 일반적으로 RDBMS에서는 인라인 뷰(inline View) 또는 서브 셀렉트(Sub Select)라고 부른다.

3.2.7 DEPENDENT SUBQUERY

서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우

mysql> EXPLAIN
			SELECT e.first_name,
						(SELECT COUNT(*)
						FROM dept_emp de, dept_manager dm
						WHERE dm.dept_no=dept_no AND de.emp_no=e.emp_no) AS cnt
		FROM employees e
		WHERE e.first_name='Matt';
		
		
+-----+--------------------+-------+------+-------------------+------+-------------+
| id  | select_type        | table | type | key               | rows | Extra       |
+-----+--------------------+-------+------+-------------------+------+-------------+
| 1   | PRIMARY            | e     | ref  | ix_fristname      | 233  | Using index |
| 2   | DEPENDENT SUBQUERY | de    | ref  | ix_empno_fromdate |   1  | Using index |
| 3   | DEPENDENT SUBQUERY | dm    | ref  | PRIMARY           |   2  | Using index |
+-----+--------------------+-------+------+-------------------+------+-------------+

안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) Select 쿼리의 칼럼에 의존적이기 때문에 DEPENDET 라는 키워드가 붙는다. 외부쿼리가 먼저 수행된후 내부 쿼리(서브쿼리) 가 실행돼야 하므로 (DEPENDENT 키워드가 없는)일반 서브쿼리보다는 처리속도가 느릴 때가 많다.

3.2.8 DERIVED

FROM 절에 있는 서브쿼리일때(SQL 5.6 부터는 옵티마이저에 판단에 따라 서브쿼리를 외부 쿼리와 JOIN 하는 형태로 최적화 하기도 한다.)
SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는것을 의미한다.

3.2.9 DEPENDENT DERIVED

SQL 8.0 이후 버전 부터는 래터럴 조인 기능을 사용했을 때 FROM 절의 서브쿼리에서 외부 칼럼을 참조할 수 있다.

3.2.10 UNCACHEABLE SUBQUERY

한 쿼리에서 서브쿼리가 한번만 작동하는 것이 아니라 여러번 작동한다. 서브쿼리가 반복 사용될때 캐시에 담아서 결과값을 반환하는데 이때의 캐시는 임시테이블이나 쿼리 캐시가 아니다.
이 캐시를 사용못하는 경우
- 사용자 변수가 서브쿼리에 사용된 경우
- NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
- UUID() 나 RAND() 와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

3.2.11 UNCACHEABLE UNION

UNCACHEABLE SUBQUERY 와 동일하다.

3.2.12 MATERIALIZED

FROM 절이나 IN (Subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

3.3 table 칼럼

TABLE 이름이 NULL 로 FROM 으로 표시되지 않는 테이블도 표시된다.
<> 로 임시테이블인 경우

3.4 partitions 칼럼

파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝(Partition pruning) 이라고 한다.
옵티마이저는 어떤 파티션을 사용할지 파티션이 나눠져있는 것을 통해서 알기 때문에 실행계획에서 나머지 파티션에 어떻게 접근해야할지 고려 안해도 된다.
어떤 파티션을 참조하는지 알려준다.
type 이 ALL 인 이유는 파티션은 물리적으로 개별 테이블처럼 별도의 저장공간을 가지기 때문이다. 따라서 테이블 풀 스캔을 하더라도 파티션 부위만 읽을 수 있는것이다.

3.5 type 칼럼

각 테이블을 어떻게 읽었는지 알 수 있다.

3.5.1 system

테이블의 레코드가 1개나 x 일때 접근하는 방법
InnoDB 스토리지 엔진일때는 나타나지 않고 MyISAM이나 Memory 스토리지 엔진 테이블일 경우의 접근방법
InnoDB 일때는 ALL 로 표현

3.5.2 const

프라이머리 키나 유니크 키를 통해서 1건을 반환할 경우(UNIQUE INDEX SCAN)
프라이머리 키 전부를 사용해서 WHERE 조건을 통해 가져오는게 아니라 일부를 통해서 가져온다면 ref 로 표현될 가능성이 높다.

3.5.3 eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
조인에서 처음 읽는 테이블의 칼럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 칼럼의 검색 조건에 사용할 때 eq_ref 로 표시한다. 두 번째 이후에 읽는 테이블의 type 칼럼에 eq_ref 로 표시된다.

3.5.4 ref

eq_ref 와는 달리 조인 순서나 프라이머리 키, 유니크 키 등의 제약조건도 없다.
동등조건으로 검색할 때는 ref 접근 방법이 사용된다.

mysql> EXPLAIN
	SELECT * FROM dept_emp WHERE dept_no='d005';
	
	
+-----+-------------+-----------+--------+---------+------------+-------------+
| id  | select_type | table     | type   | key     | key_len    | ref         |
+-----+-------------+-----------+--------+---------+------------+-------------+
| 1   | SIMPLE      | dept_emp  | ref    | PRIMARY | 16         | const       |
+-----+-------------+-----------+--------+---------+------------+-------------+

dept_emp 의 프라이머리 키는 (dept_no, emp_no) 인데 이중 일부만 사용했기 때문에 레코드가 1건이라는 보장이 있을 수 없다 따라서 const 로 결정지을 수 없다.
const : 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 칼럼에 대한 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)
eq_ref: 조인에서 첫 번째 읽은 테이블의 칼럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
ref: : 조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 된다.)

3.5.5 fulltext

전문검색(Full-Text_Search) 인덱스를 사용해 레코드를 읽는 접근방법을 의미한다.

3.5.6 ref_or_null

ref 접근 방법에 NULL 비교가 추가된 형태

3.5.7 unique_subquery

WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법이다.
서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때

mysql> EXPLAIN
	SELECT * FROM departments
	WHERE dept_no IN (SELECT dept_no FROM dept_emp WHERE emp_no=10001);
	
+----+--------------------+-------------+-----------------+-------------+---------+
| id | select_type        | table       | type            | key         | key_len |
+----+--------------------+-------------+-----------------+-------------+---------+
| 1  | PRIMARY            | departments | index           | ux_deptname | 162     |
| 2  | DEPENDENT SUBQUERY | dept_emp    | unique_subquery | PRIMARY     | 20      |
+----+--------------------+-------------+-----------------+-------------+---------+

3.5.8 index_subquery

IN(subquery) 에서 subquery가 중복된 값을 반환할 수도 있다.이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을때 index_subquery 접근 방법이 사용된다.
unique_subquery : IN (subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복제거 작업이 필요하지 않음
index_subquery : IN (subquery) 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음

3.5.9 range

“<, >, IS NULL, BETWEEN, IN, LIKE” 등의 연산자를 이용해 범위 인덱스 검색을 할때 사용된다.

3.5.10 index_merge

2개 이상의 인덱스를 이용해 각각의 검색결과를 만들어낸 후, 그 결과를 병합하는 방식이다.
- 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
- 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge 가 적용되지 않는다.
- index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

3.5.11 index

인덱스 풀 스캔
range나 cosnt, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우(즉, 테이블 파일을 읽지 않아도 되는 경우)
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)

3.5.12 ALL

테이블 풀 스캔

3.6 possible_keys 칼럼

옵티마이저가 사용할뻔한 인덱스 목록(그냥 무시하면 됨)

3.7 key 칼럼

최종 선택된 실행 계획에서 사용된 인덱스
PRIMARY KEY 는 PRIMARY 로 표시 나머지는 인덱스 이름으로 표시

3.8 key_len 칼럼

다중 인덱스 칼럼사용시 몇개를 사용했는지 알려줄 수 있다, 바이트 단위로 표시

3.9 ref 칼럼

참조 조건(Equal 비교 조건)으로 어떤 값이 제공됬는지 보여준다.
상숫값이 지정됐으면 const이고 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.
func (Function)라고 표시될때는 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조 됐다는것을 의미한다.

3.10 rows 칼럼

대상 테이블에 얼마나 많은 레코드가 포함되어있는지 알 수 있다.
스토리지 엔진이 가지고 있는 통계정보로 예측하기 때문에 정확하지 않다.
레코드가 반환될 예측치가 아니라 결과값을 반환하기 위해 얼마나 많은 레코드를 읽어야 할지에 대한 정보이다.

3.11 filtered 칼럼

WHERE 조건절을 통해서 필터링되고 남은 레코드의 예측 비율

3.12 Extra 칼럼

3.12.1 const row not found

쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 칼럼에 이 내용이 표식된다.

3.12.2 Deleting all rows

모든 레코드 삭제한다.
WHERE 조건절이 없는 DELETE 문장의 실행계획에 자주 표시된다.

3.12.3 Distinct

mysql> EXPLAIN
			SELECT DISTINCT d.dept_no
			FROM departments d, dept_emp de WHERE de.dept_no=d.dept_no;
			
			
+----+-------------+-------+-------+-------------+------------------------------+
| id | select_type | table | type  | key         | Extra                        |
+----+-------------+-------+-------+-------------+------------------------------+
| 1  | SIMPLE      | d     | index | ux_deptname | Using index; Using temporary |
| 1  | SIMPLE      | de    | ref   | PRIMARY     | Using index; Distinct        |
+----+-------------+-------+-------+-------------+------------------------------+

위 쿼리에서 실제 조회하려는 값은 dept_no 인데, departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no 만 중복 없이 유니크하게 가져오기 위한 쿼리이다. 그래서 두 테이블을 조인해서 그 결과에 다시 DISTINCT 처리를 넣은 것이다.

3.12.4 FirstMatch

세미조인 최적화중 FirstMatch 전략이 사용됐을때

3.12.5 Full scan on NULL key

col1 IN (SELECT col2 FROM …) 같은 조건을 가진 쿼리에서 col1 의 값이 NULL 이된다면 NULL IN (SELECT col2 FROM ...) 로 된다.
- 서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
- 서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔을 해야만 NULL 과 비교 할 수 있다.
즉 NULL 을 만났을때는 서브쿼리에서 NULL 과 비교하기 위해서 풀 테이블 스캔을 진행한다.

mysql> EXPLAIN
			SELECT d.dept_no, 
			NULL IN (SELECT id.dept_name FROM departments d2) FROM departments d1;
			
+-----+-------------+---------+----------------+-------------------------------------------------+
| id  | select_type | table   | type           | Extra                                           |
+-----+-------------+---------+----------------+-------------------------------------------------+
| 1   | PRIMARY     | d1      | index          | Using index                                     |
| 2   | SUBQUERY    | d2      | index_subquery | Using where; Using index; Full scan on NULL key |
+-----+-------------+---------+----------------+-------------------------------------------------+

이런 상황을 방지하기 위해서 col1이 NULL 이 될 수 없다는것을 알려주면 된다.

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

3.12.6 Impossible HAVING

HAVING 을 만족하는 레코드가 없을 때 실행계획에 표시된다.

3.12.7 Impossible WHERE

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

3.12.8 LooseScan

세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용됐을 경우

3.12.9 No matching min/max row

MIN(), MAX() 함수에 만족하는 레코드가 없을 경우

3.12.10 no matching row in const table

WHERE 조건절에 const 로 row를 찾는데 없는 경우

3.12.11 No matching rows after partition pruning

파티션에 UPDATE 나 DELETE 할게 없을 경우

3.12.12 No tables used

FROM 절에 테이블이 없거나 상수 테이블을 의미하는 DUAL 이 사용될때

3.12.13 Not exists

LEFT JOIN
아투어 조인을 이용해 안티-조인을 수행하는 쿼리에서 표시

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 | type      | key       | Extra                       |
+------+-------------+-------+-----------+-----------+-----------------------------+
| 1    | SIMPLE      | de    | ALL       | NULL      | NULL                        |
| 1    | SIMPLE      | d     | eq_ref    | PRIMARY   | Using where; Not exists     |
+------+-------------+-------+-----------+-----------+-----------------------------+

3.12.14 Plan isn’t ready yet

다른 커넥션의 쿼리의 실행계획을 볼 수 있는데 EXPLAIN FOR CONNECT 명령어를 실행했을때 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못했을때

3.12.15 Range checked for each record(index map : N)

인덱스로 되어있지 않는 조건 두개를 각각 가지고 있는 테이블을 비교할때 range 로 비교할지 ALL 로 비교할지 선택을 하게 된다.

3.12.16 Recursive

mysql> WITH RECURSIVE cte (n) AS
			(
				SELECT 1
				UNION ALL
				SELECT n + 1 FROM cte WHERE n < 5
			)
			SELECT * FROM cte;

재귀 쿼리를 사용할때

3.12.17 Rematerialize

래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다.

3.12.18 Select tables optimized away

MIN() 또는 MAX() 만 SELECT 되는 절에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용됐을때

3.12.19 Start temporary, End temporary

세미 조인 최적화 중에서 Duplicate Weed-Out 최적화 전략이 사용됐을때

3.12.20 unique row not found

두 개의 테이블이 각각 유니크(프라이머리 키 포함)칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을때

3.12.21 Using filesort

ORDER BY를 처리할때 적절한 인덱스를 사용하지 못할때 소트 버퍼에서 ORDER BY 진행

3.12.22 Using index(커버링 인덱스)

인덱스만 읽어서 쿼리를 모두 처리할때

3.12.23 Using index condition

인덱스 컨디션 푸시다운 최적화를 사용할때

3.12.24 Using index for group-by

GROUP BY 처리가 인덱스를 이용할때(루스 인덱스 스캔 사용)
- 타이트 인덱스 스캔을 통한 GROUP BY 처리
  - AVG(), SUM(), COUNT() 처럼 조회하려는 값을 전부 읽어야 할때는 루스 인덱스 스캔을 사용할 수 없다. 그렇기 때문에 Using index for group-by 도 출력되지 않는다.
- 루스 인덱스 스캔을 통한 GROUP BY 처리
  - 단일 칼럼으로 구성된 인덱스에서는 구루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다.
  - 다중 칼럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있고 MIN() 이나 MAX() 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 사용가능하다.
WHERE 절에서 사용하는 인덱스에 의해서도 GROUP BY 절의 인덱스 사용 여부가 영향을 받는다는 사실
루스 인덱스 스캔(Loose Index Scan)에서 GROUP BY가 인덱스를 활용하려면 WHERE 조건이 어떻게 되어야 하는가?
루스 인덱스 스캔(Loose Index Scan)은 인덱스를 사용하여 불필요한 행을 읽지 않고 GROUP BY를 최적화하는 방식이다. 그러나 루스 인덱스 스캔을 사용하려면 특정한 WHERE 조건이 필요하다. 즉, 적절한 WHERE 조건이 없으면 MySQL 옵티마이저는 루스 인덱스 스캔 대신 풀 테이블 스캔(Full Table Scan)이나 타이트 인덱스 스캔(Tight Index Scan)을 사용할 수 있다.
루스 인덱스 스캔(Loose Index Scan)이란?
루스 인덱스 스캔(Loose Index Scan)
- 인덱스를 활용하여 GROUP BY를 효율적으로 수행하는 방법
- 전체 데이터를 스캔하지 않고, 그룹별로 필요한 데이터만 인덱스를 통해 빠르게 찾음
- 불필요한 행을 읽지 않기 때문에 성능이 향상됨
  
  루스 인덱스 스캔이 적용되는 경우
- WHERE 조건이 인덱스의 "선두 컬럼"을 포함해야 함
- GROUP BY에 사용되는 컬럼이 "인덱스의 연속적인 컬럼"이어야 함
- WHERE 조건이 "범위 조건(>, <, BETWEEN)"이 아니라, "동등 조건(=, IN)"일 때 최적화 가능
  
  예제: 루스 인덱스 스캔이 적용되는 경우
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')
GROUP BY category;
```
  여기서 category가 인덱스의 선두 컬럼이라면, 루스 인덱스 스캔이 가능하다!
  
  즉, WHERE 조건이 category의 동등 조건(IN 또는 =)이기 때문에, 인덱스를 효율적으로 사용할 수 있음.
  
  WHERE 조건이 어떤 경우여야 루스 인덱스 스캔이 가능한가?
  
  루스 인덱스 스캔이 적용되려면, WHERE 조건이 특정한 패턴을 따라야 한다.
  
  1. WHERE 조건이 인덱스의 "선두 컬럼"을 포함해야 한다.
  
  예제 (적용 가능)
```
sql
복사편집
CREATE INDEX idx_category_price ON products (category, price);
```
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')  --  인덱스 선두 컬럼 사용
GROUP BY category;
```
  category가 인덱스의 첫 번째 컬럼이므로, 루스 인덱스 스캔이 적용될 수 있음.
  
  즉, WHERE category IN (...)이 인덱스의 선두 컬럼에 대한 조건이므로, 불필요한 행을 읽지 않고 최적화할 수 있음.
  
  예제 (적용 불가능)
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE price > 100  --  인덱스의 첫 번째 컬럼이 아님
GROUP BY category;
```
  price는 idx_category_price에서 두 번째 컬럼이므로, 루스 인덱스 스캔이 불가능함.
  
  이 경우, 타이트 인덱스 스캔(Tight Index Scan) 또는 풀 테이블 스캔이 사용될 가능성이 높음.
  
  2. WHERE 조건이 "동등 조건(=, IN)"일 때 루스 인덱스 스캔이 적용됨.
  
  예제 (적용 가능)
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')  -- 동등 조건 (IN)
GROUP BY category;
```
  WHERE 조건이 = 또는 IN이므로, 루스 인덱스 스캔이 적용될 수 있음.
  
  예제 (적용 불가능)
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE category BETWEEN 'A' AND 'C'  --  범위 조건 (BETWEEN)
GROUP BY category;
```
  BETWEEN과 같은 범위 조건이 들어가면, 루스 인덱스 스캔이 불가능해짐.
  
  이 경우, MySQL은 타이트 인덱스 스캔(Tight Index Scan) 또는 풀 테이블 스캔을 사용할 가능성이 높음.
  
  3. GROUP BY 컬럼들이 "인덱스의 연속된 컬럼"이어야 함.
  
  예제 (적용 가능)
```
sql
복사편집
CREATE INDEX idx_category_price ON products (category, price);
```
```
sql
복사편집
SELECT category, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')
GROUP BY category;
```
  GROUP BY 컬럼이 인덱스의 연속된 컬럼이므로, 루스 인덱스 스캔이 가능함.
  
  예제 (적용 불가능)
```
sql
복사편집
SELECT category, brand, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')
GROUP BY category, brand;
```
  brand 컬럼이 인덱스의 연속된 컬럼이 아니면, 루스 인덱스 스캔이 적용되지 않음.
  
  이 경우, 타이트 인덱스 스캔(Tight Index Scan) 또는 풀 테이블 스캔이 사용될 가능성이 높음.
  
  실행 계획(EXPLAIN)으로 루스 인덱스 스캔 확인 방법
  
  EXPLAIN을 사용하여 실행 계획을 확인하면 루스 인덱스 스캔이 적용되었는지 확인할 수 있다.
  
  예제: 실행 계획 확인
```
sql
복사편집
EXPLAIN SELECT category, MIN(price)
FROM products
WHERE category IN ('A', 'B', 'C')
GROUP BY category;
```
  루스 인덱스 스캔이 적용된 경우
```
pgsql
복사편집
id | select_type | table    | type  | key                | key_len | rows  | Extra
-------------------------------------------------------------------------------------
1  | SIMPLE      | products | index | idx_category_price | 50      | 3     | Using index for group-by
```
  Using index for group-by가 나오면 루스 인덱스 스캔이 적용된 것이다.
  
  루스 인덱스 스캔이 적용되지 않은 경우
```
pgsql
복사편집
id | select_type | table    | type  | key     | key_len | rows  | Extra
---------------------------------------------------------------------------------
1  | SIMPLE      | products | index | NULL    | NULL    | 10000 | Using temporary; Using filesort
```
  Using temporary; Using filesort가 나오면 루스 인덱스 스캔이 적용되지 않은 것이다.
  
  즉, MySQL이 임시 테이블과 정렬을 사용하여 GROUP BY를 처리하고 있음.
  
  결론
  
  ✔ 루스 인덱스 스캔을 사용하려면, WHERE 조건이 인덱스의 "선두 컬럼"을 포함해야 한다.
  
  ✔ WHERE 조건이 =, IN 같은 동등 조건이어야 하며, BETWEEN, <, > 같은 범위 조건이면 루스 인덱스 스캔이 불가능하다.
  
  ✔ GROUP BY 컬럼이 인덱스의 연속된 컬럼이어야 한다.
  
  ✔ EXPLAIN 실행 계획에서 Using index for group-by가 나오면 루스 인덱스 스캔이 적용된 것이다.
  
  최종 정리
  
  "루스 인덱스 스캔을 사용하려면 WHERE 조건이 인덱스의 선두 컬럼을 포함하고, 동등 조건(= 또는 IN)이어야 하며, GROUP BY 컬럼이 인덱스의 연속된 컬럼이어야 한다. EXPLAIN 실행 계획에서 Using index for group-by가 나오면 루스 인덱스 스캔이 적용된 것이다!"

3.12.25 Using index for skin scan

인덱스 스킵 스캔 최적화를 사용했을 경우

mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

mysql> EXPLAIN SELECT gender, birth_date FROM employees 
WHERE birth_date>='1965-02-01'

+----+-----------+------+---------------------+----------------------------------------+
| id | table     | type | key                 | Extra                                  |
+----+-----------+------+---------------------+----------------------------------------+
| 1  | employees | range| ix_gender_birthdate | Using where; Using index for skip skan |
+----+-----------+------+---------------------+----------------------------------------+

3.12.26 Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

인덱스가 없는 경우 인덱스가 없는 테이블을 먼저 읽는다. 인덱스가 있는 테이블을 드리븐 테이블로 사용하려고 한다
드리븐 테이블에도 인덱스가 없다면 블록 네스티드 루프 조인이나 해시 조인을 사용한다. (조인 버퍼를 사용 Using join buffer)

3.12.27 Using MRR

MySQL 엔진에서 InnoDB 스토리지로 레코드를 하나씩 보내는건 효율이 좋지 않아서 MySQL 엔진은 여러 개의 키 값을 한번에 스토리지 엔진으로 전달해서 대량의 값을 조인하거나 처리한다.
MRR 과 배치 키 액세스 참고

3.12.28 Using sort_union(…), Using union(…), Using interect(…)

index merge ( 정렬 합집합, 합집합, 교집합)

3.12.29 Using temporary

임시 테이블 사용시
FROM 절에 서브쿼리가 있을 시
COUNT(DISTINCT column1 를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우
UNION 이나 UNION DISTINCT 를 사용할 경우
인덱스를 사용하지 못하는 정렬 작업

3.12.30 Using where

MySQL 엔진 레이어에서 별도의 가공을 해서 필터링(여과) 작업을처리한 경우일때
스토리지 엔진에서 읽은 데이터를 MySQL 엔진에서 여과 없이 그대로 반환하면 Using where 로 표시 되지않는다.

mysql> EXPLAIN
		SELECT *
		FROM employees
		WHERE emp_no BETWEEN 10001 AND 10100
		AND gender='F';
		
		
		
+----+-------------+-----------+--------+---------+------+-----------+-------------+
| id | select_type | table     | type   | key     | rows | filtered  | Extra       |
+----+-------------+-----------+--------+---------+------+-----------+-------------+
| 1  | SIMPLE      | employees | range  | PRIMARY | 100  | 50.00     | Using where |
+----+-------------+-----------+--------+---------+------+-----------+-------------+

작업 범위 결정 조건은 emp_no BETWEEN 10001 AND 10100 이며 gender='F' 는 체크 조건이다.

3.12.31 Zero limit

메타데이터만 필요할때 LIMIT 0 을 사용했을 경우

진성대

주니어 개발자

이전 포스트

실행계획

SQL

1. 통계 정보

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

1.1.1 MySQL 서버의 통계 정보

1.2 히스토그램

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

1.2.2 히스토그램의 용도

1.2.3 히스토그램과 인덱스

1.3 코스트 모델(Cost Model)

2. 실행 계획 확인

2.1 실행 계획 출력 포맷

2.2 쿼리의 실행 시간 확인

3. 실행 계획 분석

3.1 id 칼럼

3.2 select_type 칼럼

3.2.1 SIMPLE

3.2.2 PRIMARY

3.2.3 UNION

3.2.4 DEPENDENT UNION

3.2.5 UNION RESULT

3.2.6 SUBQUERY

3.2.7 DEPENDENT SUBQUERY

3.2.8 DERIVED

3.2.9 DEPENDENT DERIVED

3.2.10 UNCACHEABLE SUBQUERY

3.2.11 UNCACHEABLE UNION

3.2.12 MATERIALIZED

3.3 table 칼럼

3.4 partitions 칼럼

3.5 type 칼럼

3.5.1 system

3.5.2 const

3.5.3 eq_ref

3.5.4 ref

3.5.5 fulltext

3.5.6 ref_or_null

3.5.7 unique_subquery

3.5.8 index_subquery

3.5.9 range

3.5.10 index_merge

3.5.11 index

3.5.12 ALL

3.6 possible_keys 칼럼

3.7 key 칼럼

3.8 key_len 칼럼

3.9 ref 칼럼

3.10 rows 칼럼

3.11 filtered 칼럼

3.12 Extra 칼럼

3.12.1 const row not found

3.12.2 Deleting all rows

3.12.3 Distinct

3.12.4 FirstMatch

3.12.5 Full scan on NULL key

3.12.6 Impossible HAVING

3.12.7 Impossible WHERE

3.12.8 LooseScan

3.12.9 No matching min/max row

3.12.10 no matching row in const table

3.12.11 No matching rows after partition pruning

3.12.12 No tables used

3.12.13 Not exists

3.12.14 Plan isn’t ready yet

3.12.15 Range checked for each record(index map : N)

3.12.16 Recursive

3.12.17 Rematerialize

3.12.18 Select tables optimized away

3.12.19 Start temporary, End temporary

3.12.20 unique row not found

3.12.21 Using filesort

3.12.22 Using index(커버링 인덱스)

3.12.23 Using index condition

3.12.24 Using index for group-by

루스 인덱스 스캔(Loose Index Scan)에서 GROUP BY가 인덱스를 활용하려면 WHERE 조건이 어떻게 되어야 하는가?

루스 인덱스 스캔(Loose Index Scan)이란?

WHERE 조건이 어떤 경우여야 루스 인덱스 스캔이 가능한가?

1. WHERE 조건이 인덱스의 "선두 컬럼"을 포함해야 한다.

2. WHERE 조건이 "동등 조건(=, IN)"일 때 루스 인덱스 스캔이 적용됨.

3. GROUP BY 컬럼들이 "인덱스의 연속된 컬럼"이어야 함.