SQL 쿼리 최적화 기법

#01. SQL 최적화 개요

1. SQL 최적화란?

SQL 최적화는 데이터베이스 쿼리의 실행 시간을 단축하고 시스템 리소스 사용량을 최소화하여 전반적인 성능을 향상시키는 과정입니다.

2. 최적화가 필요한 이유

• 응답 시간 개선: 사용자 경험 향상
• 리소스 절약: CPU, 메모리, I/O 사용량 감소
• 동시 처리 능력 향상: 더 많은 사용자 지원
• 비용 절감: 하드웨어 및 운영 비용 절약

3. 최적화 접근 방법

1. 쿼리 레벨 최적화: SQL 문장 개선
2. 인덱스 최적화: 적절한 인덱스 설계
3. 스키마 최적화: 테이블 구조 개선
4. 시스템 레벨 최적화: 서버 설정 튜닝

#02. 실행 계획 분석

1. MySQL EXPLAIN

-- 기본 실행 계획
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

-- 상세 실행 계획
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 10;

-- 실제 실행 통계 (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

2. PostgreSQL EXPLAIN

-- 기본 실행 계획
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

-- 상세 실행 계획
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM employees WHERE department_id = 10;

-- JSON 형태로 출력
EXPLAIN (FORMAT JSON, ANALYZE)
SELECT * FROM employees WHERE department_id = 10;

3. 실행 계획 읽는 방법

MySQL 실행 계획 주요 컬럼

컬럼	설명
select_type	SELECT 문의 유형
table	테이블 이름
type	조인 타입 (성능 순: const > eq_ref > ref > range > index > ALL)
possible_keys	사용 가능한 인덱스
key	실제 사용된 인덱스
rows	검사할 예상 행 수
Extra	추가 정보

-- 좋은 실행 계획 예시
EXPLAIN SELECT * FROM employees WHERE employee_id = 100;
-- type: const, rows: 1

-- 나쁜 실행 계획 예시
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- type: ALL, rows: 전체 행 수

#03. 인덱스 최적화

1. 인덱스 기본 원리

-- 단일 컬럼 인덱스
CREATE INDEX idx_employee_department ON employees(department_id);

-- 복합 인덱스
CREATE INDEX idx_employee_dept_salary ON employees(department_id, salary);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_employees ON employees(department_id)
WHERE status = 'ACTIVE';

2. 인덱스 설계 원칙

복합 인덱스 컬럼 순서

-- 잘못된 인덱스 설계
CREATE INDEX idx_bad ON employees(salary, department_id);

-- 올바른 인덱스 설계 (선택도가 높은 컬럼 우선)
CREATE INDEX idx_good ON employees(department_id, salary);

-- 쿼리 예시
SELECT * FROM employees
WHERE department_id = 10 AND salary > 50000;

커버링 인덱스

-- 커버링 인덱스 생성
CREATE INDEX idx_covering ON employees(department_id, salary, first_name, last_name);

-- 이 쿼리는 테이블에 접근하지 않고 인덱스만으로 처리 가능
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 10;

3. 인덱스 사용을 방해하는 경우

-- 함수 사용으로 인한 인덱스 미사용
-- 나쁜 예
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 좋은 예
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

-- 데이터 타입 불일치
-- 나쁜 예 (employee_id가 INT인 경우)
SELECT * FROM employees WHERE employee_id = '100';

-- 좋은 예
SELECT * FROM employees WHERE employee_id = 100;

-- LIKE 패턴의 앞에 와일드카드
-- 나쁜 예
SELECT * FROM employees WHERE first_name LIKE '%John';

-- 좋은 예
SELECT * FROM employees WHERE first_name LIKE 'John%';

#04. JOIN 최적화

1. JOIN 순서 최적화

-- 작은 테이블을 먼저 처리
SELECT e.first_name, d.department_name
FROM departments d  -- 작은 테이블
JOIN employees e ON d.department_id = e.department_id  -- 큰 테이블
WHERE d.location_id = 1700;

2. 적절한 JOIN 타입 선택

-- INNER JOIN (필요한 데이터만)
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- EXISTS 사용 (존재 여부만 확인)
SELECT e.first_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

-- IN vs EXISTS 성능 비교
-- IN: 서브쿼리 결과가 작을 때 유리
SELECT * FROM employees
WHERE department_id IN (10, 20, 30);

-- EXISTS: 메인 쿼리 결과가 작을 때 유리
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
    AND d.budget > 1000000
);

