SQL 쿼리를 더 빠르게! Query Planner & Execution Plan 이해하기

데이터베이스 성능을 높이는 가장 중요한 방법은 무엇일까요?

바로 "쿼리 최적화(Query Optimization)"입니다.

SQL 쿼리의 실행 속도를 높이고, 데이터베이스 시스템의 부하를 줄이는 방법을 알고 싶다면 "Query Planner(쿼리 계획 수립)과 Execution Plan(실행 계획 분석)"을 이해하는 것이 필수입니다.

이 글에서는 쿼리 최적화의 핵심 개념, Query Planner와 Execution Plan의 동작 방식, 그리고 성능을 높이는 실전 최적화 방법까지 자세히 설명하겠습니다.

---

📌 1. 쿼리 최적화란?

쿼리 최적화(Query Optimization)란 데이터베이스가 SQL 쿼리를 실행하는 방식을 최적화하여 성능을 향상시키는 과정을 의미합니다.

쿼리 최적화의 목적:

• ✔ 빠른 데이터 조회 → 실행 속도를 높여 사용자 경험 향상
• ✔ 시스템 자원 절약 → CPU, 메모리, 디스크 I/O 부담 감소
• ✔ 대규모 데이터 처리 최적화 → 빅데이터 환경에서 성능 개선

DBMS(Database Management System)는 "Query Planner(쿼리 플래너)"를 이용해 쿼리 실행 계획을 수립하고, "Execution Plan(실행 계획)"을 통해 최적화된 방법으로 쿼리를 실행합니다.

---

📌 2. Query Planner(쿼리 플래너)란?

✔ Query Planner의 역할

Query Planner는 SQL 문을 최적화된 실행 계획으로 변환하는 역할을 수행합니다.

즉, 어떤 인덱스를 사용할지, 어떤 조인 방식을 적용할지 결정하는 과정입니다.

✔ Query Planner의 주요 동작 과정

1️⃣ SQL 파싱 (Parsing)

• SQL 문법 분석 및 트랜잭션 검토
• 예시: SELECT * FROM users WHERE age > 30; → SQL 구문 분석

2️⃣ 쿼리 변환 (Query Rewriting)

• 불필요한 연산 제거 (예: WHERE age > 30 AND age > 20 → WHERE age > 30)
• 서브쿼리를 JOIN으로 변환하여 성능 개선

3️⃣ 최적 실행 계획 탐색 (Execution Plan Generation)

• 사용 가능한 인덱스 확인
• 테이블 스캔 방식 선택 (Index Scan vs. Full Table Scan)
• 조인 방식 결정 (Nested Loop Join, Hash Join, Merge Join)

4️⃣ 최적 실행 계획 선택 (Cost-Based Optimization)

• "쿼리 비용(Cost)"을 평가하여 가장 효율적인 실행 계획 선택
• CPU, 메모리, 디스크 I/O 사용량을 예측

---

📌 3. Execution Plan(실행 계획) 분석

✔ Execution Plan이란?

Execution Plan(실행 계획)은 DBMS가 쿼리를 실행하는 방식을 보여주는 문서입니다.

SQL 실행 전에 실행 계획을 분석하면 쿼리 성능 저하 원인을 파악하고 최적화할 수 있습니다.

✔ Execution Plan 확인 방법 (MySQL, PostgreSQL, Oracle)

1️⃣ MySQL

EXPLAIN SELECT * FROM users WHERE age > 30;

• EXPLAIN 명령어를 사용하면 실행 계획을 확인할 수 있음
• rows, filtered, key, type 등의 정보를 분석

2️⃣ PostgreSQL

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

• EXPLAIN ANALYZE 명령어는 실행 계획과 실제 실행 통계를 함께 제공
• 실행 시간, 비용, 테이블 스캔 방식 등을 확인 가능

3️⃣ Oracle

EXPLAIN PLAN FOR SELECT * FROM users WHERE age > 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

• DBMS_XPLAN.DISPLAY를 사용하여 실행 계획 출력

실제 SQL 예제

