데이터베이스 쿼리 실행계획

우리네 개발인생 쿼리가 느려지면 인덱스를 추가할생각부터 한다.

그러나 데이터베이스가 실제로 어떻게 쿼리를 처리하는지 분석한적이 있던가?

 

이번에는 데이터베이스 성능 최적화의 핵심인 실행계획 이해하고

실제로 어떻게 성능을 튜닝할 수 있는지 알아보자

 

---

 

 

1. 실행계획? 그게 뭔데

DB 실행계획은 EXPLAIN 명령어로 확인할 수 있는 쿼리 실행계획을 분석하고 개선하는 과정을 말한다.

실행계획은 DB 엔진이 수행하는 쿼리의 순서와 방법을 미리 계획한 로드맵과 같다고 할 수 있다.

 

이를 통해 궁극적으로

쿼리가 왜 느린지,

어디서 병목이 발생했는지,

그리고 어떻게 개선할 수 있는지를 파악할 수 있다.

 

 

 

 

---

 

2. 실행계획 프로세스

 

실행 계획 순서

1. 실행계획 확인
  1.1 EXPLAIN or EXPLAIN ANALYZE 명령어 사용
  1.2 각 단계별 소요시간, 처리 행 수 등 확인

 

2. 주요 분석 포인트 파악
  2.1 조인 방식 파악
  2.2 테이블 스캔 파악 (Full Scan vs Index Scan)

  2.3 필터링(where 조건 처리 순서 및 효율성)
  2.4 정렬 (ORDER BY, GROUP BY)

 

3. 개선 작업
  3.1 인덱스 추가 및 수정
  3.2 쿼리 구조 변경 (조인, 서브쿼리 등)

 

 

---

 

3. 실행계획 예시

 

 

3. 1 쉬운것부터 시작해보자

explain select * from orders;

 

결과:

id	select_type	table	type	possible_keys	keys	key_len	ref	rows	filtered	extra
1	SIMPLE	orders	ALL					79	100

 

 

도출된 결과를 한 눈에 정리하자면 아래와 같다.

id=1,	=> 실행 계획 첫번째
select_type=SIMPLE,		=> 조회타입(select_type) -> 전체
table=orders, 	=> 테이블 명
type=ALL,	=> 풀스캔
key=NULL,	=> 인덱스 사용X
rows=79,	=> 전체 row 수 
filtered=100	=> 필터(where 절)이 없으므로 전부 읽음

 

 

 

위 결과를 단순히 해석해보자

 

=> 현재 쿼리 결과는 풀스캔, 조건절 없이 전부 읽고 처리하고 있다.

=> 쿼리의 목적 자체가 orders 테이블 전체 조회용이다.

=> 여러가지 개선 방향이 존재한다.

 

 

개선안
실행계획 튜닝은 쿼리 자체를 더 구체적으로 만드는 것에서 시작한다.

 

 

 

1. 필요한 컬럼만 조회

ex) SELECT id, created_at, status FROM orders;

 

2. 페이징 / 조건 처리

  - 전체 데이터를 굳이 조회할 필요가 없다면 일부분만 가져오도록 처리한다.

ex) SELECT *FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;

 

3. 인덱스 활용

  - 자주 사용하는 쿼리인 경우 인덱스를 추가한다.

ex) CREATE INDEX idx_orders_created_at ON orders(created_at);

 

 

 

---

 

 

 

 

 

 

 

3. 2 더 복잡한 상황에서 어떻게 실행계획을 활용할 수 있는지 알아보자.

EXPLAIN
SELECT o.id, o.created_at, o.status, oi.id AS item_id, oi.price, pv.voucher_code, uv.unit_code
FROM orders o
LEFT JOIN order_item oi ON oi.parent_order_idx = o.id
LEFT JOIN product_voucher pv ON pv.order_item_idx = oi.id
LEFT JOIN unit_voucher uv ON uv.product_voucher_idx = pv.id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'COMPLETED'
ORDER BY o.created_at DESC
LIMIT 50;

 

 

 

결과: 