3. 서브쿼리 최적화

-- 상관 서브쿼리를 JOIN으로 변환
-- 비효율적인 상관 서브쿼리
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

-- JOIN으로 최적화
SELECT e.first_name, e.salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) avg_sal ON e.department_id = avg_sal.department_id
WHERE e.salary > avg_sal.avg_salary;

#05. WHERE 절 최적화

1. 조건절 순서

-- 선택도가 높은 조건을 앞에 배치
SELECT * FROM employees
WHERE department_id = 10        -- 선택도 높음 (소수의 행)
  AND status = 'ACTIVE'         -- 선택도 중간
  AND salary > 30000;           -- 선택도 낮음 (많은 행)

2. OR 조건 최적화

-- OR 조건을 UNION으로 변환
-- 비효율적인 OR
SELECT * FROM employees
WHERE department_id = 10 OR department_id = 20;

-- UNION으로 최적화 (각각 인덱스 사용 가능)
SELECT * FROM employees WHERE department_id = 10
UNION
SELECT * FROM employees WHERE department_id = 20;

-- IN 사용 (더 간단한 경우)
SELECT * FROM employees
WHERE department_id IN (10, 20);

3. LIKE 조건 최적화

-- 전체 텍스트 검색이 필요한 경우 전용 엔진 사용
-- MySQL FULLTEXT 인덱스
CREATE FULLTEXT INDEX idx_fulltext ON products(product_name, description);

SELECT * FROM products
WHERE MATCH(product_name, description) AGAINST('smartphone' IN BOOLEAN MODE);

-- PostgreSQL 전문 검색
CREATE INDEX idx_fulltext ON products
USING GIN(to_tsvector('english', product_name || ' ' || description));

SELECT * FROM products
WHERE to_tsvector('english', product_name || ' ' || description)
@@ to_tsquery('english', 'smartphone');

#06. 집계 쿼리 최적화

1. GROUP BY 최적화

-- 인덱스를 활용한 GROUP BY
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- 효율적인 집계
SELECT department_id, AVG(salary), COUNT(*)
FROM employees
GROUP BY department_id;

-- HAVING 대신 WHERE 사용 (가능한 경우)
-- 비효율적
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20);

-- 효율적
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (10, 20)
GROUP BY department_id;

2. 윈도우 함수 최적화

-- 효율적인 윈도우 함수 사용
SELECT
    employee_id,
    first_name,
    salary,
    -- 같은 PARTITION BY를 여러 윈도우 함수에서 재사용
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
    MAX(salary) OVER (PARTITION BY department_id) as dept_max,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_in_dept
FROM employees;

#07. 대용량 데이터 처리 최적화

1. 페이징 최적화

-- 비효율적인 OFFSET 사용
SELECT * FROM employees
ORDER BY employee_id
LIMIT 20 OFFSET 10000;  -- 10000개 행을 건너뛰므로 비효율적

-- 커서 기반 페이징
SELECT * FROM employees
WHERE employee_id > 10000
ORDER BY employee_id
LIMIT 20;

-- 복합 정렬 기준의 커서 페이징
SELECT * FROM employees
WHERE (department_id, employee_id) > (10, 1000)
ORDER BY department_id, employee_id
LIMIT 20;

2. 배치 처리

-- 대량 INSERT 최적화
-- 단건 INSERT (비효율적)
INSERT INTO temp_table VALUES (1, 'data1');
INSERT INTO temp_table VALUES (2, 'data2');

-- 배치 INSERT (효율적)
INSERT INTO temp_table VALUES
(1, 'data1'),
(2, 'data2'),
(3, 'data3');

-- 대량 UPDATE 최적화
-- 배치 사이즈로 나누어 처리
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10
  AND employee_id BETWEEN 1000 AND 1999;

3. 파티셔닝 활용

-- 범위 파티셔닝 (MySQL)
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 파티션 프루닝을 활용한 쿼리
SELECT * FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

#08. 통계 정보 관리

1. MySQL 통계 정보

-- 테이블 통계 정보 업데이트
ANALYZE TABLE employees;

-- 인덱스 통계 확인
SHOW INDEX FROM employees;

-- 쿼리 캐시 상태 확인
SHOW STATUS LIKE 'Qcache%';

