9장은 "MySQL이 내 쿼리를 어떻게 실행하기로 결정하는가"에 대한 장이다. 옵티마이저(Optimizer)는 내비게이션이라고 생각하면 쉽다. 목적지(쿼리 결과)는 SQL이 정해주지만, 어느 길(인덱스를 탈지, 어떤 테이블부터 읽을지, 정렬을 어떻게 할지)로 갈지는 옵티마이저가 통계 정보를 보고 고른다. 내비가 가끔 이상한 길로 안내하듯 옵티마이저도 틀릴 때가 있고, 그럴 때 사람이 "이 길로 가"라고 찍어주는 게 힌트다.

9.1 개요 — 쿼리가 실행되는 3단계

1. SQL 파싱: SQL 문장을 MySQL이 이해할 수 있는 트리(파스 트리)로 쪼갠다. 문법 오류는 여기서 걸린다.
2. 최적화 및 실행 계획 수립: 파스 트리를 보고 불필요한 조건 제거, 조인 순서 결정, 어떤 인덱스를 쓸지 결정한다. 옵티마이저가 일하는 단계이고, 이 결과물이 실행 계획이다.
3. 실행: 실행 계획대로 스토리지 엔진에서 레코드를 읽어오고, MySQL 엔진이 조인·정렬 등 가공을 한다.

옵티마이저에는 **규칙 기반(RBO)**과 **비용 기반(CBO, Cost-Based Optimizer)**이 있다. 규칙 기반은 "인덱스 있으면 무조건 인덱스" 같은 고정된 우선순위를 따르는 방식이고, 비용 기반은 여러 실행 방법의 비용을 통계 정보로 추정해서 가장 싼 걸 고른다. 지금 거의 모든 RDBMS는 비용 기반이다. 그래서 통계 정보가 틀리면 실행 계획도 틀린다 — 이게 10장까지 이어지는 핵심 전제다.

9.2 기본 데이터 처리

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

옵티마이저가 풀 테이블 스캔을 고르는 건 대체로 이런 경우다.

• 테이블 레코드가 아주 적어서 인덱스를 거치는 게 오히려 느릴 때
• WHERE/ON 조건에 인덱스를 쓸 수 있는 조건이 없을 때
• 인덱스 레인지 스캔이 가능해도 일치하는 레코드가 너무 많을 때 (8장의 "20~25% 넘으면 풀 스캔이 낫다")

InnoDB는 풀 스캔할 때 **리드 어헤드(Read ahead)**를 쓴다. 특정 영역의 페이지를 연속으로 읽기 시작하면, 다음에 읽을 페이지를 백그라운드 스레드가 미리 버퍼 풀로 가져다 놓는 것이다. 풀 스캔이 생각보다 빠른 이유다.

SELECT COUNT(*) FROM employees처럼 레코드 건수만 필요하면 옵티마이저는 테이블 대신 더 작은 인덱스를 풀 스캔한다(풀 인덱스 스캔). 칼럼 값까지 필요하면 테이블을 읽어야 하니 이 트릭은 못 쓴다.

9.2.2 병렬 처리

8.0부터 innodb_parallel_read_threads로 한 쿼리를 여러 스레드가 나눠 처리할 수 있다. 다만 WHERE 조건 없는 COUNT(*) 정도만 병렬로 돈다. 그리고 스레드를 CPU 코어 수 이상으로 늘리면 오히려 느려진다. 실무에서 신경 쓸 일은 거의 없다.

9.2.3 ORDER BY 처리 (Using filesort)

정렬은 두 가지 방법 중 하나로 처리된다.

	장점	단점
인덱스 이용	이미 정렬돼 있으니 순서대로 읽기만 하면 돼서 매우 빠름	INSERT/UPDATE/DELETE 때 인덱스 유지 비용, 디스크 공간
Filesort	인덱스를 안 만들어도 됨. 정렬 대상이 적으면 충분히 빠름	쿼리 실행 시점에 정렬하므로 레코드가 많을수록 느림

실행 계획 Extra에 **Using filesort**가 보이면 인덱스 대신 별도 정렬을 했다는 뜻이다. 모든 정렬을 인덱스로 할 수는 없다 — 정렬 기준이 너무 다양하거나, GROUP BY·DISTINCT 결과를 정렬하거나, UNION 결과를 정렬하거나, 랜덤 정렬일 때는 불가능하다.

