[Real MySQL 8.0 (1권)] 9장 - 옵티마이저와 힌트

Real MySQL 8.0 1권 9장의 내용을 정리합니다.

Posted Dec 31, 2025

mysql

By ydj515

views 45 min read

본 글은 MySQL 8.0 기준으로 설명한다.

9장에서 던지는 질문

아래 질문들은 실무에서 실행 계획을 확인하거나 튜닝 방향을 잡을 때 자주 등장한다.

쿼리 실행은 어떤 단계로 나뉘며, 각 단계에서 어떤 정보가 만들어지는가?
비용 기반 옵티마이저는 어떤 통계를 참고하고, 그 통계가 틀리면 어떤 문제가 생기는가?
풀 테이블 스캔과 풀 인덱스 스캔 중 어느 쪽이 유리한지 판단 기준은 무엇인가?
ORDER BY 가 인덱스로 해결되지 않는 경우 filesort 가 언제/왜 발생하는가?
소트 버퍼 크기를 늘리면 항상 빨라지는가? 디스크 임시 파일은 언제 생기는가?
GROUP BY 가 인덱스로 처리되는 조건은 무엇이며, 루스 인덱스 스캔은 언제 가능한가?
DISTINCT 는 GROUP BY 와 어떻게 동일하게 처리되는가? 집합 함수와 결합되면 무엇이 달라지는가?
내부 임시 테이블이 메모리에서 디스크로 전환되는 조건은 무엇인가?
MRR/BKA 는 어떤 I/O 패턴을 개선하며, 조인 버퍼는 어떤 상황에서 효과적인가?
인덱스 컨디션 푸시다운이 없으면 어떤 종류의 불필요한 테이블 읽기가 생기는가?
인덱스 머지(교집합/합집합/정렬 후 합집합)는 각각 어떤 쿼리 패턴에서 유리한가?
세미 조인 최적화(테이블 풀-아웃/퍼스트 매치/루스 스캔/구체화/중복 제거)의 차이는 무엇인가?
해시 조인이 빠른데도 응답 시간이 느려지는 이유는 무엇인가?
조인 최적화 알고리즘(Exhaustive/Greedy)의 시간 복잡도는 어떻게 다른가?
힌트를 넣었는데도 무시되는 경우는 어떤 제약 때문인가?

이어질 수 있는 질문

9.2 정렬/그루핑 파트만 더 깊게 확장해도 될까?
각 최적화 옵션별 실제 EXPLAIN 예시도 추가할까?
힌트 사용 시점과 회피 시점을 표로 정리할까?
내부 임시 테이블 디스크 전환 조건을 버전별로 분리할까?

정리

9.1 개요

쿼리 실행 절차는 대략 3단계로 구분된다.
- SQL 파싱: 문법 검증, 파스 트리 생성.
- 최적화 및 실행 계획 수립: 접근 경로/조인 순서/임시 테이블 사용 여부 결정.
- 실행: 스토리지 엔진에 읽기 요청, 결과 반환.
옵티마이저의 종류
- 규칙 기반: 내장된 우선순위에 따라 동일한 실행 계획을 선택.
- 비용 기반: 통계 정보로 다양한 계획의 비용을 계산해 최적 선택(현행 MySQL 기본).

9.2 기본 데이터 처리

9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

인덱스를 활용할 수 없거나, 대부분 레코드를 읽어야 할 때 풀 테이블 스캔이 선택될 수 있다.
SELECT COUNT(*) 같은 경우에는 풀 인덱스 스캔이 더 유리할 수 있다.

9.2.2 병렬 처리

대량 스캔 작업을 여러 스레드로 분할해 처리 시간을 줄이는 방식이다.
스레드 수가 늘수록 감소 폭은 체감되며, 병렬화 비용도 고려해야 한다.

9.2.3 ORDER BY 처리(Using filesort)

9.2.3.1 소트 버퍼

정렬이 필요한 순간에만 할당되는 메모리 영역이며, 크기는 가변적이다.
정렬 대상이 크면 디스크 임시 파일을 사용해 멀티 머지를 수행한다.
정렬 버퍼가 커질수록 메모리 압박이 커지고, 동시 정렬 쿼리가 많으면 역효과가 난다.

