MYSQL

SQL 성능 최적화: 바로 써먹어 보기

2025년 09월 18일

sql performance optimization database tuning

SQL 성능 최적화가 왜 중요한가?

데이터베이스 성능은 애플리케이션 전체의 성능을 좌우한다. 잘못 작성된 쿼리 하나가 전체 시스템을 마비시킬 수 있고, 반대로 최적화된 쿼리는 수십 배의 성능 향상을 가져다준다.

실무에서 가장 많이 마주치는 문제는 느린 쿼리다. 사용자가 3초 이상 기다리면 이탈률이 급격히 증가하고, 비즈니스에 직접적인 손실을 가져온다. 오늘은 실무에서 바로 적용할 수 있는 SQL 성능 최적화 기법들을 알아보자.

1. 인덱스 전략

인덱스의 기본 원리

인덱스는 데이터베이스의 책 목차와 같다. 전체 책을 읽지 않고도 원하는 내용을 빠르게 찾을 수 있게 해준다.

-- 인덱스 생성 전후 성능 비교
-- 인덱스 없이 100만 건 조회
SELECT * FROM users WHERE email = 'user@example.com';
-- 실행 시간: 2.3초

-- 인덱스 생성
CREATE INDEX idx_users_email ON users(email);

-- 인덱스 생성 후 동일 쿼리
SELECT * FROM users WHERE email = 'user@example.com';
-- 실행 시간: 0.01초 (230배 향상)

복합 인덱스 설계

복합 인덱스는 여러 컬럼을 조합해서 만드는 인덱스다. 컬럼 순서가 성능에 큰 영향을 미친다.

-- 잘못된 복합 인덱스 설계
CREATE INDEX idx_bad ON orders(customer_id, status, created_at);

-- 이 쿼리는 인덱스를 제대로 활용하지 못함
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01';

-- 올바른 복합 인덱스 설계
CREATE INDEX idx_good ON orders(status, created_at, customer_id);

-- 이제 모든 쿼리가 인덱스를 활용할 수 있음
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01';
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2025-01-01' AND customer_id = 123;

복합 인덱스 설계 원칙:

선택도가 높은 컬럼을 앞에 배치
자주 함께 사용되는 컬럼들을 그룹화
범위 조건은 뒤에 배치

2. 쿼리 최적화 기법

EXISTS vs IN vs JOIN

같은 결과를 얻는 여러 방법이 있지만, 성능은 천차만별이다.

-- EXISTS 사용 (가장 효율적)
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- IN 사용 (중간 효율)
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE status = 'completed'
);

-- JOIN 사용 (가장 비효율적 - 중복 발생)
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

서브쿼리 최적화

서브쿼리는 종종 JOIN으로 변환하면 성능이 크게 향상된다.

-- 비효율적인 서브쿼리
SELECT * FROM products p
WHERE p.category_id IN (
    SELECT id FROM categories 
    WHERE name LIKE '%electronics%'
);

-- 최적화된 JOIN 쿼리
SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name LIKE '%electronics%';

LIMIT과 OFFSET 최적화

대용량 데이터에서 페이지네이션을 구현할 때 주의해야 한다.

-- 비효율적인 OFFSET 사용
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 10000;
-- 10,000개 행을 건너뛰어야 하므로 매우 느림

-- 커서 기반 페이지네이션 (권장)
SELECT * FROM orders 
WHERE created_at < '2025-09-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 10;

3. 실행 계획 분석

EXPLAIN 활용

MySQL의 실행 계획을 분석하면 쿼리 성능을 정확히 파악할 수 있다.

EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
HAVING order_count > 5;

중요한 지표들:

type: ALL(전체 테이블 스캔) → index → range → ref → const 순으로 좋음
rows: 검사해야 할 행 수 (적을수록 좋음)
Extra: Using filesort, Using temporary 등 주의해야 할 사항

인덱스 힌트 사용

MySQL이 잘못된 실행 계획을 선택할 때 힌트를 사용한다.

-- 특정 인덱스 강제 사용
SELECT * FROM orders USE INDEX(idx_created_at) 
WHERE created_at > '2025-01-01';

-- 인덱스 사용 금지
SELECT * FROM orders IGNORE INDEX(idx_created_at) 
WHERE created_at > '2025-01-01';

-- 인덱스 강제 사용 (다른 인덱스 무시)
SELECT * FROM orders FORCE INDEX(idx_created_at) 
WHERE created_at > '2025-01-01';

4. 데이터 타입 최적화

적절한 데이터 타입 선택

데이터 타입은 저장 공간과 성능에 직접적인 영향을 미친다.

-- 비효율적인 데이터 타입
CREATE TABLE bad_design (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(255),  -- ENUM이 더 효율적
    created_at DATETIME,  -- TIMESTAMP가 더 효율적
    amount DECIMAL(10,2), -- FLOAT이 더 효율적일 수 있음
    description TEXT      -- VARCHAR가 더 효율적일 수 있음
);

-- 최적화된 데이터 타입
CREATE TABLE good_design (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('pending', 'completed', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount FLOAT,
    description VARCHAR(500)
);

NULL 처리 최적화

NULL은 인덱스 효율성을 떨어뜨린다. 가능하면 NOT NULL을 사용하자.

-- NULL 허용 (비효율적)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) NULL  -- NULL 허용
);