소트 버퍼(Sort buffer)

정렬할 때 쓰는 메모리 공간이 소트 버퍼이고 크기는 sort_buffer_size로 정한다. 정렬할 레코드가 소트 버퍼보다 크면 **여러 조각으로 나눠 정렬한 뒤 디스크에 임시로 써두고, 그걸 다시 병합(멀티 머지, Multi-merge)**한다. 디스크 I/O가 끼니 느려진다.

그럼 소트 버퍼를 크게 잡으면 되냐 하면 아니다. 책의 벤치마크에서 256KB~8MB 사이가 최적이었고 그 이상은 오히려 느려졌다. 게다가 소트 버퍼는 세션(로컬) 메모리라서 커넥션마다 따로 할당된다. 커넥션 100개가 동시에 대량 정렬을 하면 100 × sort_buffer_size만큼 메모리를 먹고, 최악엔 운영체제의 OOM-Killer(메모리가 부족할 때 메모리를 제일 많이 쓰는 프로세스를 강제 종료하는 리눅스 기능)가 MySQL을 죽인다. 책의 권장값은 56KB~1MB다.

🐘 PostgreSQL 비교 — PG에서 소트 버퍼 역할을 하는 게 work_mem이다. 똑같이 "넘치면 디스크로" 가고, EXPLAIN ANALYZE 결과에 Sort Method: quicksort Memory: ...(메모리에서 끝남) 또는 Sort Method: external merge Disk: ...(디스크로 넘침)로 바로 표시돼서 확인이 더 쉽다. 주의할 점은 work_mem이 커넥션당이 아니라 정렬·해시 작업 하나당 할당된다는 것. 한 쿼리에 정렬이 3개 있으면 3배를 쓴다. 그래서 MySQL과 마찬가지로 전역으로 크게 잡는 건 위험하다.

싱글 패스 vs 투 패스

• 싱글 패스(Single-pass): SELECT할 칼럼까지 통째로 소트 버퍼에 담아 정렬하고, 정렬 결과를 그대로 반환한다. 테이블을 한 번만 읽는다.
• 투 패스(Two-pass): 정렬 기준 칼럼 + PK만 담아 정렬한 뒤, 정렬된 순서대로 테이블을 다시 읽어 나머지 칼럼을 가져온다. 테이블을 두 번 읽는다.

요즘은 싱글 패스가 기본이다. 다만 레코드 크기가 max_length_for_sort_data보다 크거나 BLOB/TEXT 칼럼을 SELECT하면 투 패스로 바뀐다.

SELECT * 를 피해야 하는 진짜 이유

싱글 패스는 SELECT한 칼럼을 전부 소트 버퍼에 싣는다. SELECT *로 필요 없는 칼럼까지 가져오면 소트 버퍼에 들어가는 레코드 수가 줄어들고, 그만큼 디스크로 넘쳐서 멀티 머지를 하게 된다. 정렬이 들어간 쿼리일수록 필요한 칼럼만 SELECT하자.

정렬 처리 방법 3가지 (빠른 순)

정렬 처리 방법	Extra 칼럼
인덱스를 사용한 정렬	별도 표기 없음
조인의 드라이빙 테이블만 정렬	Using filesort
조인 결과를 임시 테이블로 저장 후 정렬	Using temporary; Using filesort

조인을 할 때 드라이빙 테이블(조인에서 먼저 읽는 테이블)만 정렬하면 정렬 대상이 적다. 조인은 레코드를 불려놓기 때문에, 조인하기 전에 정렬해 두는 게 훨씬 싸다. 반대로 ORDER BY에 드리븐 테이블(나중에 읽히는 쪽)의 칼럼이 있으면, 조인을 다 끝낸 결과를 임시 테이블에 담아서 정렬해야 하니 가장 느리다.

그러니 정렬 튜닝의 방향은 **"인덱스로 정렬하게 → 안 되면 드라이빙 테이블 칼럼으로만 정렬하게"**다.

스트리밍 vs 버퍼링 — LIMIT이 안 먹히는 이유