9.2.3.2 정렬 알고리즘

싱글 패스: 정렬 키와 모든 칼럼을 소트 버퍼에 담아 1회 정렬.
투 패스: 정렬 키와 PK만 정렬 후, PK로 다시 테이블을 읽는다.

9.2.3.3 정렬 처리 방법

인덱스를 이용한 정렬: ORDER BY 칼럼이 선행 인덱스와 일치할 때.
드라이빙 테이블만 정렬: 조인 결과 폭증을 피하고자 먼저 정렬.
임시 테이블 정렬: 드리븐 테이블 칼럼 정렬이 필요할 때 사용.

9.2.3.3.1 인덱스를 이용한 정렬

ORDER BY 절 칼럼이 인덱스 선행 칼럼 순서와 일치해야 한다.
WHERE 절 조건이 인덱스 사용을 깨뜨리면 filesort가 발생한다.

예시(인덱스 정렬)

  
EXPLAIN
SELECT emp_no, last_name
FROM employees
WHERE last_name >= 'A'
ORDER BY last_name;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —– | ————- | ———— | ——- | —- | —– | ———————— | | 1 | SIMPLE | employees | range | ix_last_name | ix_last_name | 16 | NULL | 20000 | Using where; Using index |

9.2.3.3.2 조인의 드라이빙 테이블만 정렬

조인 전 드라이빙 테이블을 먼저 정렬해 결과 폭증을 줄인다.
ORDER BY 칼럼이 드라이빙 테이블에만 있어야 가능하다.

9.2.3.3.3 임시 테이블을 이용한 정렬

드리븐 테이블 칼럼으로 정렬할 때 주로 사용된다.
임시 테이블 크기가 커지면 디스크 I/O가 급증한다.

예시(드리븐 테이블 칼럼 정렬)

  
EXPLAIN
SELECT d.dept_no, e.hire_date
FROM dept_emp d
JOIN employees e ON e.emp_no = d.emp_no
ORDER BY e.hire_date;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——– | —— | ——————————- | | 1 | SIMPLE | d | index | PRIMARY | PRIMARY | 8 | NULL | 300000 | Using index | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | d.emp_no | 1 | Using temporary; Using filesort |

9.2.3.3.4 정렬 처리 방법의 성능 비교

인덱스 정렬은 추가 정렬 비용이 없어 가장 빠르다.
드라이빙 테이블 정렬은 차선책이며, 임시 테이블 정렬은 최후의 선택이다.
스트리밍 방식: 레코드를 찾는 즉시 결과를 전달해 응답이 빠르다.
버퍼링 방식: 전체 레코드를 모아 정렬/그루핑 후 전달한다.

9.2.4 GROUP BY 처리

타이트 인덱스 스캔: 드라이빙 테이블의 인덱스를 순서대로 읽어 그루핑.
루스 인덱스 스캔: 인덱스 레코드를 건너뛰어 유니크 그룹만 추출.
임시 테이블 사용: 인덱스로 처리 불가하거나 드리븐 테이블 기준일 때.

9.2.4.1 인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

GROUP BY 칼럼이 인덱스 선행 칼럼과 정렬이 맞아야 한다.
Extra 컬럼에 별도의 정렬/임시 테이블 코멘트가 표시되지 않는다.

예시(인덱스 조건과 실행 계획)

  
EXPLAIN
SELECT dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——- | ——- | —- | —— | ———– | | 1 | SIMPLE | dept_emp | index | ix_dept | ix_dept | 4 | NULL | 300000 | Using index |

9.2.4.2 루스 인덱스 스캔을 이용하는 GROUP BY

인덱스 레코드를 건너뛰며 그룹 대표값만 읽는다.
Extra 컬럼에 “Using index for group-by”가 표시된다.

예시(인덱스 조건과 실행 계획)

가정: dept_emp에 복합 인덱스 KEY ix_dept_emp (dept_no, emp_no)가 존재한다.

  
EXPLAIN
SELECT dept_no, MIN(emp_no) AS min_emp_no
FROM dept_emp
GROUP BY dept_no;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ———– | ——- | —- | —— | ———————— | | 1 | SIMPLE | dept_emp | range | ix_dept_emp | ix_dept_emp | 8 | NULL | 300000 | Using index for group-by |

