[DB MySQL] 옵티마이저(Optimizer)란? INDEX EXPLAIN 실행

옵티마이저(Optimizer)는 SQL 쿼리를 실행할 때 가장 효율적인 실행 계획을 선택하는 MySQL의 내부 엔진입니다.
즉, MySQL이 쿼리를 실행할 때 "어떤 인덱스를 사용할지", "어떤 방식으로 데이터를 검색할지" 등을 결정하는 역할을 합니다.

쉽게 말해, 옵티마이저는 쿼리 성능을 최적화해주는 엔진입니다.

---

EXPLAIN 실행 시 type이 ALL로 나오는 경우

EXPLAIN SELECT * FROM users WHERE age > 30;

 

 

 

- type = ALL이란?

• "풀 테이블 스캔(Full Table Scan)"이 발생한 상태입니다.
• 즉, MySQL이 테이블의 모든 행을 검사하면서 age > 30 조건을 만족하는 데이터를 찾고 있습니다.
• 인덱스를 사용하지 않기 때문에 성능이 매우 비효율적입니다.

- "ALL"이 나오는 이유

1. age 컬럼에 인덱스가 없기 때문 (CREATE INDEX idx_users_age ON users(age); 해주면 해결 가능)
2. WHERE 조건이 인덱스에 적합하지 않거나(예: LIKE '%abc%') 인덱스를 무시하는 경우

- 해결 방법

• WHERE 조건에 사용된 컬럼에 적절한 인덱스를 추가하면 ALL 대신 range 또는 ref로 바뀌면서 성능이 개선됨

---

type의 종류와 의미

EXPLAIN 실행 시 type 칼럼은 MySQL이 데이터를 검색하는 방식을 나타내며, 성능에 중요한 영향을 미칩니다.
type이 왼쪽으로 갈수록 효율이 낮고, 오른쪽으로 갈수록 효율이 높아집니다. 🚀

 

 

---

각 type 값의 의미와 예제

1. ALL (풀 테이블 스캔, Full Table Scan)

EXPLAIN SELECT * FROM users WHERE age > 30;

 

 

 

- 문제점:

• 모든 행을 검사해야 하기 때문에 테이블 크기가 커질수록 성능이 급격히 저하됨
• 인덱스를 활용하지 않기 때문에 디스크 I/O가 많아짐

- 해결 방법:

• WHERE에서 사용된 컬럼(age)에 인덱스를 추가
• 불필요한 SELECT * 대신 필요한 컬럼만 조회

CREATE INDEX idx_users_age ON users(age);

---

2. index (인덱스 풀 스캔, Index Full Scan)

EXPLAIN SELECT id FROM users;

 

 

- 특징:

• ALL보다 나은 방식이지만, 여전히 인덱스의 모든 값을 순차적으로 검색해야 함
• 인덱스에서 직접 데이터를 가져오기 때문에 테이블 접근은 없음
• ORDER BY가 인덱스 순서와 일치할 때 발생 가능

---

3. range (인덱스 범위 검색, Index Range Scan)

EXPLAIN SELECT * FROM users WHERE age BETWEEN 25 AND 30;

 

 

- 특징:

• 특정 범위(BETWEEN, >, <, LIKE 'abc%')만 검색
• ALL보다 훨씬 효율적이며, 필요한 데이터만 빠르게 검색 가능

---

4. ref (인덱스를 활용한 검색, Index Lookup)

EXPLAIN SELECT * FROM users WHERE email = 'abc@example.com';

 

 

 

- 특징:

• 인덱스를 사용하여 특정 값을 검색
• 하지만 UNIQUE INDEX가 아니므로 중복된 값도 검색 가능

---

5. eq_ref (유니크 인덱스를 활용한 검색, Unique Index Lookup)

EXPLAIN SELECT * FROM users WHERE id = 1;

 

 

- 특징:

• PRIMARY KEY 또는 UNIQUE 제약 조건이 걸린 컬럼을 =로 검색할 때 발생
• 항상 단 하나의 행만 검색되므로 성능이 가장 좋음

 

 

 

풀 테이블 스캔(Full Table Scan)

**풀 테이블 스캔(Full Table Scan)**이란,
* MySQL이 테이블의 모든 행을 하나씩 읽으며 조건을 검사하는 방식을 의미합니다.
*  EXPLAIN에서 type = ALL이면 풀 테이블 스캔이 발생한 것입니다.
* 대용량 데이터에서 풀 테이블 스캔이 발생하면 성능이 크게 저하됨

 

- 풀 테이블 스캔 방지 방법 1) WHERE 조건에 사용되는 컬럼에 인덱스 추가
2) 불필요한 SELECT * 대신 필요한 컬럼만 조회
3) LIKE '%abc%' 같은 비효율적인 패턴을 피하고 LIKE 'abc%'처럼 활용
4) JOIN 시 적절한 인덱스 활용

---

정리

- 옵티마이저는 SQL 실행 계획을 최적화하는 엔진
- EXPLAIN의 type 칼럼이 실행 방식 결정
- "ALL" (풀 테이블 스캔)은 최악의 경우로 피해야 함
- 최적화 방법: 인덱스 추가, SELECT 최적화, 범위 검색 개선

 

 

다음 포스팅에서는 옵티마이저에게 특정 실행 계획을 강제하는 명령어인 '힌트(Hint)'에 대해 포스팅하도록 하겠습니다.