• 스트리밍 처리: 조건에 맞는 레코드가 나오는 족족 클라이언트로 보낸다. LIMIT 10이면 10건 찾자마자 끝낸다.
• 버퍼링 처리: ORDER BY/GROUP BY처럼 전부 모아야 결과를 낼 수 있는 작업. WHERE에 맞는 레코드를 다 가져와 정렬한 뒤에야 첫 건을 보낼 수 있다.

인덱스로 정렬하면 스트리밍이 가능하지만, Filesort는 버퍼링이다. 그래서 인덱스를 못 타는 정렬 쿼리는 LIMIT 10을 붙여도 기대만큼 빨라지지 않는다. 1,000건을 다 읽어서 정렬은 해야 하기 때문이다(상위 N건만 필요하니 힙 정렬로 조금 줄일 뿐). 페이징 쿼리가 느릴 때 제일 먼저 의심할 부분이다.

9.2.4 GROUP BY 처리

GROUP BY도 인덱스를 쓸 수 있느냐로 갈린다.

• 타이트 인덱스 스캔: 인덱스를 순서대로 읽으며 그룹핑. 추가 정렬·임시 테이블이 필요 없다.
• 루스 인덱스 스캔(Loose index scan): 인덱스를 건너뛰면서 필요한 부분만 읽는다. Extra에 Using index for group-by. 예를 들어 (emp_no, from_date) 인덱스로 GROUP BY emp_no + WHERE from_date = ...를 처리할 때, emp_no 그룹마다 조건에 맞는 한 건만 찾고 다음 emp_no로 점프한다. 유니크한 값이 적을수록(분포도가 나쁠수록) 오히려 빠르다는 게 일반 인덱스 스캔과 반대인 점이 재밌다.
• 임시 테이블 사용: 인덱스를 전혀 못 쓰면 GROUP BY 칼럼으로 유니크 인덱스를 가진 임시 테이블을 만들어 중복 제거와 집계를 한다. Extra에 Using temporary.

8.0부터 GROUP BY가 정렬을 안 해준다

5.7까지는 GROUP BY를 하면 그룹 칼럼 기준으로 묵시적 정렬까지 해줬다. 8.0부터는 안 한다. 정렬된 결과가 필요하면 ORDER BY를 명시해야 한다. 옛날 코드에서 GROUP BY만 믿고 순서를 가정했다면 8.0 업그레이드 후 결과 순서가 바뀔 수 있다. (반대로 5.7 때 정렬을 막으려고 붙이던 ORDER BY NULL은 이제 필요 없다.)

🐘 PostgreSQL 비교 — 루스 인덱스 스캔은 MySQL이 오래 앞서 있던 기능이다. PG는 비슷한 "인덱스 건너뛰며 읽기"를 PG 18(2025)에서야 B-Tree 스킵 스캔(Skip scan)으로 도입했고, 그 전에는 재귀 CTE로 흉내 내는 트릭을 썼다. 한편 PG의 GROUP BY는 "정렬 후 묶기(GroupAggregate)"와 "해시 테이블에 담아 묶기(HashAggregate)" 중 옵티마이저가 고르는데, 해시 쪽이 MySQL의 "임시 테이블로 그룹핑"과 비슷한 역할이다. PG도 GROUP BY가 정렬을 보장하지 않으니 ORDER BY를 붙이는 습관은 둘 다 똑같이 필요하다.

9.2.5 DISTINCT 처리

• SELECT DISTINCT: 사실상 GROUP BY와 같은 방식으로 처리된다.
• 흔한 오해: SELECT DISTINCT(first_name), last_name처럼 괄호를 쳐도 first_name만 유니크하게 가져오는 게 아니다. DISTINCT는 함수가 아니라서 괄호는 그냥 무시되고, SELECT한 칼럼 조합 전체가 유니크한 레코드를 가져온다.
• 집합 함수 안의 DISTINCT(COUNT(DISTINCT s.salary))는 그 인자 칼럼만 유니크하게 센다. 이때는 내부적으로 임시 테이블을 쓰는데, 실행 계획에 Using temporary가 표시되지 않는다는 점을 기억해 두자.

9.2.6 내부 임시 테이블

MySQL 엔진이 정렬·그룹핑 중간 결과를 담으려고 내부적으로 만드는 임시 테이블이다(CREATE TEMPORARY TABLE로 만드는 것과는 다른 것). 쿼리가 끝나면 자동 삭제된다.

