[SQL] 개발자가 반드시 알아야 할 쿼리 튜닝의 핵심

status

Public

date

Jan 9, 2026

slug

sql-tuning-guide-mysql-execution-plan

summary

실무에서 바로 사용하는 SQL 튜닝 가이드. MySQL 아키텍처부터 실행 계획(Explain) 분석 방법, 드라이빙 테이블 선정 기준, 그리고 악성 쿼리(JOIN, UNION) 최적화 사례까지 성능 개선을 위한 핵심 기법을 정리했습니다. A practical guide to SQL tuning for developers. Covers MySQL architecture, execution plan analysis, driving table selection, and real-world optimization examples for JOINs and aggregations to boost query performance.

type

Post

1. 물리 엔진과 SQL 수행 절차

SQL 튜닝을 하려면 DB가 데이터를 어떻게 처리하는지 알아야 한다. MySQL은 크게 머리 역할을 하는 엔진(Engine)과 손발 역할을 하는 스토리지(Storage)로 나뉜다.

MySQL 아키텍처


graph TD
    User["사용자 (Client)"] -->|"SQL 전송"| Server["MySQL Server"]

    subgraph SQLEngine ["1. MySQL 엔진 (Brain)"]
        direction TB
        Parser["(1) Parser"] 
        Optimizer["(2) Optimizer"]
        Executor["(3) Executor"]
    end

    subgraph Storage ["2. 스토리지 엔진 (Hands/Feet)"]
        InnoDB["InnoDB"]
        Disk[("Data (Disk)")]
    end

    Server --> Parser
    Parser -->|"파서 트리"| Optimizer
    Optimizer -->|"실행 계획"| Executor
    Executor -->|"Handler API 요청"| InnoDB
    InnoDB --- Disk

Parser (파서): 문법 검사 및 파서 트리 생성 (Syntax/Lexical Analysis).

Optimizer (옵티마이저): 비용(Cost) 기반으로 최적의 실행 계획 수립 (인덱스 선택, 조인 순서 결정).

Executor (실행기): 스토리지 엔진에 데이터를 요청하여 결과 반환.

Storage Engine (스토리지 엔진): 디스크/메모리에서 실제 데이터를 I/O 수행 (InnoDB 등).

2. 논리적 용어와 서브쿼리 최적화

서브쿼리의 종류와 특성

스칼라 서브쿼리: 단 1행 1열(값 하나)만 반환. Select 절에 주로 사용.

인라인 뷰 (Inline View): From 절에 사용. 임시 테이블을 생성할 수 있어 주의 필요.

중첩 서브쿼리: Where 절에 사용.

인라인 뷰(Derived Table) vs 조인(Join)

인라인 뷰: GROUP BY나 DISTINCT가 포함되면 옵티마이저가 뷰 병합(View Merging)을 하지 못하고 임시 테이블(Materialized Table)을 생성함. 메모리 오버헤드 발생 가능.

조인: 데이터를 미리 복사하지 않고 인덱스를 타고 실시간으로 연결. 대량의 데이터 집계가 필요한 경우가 아니라면 조인이 메모리 효율 측면에서 유리함.

3. 쿼리 튜닝의 핵심 메커니즘

Driving Table vs Driven Table

조인 성능의 90%는 드라이빙 테이블 결정에 달려있다.

Driving Table (Outer): 먼저 액세스하는 테이블. 필터링 후 결과 건수가 적은 테이블을 선택해야 함.

Driven Table (Inner): 나중에 액세스하는 테이블. 조인 조건(Join Key)에 인덱스가 필수적으로 있어야 함.

Why? Nested Loop Join 특성상, Driving Table의 결과 행 수만큼 Driven Table을 반복 탐색(Random Access)하기 때문.

Selectivity(선택도)와 Cardinality(카디널리티)

옵티마이저가 인덱스를 탈지 말지 결정하는 기준.

지표	의미	인덱스 효율성
NDV (Number of Distinct Values)	컬럼의 고유 값 개수	높을수록 좋음
Selectivity (선택도)	특정 조건으로 선택되는 비율	낮을수록 좋음 (보통 5~10% 미만)
Cardinality	예상되는 행의 수	낮을수록 좋음

결론: 데이터의 분포가 넓게 퍼져 있어 특정 소량만 가져올 수 있을 때 인덱스가 효율적임. 전체의 15% 이상을 가져와야 한다면 Full Table Scan이 더 빠를 수 있음.

