[Index-6][Optimization] 실전 분석 (2) - 실행 계획 타입(Type)

1. 들어가며

 이전 장에서 우리는 데이터베이스의 두뇌인 옵티마이저가 최적의 경로를 찾는 과정인 '쿼리 실행 계획'의 개념을 살펴보았다. 이번 장에서는 실제 MySQL에서 제공하는 도구를 활용하여 실행 계획을 확인하는 방법과, 출력되는 지표 중 핵심적인 'Type'을 해석하는 방법을 상세히 고찰해 보고자 한다.

---

2. 실행 계획 확인 방법: DESC 명령의 활용

 MySQL에서 실행 계획을 확인하는 방법은 매우 간단하다. 실행하고자 하는 SELECT 쿼리 앞에 DESC 또는 EXPLAIN 키워드를 붙여 실행하면 된다.

DESC SELECT * FROM book;

실행 결과로 출력되는 표에는 다양한 정보가 포함되어 있으나, 성능 최적화 관점에서 반드시 주목해야 할 핵심 항목은 다음과 같다.

• id: 쿼리 내의 처리 순서를 나타낸다.
• table: 조회의 대상이 되는 테이블 명이다.
• type: 데이터를 조회한 방식(스캔 방식)을 나타내며, 성능을 판단하는 가장 중요한 지표이다.
• key: 데이터 조회 시 실제로 사용된 인덱스이다.
• rows: 쿼리 수행을 위해 접근한 예상 행 수이다. 이 수치가 낮을수록 쿼리 효율이 높으며, 튜닝의 핵심은 이 값을 줄이는 데 있다.
• filtered: 필터 조건에 의해 걸러지고 남은 데이터의 비율(%)을 나타낸다.

---

3. 실행 계획 타입(Type)별 심층 분석 (★)

 실행 계획에서 가장 주목해야 할 지표는 type이다. 이는 데이터베이스가 테이블의 레코드를 어떤 방식으로 읽었는지를 나타내는 핵심적인 정보이다. 성능이 낮은 순서부터 단계별로 그 특징과 B+Tree 내부의 동작 원리를 고찰해 보고자 한다.

3.1. ALL: 풀 테이블 스캔 (Full Table Scan)

ALL 타입은 인덱스를 전혀 활용하지 못하고 테이블의 처음부터 끝까지 모든 레코드를 읽는 방식이다.

-- book 테이블에 인덱스가 없는 price 컬럼 조회
DESC SELECT * FROM book WHERE price = 11000;

• 동작 원리: B+Tree 구조를 전혀 사용하지 않으며, 물리적인 데이터 페이지를 순차적으로 모두 읽어 들인다.
• 발생 상황: 조회 조건에 해당하는 인덱스가 없거나, 옵티마이저가 전체 데이터를 읽는 것이 더 빠르다고 판단했을 때 발생한다. 가장 비효율적인 방식이므로 튜닝의 1순위 대상이 된다.

3.2. index: 인덱스 풀 스캔 (Index Full Scan)

실제 테이블에 접근하지 않고 인덱스의 리프 노드만을 순차적으로 스캔하는 방식이다.

-- book 테이블에 idx_price 인덱스가 있는 경우
CREATE INDEX idx_price ON book(price);
-- 인덱스 칼럼만 조회 (커버링 인덱스)
DESC SELECT price FROM book;

• 동작 원리: 인덱스가 정렬되어 있다는 특성을 활용하여 테이블 접근 없이 인덱스 페이지 내에서 모든 데이터를 가져온다. '커버링 인덱스' 상황에서 발생한다.
• 비교: ALL보다는 효율적이지만, 인덱스 전체를 훑어야 한다는 점에서는 다른 타입들보다 비용이 높을 수 있다. 하지만 테이블 조회를 생략한다는 점에서 특정 상황에서 매우 유용하다.

💡커버링 인덱스란?
쿼리에서 필요한 모든 데이터가 인덱스에 포함되어 있어, 테이블을 접근할 필요가 없는 상황

-- 커버링 인덱스 O: 인덱스 칼럼만 조회
SELECT price FROM book;  -- type: index

-- 커버링 인덱스 X: 다른 칼럼도 조회
SELECT price, title FROM book;  -- type: ALL (테이블 접근 필요)​

3.3. range: 인덱스 범위 스캔 (Index Range Scan)

인덱스를 사용하여 특정 범위 내의 데이터만 읽어 들이는 방식이다.

-- 인덱스 생성 후 범위 검색
CREATE INDEX idx_price ON book(price);
DESC SELECT * FROM book WHERE price BETWEEN 10000 AND 20000;

• 동작 원리: Root에서 시작해서 10000이 있는 leaf node까지 내려간 후, 그 지점부터 20000이 있는 leaf node까지 순차적으로 스캔한다. 리프 노드들이 링크드 리스트처럼 연결되어 있어 범위 스캔이 가능하다.
• 효과: BETWEEN, >, < 등의 연산자 사용 시 발생하며, 특정 범위의 리프 노드만 읽으므로 ALL 타입보다 월등히 효율적이다.