• 8.0부터 메모리 임시 테이블은 TempTable 엔진(가변 길이 지원), 디스크 임시 테이블은 InnoDB를 쓴다. 옛날 MEMORY 엔진은 VARCHAR도 최대 길이만큼 메모리를 잡아서 낭비가 심했는데 개선된 것이다.
• 메모리 임시 테이블이 temptable_max_ram(기본 1GB)을 넘으면 디스크로 넘어간다.

임시 테이블이 생기는 대표적인 경우는 이렇다.

• ORDER BY와 GROUP BY 칼럼이 다를 때
• ORDER BY/GROUP BY 칼럼이 조인 순서상 첫 번째 테이블이 아닐 때
• DISTINCT와 ORDER BY가 같이 있는데 DISTINCT를 인덱스로 처리 못할 때
• UNION/UNION DISTINCT, 그리고 FROM절 서브쿼리(select_type이 DERIVED)

임시 테이블이 메모리에서 처리됐는지 디스크로 넘어갔는지는 실행 계획만으로는 모른다. 상태 변수로 확인한다.

FLUSH STATUS;
SELECT first_name, last_name FROM employees GROUP BY first_name, last_name;
SHOW SESSION STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables       1  ← 임시 테이블이 만들어진 총 개수
-- Created_tmp_disk_tables  1  ← 그중 디스크에 만들어진 개수

9.3 고급 최적화 — 옵티마이저 스위치

옵티마이저의 세부 기능들은 optimizer_switch 변수로 켜고 끈다. 전역·세션·쿼리 단위로 조절할 수 있다.

SET SESSION optimizer_switch='index_merge=off';
SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersection=off') */ * FROM ...;

기능이 스무 개 가까이 되는데, 전부 알 필요는 없다. "이런 최적화가 있고 실행 계획의 Extra에 이렇게 찍힌다" 정도로 알아두면, 나중에 실행 계획을 읽을 때 "아, 이게 그거구나" 하고 알아볼 수 있다.

조인 관련

• 블록 네스티드 루프(Block Nested Loop) → Using join buffer (block nested loop)
  기본 조인 방식은 네스티드 루프 조인이다. 이중 for문과 똑같다 — 드라이빙 테이블 레코드 하나마다 드리븐 테이블에서 짝을 찾는다. 드리븐 테이블에 쓸 인덱스가 없으면 매번 풀 스캔을 해야 하니, 드라이빙 테이블 레코드를 **조인 버퍼(join_buffer_size)**에 모아 두고 드리븐 테이블을 한 번 읽으면서 한꺼번에 비교하는 게 블록 네스티드 루프다. 조인 버퍼를 쓰면 결과 순서가 드라이빙 테이블 순서를 따르지 않을 수 있다. 8.0.20부터는 이 방식이 없어지고 해시 조인으로 대체됐다.

• 해시 조인(Hash join) → Using join buffer (hash join) — 8.0.18부터
  작은 테이블로 해시 테이블을 만들고(빌드 단계), 큰 테이블을 읽으며 해시 테이블에서 짝을 찾는다(프로브 단계). 흔히 "해시 조인이 무조건 빠르다"고 오해하는데, 책의 설명이 명쾌하다.

  • 해시 조인은 첫 레코드는 늦게 나오지만 마지막 레코드는 빨리 나온다 → 전체 처리량(Best Throughput)이 좋다 → 분석 쿼리 향
  • 네스티드 루프는 첫 레코드가 빨리 나온다 → 응답 속도(Best Response-time)가 좋다 → OLTP(웹 서비스) 향

  MySQL은 OLTP용 DB라서 해시 조인을 **인덱스를 못 쓸 때의 차선책(Fallback)**으로 설계했다. 그래서 힌트로 억지로 해시 조인을 유도하는 건 좋지 않다.

• MRR과 BKA(Batched Key Access) → Using MRR
  드라이빙 테이블 레코드를 조인 버퍼에 모았다가, 드리븐 테이블 키를 정렬해서 한꺼번에 스토리지 엔진에 요청한다. 디스크를 순서대로 읽게 돼서 랜덤 I/O가 준다. BKA는 기본값이 꺼져 있다(오히려 정렬 비용 때문에 손해인 경우가 있어서).