4. 실행 계획(Explain) 분석 필수 항목

EXPLAIN 명령어로 쿼리 앞에 붙여 실행 계획을 확인한다.

type: 접근 방식의 효율성 등급.

const, eq_ref, ref: 좋음 (인덱스 활용)
range: 보통 (범위 검색)
index: 나쁨 (인덱스 풀 스캔)
ALL: 최악 (테이블 풀 스캔)

rows: 스캔할 것으로 예상되는 행의 수. (적을수록 좋음)

Extra:

Using index: 커버링 인덱스 (매우 빠름).
Using filesort: 별도의 정렬 작업 발생 (튜닝 필요).
Using temporary: 임시 테이블 생성 (튜닝 필요).

5. 실전 악성 SQL 튜닝 사례

Case 1. 잘못된 Driving Table 선정

옵티마이저가 통계 정보 오류로 인해 데이터가 많은 테이블을 먼저 읽는(Driving) 경우.

[Before] 비효율적인 실행 계획

dept_emp_mapping 테이블(대용량)을 먼저 읽고 dept 테이블을 조인함.


SELECT de.emp_id, d.dept_id
  FROM dept_emp_mapping de, dept d
 WHERE de.dept_id = d.dept_id
   AND de.start_date >= '2002-03-01';

[After] STRAIGHT_JOIN 힌트 사용

dept 테이블(소량)을 먼저 읽도록 강제하거나, 조건절(start_date)로 데이터가 획기적으로 줄어드는 테이블을 드라이빙으로 유도.


- 옵티마이저에게 순서를 강제 (de -> d 순서가 효율적이라 판단될 경우)

SELECT straight_join de.emp_id, d.dept_id 
FROM dept_emp_mapping de, dept d 
WHERE de.dept_id = d.dept_id AND de.start_date >= '2002-03-01';

핵심: Where 절 필터링 적용 시 결과 건수가 더 적은 쪽을 먼저 읽게 해야 Random Access 횟수가 줄어듦.

Case 2. 통계 쿼리의 반복 수행 (UNION vs ROLLUP)

소계 및 총계를 구하기 위해 같은 테이블을 여러 번 읽어 UNION ALL로 합치는 경우.

[Before] UNION ALL 사용 (Table Scan 3회 발생)


SELECT region, null, COUNT(*) FROM entry_record GROUP BY region
UNION ALL
SELECT region, gate, COUNT(*) FROM entry_record GROUP BY region, gate
UNION ALL
SELECT null, null, COUNT(*) FROM entry_record;

[After] GROUP BY WITH ROLLUP 사용 (Table Scan 1회로 단축)


SELECT region, gate, COUNT(*)
  FROM entry_record
 GROUP BY region, gate WITH ROLLUP;

핵심: 데이터를 한 번만 읽고 메모리 상에서 집계하는 ROLLUP이 I/O 측면에서 압도적으로 유리함.

+) 쿼리 튜닝이 필요한 5가지 판단 포인트

SQL 쿼리 성능 최적화의 핵심은 "데이터베이스 엔진이 얼마나 적은 데이터를 읽고, 얼마나 효율적으로 결과를 걸러내는가"를 파악하는 것입니다.

1. type: 테이블 접근 방식 (Access Type)

가장 먼저 확인해야 할 컬럼입니다. 테이블을 어떻게 읽었는지를 나타냅니다.

튜닝 대상 (Bad Signals)

ALL (Full Table Scan): 인덱스를 타지 않고 테이블 전체를 읽는 방식입니다. 데이터가 적다면 괜찮지만, 대용량 테이블에서는 디스크 I/O 병목의 주원인입니다.
index (Full Index Scan): 인덱스 전체를 스캔합니다. ALL보다는 빠르지만(인덱스 파일이 데이터 파일보다 작으므로), 여전히 비효율적입니다.

권장 목표 (Good Signals)

const, eq_ref (PK나 Unique Key로 단 1건 조회)
ref (인덱스를 통해 동등 검색)
range (인덱스를 통해 범위 검색)

2. Extra: 추가적인 부하 발생 여부

쿼리 실행 과정에서 엔진이 수행한 추가 작업을 보여줍니다. 성능에 치명적인 키워드가 포함되어 있는지 확인해야 합니다.