3.4. ref: 비고유 인덱스 참조 (Non-Unique Index Lookup)

고유하지 않은 일반 인덱스(Secondary Index)를 사용하여 동등(=) 조건으로 검색할 때 발생한다.

-- price에 중복 값이 있을 수 있는 일반 인덱스
DESC SELECT * FROM book WHERE price = 11000;

• 동작 원리: B+Tree를 통해 특정 값이 위치한 리프 노드까지 한 번에 찾아간다. 범위 전체를 스캔할 필요 없이 특정 값의 위치만 정확히 타격하므로 range보다 더 효율적인 탐색이 가능하다.

3.5. const: 고유 인덱스 참조 (Unique Index Lookup)

PRIMARY KEY나 UNIQUE 인덱스를 사용하여 단 하나의 행을 찾을 때 발생하는 가장 빠른 검색 방식이다.

-- id는 PRIMARY KEY
DESC SELECT * FROM book WHERE id = 1;

• 동작 원리: 고유한 값을 찾기 때문에 B+Tree 탐색 결과가 반드시 하나임을 보장한다. 단 하나의 리프 노드만 확인하면 탐색이 종료되며, 데이터베이스 입장에서는 조회 결과를 상수(constant)처럼 취급할 수 있을 정도로 빠르다.

---

4. 성능 비교 요약 및 실행 계획의 유동성

성능의 효율성은 일반적으로 const > ref > range > index > ALL 순으로 평가된다. 그러나 실행 계획 수립에는 절대적인 법칙이 존재하지 않는다.

1. 데이터 분포에 따른 변화: 동일한 범위 검색 쿼리라도 검색 범위가 좁으면 range 스캔을 선택하지만, 범위가 너무 넓어 전체 데이터의 상당 부분을 차지하면 옵티마이저는 인덱스 대신 ALL 스캔을 선택하는 영리함을 발휘한다. 인덱스를 타고 데이터를 하나씩 찾는 것보다 통째로 읽는 것이 비용 면에서 유리하기 때문이다.
2. 현실적인 최적화: '구린' 실행 계획인 ALL이 반드시 성능 장애를 의미하지는 않는다. 데이터 양이 매우 적은 경우 풀 스캔으로도 수 밀리초 내에 처리가 가능하기 때문이다.

결론적으로, 개발자는 모든 쿼리를 무조건 const로 만드는 데 집착하기보다, 실제 서비스에서 어떤 쿼리가 어느 정도의 부하를 유발하는지를 통계적으로 파악하고, 성능에 유의미한 영향이 큰 구간부터 전략적으로 최적화하는 태도를 가져야 한다.

Type	사용 시나리오	인덱스 사용 방식	테이블 접근	효율성
ALL	인덱스가 없거나 옵티마이저가 풀 스캔이 더 유리하다고 판단한 경우	❌ 인덱스 미사용	✅ 테이블 전체 스캔	가장 낮음
index	커버링 인덱스 쿼리 (SELECT 컬럼이 인덱스에 모두 포함)	✅ 인덱스 전체 리프 노드 스캔	❌ 테이블 접근 없음	ALL보다 나음
range	범위 검색 (BETWEEN, >, <, >=, <=)	✅ 시작점 탐색 후 범위 스캔	✅ 필요한 행만 접근	매우 효율적
ref	일반(비고유) 인덱스의 동등 조건 검색 (=)	✅ 특정 값 위치 정확히 탐색	✅ 필요한 행만 접근	range보다 효율적
const	PK 또는 UNIQUE 인덱스로 단일 행 검색	✅ 단 하나의 노드만 탐색	✅ 단일 행만 접근	가장 효율적

💡 실행 계획의 유동성
옵티마이저의 판단은 데이터의 분포에 따라 달라질 수 있다. 예를 들어, 범위 검색이라도 조회하려는 데이터가 전체의 상당 부분(보통 20~25% 이상)을 차지하면, 인덱스를 경유하는 것보다 ALL 스캔으로 한꺼번에 읽는 것이 더 비용이 적다고 판단하여 실행 계획을 변경하기도 한다.

---

5. 분석 고도화: DESC ANALYZE

이때 단순한 실행 계획 예측을 넘어, 실제 실행 시간과 비용을 확인하고 싶다면 DESC ANALYZE 명령어를 사용한다. 출력되는 메시지 중 actual time은 데이터베이스 엔진 내부에서 소요된 순수 쿼리 처리 시간을 밀리초(ms) 단위로 나타낸다. 실제 클라이언트가 체감하는 시간과는 네트워크 전송 등의 요인으로 차이가 있을 수 있으나, 쿼리 자체의 병목 구간을 파악하는 데에는 가장 정확한 지표가 된다.

DESC ANALYZE [SQL 문];

DESC ANALYZE SELECT * FROM book;
> Table scan on book (cost=100520 rows=996789) (actual time=0.248..190 rows=1e+6 loops=1)¶|