실행 계획(Execution Plan) 분석하기

📄 요약

데이터베이스 성능은 곧 애플리케이션의 응답 속도와 직결됨. 그 중심에는 '쿼리 최적화'가 있음.
이번에는 쿼리 튜닝의 첫걸음이자 가장 중요한 도구인 실행 계획(Execution Plan)을 분석하는 실무 지식을 다룸.

💡 주요 개념 1: 실행 계획 (Execution Plan)

옵티마이저가 SQL을 가장 효율적으로 처리하기 위해 세운 실행 절차와 방법. 쿼리 튜닝은 실행 계획을 읽는 것에서 시작됨.

• 분석 방법: EXPLAIN (MySQL, PostgreSQL), EXPLAIN PLAN FOR (Oracle) 키워드로 확인.

📜 EXPLAIN 결과 예시

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	o (orders)	range	order_date_idx	order_date_idx	5	NULL	312	Using where; Using filesort
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	o.customer_id	1
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	o.product_id	1	Using index

✅ MySQL EXPLAIN 지표 설명

항목	설명
id	쿼리 블록의 ID. 숫자가 클수록 하위 쿼리이거나 먼저 실행됨. JOIN이 많을 경우 여러 행으로 나옴.
select_type	쿼리 유형. 대표적으로 SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION 등이 있음.
table	접근하는 테이블의 이름. JOIN 시 여러 테이블이 등장할 수 있음.
type	테이블 접근 방식 (가장 중요) 접근 효율의 순서는 아래와 같음: system > const > eq_ref > ref > range > index > ALL
possible_keys	옵티마이저가 이 쿼리에 대해 사용을 고려할 수 있는 인덱스 목록
key	실제로 선택되어 사용된 인덱스. possible_keys에 있어도 key가 NULL이면 인덱스를 타지 못한 것임
key_len	인덱스의 길이 (바이트 단위). 얼마나 인덱스가 효율적으로 사용됐는지 알 수 있음
ref	인덱스를 통해 어떤 값으로 비교하는지 (ref 컬럼 = 다른 테이블의 컬럼 또는 상수 등)
rows	MySQL이 예측한 탐색 대상 행(row) 수. 너무 많으면 성능 병목 가능성
filtered	(옵션) 필터 조건을 만족하는 레코드 비율(%) — >rows * (filtered / 100) = 실제 처리 예상 row 수 옵티마이저의 예측이므로 정확하지는 않음.
Extra	추가적인 실행 정보. 아래에 별도로 상세 정리

🔍 type 접근 방식 (성능 중요도 순)

type	설명
system	테이블에 1개의 row만 있을 때. 가장 빠름
const	PK, UK 기반으로 1건만 읽는 경우 (WHERE id = 1)
eq_ref	조인 시, 다른 테이블의 한 row와 정확히 일치 (PK, UK 기반)
ref	인덱스를 통해 여러 건을 찾는 방식 (동등 비교)
range	인덱스 범위 검색 (<, >, BETWEEN, IN)
index	인덱스 전체를 스캔 (INDEX FULL SCAN), 테이블을 읽지는 않지만, 큰 인덱스의 경우 성능 병목 가능성이 있음.
ALL	테이블 전체 스캔 (FULL TABLE SCAN). 최악의 경우

🔁 select_type 값 정리

select_type	설명
SIMPLE	서브쿼리나 UNION 없는 일반 SELECT
PRIMARY	가장 바깥쪽 SELECT
SUBQUERY	서브쿼리 (WHERE 안의 SELECT 등)
DERIVED	FROM 안에 있는 서브쿼리 (파생 테이블)
UNION	UNION 이후의 SELECT
DEPENDENT SUBQUERY	상위 쿼리에 의존적인 서브쿼리
DEPENDENT UNION	상위 쿼리에 따라 결과가 달라지는 UNION

🧠 Extra 값 모음