9.2.4.3 임시 테이블을 사용하는 GROUP BY

드리븐 테이블의 칼럼으로 그룹핑하거나 인덱스가 없을 때 사용한다.
그룹 함수가 포함되면 임시 테이블로 처리되는 경우가 많다.

예시(인덱스 부재로 임시 테이블 사용)

  
EXPLAIN
SELECT e.hire_date, COUNT(*) AS cnt
FROM dept_emp de
JOIN employees e ON e.emp_no = de.emp_no
GROUP BY e.hire_date;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——— | —— | ——————————- | | 1 | SIMPLE | de | index | PRIMARY | PRIMARY | 8 | NULL | 300000 | Using index | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | de.emp_no | 1 | Using temporary; Using filesort |

9.2.5 DISTINCT 처리

SELECT DISTINCT: 내부적으로 GROUP BY 와 유사한 흐름으로 처리된다.
집합 함수 내 DISTINCT: 함수 인자로 전달된 칼럼 값만 유니크 처리된다.

9.2.5.1 SELECT DISTINCT…

DISTINCT는 레코드 집합을 유니크하게 만든다.
선택한 칼럼 조합 전체가 유니크 비교 대상이 된다.

예시(SELECT DISTINCT)

  
EXPLAIN
SELECT DISTINCT first_name, last_name
FROM employees
WHERE last_name LIKE 'A%';

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —– | ————- | ———— | ——- | —- | —– | —————————- | | 1 | SIMPLE | employees | range | ix_last_name | ix_last_name | 16 | NULL | 15000 | Using where; Using temporary |

예시(인덱스가 다른 경우: 복합 인덱스)

가정: employees에 KEY ix_last_first (last_name, first_name)가 존재한다.

  
EXPLAIN
SELECT DISTINCT first_name, last_name
FROM employees
WHERE last_name LIKE 'A%';

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —– | ————- | ————- | ——- | —- | —– | ———————— | | 1 | SIMPLE | employees | range | ix_last_first | ix_last_first | 30 | NULL | 15000 | Using where; Using index |

예시(인덱스 없음)

가정: employees에 last_name 관련 인덱스가 없다.

  
EXPLAIN
SELECT DISTINCT first_name, last_name
FROM employees
WHERE last_name LIKE 'A%';

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | —- | ——- | —- | —— | —————————- | | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300000 | Using where; Using temporary |

예시(인덱스 없음 + 디스크 임시 테이블 가능성)

가정: last_name 인덱스가 없고, 결과가 커서 정렬/임시 테이블이 디스크로 전환될 수 있다.

  
EXPLAIN
SELECT DISTINCT first_name, last_name
FROM employees
ORDER BY last_name;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | —- | ——- | —- | —— | ——————————- | | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300000 | Using temporary; Using filesort |

9.2.5.2 집합 함수와 함께 사용된 DISTINCT

집계 함수 인자만 유니크 대상으로 처리한다.
내부적으로는 임시 테이블을 사용하지만 표시되지 않을 수 있다.

예시(집합 함수 DISTINCT)

  
EXPLAIN
SELECT COUNT(DISTINCT emp_no) AS cnt
FROM salaries
WHERE salary >= 120000;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——— | ——- | —- | —- | ———————— | | 1 | SIMPLE | salaries | range | ix_salary | ix_salary | 4 | NULL | 5000 | Using where; Using index |

예시(DISTINCT + 집합 함수 + 디스크 임시 테이블 가능성)

가정: salaries에 salary 관련 인덱스가 없고, 중복 제거와 정렬로 임시 테이블이 커질 수 있다.

  
EXPLAIN
SELECT emp_no, COUNT(DISTINCT salary) AS cnt
FROM salaries
GROUP BY emp_no
ORDER BY cnt DESC;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —- | ————- | —- | ——- | —- | —— | ——————————- | | 1 | SIMPLE | salaries | ALL | NULL | NULL | NULL | NULL | 300000 | Using temporary; Using filesort |

9.2.6 내부 임시 테이블 활용

