MySQL 쿼리 최적화: 테이블 스캔, 인덱스 스캔, 옵티마이저, 인덱스 힌트, EXPLAIN ANALYZE

MySQL 쿼리 최적화: 테이블 스캔, 인덱스 스캔, 옵티마이저, 인덱스 힌트, EXPLAIN ANALYZE

MySQL에서 쿼리 성능 최적화를 위해 가장 중요한 요소 중 하나는 옵티마이저(Optimizer)가 선택하는 실행 계획(Execution Plan) 이다.
MySQL의 옵티마이저가 올바른 인덱스를 선택하지 못할 경우, 인덱스 힌트(Index Hints)를 사용하여 최적의 실행 계획을 유도할 수 있다.

즉, EXPLAIN ANALYZE를 활용하여 옵티마이저의 실행 계획을 분석하고, 인덱스 힌트를 사용한다.

1. 테이블 스캔 vs 인덱스 스캔

테이블 스캔 (Full Table Scan)

• 정의:
  인덱스를 사용하지 않고 테이블의 모든 행(데이터 페이지)을 순차적으로 읽어오는 방식이다.

• 특징:

  • EXPLAIN 결과의 “type” 컬럼에 ALL로 표시된다.
  • 적절한 인덱스가 없거나, 반환할 행 수가 테이블 전체의 큰 비율일 때 옵티마이저가 선택한다.
  • 순차적 읽기(시퀀셜 I/O)로 디스크 접근은 효율적일 수 있지만, 불필요한 행도 모두 읽기 때문에 비용이 높다.

---

인덱스 스캔 (Index Scan)

• 정의:
  B-Tree 인덱스를 사용하여 조건에 맞는 인덱스 리프 노드를 탐색한 후, 해당 값에 대응하는 행(주로 PK)를 찾아 데이터를 조회하는 방식이다.
  커버링 인덱스(covering index): 만약 쿼리에서 필요한 모든 컬럼이 인덱스 내에 포함되어 있다면, 실제 테이블 데이터까지 접근하지 않고 인덱스만으로 처리할 수 있다.

• 특징:

  • EXPLAIN 결과에서 range, ref, eq_ref 등으로 표시된다.
  • 조건에 맞는 행이 테이블 전체에 비해 상대적으로 적은 경우(예: 5~25% 미만) 비용이 낮아진다.
  • 인덱스에 포함되지 않은 컬럼이 필요하면 인덱스에서 찾은 PK로 추가 랜덤 I/O가 발생할 수 있다. (비 클러스터 형 인덱스 사용 시)

---

요약

• 테이블 스캔:
  전체 테이블 데이터를 순차적으로 읽어 “ALL” 방식으로 처리하며, 인덱스가 없거나 반환 행이 많을 때 선택된다.

• 인덱스 스캔:
  인덱스를 통해 조건에 맞는 행의 위치를 빠르게 찾고(종종 커버링 인덱스를 활용), “range”, “ref”, “eq_ref” 방식으로 처리되어 반환 행이 적을 때 효율적이다.

MySQL 옵티마이저는 데이터 분포, 인덱스 커버리지, 예상 반환 행 수 등을 고려하여 두 방식을 자동으로 선택한다.

출처:
dev.mysql.com
velog.io

2. MySQL 인덱스 힌트(Index Hints)란?

MySQL 옵티마이저는 기본적으로 쿼리 실행 시 가장 효율적인 인덱스를 자동으로 선택한다.
하지만, 때로는 옵티마이저의 선택이 최적이 아닐 때 인덱스 힌트(Index Hints) 를 사용하여 원하는 인덱스를 사용하거나 무시하도록 지시할 수 있다.

MySQL에서는 기본적으로 옵티마이저(Optimizer)가 자동으로 최적의 인덱스를 선택한다.
하지만, 경우에 따라 MySQL이 적절한 인덱스를 사용하지 않고, 풀 테이블 스캔을 수행할 수도 있다.

👉 이럴 때 특정 인덱스를 강제로 사용하도록 하는 방법이 FORCE INDEX, USE INDEX, IGNORE INDEX 이다.

1-1. 인덱스 힌트 종류

인덱스 힌트	설명	예제
USE INDEX (index_list)	특정 인덱스를 사용하도록 권장	SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 1001;
FORCE INDEX (index_list)	특정 인덱스를 반드시 사용	SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 1001;
IGNORE INDEX (index_list)	특정 인덱스를 무시	SELECT * FROM orders IGNORE INDEX (idx_customer_id) WHERE customer_id = 1001;

🚨 주의:

• USE INDEX는 옵티마이저에게 특정 인덱스를 "사용하라"고 요청하지만, 필수는 아님.
• FORCE INDEX는 옵티마이저가 무조건 해당 인덱스를 사용하도록 강제함.
• IGNORE INDEX는 특정 인덱스를 무시하고 다른 인덱스를 사용하거나 풀 테이블 스캔을 유도함.

📌 사용 시점

• 옵티마이저가 적절한 인덱스를 사용하지 않을 경우
• 실행 계획을 분석한 후 특정 인덱스를 강제 적용해야 할 필요가 있을 경우

---

2. EXPLAIN ANALYZE로 실행 계획 분석

2-1. EXPLAIN, EXPLAIN ANALYZE란?

MySQL 8.0부터 도입된 EXPLAIN ANALYZE를 사용하면 실제 실행 계획과 소요 시간, I/O 통계 등을 확인할 수 있다.

✅ 사용 예제

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1001;

✅ 실행 결과 예시
EXPLAIN 문

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	execution time
1	SIMPLE	orders	ref	idx_customer_id	idx_customer_id	4	const	10	100.00	0.005 sec
![[무제 1-20250225143926508.webp]]
EXPLAIN ANALYZE 문

📌 분석 방법

• key: 사용된 인덱스 확인 (idx_customer_id 사용)
• rows: 조회된 예상 행 수 (10개)
• execution time: 실제 실행 시간 (0.005초)
• type: ALL(풀 스캔) → ref → range → const → system 순으로 효율적

✅ 활용 방법

• EXPLAIN ANALYZE 실행 후 인덱스 미사용 시 → USE INDEX / FORCE INDEX 고려
• rows 값이 지나치게 크면 → 인덱스 추가 또는 조건 최적화 필요

참고 자료:

• MySQL 8.0 Reference Manual – Index Hints
• MySQL 8.0 Reference Manual – EXPLAIN ANALYZE