id	select_type	table	type	possible_keys	keys	key_len	ref	rows	filtered	extra
1	SIMPLE	o	ALL	idx_created_at_status	NULL	NULL	NULL	500000	10	Using where; Using filesort
1	SIMPLE	oi	ref	FK_order_item_order	FK_order_item_order	8	o.id	5	100
1	SIMPLE	pv	ref	FK_pv_order_item	FK_pv_order_item	8	oi.id	30	100
1	SIMPLE	uv	ref	FK_uv_product_voucher	FK_uv_product_voucher	8	pv.id	20	100

 

 

아까보다 뭐가 더 많아졌다.

이번에도 결과를 먼저 한눈에 정리해보자.

1. Orders o
type=ALL,	=> 풀스캔
rows=500000,	=> orders의 전체 레코드 50만 건 조회
filtered=10	=> where절의 o.status, o.createdAt 처리를 통해 정체 중 약 10%만 사용
extra	=> Where절이 추가되었지만, 정렬(ORDER BY created_at DESC) 때문에 추가적인 정렬 비용 발생



2. OrderItem oi
type=ref,
  => 인덱스를 이용해서 동등조건으로 탐색
  => 인덱스 컬럼에 대한 "=" 조건으로 매칭할 때 주로 나옴 (oi.parent_order_idx = o.id)
  
rows=5
  => 평균적으로 order당 매칭되는 orderItem이 5개로 예측
  => 조인된 테이블이고 type=ref이므로 "조회" 개념이 아닌 상위 테이블과 매칭되는 갯수를 의미
    
    
3. ProductVoucher pv	=> 2번과 같음 (pv.order_item_idx = oi.id)


4. UnitVoucher uv	=> 2번과 같음 (uv.product_voucher_idx = pv.id)

 

 

 

문제점 분석

- Orders 테이블의 풀스캔과 정렬에 대한 비용

- 정렬 비용에 의한 병목 발생 여지

- 하위 테이블들은 인덱스를 사용하여 문제가 없음

 

 

 

 

 

개선안

 

 

 

1. select절 최적화

  - 불필요한 수십개의 컬럼들을 모두 조회하는건 I/O 낭비

ex) SELECT o.id, o.created_at, o.status, oi.id AS item_id, oi.price, pv.voucher_code, uv.unit_code

 

 

2. 복합 인덱스 활용(위 상황에 적합한)

  - 위 쿼리의 주 관심사인 컬럼들을 복합 인덱스 처리하여 조회하는 row의 스캔 범위를 줄인다

  - 실행 계획 시 ALL => ref or range로 변화 기대

  - 조건절 필터링 + 정렬을 동시에 해결하여 "filesort"를 해결할 수 있다.

ex) CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);

 

 

3. 커버링 인덱스 활용(자주 사용되는 쿼리일 경우)

  - 실제 테이블 접근 없이 인덱스 테이블을 통해서 조회 처리 기대

  - orders 테이블 풀스캔(ALL) → index range scan으로 변경

ex) CREATE INDEX idx_orders_status_created_at_id ON orders (status, created_at, id);

 

 

 

 

* 복합인덱스와 커버링인덱스

복합 인덱스:

- 여러개의 컬럼을 하나의 인덱스로 묶어서 사용하는 방식의 인덱스

- WHERE절과 ORDER BY 최적화에 주로 사용

 

커버링 인덱스:

- 실제 데이터베이스 접근 없이 인덱스 테이블만 거쳐서 조회 요청을 처리하는 것.

- ex) id, username, age, cellphoneNumber를 모두 포함한 복합 인덱스 생성 후

   id, username, age, cellphoneNumber만 조회하는 쿼리를 수행하는 것

 

 

 

---

 

결론:
실행계획은 DB 성능 튜닝의 출발점이다.

무작정 인덱스를 추가하기보다는 EXPLAIN으로 병목 지점을 정확히 파악하고,
쿼리 목적에 맞는 최적화 전략을 선택해야 한다.

풀스캔이 나쁜 게 아니라 불필요한 풀스캔이 문제이고,
인덱스도 만능이 아니라 올바른 상황에서 올바르게 사용해야 진짜 효과를 볼 수 있다.

성능 튜닝의 핵심은 측정 → 분석 → 개선 → 재측정의 반복이다.