1️⃣ 실행 계획 분석 (EXPLAIN 사용법)

문제: 테이블 전체 스캔으로 성능 저하

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

🔎 실행 계획 분석 결과

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

🚨 문제점:

ALL(Full Table Scan) 발생 → email 컬럼에 인덱스 없음

해결: 적절한 인덱스 추가

CREATE INDEX idx_users_email ON users(email);

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

✅ 개선 결과

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | idx_users_email | idx_users_email | 767     | const |    1 | Using index |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

🔥 최적화 효과:

테이블 전체 스캔(ALL) → 인덱스 검색(REF)으로 변경 → 속도 향상 🚀

---

📌 4. 실행 계획 분석 및 최적화 방법

✔ 1️⃣ 인덱스(Index) 최적화

🔹 문제:

테이블 풀스캔(Full Table Scan) 발생 시 성능 저하

🔹 해결 방법:

적절한 인덱스 적용

-- 기존 쿼리 (인덱스 없음)
SELECT * FROM users WHERE email = 'user@example.com';

-- 인덱스 추가 후 성능 개선
CREATE INDEX idx_users_email ON users(email);

🔥 효과:

email 컬럼 검색 속도 향상 🚀

---

✔ 2️⃣ 조인(Join) 최적화

🔹 문제:

조인 방식(Nested Loop Join, Hash Join, Merge Join) 선택이 비효율적일 경우 속도 저하

🔹 해결 방법:

적절한 조인 방식 선택 & 인덱스 활용

-- 기존 쿼리 (비효율적인 조인)
SELECT orders.*, customers.name 
FROM orders 
JOIN customers ON orders.customer_id = customers.id;

-- 고객 ID에 인덱스 추가하여 성능 개선
CREATE INDEX idx_customers_id ON customers(id);

🔥 효과:

조인 시 Index Scan 사용 가능, Full Table Scan 방지 🚀

문제: 두 테이블 조인 시 인덱스 미사용

SELECT orders.*, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.name = 'Alice';

🚨 문제점:

customers.name에 인덱스가 없어 Full Table Scan 발생

해결: 인덱스 추가하여 조인 속도 개선

CREATE INDEX idx_customers_name ON customers(name);

EXPLAIN SELECT orders.*, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.name = 'Alice';

✅ 개선 결과

• customers.name 검색 시 인덱스 사용
• customers.id와 orders.customer_id에 적절한 인덱스가 있다면 Nested Loop Join 수행
• 실행 속도 향상 🚀

---

📌 5. 실행 계획 분석 및 최적화 방법

✔ 1️⃣ WHERE 조건 최적화

🔹 문제:

WHERE 절에 인덱스를 활용하지 못하면 성능 저하 발생

🔹 해결 방법:

WHERE 조건을 인덱스가 활용할 수 있도록 수정

-- 비효율적인 WHERE 절
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 개선된 WHERE 절 (인덱스 활용 가능)
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

🔥 효과:

• created_at 인덱스 활용 가능하여 검색 속도 향상

문제: 함수 사용으로 인덱스 무효화

SELECT * FROM users WHERE YEAR(created_at) = 2023;

🚨 문제점:

• YEAR(created_at) 함수 사용으로 인덱스가 비활성화됨 → 전체 테이블 스캔 발생

해결: 범위 조건으로 변경

SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

🔥 최적화 효과:

• created_at 컬럼의 인덱스 활용 가능
• 실행 속도 향상 🚀

---

📌 6. 고급 최적화 기법 (Advanced Optimization Techniques)

✔ 1️⃣ 서브쿼리(Subquery) vs. 조인(Join)

🔹 문제:

서브쿼리 사용 시 불필요한 연산 발생

🔹 해결 방법:

조인으로 변환하여 최적화

-- 비효율적인 서브쿼리
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- JOIN을 사용한 최적화
SELECT users.* FROM users JOIN orders ON users.id = orders.user_id;

🔥 효과:

• 서브쿼리 대신 조인을 사용하여 성능 개선

서브쿼리 최적화 (서브쿼리 → JOIN 변경)