메모리 임시 테이블이 기본이지만, 크기 제한/데이터 타입 제약 등으로 디스크로 전환될 수 있다.
임시 테이블 사용은 정렬, 그루핑, DISTINCT, 일부 서브쿼리에서 흔하다.
상태 변수로 생성 횟수/디스크 전환 횟수를 추적할 수 있다.

9.2.6.1 메모리 임시 테이블과 디스크 임시 테이블

메모리 임시 테이블은 빠르지만 크기 제한이 있다.
디스크 임시 테이블은 I/O 비용이 커지고 정렬/그루핑 비용이 상승한다.

임시 테이블 관련 변수(MySQL 8.0) | 변수 | 설명 | 비고 | 예시 값 | | ——————————- | ——————————— | ————————————————– | ——— | | tmp_table_size | 메모리 임시 테이블 최대 크기 | max_heap_table_size와 함께 최소값이 상한이 된다. | 256M | | max_heap_table_size | MEMORY 엔진 임시 테이블 최대 크기 | tmp_table_size보다 크더라도 상한은 최소값이다. | 256M | | internal_tmp_mem_storage_engine | 내부 임시 테이블 메모리 엔진 | 기본값은 TempTable. | TempTable | | temptable_max_ram | TempTable 메모리 한도 | 초과 시 디스크로 스필된다. | 1G |

튜닝 기준(권장 범위 예시)

tmp_table_size와 max_heap_table_size는 동일하게 맞춰 관리한다.
temptable_max_ram은 전체 메모리의 10~25% 내에서 시작한다.
디스크 전환 비율이 높으면 정렬/그루핑 쿼리를 먼저 재작성한다.

9.2.6.2 임시 테이블이 필요한 쿼리

ORDER BY, GROUP BY, DISTINCT가 조합된 쿼리.
UNION, 서브쿼리 구체화, DISTINCT + 집계 조합.

9.2.6.3 임시 테이블이 디스크에 생성되는 경우

임시 테이블 크기가 임계값을 넘을 때.
BLOB/TEXT 등 메모리 임시 테이블에 부적합한 타입이 포함될 때.

예시(디스크 임시 테이블 유도)

  
EXPLAIN
SELECT dept_no, COUNT(*) AS cnt
FROM dept_emp
GROUP BY dept_no
ORDER BY cnt DESC;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——- | ——- | —- | —— | ——————————- | | 1 | SIMPLE | dept_emp | index | ix_dept | ix_dept | 4 | NULL | 300000 | Using temporary; Using filesort |

9.2.6.4 임시 테이블 관련 상태 변수

Created_tmp_tables, Created_tmp_disk_tables 등으로 빈도를 파악한다.
디스크 전환 비율이 높으면 정렬/그루핑 설계를 재검토한다.

예시(상태 변수 확인)

  
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

예시 출력 | Variable_name | Value | | ———————– | —– | | Created_tmp_tables | 1200 | | Created_tmp_disk_tables | 180 |

9.3 고급 최적화

예시 스키마(Real MySQL 샘플 employees 기반)

아래 예시는 책에서 자주 사용되는 employees 샘플 스키마를 간소화한 형태이며, 이후 EXPLAIN 설명의 기준이 된다.

  
CREATE TABLE employees (
  emp_no INT NOT NULL,
  first_name VARCHAR(14) NOT NULL,
  last_name VARCHAR(16) NOT NULL,
  hire_date DATE NOT NULL,
  PRIMARY KEY (emp_no),
  KEY ix_last_name (last_name),
  KEY ix_first_last (first_name, last_name)
);

CREATE TABLE dept (
  dept_no CHAR(4) NOT NULL,
  dept_name VARCHAR(40) NOT NULL,
  PRIMARY KEY (dept_no)
);

CREATE TABLE dept_emp (
  emp_no INT NOT NULL,
  dept_no CHAR(4) NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  PRIMARY KEY (emp_no, dept_no),
  KEY ix_dept (dept_no),
  KEY ix_fromdate (from_date)
);

CREATE TABLE dept_emp_no_idx (
  emp_no INT NOT NULL,
  dept_no CHAR(4) NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL
);

CREATE TABLE salaries (
  emp_no INT NOT NULL,
  salary INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  PRIMARY KEY (emp_no, from_date),
  KEY ix_salary (salary)
);