🐘 PostgreSQL 비교 — PG는 처음부터 조인 방식을 세 가지 다 가지고 있었다. 네스티드 루프, 해시 조인, 그리고 머지 조인(양쪽을 조인 키로 정렬해 두고 지퍼 올리듯 나란히 맞춰가는 방식). MySQL이 8.0.18에 와서야 해시 조인을 얻은 것과 달리, PG 옵티마이저는 데이터 크기를 보고 셋 중 하나를 자유롭게 고른다. 그래서 대용량 분석 쿼리는 PG가 전통적으로 강했다. 응답 속도 vs 처리량의 트레이드오프 설명은 PG에도 그대로 들어맞는다.

인덱스 관련

• 인덱스 컨디션 푸시다운(ICP) → Using index condition
INDEX(last_name, first_name)에 WHERE last_name='Acton' AND first_name LIKE '%sal'. first_name LIKE '%sal'은 범위를 좁히지 못하니 인덱스 탐색엔 못 쓴다. 예전엔 last_name으로 찾은 레코드를 전부 테이블에서 읽은 뒤 first_name을 비교했다. 그런데 first_name은 인덱스에 이미 있는 칼럼이니 테이블을 읽기 전에 인덱스에서 걸러낼 수 있지 않나? 그걸 하는 게 ICP다. 필터링을 스토리지 엔진으로 "밀어 내려(push down)" 불필요한 테이블 읽기를 없앤다. 기본으로 켜져 있고, 쿼리 성능이 몇 배~몇십 배 좋아질 수 있다.

• 인덱스 확장(use_index_extensions)
  8장에서 봤듯 InnoDB 세컨더리 인덱스엔 PK가 숨어 있다. PRIMARY KEY(dept_no, emp_no) + INDEX(from_date)면 실제로는 (from_date, dept_no, emp_no) 인덱스처럼 동작한다. 옵티마이저가 이걸 알고 활용하게 하는 옵션이다. 그래서 WHERE from_date=? ORDER BY dept_no는 별도 정렬 없이 처리된다.

• 인덱스 머지(index_merge) → Using intersect(...), Using union(...), Using sort_union(...)
  보통 테이블 하나엔 인덱스 하나만 쓰지만, 조건이 여러 개고 각각 다른 인덱스를 쓸 수 있으면 여러 인덱스로 각각 검색한 뒤 결과를 합친다.

  • intersect: AND 조건 → 교집합
  • union: OR 조건 → 합집합 (두 결과가 PK로 이미 정렬돼 있어서 정렬 없이 중복 제거)
  • sort_union: OR 조건인데 결과가 정렬돼 있지 않으면 정렬 후 합집합

  참고로 OR 조건은 둘 중 하나라도 인덱스를 못 쓰면 풀 스캔이 된다. AND와 OR의 결정적인 차이다.

• 인비저블 인덱스(use_invisible_indexes) — 8.0
  ALTER TABLE ... ALTER INDEX ix INVISIBLE로 인덱스를 지우지 않고 옵티마이저에게만 안 보이게 할 수 있다. "이 인덱스 지워도 되나?" 싶을 때 먼저 숨겨서 영향을 보고, 문제가 있으면 바로 되살리는 용도로 아주 쓸모 있다.

• 스킵 스캔(skip_scan) — 8.0
  INDEX(gender, birth_date)인데 WHERE birth_date >= ...만 있으면 원래는 선행 칼럼이 없어서 인덱스를 못 쓴다. 스킵 스캔은 gender의 값('M', 'F')마다 쪼개서 gender='M' AND birth_date>=..., gender='F' AND ...처럼 검색한다. 선행 칼럼의 유니크 값 개수가 적을 때만 효과가 있다.

서브쿼리 관련 — 세미 조인

WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE ...)처럼, 다른 테이블과 실제 조인은 안 하고 일치하는 게 있는지만 확인하는 쿼리를 **세미 조인(Semi-join)**이라 한다. 5.7 전까지 MySQL은 이걸 정말 못했다 — 서브쿼리를 먼저 실행하는 게 아니라 바깥 테이블을 풀 스캔하면서 한 건마다 서브쿼리를 돌렸다. 57건이면 끝날 걸 30만 건을 읽었다. 그래서 "MySQL에선 IN 서브쿼리 쓰지 말고 조인으로 풀어라"는 말이 유명해졌다.