Extra 값	의미
Using where	인덱스를 통해 필터된 후, MySQL 레벨에서 다시 필터링
Using index	인덱스만으로 결과를 도출함. 주로 커버링 인덱스 상황에서 발생하며, 테이블 row에 접근하지 않음. 단, Using index; Using where처럼 함께 나올 경우에는 인덱스를 통해 후보를 찾고, 추가 필터링을 수행함.
Using index condition	ICP(Index Condition Pushdown). 일부 조건은 인덱스에서 필터링
Using filesort	정렬을 인덱스로 처리하지 못하고 메모리나 디스크로 정렬 수행 → 성능 저하
Using temporary	GROUP BY, DISTINCT, ORDER BY 등의 연산을 위해 임시 테이블 생성 → 성능 저하 가능
Using join buffer	인덱스 없이 JOIN 수행 → 조인 버퍼에 저장해서 처리. 비효율적
Impossible WHERE	WHERE 조건이 절대 참이 될 수 없음 → 결과 없음
Using where with pushed condition	InnoDB가 일부 WHERE 조건을 스토리지 엔진에서 필터링

---

✅ EXPLAIN 순서 예시

SELECT e.name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.dept_id IN (
  SELECT id FROM departments WHERE location = 'SEOUL'
);

📄 EXPLAIN 결과

id	select_type	table	type	key	rows	Extra
1	PRIMARY	e	ref	dept_id	500	Using where
1	PRIMARY	d	eq_ref	PRIMARY	1
2	SUBQUERY	departments	ref	location	10	Using where

🧭 실행 계획 트리 구조
쿼리는 위에서 아래 방향으로 작성되지만, 실행 순서는 하위 쿼리부터 처리됨

[2] SUBQUERY (먼저 실행)
│
└───> departments (WHERE location = 'SEOUL')
      ⇒ id 리스트 반환

[1] PRIMARY 쿼리 실행
│
├───> employees (WHERE dept_id IN 결과)
│     ⇒ rows ≈ 500건 예상
│
└───> JOIN departments ON dept_id = id
      ⇒ type = eq_ref (정확한 매칭, 매우 효율적)

🧠 실행 순서 정리

실행 단계	설명
1단계	id = 2: 서브쿼리 실행 → departments 테이블에서 SEOUL에 해당하는 id 추출
2단계	id = 1: employees 테이블에서 dept_id IN (위 결과)로 필터링
3단계	departments 테이블과 JOIN (조인 조건은 PK 기반으로 매우 빠름)

---

✅ 추가 예제: 단순 JOIN

SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

📄 EXPLAIN 결과

id	select_type	table	type	key	rows	Extra
1	SIMPLE	c	ALL	NULL	1000
1	SIMPLE	o	ref	cust_id_fk	300	Using where

🧭 실행 순서 시각화

[1] SIMPLE 쿼리 (JOIN 순서)
│
├───> customers (Full Table Scan)
│
└───> orders (WHERE customer_id = c.id)
      ⇒ 인덱스를 통해 효율적 탐색

※ 같은 id(1) 값을 가지는 경우, 위에서부터 순서대로 실행됨
※ 옵티마이저는 통계 정보에 따라 더 효율적인 테이블부터 접근할 수 있음.
-> EXPLAIN의 table 순서가 실제 실행 순서를 나타냄.

핵심 요약

• id가 클수록 하위 쿼리, 먼저 실행됨

• 같은 id면 위에서 아래로 조인 순서대로 실행

• Extra에 Using filesort, Using temporary가 있다면 성능 이슈 가능

• type 값이 ALL, index이면 비효율적 접근일 수 있음 → 인덱스 고려

---

💡 주요 개념 2: 쿼리 캐시와 현대적 접근

과거 MySQL 5.x의 쿼리 캐시는 동일한 SQL 문자열의 '결과'를 캐싱했음. 하지만 데이터 변경 시 캐시를 무효화하는 과정의 락(Lock)이 오히려 동시성 저하를 일으켜 MySQL 8.0부터 완전히 제거됨.