튜닝 대상 (Bad Signals)

Using filesort: 인덱스를 이용해 정렬하지 못하고, 별도의 메모리 버퍼(Sort Buffer)나 디스크에서 정렬을 수행했다는 의미입니다. CPU 사용량이 급증합니다. -> 인덱스 추가 및 변경 필요
Using temporary: 결과를 담기 위해 임시 테이블(디스크 또는 메모리)을 생성했다는 의미입니다. GROUP BY, DISTINCT, ORDER BY 처리 시 자주 발생하며 I/O 부하를 유발합니다.

권장 목표

Using index (커버링 인덱스): 실제 데이터 테이블(Heap) 접근 없이 인덱스만으로 쿼리를 처리함. 가장 이상적인 형태입니다.

3. rows: 스캔 범위의 효율성

쿼리 처리를 위해 "조사할 것으로 예측되는 행의 수"입니다. 실제 결과 건수와 rows의 차이가 클수록 비효율적인 쿼리입니다.

판단 기준:

예를 들어 결과는 10건인데 rows가 100만 건이라면, 10건을 찾기 위해 불필요하게 100만 건을 읽었다는 뜻입니다. -> 인덱스 컬럼의 순서 조정 또는 필터링 조건 강화 필요

4. filtered: 필터링 후 남은 데이터 비율

스토리지 엔진이 읽어온 데이터 중, 필터 조건(WHERE)에 의해 최종적으로 남은 데이터의 비율(%)을 나타냅니다.

튜닝 대상:

수치가 낮을수록(예: 0.1, 1.0) 비효율적입니다. 엔진이 열심히 읽어왔지만 대부분 버려졌다는 뜻입니다.

최적화 관점:

이 비율을 100에 가깝게 유지하거나, rows 자체를 줄이는 방향으로 인덱스를 재설계해야 합니다.

5. key_len: 인덱스 사용 효율

복합 인덱스(Composite Index)를 사용할 때, 인덱스의 몇 바이트까지 사용했는지 보여줍니다.

판단 기준:

복합 인덱스 (A, B, C)가 있을 때, 쿼리가 A 조건만 사용했다면 key_len은 A의 길이만큼만 나옵니다.
의도한 대로 복합 인덱스의 모든 컬럼을 활용하고 있는지 체크할 때 유용합니다.

성능 최적화 예시 (Code Example)

아래는 Using filesort와 ALL이 발생하는 비효율적인 쿼리를 인덱스를 통해 최적화하는 과정입니다.

1. 튜닝 전 (Before)

특정 부서(dept_no)의 사원들을 입사일(hire_date) 순으로 정렬하여 조회하는 경우입니다.


- 적절한 인덱스가 없는 상태
EXPLAIN
SELECT * FROM employees
WHERE dept_no = 'd005'
ORDER BY hire_date;

실행 계획 결과 (가정):

type: ALL (테이블 전체 스캔 발생)

key: NULL (인덱스 미사용)

Extra: Using where; Using filesort (메모리 정렬 발생)

성능 관점의 문제점 > 디스크 I/O가 전체 테이블 크기만큼 발생하며(O(N)), 정렬을 위해 CPU와 메모리 자원을 추가로 소모합니다. 데이터가 많아질수록 응답 속도는 급격히 느려집니다.

2. 튜닝 후 (After)

WHERE 조건과 ORDER BY 조건을 모두 만족하는 복합 인덱스를 생성합니다.


-- 복합 인덱스 생성
CREATE INDEX idx_dept_hire ON employees (dept_no, hire_date);
-- 다시 실행 계획 확인
EXPLAIN
SELECT * FROM employees
WHERE dept_no = 'd005'ORDER BY hire_date;

실행 계획 결과 (개선됨):

type: ref (인덱스를 통한 동등 검색)

key: idx_dept_hire

Extra: Using index condition (또는 None)

성능 최적화 효과:
Random I/O 감소: dept_no가 모여있는 인덱스 리프 노드만 접근하므로 I/O가 획기적으로 줍니다.
CPU/메모리 절약: 인덱스는 이미 hire_date 순으로 정렬되어 저장되어 있으므로, 별도의 Sort 과정(Using filesort)이 생략됩니다.
시간 복잡도 개선: 탐색 비용이 O(N)에서 O(log N)수준으로 개선됩니다.