8.0에서는 세미 조인 최적화 전략 여러 개가 추가돼서 이 문제가 거의 사라졌다.

전략	한 줄 설명	Extra 표시
Table Pull-out	서브쿼리를 아예 조인으로 바꿔 바깥으로 끄집어냄 (가능하면 항상 이득)	표시 없음 (id가 같은 값으로 나옴)
First Match	EXISTS처럼 첫 번째 일치 건만 찾으면 멈춤	FirstMatch(e)
Loose Scan	서브쿼리 테이블 인덱스를 루스 인덱스 스캔으로 유니크하게 읽음	LooseScan
Materialization	서브쿼리 결과를 임시 테이블로 구체화한 뒤 조인	select_type MATERIALIZED
Duplicate Weed-out	일반 조인으로 바꿔 실행하고 마지막에 중복 제거	Start temporary, End temporary

🐘 PostgreSQL 비교 — PG는 IN (서브쿼리)나 EXISTS를 원래부터 세미 조인으로 변환해서 처리해 왔다. 실행 계획에 Hash Semi Join, Nested Loop Semi Join처럼 나온다. 그래서 "IN 서브쿼리는 조인으로 풀어 써라"는 MySQL 5.x 시절의 튜닝 조언은 PG엔 애초에 해당이 없었다. 지금은 MySQL 8.0도 따라잡아서 둘 다 서브쿼리를 편하게 써도 된다.

그 외

• 파생 테이블 머지(derived_merge): FROM절 서브쿼리(파생 테이블)를 임시 테이블로 만들지 않고 바깥 쿼리로 병합한다. 다만 집계 함수·DISTINCT·GROUP BY·LIMIT·UNION이 들어간 서브쿼리는 병합이 안 되니, 가능하면 서브쿼리를 직접 바깥 쿼리로 풀어 쓰는 게 성능에 좋다.
• 컨디션 팬아웃(condition_fanout_filter): 인덱스로 쓰는 조건 외의 나머지 조건까지 고려해서 "몇 %가 남을지"(filtered 칼럼)를 더 정확히 예측한다. 이 예측이 조인 순서 결정에 쓰인다. 정확해지는 대신 예측에 드는 시간이 조금 늘어난다.
• 인덱스 정렬 선호(prefer_ordering_index): ORDER BY ... LIMIT 쿼리에서 옵티마이저가 WHERE용 인덱스 대신 ORDER BY용 인덱스를 과하게 선호하는 실수를 자주 했다. 그럴 때 이 옵션을 끈다.

9.3.2 조인 최적화 알고리즘

테이블 N개를 조인할 때 가능한 조인 순서는 **N!(팩토리얼)**가지다. 옛날의 Exhaustive 검색은 이걸 전부 비교해서, 테이블 10개만 넘어도 실행 계획 짜는 데 몇 분이 걸렸다. 5.0부터 쓰는 Greedy 검색은 optimizer_search_depth만큼만 조합을 보고 가장 좋은 걸 하나씩 확정해 나간다. 8.0에선 많이 개선돼서 기본값(optimizer_prune_level=1)을 건드릴 일이 거의 없다.

9.4 쿼리 힌트

옵티마이저가 틀렸을 때 사람이 개입하는 방법이다. 두 종류가 있다.

인덱스 힌트 (옛날 방식)

SELECT STRAIGHT_JOIN ... FROM employees e, dept_emp de, departments d ...;  -- FROM절 순서대로 조인
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE ...;

• STRAIGHT_JOIN: FROM절에 쓴 순서대로 조인하게 강제한다. 보통 "레코드가 적은 쪽을 드라이빙으로" 가 기준이다(여기서 레코드 수는 테이블 전체가 아니라 WHERE 조건을 적용한 뒤의 건수다).
• USE INDEX / FORCE INDEX / IGNORE INDEX: 특정 인덱스를 쓰라고 권하거나, 강제하거나, 못 쓰게 한다. USE와 FORCE는 실제로 거의 차이가 없다.
• SQL_CALC_FOUND_ROWS: LIMIT을 걸어도 끝까지 다 검색해서 전체 건수를 세 둔다. 페이징 구현에 편해 보이지만 쓰지 마라. 전체 레코드를 테이블에서 읽느라 랜덤 I/O가 폭발한다. COUNT(*) 쿼리를 따로 날리는 게(커버링 인덱스로 처리돼서) 훨씬 빠르다. 8.0.17부터 deprecated다.