• 현대의 패러다임: '결과' 캐싱이 아닌, '계획'과 '데이터 블록'의 캐싱으로 전환됨.
  • Prepared Statement (Plan Cache): Server Side에서 SQL을 파싱하고 생성한 실행 계획을 재사용. 반복적인 쿼리의 파싱 비용을 줄여줌.
  • 버퍼 풀 (Buffer Pool, InnoDB): 디스크의 데이터/인덱스 페이지를 메모리에 캐싱. 디스크 I/O를 최소화하여 성능을 극대화하는 RDBMS의 핵심 메모리 공간.

결론적으로, 이제는 낡은 쿼리 캐시에 의존하는 것이 아니라 최적의 실행 계획이 수립되도록 쿼리를 작성하는 것이 성능의 핵심임.

🧠 코드 예시 (심화)

name 컬럼에 인덱스가 있는 users 테이블에서 흔히 발생하는 성능 저하 케이스 비교.

-- 예시 테이블
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    current_status VARCHAR(10) NOT NULL,
    INDEX idx_name_status (name, current_status) -- 복합 인덱스
);
-- (수백만 건의 데이터가 있다고 가정)

Case 1: 가장 이상적인 인덱스 사용 (Index Seek)

EXPLAIN SELECT id, email FROM users WHERE name = 'dev_user';

• 결과 예측: type: ref. key: idx_name_status. rows: 1 (예상).
  -> idx_name_status 인덱스를 통해 정확히 한 건을 찾아냄.

Case 2: 인덱스 컬럼에 함수 사용 (Index 사용 불가)

EXPLAIN SELECT id, email FROM users WHERE UPPER(name) = 'DEV_USER';

• 결과 예측: type: ALL. key: NULL.
  -> 인덱스가 걸린 name 컬럼에 UPPER() 함수를 적용하는 순간, 옵티마이저는 인덱스를 활용할 수 없게 되어 Full Table Scan을 수행함. (애플리케이션단에서 대문자로 변환 후 쿼리해야 함)

Case 3: 커버링 인덱스 (Covering Index)

EXPLAIN SELECT name, current_status FROM users WHERE name = 'dev_user';

• 결과 예측: type: ref. Extra: Using index.
  -> SELECT 절에 필요한 name, current_status 컬럼이 모두 idx_name_status 인덱스에 포함되어 있음. 따라서 DB는 테이블 데이터를 읽지 않고 인덱스만으로 결과를 반환. I/O를 극적으로 줄이는 최고의 최적화 기법.

Case 4: 복합 인덱스의 순서 오류

EXPLAIN SELECT id, email FROM users WHERE current_status = 'ACTIVE';

• 결과 예측: type: ALL 또는 type: index.
  -> key가 idx_name_status를 사용하더라도 비효율적. 복합 인덱스 (name, current_status)는 name 조건이 먼저 와야 효율적으로 동작함. current_status만으로는 인덱스의 선행 컬럼 조건을 만족하지 못해 인덱스 전체를 훑거나(index scan) 테이블 전체를 훑음(full scan).

---

🔍 더 깊이 찾아보기

• 복합 인덱스 (Composite Index)와 컬럼 순서:
  • 어떤 컬럼을 앞에 두느냐에 따라 인덱스 효율이 극명하게 달라짐.
  • Cardinality가 높은(고유한 값의 비율이 높은) 컬럼을 앞에 두는 것이 일반적.
• 옵티마이저 힌트 (Optimizer Hints):
  • 옵티마이저가 비효율적인 실행 계획을 세울 때, 개발자가 직접 특정 인덱스를 사용하도록 강제하는 기법.
  • 남용은 금물이지만, 통계 정보 문제 등으로 잘못된 판단을 내릴 때 유용함.
• 데이터베이스 통계 정보 (Database Statistics):
  • 옵티마이저는 통계 정보를 기반으로 실행 계획을 수립함.
  • 대량의 데이터 변경 후 ANALYZE TABLE 같은 명령어로 통계 정보를 갱신해주면 더 정확한 실행 계획을 유도할 수 있음.
• 클러스터링 인덱스 (Clustering Index):
  • 데이터의 물리적 저장 순서를 결정하는 인덱스(ex: InnoDB의 Primary Key).
  • 클러스터링 인덱스의 설계는 데이터 조회, 삽입 성능 전반에 큰 영향을 미침.