04_데이터베이스_성능_최적화

데이터베이스 성능 최적화

---

데이터베이스 성능 최적화란?

데이터베이스 성능 최적화는 쿼리 실행 시간을 단축하고, 시스템 리소스 사용을 효율화하며, 동시 사용자 처리 능력을 향상시키는 과정입니다. 올바른 최적화는 사용자 경험과 시스템 비용에 직접적인 영향을 미칩니다.

---

성능 최적화 접근법

1. 측정 우선 (Measure First)

• 현재 성능 측정
• 병목 지점 식별
• 성능 기준선 설정

2. 최적화 대상 선정

• 가장 느린 쿼리
• 가장 자주 실행되는 쿼리
• 가장 많은 리소스를 사용하는 쿼리

3. 최적화 실행

• 인덱스 추가/수정
• 쿼리 재작성
• 스키마 조정

4. 검증

• 성능 개선 확인
• 부작용 확인
• 모니터링 지속

---

쿼리 최적화

SELECT 최적화

불필요한 컬럼 제거

-- 나쁜 예
SELECT * FROM users;
 
-- 좋은 예
SELECT id, username, email FROM users;

WHERE 절 최적화

-- 인덱스 활용
SELECT * FROM users WHERE email = 'test@example.com';
 
-- 함수 사용 지양
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- 나쁜 예
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';  -- 좋은 예

LIMIT 사용

-- 필요한 만큼만 조회
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

JOIN 최적화

인덱스 활용

-- JOIN 컬럼에 인덱스 생성
CREATE INDEX idx_user_id ON orders(user_id);
 
SELECT u.username, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id;

JOIN 순서

-- 작은 테이블을 먼저 조인
SELECT *
FROM small_table s
JOIN large_table l ON s.id = l.small_id;

불필요한 JOIN 제거

-- 필요한 테이블만 조인
-- 가능하면 서브쿼리나 EXISTS 사용 고려

서브쿼리 최적화

EXISTS vs IN

-- EXISTS가 더 효율적일 수 있음
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
 
-- IN 사용
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

서브쿼리를 JOIN으로 변환

-- 서브쿼리
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
 
-- JOIN으로 변환
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

---

인덱스 최적화

인덱스 생성 전략

자주 조회되는 컬럼

CREATE INDEX idx_email ON users(email);

WHERE 절에 사용되는 컬럼

CREATE INDEX idx_status ON orders(status);

JOIN에 사용되는 컬럼

CREATE INDEX idx_user_id ON orders(user_id);

ORDER BY에 사용되는 컬럼

CREATE INDEX idx_created_at ON users(created_at);

복합 인덱스

컬럼 순서 중요

-- WHERE와 ORDER BY 모두 활용
CREATE INDEX idx_status_created ON orders(status, created_at);
 
-- 쿼리
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

카디널리티 고려

-- 높은 카디널리티를 먼저
CREATE INDEX idx_user_date ON orders(user_id, order_date);

인덱스 모니터링

사용되지 않는 인덱스 확인

-- MySQL
SELECT * FROM sys.schema_unused_indexes;
 
-- PostgreSQL
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;

인덱스 크기 확인

-- MySQL
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size';

---

실행 계획 분석

EXPLAIN 사용

MySQL

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- 상세 정보
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

PostgreSQL

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- 실제 실행 시간 포함
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

실행 계획 해석

접근 방법

• INDEX: 인덱스 스캔
• FULL TABLE SCAN: 전체 테이블 스캔 (비효율적)
• RANGE SCAN: 범위 스캔

JOIN 타입

• eq_ref: 고유 인덱스 조인
• ref: 비고유 인덱스 조인
• ALL: 전체 테이블 스캔

---

스키마 최적화

데이터 타입 최적화

적절한 데이터 타입 선택

-- 나쁜 예
CREATE TABLE users (
    id VARCHAR(255),
    age VARCHAR(10)
);
 
-- 좋은 예
CREATE TABLE users (
    id INT,
    age TINYINT
);

NULL 처리

-- NULL이 필요 없는 컬럼은 NOT NULL
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

정규화 vs 반정규화

읽기 성능 향상을 위한 반정규화

-- 주문 테이블에 고객명 추가
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- 반정규화
    total DECIMAL(10,2)
);

---

캐싱 전략

애플리케이션 레벨 캐싱

자주 조회되는 데이터 캐싱

# Python 예시
import redis
 
r = redis.Redis()
 
def get_user(user_id):
    # 캐시에서 먼저 확인
    cached = r.get(f'user:{user_id}')
    if cached:
        return json.loads(cached)
    
    # DB에서 조회
    user = db.query(f'SELECT * FROM users WHERE id = {user_id}')
    
    # 캐시에 저장
    r.setex(f'user:{user_id}', 3600, json.dumps(user))
    
    return user

쿼리 캐싱

MySQL Query Cache

-- 쿼리 캐시 활성화
SET GLOBAL query_cache_size = 268435456;
SET GLOBAL query_cache_type = 1;

PostgreSQL에는 내장 쿼리 캐시 없음

• 애플리케이션 레벨 캐싱 필요

---

연결 관리

연결 풀링

적절한 연결 수 설정

# Python 예시
from sqlalchemy import create_engine
 
engine = create_engine(
    'mysql://user:pass@localhost/db',
    pool_size=10,           # 기본 연결 수
    max_overflow=20,        # 최대 추가 연결
    pool_recycle=3600      # 연결 재사용 시간
)

연결 모니터링

활성 연결 확인

-- MySQL
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';
 
-- PostgreSQL
SELECT * FROM pg_stat_activity;

---

파티셔닝

파티셔닝의 이점

• 쿼리 성능 향상
• 관리 용이성
• 데이터 삭제 효율성

파티셔닝 전략

날짜 기반 파티셔닝

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    total DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

---

하드웨어 최적화

메모리

• 버퍼 풀 크기 조정
• 쿼리 캐시 크기
• 연결 메모리

디스크

• SSD 사용
• RAID 구성
• I/O 최적화

CPU

• 쿼리 병렬 처리
• 인덱스 빌드 병렬화

---

모니터링

성능 메트릭

쿼리 성능

• 느린 쿼리 로그
• 쿼리 실행 시간
• 쿼리 빈도

리소스 사용

• CPU 사용률
• 메모리 사용률
• 디스크 I/O
• 네트워크 I/O

모니터링 도구

MySQL

• Performance Schema
• MySQL Enterprise Monitor
• Percona Monitoring

PostgreSQL

• pg_stat_statements
• pgAdmin
• pgBadger

일반 도구

• Prometheus + Grafana
• Datadog
• New Relic

---

성능 최적화 체크리스트

쿼리 최적화

• SELECT * 사용 지양
• 적절한 WHERE 조건 사용
• 인덱스 활용
• 불필요한 JOIN 제거
• 서브쿼리 최적화

인덱스 최적화

• 자주 조회되는 컬럼에 인덱스
• 복합 인덱스 컬럼 순서 최적화
• 사용되지 않는 인덱스 제거
• 인덱스 크기 모니터링

스키마 최적화

• 적절한 데이터 타입 선택
• NULL 처리 최적화
• 정규화/반정규화 균형

하드웨어 최적화

• 충분한 메모리
• SSD 사용
• 적절한 CPU

모니터링

• 성능 메트릭 수집
• 느린 쿼리 로그 분석
• 정기적인 성능 검토

---

참고

• 성능 최적화는 지속적인 과정입니다
• 측정 없이 최적화하지 마세요
• 작은 개선부터 시작하세요
• 트레이드오프를 이해하세요
• 정기적인 모니터링이 필수입니다