옵티마이저 힌트 (8.0 권장 방식)

주석 형태 /*+ ... */로 쓰고, 쿼리 블록·테이블·인덱스 단위로 더 정교하게 지정할 수 있다. 주석이라서 다른 DB에서는 그냥 무시되는 것도 장점이다.

SELECT /*+ MAX_EXECUTION_TIME(100) */ * FROM ...;                  -- 100ms 넘으면 중단
SELECT /*+ SET_VAR(sort_buffer_size=16777216) */ * FROM ...;        -- 이 쿼리만 변수 변경
SELECT /*+ JOIN_ORDER(e, de) */ * FROM ...;                        -- 조인 순서
SELECT /*+ INDEX(employees ix_firstname) */ * FROM employees ...;  -- 인덱스 지정
SELECT /*+ NO_INDEX(employees ix_firstname) */ ...;
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION) */ ...;               -- 세미 조인 전략 지정

실무에서 제일 쓸모 있는 건 MAX_EXECUTION_TIME(폭주하는 쿼리 방지)과 SET_VAR(특정 쿼리만 소트 버퍼·조인 버퍼를 키우기)이다.

힌트는 최후의 수단

힌트는 **"지금 이 데이터 분포"**에 맞춘 처방이다. 데이터가 바뀌면 오늘의 좋은 힌트가 내일은 독이 된다. 그리고 힌트가 코드에 박혀 있으면 아무도 다시 안 본다. 책도 "가능하면 옵티마이저가 알아서 좋은 계획을 세우게 하고(통계 정보 갱신, 인덱스 설계, 쿼리 재작성), 힌트는 그게 안 될 때만" 쓰라고 한다.

🐘 PostgreSQL 비교 — PG는 공식적으로 쿼리 힌트가 없다. 개발팀의 철학이 "힌트로 덮지 말고 옵티마이저가 틀린 원인(통계, 인덱스, 설정)을 고쳐라"이기 때문이다. 급할 때 쓰는 방법은 두 가지다. ① SET enable_nestloop = off처럼 특정 실행 방식을 세션에서 꺼버리기 — MySQL의 optimizer_switch와 비슷한 감각이다. ② pg_hint_plan이라는 확장 모듈을 설치하면 MySQL처럼 /*+ ... */ 주석 힌트를 쓸 수 있다(AWS RDS 등 대부분 매니지드 서비스에서 지원한다).

정리

9장에서 실무로 들고 갈 것.

• 옵티마이저는 통계 기반으로 비용을 추정하는 내비게이션이다. 통계가 틀리면 계획도 틀린다.
• Using filesort, Using temporary는 경고등이다. 정렬은 인덱스로 → 안 되면 드라이빙 테이블 칼럼으로만 정렬하게 유도한다.
• 인덱스를 못 타는 ORDER BY ... LIMIT은 LIMIT이 있어도 다 읽는다. 페이징이 느리면 여기부터 본다.
• 정렬 쿼리에서 SELECT * 금지. 소트 버퍼를 불필요한 칼럼이 잡아먹는다.
• sort_buffer_size·join_buffer_size는 전역으로 크게 잡지 마라. 커넥션마다 할당돼서 OOM 위험이 있다. 필요한 쿼리만 SET_VAR 힌트로 키운다.
• 8.0부터 GROUP BY는 정렬을 보장하지 않는다. 순서가 필요하면 ORDER BY를 명시한다.
• 8.0이면 IN 서브쿼리를 두려워하지 않아도 된다. 세미 조인 최적화가 알아서 조인으로 바꾼다.
• 인덱스를 지우기 전에 INVISIBLE로 먼저 숨겨 본다.
• 힌트는 최후의 수단. 쓴다면 MAX_EXECUTION_TIME과 SET_VAR부터.

반대로 지금 깊이 안 파도 되는 것 — 세미 조인 전략 5가지의 세부 동작, 인덱스 머지 3종류의 차이, 조인 최적화 알고리즘(Exhaustive/Greedy), MRR/BKA, 병렬 처리. 실행 계획에서 해당 키워드를 봤을 때 찾아보면 된다. 옵티마이저 스위치 이름들도 외울 필요 없다.