예시 실행 계획은 실행 환경(데이터 분포/버퍼/통계)에 따라 달라질 수 있다.

옵티마이저 스위치 옵션

optimizer_switch로 제어하며 세션/글로벌 단위 설정 가능.
특정 옵션을 켜거나 끌 때는 EXPLAIN으로 반드시 실행 계획을 확인한다.

  
SET SESSION optimizer_switch='mrr=on,batched_key_access=on';

MRR과 배치 키 액세스(BKA)

조인 버퍼에 드라이빙 테이블 레코드를 모아, 데이터 페이지 접근 순서를 개선한다.
랜덤 I/O를 줄이는 것이 핵심이며, 드리븐 테이블 인덱스 접근이 많은 경우 효과가 크다.

  
EXPLAIN
SELECT /*+ BKA(e) */ d.dept_no, e.emp_no
FROM dept_emp AS d
JOIN employees AS e ON e.emp_no = d.emp_no
WHERE d.dept_no BETWEEN 'd001' AND 'd005';

설명

d 테이블은 ix_dept로 범위를 좁히고, 조인 버퍼에 emp_no 목록을 모은다.
e 테이블은 PK(emp_no)로 빠르게 탐색하며, Extra에 “Using join buffer (Batched Key Access)”가 보일 수 있다.

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——– | —- | ————————————– | | 1 | SIMPLE | d | range | ix_dept | ix_dept | 4 | NULL | 5000 | Using where | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | d.emp_no | 1 | Using join buffer (Batched Key Access) |