문제: 서브쿼리 사용으로 불필요한 실행

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

🚨 문제점:

• 서브쿼리가 매번 실행됨 → 비효율적

해결: 서브쿼리를 JOIN으로 변경

SELECT users.* 
FROM users 
JOIN orders ON users.id = orders.user_id;

🔥 최적화 효과:

• 서브쿼리 제거 → 조인으로 실행 횟수 줄임
• users.id, orders.user_id에 인덱스 적용 가능
• 실행 속도 향상 🚀

---

✔ 2️⃣ 데이터 파티셔닝(Partitioning)

🔹 문제: 대량 데이터 테이블 검색 속도 저하

🔹 해결 방법: 테이블 파티셔닝 적용

-- 범위 파티셔닝 예제 (MySQL)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE NOT NULL
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

🔥 효과:

• 특정 연도 데이터만 검색하여 속도 향상 🚀

---

✔ 3️⃣ 페이징 최적화 SQL

LIMIT + OFFSET 성능 개선

문제: 큰 OFFSET 값으로 인해 속도 저하

SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;

🚨 문제점:

• OFFSET이 클 경우 데이터베이스가 불필요한 100,000개의 행을 읽음

해결: WHERE 조건으로 페이징 최적화

SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 1) 
ORDER BY created_at DESC LIMIT 10;

🔥 최적화 효과:

• 불필요한 데이터 스캔을 줄여 페이징 속도 향상 🚀

---

✔ 4️⃣ GROUP BY 최적화 (임시 테이블 사용 방지)

문제: GROUP BY 시 임시 테이블(Temporary Table) 생성

SELECT department, COUNT(*) FROM employees GROUP BY department;

🚨 문제점:

• GROUP BY 수행 시 임시 테이블 사용으로 속도 저하

해결: 적절한 인덱스 추가

CREATE INDEX idx_department ON employees(department);

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department
ORDER BY department;

🔥 최적화 효과:

• 인덱스를 활용하여 GROUP BY 연산 최적화
• 정렬 속도 향상 🚀

---

✔ 5️⃣ 대용량 데이터 처리 – 테이블 파티셔닝

문제: 10억 개 이상의 데이터가 저장된 테이블 검색 속도 저하

SELECT * FROM orders WHERE order_date >= '2023-01-01';

🚨 문제점:

• 대량 데이터를 검색할 때 모든 파티션을 탐색

해결: 범위 파티셔닝 적용

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_date DATE NOT NULL
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

🔥 효과:

• 특정 연도 데이터만 검색하여 속도 향상 🚀

---

✔ 6️⃣ 캐싱 활용 (MySQL Query Cache, Redis)

문제: 동일한 쿼리가 반복 실행됨

SELECT COUNT(*) FROM users WHERE status = 'active';

🚨 문제점:

• 매번 쿼리를 실행하여 불필요한 데이터베이스 부하 발생

해결: Redis 캐싱 활용

-- Redis에 데이터 저장
SET user_active_count 50000;

-- 필요할 때 Redis에서 조회
GET user_active_count;

🔥 최적화 효과:

• 동일한 요청 시 데이터베이스가 아닌 캐시에서 바로 응답
• 처리 속도 향상 🚀

---

🔥 결론: SQL 성능 최적화를 위한 핵심 전략

• ✔ 인덱스를 적절하게 활용하라
• ✔ 불필요한 서브쿼리는 조인으로 변환하라
• ✔ WHERE 조건에서 함수 사용을 지양하라
• ✔ 대량 데이터는 파티셔닝을 고려하라
• ✔ 자주 실행되는 쿼리는 캐싱을 활용하라

SQL 성능 최적화는 Query Planner와 Execution Plan을 이해하는 것에서 시작됩니다.

지속적인 실행 계획 분석과 최적화를 통해 데이터베이스 성능을 극대화하세요! 🚀

이제 SQL 성능 최적화를 위한 실전 기술을 적용하여 더 빠르고 효율적인 데이터베이스를 운영해보세요!

 

✅ 도움이 되셨다면 공유 & 댓글 부탁드립니다! 😊