-- NOT NULL 사용 (효율적)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

5. 대용량 데이터 처리

배치 처리

대용량 데이터를 한 번에 처리하면 메모리 부족이나 락 문제가 발생할 수 있다.

-- 비효율적인 대용량 업데이트
UPDATE orders SET status = 'processed' WHERE created_at < '2025-01-01';
-- 100만 건을 한 번에 업데이트하면 락이 오래 걸림

-- 배치 처리 (권장)
DELIMITER $$
CREATE PROCEDURE BatchUpdateOrders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE affected_rows INT;
    
    REPEAT
        UPDATE orders 
        SET status = 'processed' 
        WHERE created_at < '2025-01-01' 
        AND status != 'processed'
        LIMIT batch_size;
        
        SET affected_rows = ROW_COUNT();
        
        -- 배치 간 잠시 대기 (시스템 부하 방지)
        SELECT SLEEP(0.1);
        
    UNTIL affected_rows = 0 END REPEAT;
END$$
DELIMITER ;

CALL BatchUpdateOrders();

파티셔닝

대용량 테이블을 논리적으로 분할하면 성능이 크게 향상된다.

-- 날짜별 파티셔닝
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 파티션 프루닝으로 성능 향상
SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- 2025년 파티션만 검색하므로 매우 빠름

6. 캐싱 전략

쿼리 결과 캐싱

자주 사용되는 쿼리 결과를 캐싱하면 성능을 크게 향상시킬 수 있다.

-- 쿼리 캐시 활성화 (MySQL 8.0 이전)
SET GLOBAL query_cache_size = 268435456;  -- 256MB
SET GLOBAL query_cache_type = ON;

-- 자주 사용되는 통계 쿼리
SELECT 
    DATE(created_at) as date,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(created_at);
-- 이 쿼리는 캐시되어 반복 실행 시 매우 빠름

애플리케이션 레벨 캐싱

데이터베이스 외부에서 캐싱하는 방법들이다.

# Redis를 이용한 쿼리 결과 캐싱
import redis
import json
import hashlib

redis_client = redis.Redis(host='localhost', port=6379, db=0)

def get_cached_query_result(query, params):
    # 쿼리와 파라미터를 해시화해서 캐시 키 생성
    cache_key = f"query:{hashlib.md5(f'{query}{params}'.encode()).hexdigest()}"
    
    # 캐시에서 결과 조회
    cached_result = redis_client.get(cache_key)
    if cached_result:
        return json.loads(cached_result)
    
    # 캐시에 없으면 DB에서 조회
    result = execute_query(query, params)
    
    # 결과를 캐시에 저장 (5분간 유지)
    redis_client.setex(cache_key, 300, json.dumps(result))
    
    return result

7. 모니터링과 프로파일링

느린 쿼리 로그

MySQL의 느린 쿼리 로그를 활성화해서 성능 문제를 찾자.

-- 느린 쿼리 로그 설정
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 걸리는 쿼리 로깅
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 현재 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

성능 스키마 활용

MySQL 5.7+에서 제공하는 성능 스키마로 상세한 성능 정보를 얻을 수 있다.

-- 가장 많이 실행되는 쿼리 TOP 10
SELECT 
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec,
    SUM_ROWS_EXAMINED as total_rows_examined
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY COUNT_STAR DESC 
LIMIT 10;

-- 가장 느린 쿼리 TOP 10
SELECT 
    DIGEST_TEXT as query,
    AVG_TIMER_WAIT/1000000000000 as avg_time_sec,
    MAX_TIMER_WAIT/1000000000000 as max_time_sec
FROM performance_schema.events_statements_summary_by_digest 
WHERE AVG_TIMER_WAIT > 1000000000  -- 1초 이상
ORDER BY AVG_TIMER_WAIT DESC 
LIMIT 10;

8. 실무 체크리스트

성능 최적화 체크리스트

인덱스 최적화:

자주 사용되는 WHERE 조건에 인덱스가 있는가?
복합 인덱스의 컬럼 순서가 적절한가?
사용하지 않는 인덱스는 제거했는가?

쿼리 최적화:

서브쿼리를 JOIN으로 변환할 수 있는가?
EXISTS를 적절히 사용하고 있는가?
LIMIT과 OFFSET을 효율적으로 사용하고 있는가?

데이터 타입:

적절한 데이터 타입을 사용하고 있는가?
NULL을 최소화했는가?
불필요하게 큰 데이터 타입은 없는가?

모니터링:

느린 쿼리 로그를 활성화했는가?
성능 스키마를 활용하고 있는가?
정기적으로 쿼리 성능을 점검하고 있는가?

마무리

SQL 성능 최적화는 한 번에 모든 걸 적용하려고 하지 말고, 점진적으로 개선해나가는 게 중요하다.

가장 먼저 해야 할 일은:

성능 측정 - 현재 상태를 파악한다
병목 지점 찾기 - 가장 느린 쿼리부터 개선한다
인덱스 최적화 - 가장 효과적인 방법이다
지속적 모니터링 - 개선 효과를 확인한다

성능 최적화는 개발자의 핵심 역량이다. 꾸준히 공부하고 실무에 적용해보자.