2. PostgreSQL 통계 정보

-- 통계 정보 수집
ANALYZE employees;

-- 자동 통계 수집 설정 확인
SHOW auto_explain.log_min_duration;

-- 테이블 통계 조회
SELECT
    schemaname,
    tablename,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_analyze
FROM pg_stat_user_tables;

#09. 성능 모니터링

1. 느린 쿼리 로그

-- MySQL 느린 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 느린 쿼리 분석
-- mysqldumpslow /var/log/mysql/slow-query.log

2. 성능 스키마 활용

-- MySQL Performance Schema
-- 실행 시간이 긴 쿼리 조회
SELECT
    sql_text,
    exec_count,
    avg_timer_wait/1000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

-- PostgreSQL 통계 뷰
-- 가장 느린 쿼리 조회
SELECT
    query,
    calls,
    total_time,
    mean_time,
    stddev_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

#10. 실제 최적화 사례

1. 사례 1: N+1 문제 해결

-- 문제: N+1 쿼리
-- 각 부서별로 별도 쿼리 실행 (N+1개 쿼리)
SELECT * FROM departments;  -- 1개 쿼리
-- 각 부서마다: SELECT * FROM employees WHERE department_id = ?;  -- N개 쿼리

-- 해결: JOIN 사용
SELECT
    d.department_name,
    e.first_name,
    e.last_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

2. 사례 2: 대용량 집계 최적화

-- 문제: 매번 전체 테이블 스캔
SELECT
    DATE(created_at) as date,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY DATE(created_at);

-- 해결 1: 인덱스 추가
CREATE INDEX idx_orders_created_date ON orders(DATE(created_at));

-- 해결 2: 집계 테이블 사용
CREATE TABLE daily_order_summary (
    summary_date DATE PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(15,2),
    last_updated TIMESTAMP
);

-- 배치로 집계 테이블 업데이트
INSERT INTO daily_order_summary
SELECT
    DATE(created_at),
    COUNT(*),
    SUM(amount),
    NOW()
FROM orders
WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    last_updated = VALUES(last_updated);

3. 사례 3: 검색 성능 개선

-- 문제: LIKE를 사용한 전체 텍스트 검색
SELECT * FROM products
WHERE product_name LIKE '%smartphone%'
   OR description LIKE '%smartphone%';

-- 해결: 전문 검색 인덱스 사용
-- MySQL
CREATE FULLTEXT INDEX idx_product_search ON products(product_name, description);

SELECT * FROM products
WHERE MATCH(product_name, description) AGAINST('+smartphone' IN BOOLEAN MODE);

-- PostgreSQL
CREATE INDEX idx_product_search ON products
USING GIN(to_tsvector('english', product_name || ' ' || description));

SELECT * FROM products
WHERE to_tsvector('english', product_name || ' ' || description)
@@ to_tsquery('english', 'smartphone');

#11. 최적화 체크리스트

1. 쿼리 작성 시 확인사항

• 필요한 컬럼만 SELECT 하는가?
• WHERE 조건이 인덱스를 활용하는가?
• 함수나 연산이 WHERE 절에 포함되어 있지 않은가?
• JOIN 조건이 적절한가?
• LIMIT를 사용하여 결과를 제한하는가?

2. 인덱스 설계 시 확인사항

• 자주 사용되는 WHERE 조건에 인덱스가 있는가?
• 복합 인덱스의 컬럼 순서가 적절한가?
• 사용되지 않는 인덱스는 제거했는가?
• 커버링 인덱스를 고려했는가?

3. 성능 모니터링 확인사항

• 느린 쿼리 로그를 정기적으로 분석하는가?
• 실행 계획을 주기적으로 확인하는가?
• 통계 정보를 최신으로 유지하는가?
• 시스템 리소스 사용량을 모니터링하는가?

#12. 마무리

SQL 쿼리 최적화는 애플리케이션 성능에 직접적인 영향을 미치는 중요한 작업입니다. 실행 계획 분석, 적절한 인덱스 설계, 쿼리 구조 개선 등을 통해 데이터베이스 성능을 크게 향상시킬 수 있습니다.

최적화는 일회성 작업이 아닌 지속적인 과정이므로, 정기적인 모니터링과 튜닝을 통해 최적의 성능을 유지하는 것이 중요합니다.