힌트 비교(BKA Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——– | —- | ———– | | 1 | SIMPLE | d | range | ix_dept | ix_dept | 4 | NULL | 5000 | Using where | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | d.emp_no | 1 | NULL | 주석: 드리븐 테이블 인덱스 접근이 적거나 조인 건수가 작으면 BKA 이점이 거의 없다.

After | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——– | —- | ————————————– | | 1 | SIMPLE | d | range | ix_dept | ix_dept | 4 | NULL | 5000 | Using where | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | d.emp_no | 1 | Using join buffer (Batched Key Access) | 주석: 조인 버퍼가 커지고 정렬 순서가 바뀔 수 있으므로 결과 순서 의존 쿼리에는 주의한다.

블록 네스티드 루프 조인

조인 버퍼 사용 시 조인 순서가 역전된 것처럼 동작할 수 있다.
드리븐 테이블에 적절한 인덱스가 없으면 조인 버퍼를 크게 써도 느리다.
예시에서는 조인 버퍼 동작을 보여주기 위해 STRAIGHT_JOIN으로 순서를 고정한다.

  
EXPLAIN
SELECT STRAIGHT_JOIN *
FROM employees e
JOIN dept_emp_no_idx d ON d.emp_no = e.emp_no;

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —– | ————- | ——- | ——- | —- | —— | ————————————- | | 1 | SIMPLE | e | index | PRIMARY | PRIMARY | 4 | NULL | 300000 | Using index | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 300000 | Using join buffer (Block Nested Loop) |

인덱스 컨디션 푸시다운

인덱스 단계에서 추가 조건을 평가해 불필요한 테이블 읽기를 줄인다.
Extra에 “Using index condition”이 표시되면 ICP가 적용된 경우다.

  
EXPLAIN
SELECT *
FROM employees
WHERE last_name = 'Action'
  AND first_name LIKE '%sal';

설명

ix_last_name으로 1차 후보를 찾은 뒤, first_name 조건을 인덱스 레벨에서 함께 적용한다.
ICP가 꺼져 있다면 후보 레코드를 더 많이 테이블에서 읽게 된다.

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —– | ————- | ———— | ——- | —- | —- | ——————— | | 1 | SIMPLE | employees | range | ix_last_name | ix_last_name | 16 | NULL | 1000 | Using index condition |

인덱스 확장

세컨더리 인덱스에 내장된 PK를 활용할지를 결정한다.
중복 인덱스가 많을 때는 인덱스 확장이 실행 계획에 영향 줄 수 있다.

인덱스 머지

교집합: 여러 인덱스 결과의 공통 부분만 유지.
합집합: OR 조건을 서로 다른 인덱스로 병합.
정렬 후 합집합: 중복 제거를 위해 정렬이 필요한 경우.

  
EXPLAIN
SELECT *
FROM employees
WHERE last_name = 'Lee'
   OR emp_no BETWEEN 10010 AND 10100;

설명

ix_last_name과 PK(emp_no) 결과를 합집합으로 병합한다.
Extra에 “Using sort_union” 또는 “Using union”이 표시될 수 있다.

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | ———– | ——————– | ——————– | ——- | —- | —- | ——————————— | | 1 | SIMPLE | employees | index_merge | ix_last_name,PRIMARY | ix_last_name,PRIMARY | 16,4 | NULL | 1500 | Using union(ix_last_name,PRIMARY) |

세미 조인 최적화

테이블 풀-아웃, 퍼스트 매치, 루스 스캔, 구체화, 중복 제거 등으로 구성된다.
EXISTS/IN 서브쿼리를 조인으로 변환해 레코드 탐색을 줄인다.

  
EXPLAIN
SELECT *
FROM dept d
WHERE EXISTS (
  SELECT 1 FROM dept_emp de
  WHERE de.dept_no = d.dept_no
);

설명

조건을 만족하는 de 레코드가 존재하는지만 확인한다.
옵티마이저는 상황에 따라 퍼스트 매치 또는 구체화 전략을 선택한다.

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —– | ————- | ——- | ——- | ——— | —- | ————————– | | 1 | SIMPLE | d | index | PRIMARY | PRIMARY | 4 | NULL | 9 | Using index | | 1 | SIMPLE | de | ref | ix_dept | ix_dept | 4 | d.dept_no | 3000 | Using where; FirstMatch(d) |

기타 최적화

컨디션 팬아웃: 조인 순서 결정을 위한 카디널리티 추정 강화.
파생 테이블 머지: 서브쿼리 결과를 외부 쿼리와 병합해 불필요한 임시 테이블을 줄인다.
인비저블 인덱스: 인덱스 사용 여부를 제어해 검증한다.
스킵 스캔: 선행 칼럼 없이도 후행 칼럼 조건을 제한적으로 활용한다.
해시 조인: 처리량은 높지만 첫 레코드 응답이 늦어질 수 있다.
인덱스 정렬 선호: ORDER BY/GROUP BY 가 인덱스로 처리되면 가중치가 높아진다.

  
EXPLAIN
SELECT /*+ NO_MERGE(subq) */ *
FROM (
  SELECT dept_no, COUNT(*) AS cnt
  FROM dept_emp
  GROUP BY dept_no
) AS subq
WHERE cnt > 10;

설명

NO_MERGE 힌트로 파생 테이블 머지를 막아 임시 테이블 동작을 확인할 수 있다.
쿼리 튜닝 전후에 EXPLAIN 비교가 필요하다.

예시 실행 계획 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——- | ——- | —- | —— | —————————- | | 1 | PRIMARY | subq | ALL | NULL | NULL | NULL | NULL | 20 | Using where | | 2 | DERIVED | dept_emp | index | ix_dept | ix_dept | 4 | NULL | 300000 | Using temporary; Using index |

힌트 비교(NO_MERGE Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——- | ——- | —- | —— | ———– | | 1 | SIMPLE | dept_emp | index | ix_dept | ix_dept | 4 | NULL | 300000 | Using where | 주석: 파생 테이블이 자연스럽게 머지되는 상황에서는 NO_MERGE가 오히려 느려질 수 있다.

After | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——– | —– | ————- | ——- | ——- | —- | —— | —————————- | | 1 | PRIMARY | subq | ALL | NULL | NULL | NULL | NULL | 20 | Using where | | 2 | DERIVED | dept_emp | index | ix_dept | ix_dept | 4 | NULL | 300000 | Using temporary; Using index | 주석: 임시 테이블이 디스크로 전환되면 성능이 크게 저하될 수 있다.

조인 최적화 알고리즘

Exhaustive 검색: 모든 조합의 비용을 계산(테이블 수 n이면 n!).
Greedy 검색: 단계별로 최적 조합을 선택해 비용을 줄인다.

힌트 비교: STRAIGHT_JOIN

  
EXPLAIN
SELECT e.emp_no, d.dept_no
FROM employees e
JOIN dept_emp d ON d.emp_no = e.emp_no
WHERE d.dept_no = 'd005';

힌트 비교(STRAIGHT_JOIN Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —— | ————- | ——- | ——- | ——– | —- | ———– | | 1 | SIMPLE | d | ref | ix_dept | ix_dept | 4 | const | 5000 | Using where | | 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | d.emp_no | 1 | NULL | 주석: 통계가 충분히 신뢰 가능하면 STRAIGHT_JOIN은 과도한 고정일 수 있다.

After(STRAIGHT_JOIN 적용)

  
EXPLAIN
SELECT STRAIGHT_JOIN e.emp_no, d.dept_no
FROM employees e
JOIN dept_emp d ON d.emp_no = e.emp_no
WHERE d.dept_no = 'd005';

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | —– | —– | ————— | ——- | ——- | ——– | —— | ———– | | 1 | SIMPLE | e | index | PRIMARY | PRIMARY | 4 | NULL | 300000 | NULL | | 1 | SIMPLE | d | ref | PRIMARY,ix_dept | PRIMARY | 4 | e.emp_no | 1 | Using where | 주석: 조인 순서를 잘못 고정하면 카디널리티가 큰 테이블이 먼저 읽혀 느려진다.

힌트 비교: FORCE INDEX

  
EXPLAIN
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name = 'Mary' AND last_name = 'Smith';

힌트 비교(FORCE INDEX Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————————– | ————- | ——- | ———– | —- | ———– | | 1 | SIMPLE | employees | ref | ix_first_last,ix_last_name | ix_first_last | 32 | const,const | 10 | Using where | 주석: 옵티마이저가 이미 적절한 복합 인덱스를 선택한다면 FORCE는 불필요하다.

After(FORCE INDEX 적용)

  
EXPLAIN
SELECT emp_no, first_name, last_name
FROM employees FORCE INDEX (ix_last_name)
WHERE first_name = 'Mary' AND last_name = 'Smith';

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | ———— | ——- | —– | —- | ———– | | 1 | SIMPLE | employees | ref | ix_last_name | ix_last_name | 16 | const | 200 | Using where | 주석: 덜 선택적인 인덱스를 강제하면 rows가 증가해 전체가 느려질 수 있다.

힌트 비교: USE INDEX

  
EXPLAIN
SELECT emp_no, last_name
FROM employees
WHERE last_name = 'Lee';

힌트 비교(USE INDEX Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | ———— | ——- | —– | —- | ———– | | 1 | SIMPLE | employees | ref | ix_last_name | ix_last_name | 16 | const | 200 | Using where | 주석: 특정 인덱스를 권장하되, 통계 변화가 잦으면 효과가 불안정할 수 있다.

After(USE INDEX 적용)

  
EXPLAIN
SELECT emp_no, last_name
FROM employees USE INDEX (ix_last_name)
WHERE last_name = 'Lee';

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | ———— | ——- | —– | —- | ———– | | 1 | SIMPLE | employees | ref | ix_last_name | ix_last_name | 16 | const | 200 | Using where | 주석: WHERE 조건이 바뀌면 더 나은 인덱스를 막는 부작용이 생길 수 있다.

힌트 비교: IGNORE INDEX

  
EXPLAIN
SELECT emp_no, last_name
FROM employees
WHERE last_name = 'Lee';

힌트 비교(IGNORE INDEX Before/After) Before | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | ———— | ——- | —– | —- | ———– | | 1 | SIMPLE | employees | ref | ix_last_name | ix_last_name | 16 | const | 200 | Using where | 주석: 특정 인덱스가 나쁘다고 확신할 때만 제한적으로 사용한다.

After(IGNORE INDEX 적용)

  
EXPLAIN
SELECT emp_no, last_name
FROM employees IGNORE INDEX (ix_last_name)
WHERE last_name = 'Lee';

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | — | ———– | ——— | —- | ————- | —- | ——- | —- | —— | ———– | | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300000 | Using where | 주석: 풀 스캔을 유도하므로 OLTP 쿼리에는 특히 위험하다.

9.4 쿼리 힌트

인덱스 힌트

STRAIGHT_JOIN: 조인 순서 고정.
USE INDEX / FORCE INDEX / IGNORE INDEX: 인덱스 선택 유도/강제/제외.
SQL_CALC_FOUND_ROWS: LIMIT 없이 전체 건수를 계산(현재는 비권장 경향).

옵티마이저 힌트

힌트는 영향 범위 기준으로 구분되며, 인덱스/테이블/쿼리 블록 단위로 적용된다.
인덱스 힌트보다 옵티마이저 힌트를 우선 고려하는 것이 일반적이다.

힌트 적용 범위 표

| 범위 | 대상 | 대표 힌트 | 설명 | | ——— | ——————– | —————– | ————————————————- | | 쿼리 블록 | SELECT/서브쿼리 단위 | QB_NAME, NO_MERGE | 특정 블록에만 힌트를 적용한다. | | 테이블 | 특정 테이블 | BKA, NO_BKA | 조인 방식이나 접근 방식을 테이블 단위로 제어한다. | | 인덱스 | 인덱스 단위 | INDEX, NO_INDEX | 특정 인덱스를 사용/배제한다. |

힌트 사용/회피 판단 체크리스트

힌트를 쓰기 전

최신 통계와 히스토그램이 반영됐는지 확인한다.
인덱스 설계로 해결 가능한 문제인지 먼저 점검한다.
EXPLAIN으로 후보 실행 계획을 2~3개 비교한다.
SELECTIVITY와 rows 추정치가 실제 분포와 맞는지 확인한다.

힌트를 써도 되는 경우

동일 쿼리가 반복 실행되며 실행 계획이 흔들리는 경우.
옵티마이저가 명백히 잘못된 인덱스를 선택하는 경우.
특정 조인 순서가 지속적으로 더 안정적인 경우.

힌트를 피해야 하는 경우

데이터 분포 변화가 잦고 계획 안정성이 낮은 경우.
인덱스/통계 개선으로 해결 가능한 경우.
힌트가 다른 쿼리 블록에 부작용을 주는 경우.

힌트 적용 후 확인

EXPLAIN의 type, key, rows, filtered 변화 확인.
응답 시간과 CPU/IO 변화 확인.
회귀 테스트로 다른 쿼리 영향 확인.
통계 갱신 후 재검증 일정 확보.

실전 체크리스트

EXPLAIN에서 접근 방식, rows, filtered, Extra를 먼저 확인한다.
ORDER BY/GROUP BY가 인덱스로 처리되는지 확인한다.
임시 테이블/Filesort 발생 여부와 디스크 전환 비율을 확인한다.
조인 순서와 드라이빙/드리븐 테이블 선택이 합리적인지 점검한다.
힌트는 최후의 선택으로 두고, 통계/인덱스 설계를 먼저 개선한다.

복잡도 메모

정렬: 일반적으로 O(n log n), 추가 버퍼는 O(n) 수준.
인덱스 머지 교집합: 각 인덱스 결과 길이를 a, b라 하면 O(a + b).
해시 조인: 빌드 O(n), 프로브 O(m), 메모리 O(n).
Exhaustive 조인 최적화: O(n!) 수준으로 폭증.
Greedy 조인 최적화: 대략 O(n^2) 근사(단계별 선택 비용에 비례).

주의사항

힌트는 통계/데이터 분포 변경에 취약해 장기적으로 역효과 가능하다.
ORDER BY/GROUP BY는 스트리밍을 막아 응답 시간을 늘릴 수 있다.
임시 테이블 디스크 전환은 성능 하락의 대표 원인이다.
조인 버퍼 사용 시 정렬 결과가 기대와 달라질 수 있다.

대안과 선택지

힌트 대신 통계 갱신/히스토그램/인덱스 재설계로 우선 해결하기.
DISTINCT 대신 GROUP BY 또는 집계 재작성으로 처리량 줄이기.
해시 조인 대신 네스티드 루프 조인으로 응답 지연 최소화하기.
임시 테이블 회피를 위한 인덱스 정렬 유도.

mysql, book

mysql realmysql

This post is licensed under CC BY 4.0 by the author.