데이터베이스 성능 튜닝 완전 가이드: 쿼리 최적화, 인덱스 전략, 커넥션 풀 관리까지

요청 흐름에서의 병목:
Client -> CDN -> Load Balancer -> App Server -> [Database]
                                                  ↑
                                           대부분의 지연 발생

성능 개선 효과 피라미드:

        /\
       /  \        쿼리 최적화 (가장 큰 효과)
      /____\
     /      \      인덱스 전략
    /________\
   /          \    스키마 설계
  /____________\
 /              \  하드웨어/설정 튜닝
/________________\ 캐싱/읽기 복제본

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01'
  AND o.total > 100;

QUERY PLAN
---------------------------------------------------------------------------
Nested Loop  (cost=0.85..892.45 rows=23 width=48)
             (actual time=0.045..2.341 rows=19 loops=1)
  ->  Index Scan using idx_orders_created_at on orders o
        (cost=0.42..445.23 rows=23 width=20)
        (actual time=0.025..1.123 rows=19 loops=1)
        Filter: (total > 100)
        Rows Removed by Filter: 5
  ->  Index Scan using users_pkey on users u
        (cost=0.42..8.44 rows=1 width=36)
        (actual time=0.008..0.008 rows=1 loops=19)
Planning Time: 0.234 ms
Execution Time: 2.456 ms

-- 1. Seq Scan (순차 스캔) - 전체 테이블 읽기
-- 작은 테이블이거나 대부분의 행을 읽어야 할 때 적합
EXPLAIN ANALYZE SELECT * FROM small_table;

-- 2. Index Scan - 인덱스로 행 위치 찾은 후 테이블 접근
-- 선택도가 높은(적은 행 반환) 쿼리에 적합
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- 3. Index Only Scan - 인덱스만으로 결과 반환 (테이블 접근 불필요)
-- Covering Index 사용 시
EXPLAIN ANALYZE SELECT id, email FROM users WHERE email = 'user@example.com';

-- 4. Bitmap Index Scan - 인덱스로 비트맵 생성 후 테이블 접근
-- 중간 선택도 쿼리에 적합
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND total > 100;

스캔 유형 성능 순서 (일반적):

Index Only Scan  >  Index Scan  >  Bitmap Scan  >  Seq Scan
(가장 빠름)                                         (가장 느림)

-- PostgreSQL EXPLAIN 옵션
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 42;

-- BUFFERS: I/O 정보 포함
-- FORMAT JSON: JSON 형식으로 출력
-- VERBOSE: 추가 정보 포함
-- COSTS: 비용 추정값 포함 (기본 ON)
-- TIMING: 시간 정보 포함 (기본 ON)

-- MySQL EXPLAIN 옵션
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- MySQL 8.0+에서 트리 형식 지원
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE user_id = 42;

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스 (컬럼 순서가 중요!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 복합 인덱스 활용 규칙 (Leftmost Prefix Rule)
-- idx_orders_user_date는 다음 쿼리에 활용됨:
-- WHERE user_id = 1                          (O)
-- WHERE user_id = 1 AND created_at > '2025'  (O)
-- WHERE created_at > '2025'                  (X) 선행 컬럼 없음

-- 전문검색 GIN 인덱스
CREATE INDEX idx_articles_search
ON articles USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB GIN 인덱스
CREATE INDEX idx_events_data ON events USING GIN(metadata);

-- JSONB 특정 경로 인덱스
CREATE INDEX idx_events_type ON events USING GIN((metadata -> 'type'));

-- 사용 예시
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & tuning');

SELECT * FROM events
WHERE metadata @> '{"type": "click", "page": "/home"}';

-- BRIN 인덱스 - 시계열 데이터에 매우 효율적
-- B-Tree보다 100배 이상 작은 크기
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- 물리적 정렬과 상관관계가 높아야 효과적
-- 확인 방법:
SELECT correlation
FROM pg_stats
WHERE tablename = 'logs' AND attname = 'created_at';
-- correlation이 1에 가까우면 BRIN 적합

-- 부분 인덱스 (Partial Index) - 특정 조건의 행만 인덱싱
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- 전체 행의 5%만 인덱싱 -> 크기 95% 절약

-- 표현식 인덱스 (Expression Index)
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
-- WHERE LOWER(email) = 'user@example.com' 쿼리에 활용

-- 커버링 인덱스 (Covering Index)
CREATE INDEX idx_orders_covering
ON orders(user_id, created_at) INCLUDE (total, status);
-- Index Only Scan 가능 -> 테이블 접근 불필요

인덱스 설계 체크리스트:

필수 확인:
  [x] WHERE 절에 자주 사용되는 컬럼에 인덱스
  [x] JOIN 조건 컬럼에 인덱스
  [x] ORDER BY 컬럼이 인덱스에 포함
  [x] 복합 인덱스의 컬럼 순서 최적화
  [x] 선택도(Selectivity)가 높은 컬럼 우선

주의사항:
  [x] 쓰기 성능 vs 읽기 성능 트레이드오프
  [x] 사용되지 않는 인덱스 제거
  [x] 인덱스 크기 모니터링
  [x] 인덱스 bloat 관리 (PostgreSQL)

-- pg_stat_statements 활성화 (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

-- 확장 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 느린 쿼리 Top 10 (총 시간 기준)
SELECT
  substring(query, 1, 80) AS short_query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 평균 실행 시간이 긴 쿼리
SELECT
  substring(query, 1, 100) AS short_query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows / NULLIF(calls, 0) AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

# my.cnf - Slow Query Log 설정
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1.0          # 1초 이상 쿼리 기록
log_queries_not_using_indexes = 1  # 인덱스 미사용 쿼리 기록
min_examined_row_limit = 100   # 최소 100행 이상 검사한 쿼리

# mysqldumpslow로 Slow Query 분석
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/slow-query.log \
  --limit 10 \
  --order-by query_time:sum

-- PostgreSQL: 실시간 장시간 쿼리 확인
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

-- 락 대기 쿼리 확인
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid
WHERE NOT bl.granted AND kl.granted;

# N+1 문제 예시 (Python/SQLAlchemy)

# BAD: N+1 쿼리 발생
users = session.query(User).all()  # 1번 쿼리
for user in users:
    print(user.orders)  # N번 추가 쿼리 (사용자마다 1번)

# 실제 실행되는 SQL:
# SELECT * FROM users;                    -- 1번
# SELECT * FROM orders WHERE user_id = 1;  -- +1
# SELECT * FROM orders WHERE user_id = 2;  -- +1
# SELECT * FROM orders WHERE user_id = 3;  -- +1
# ... (N명의 사용자)

# 해결 1: Eager Loading (JOIN)
users = session.query(User).options(
    joinedload(User.orders)
).all()
# SQL: SELECT * FROM users LEFT JOIN orders ON ...  -- 1번 쿼리

# 해결 2: Subquery Loading
users = session.query(User).options(
    subqueryload(User.orders)
).all()
# SQL: SELECT * FROM users;                         -- 1번
# SQL: SELECT * FROM orders WHERE user_id IN (...);  -- 1번

# 해결 3: selectinload (SQLAlchemy 추천)
users = session.query(User).options(
    selectinload(User.orders)
).all()

// Node.js (Prisma) - N+1 해결
// BAD: N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const orders = await prisma.order.findMany({
    where: { userId: user.id }
  });
}

// GOOD: Include로 해결
const users = await prisma.user.findMany({
  include: { orders: true }
});

// GOOD: 필요한 필드만 선택
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    orders: {
      select: { id: true, total: true },
      where: { total: { gt: 100 } }
    }
  }
});

// Java (JPA/Hibernate)
// BAD: LAZY 로딩으로 N+1 발생
@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Order> orders;
}

// GOOD 1: JPQL JOIN FETCH
@Query("SELECT u FROM User u JOIN FETCH u.orders")
List<User> findAllWithOrders();

// GOOD 2: EntityGraph
@EntityGraph(attributePaths = {"orders"})
@Query("SELECT u FROM User u")
List<User> findAllWithOrders();

// GOOD 3: BatchSize (Hibernate)
@Entity
public class User {
    @OneToMany(mappedBy = "user")
    @BatchSize(size = 100)
    private List<Order> orders;
}

# Spring Boot - Hibernate N+1 탐지
# application.yml
spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 25

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.stat: DEBUG

# Django - django-debug-toolbar 또는 nplusone
# settings.py
INSTALLED_APPS = [
    'nplusone.ext.django',
]

NPLUSONE_RAISE = True  # 개발 환경에서 N+1 발견 시 에러 발생

커넥션 풀 없이:
Request -> [새 연결 생성 30ms] -> [쿼리 5ms] -> [연결 종료] = 35ms

커넥션 풀 사용:
Request -> [풀에서 기존 연결 가져오기 0.1ms] -> [쿼리 5ms] -> [풀에 반환] = 5.1ms

# application.yml - HikariCP 최적 설정
spring:
  datasource:
    hikari:
      # 풀 크기 공식: connections = (core_count * 2) + spinning_disks
      # 4코어 SSD 서버: (4 * 2) + 0 = 8
      maximum-pool-size: 10
      minimum-idle: 5

      # 커넥션 타임아웃 (풀에서 대기 최대 시간)
      connection-timeout: 30000   # 30초

      # 유휴 커넥션 제거 시간
      idle-timeout: 600000        # 10분

      # 커넥션 최대 수명
      max-lifetime: 1800000       # 30분

      # 커넥션 검증 쿼리
      connection-test-query: "SELECT 1"

      # 커넥션 누수 감지
      leak-detection-threshold: 60000  # 60초

      # 풀 이름 (모니터링용)
      pool-name: "MyApp-HikariPool"

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
# 풀링 모드
# session: 세션 유지 (기본)
# transaction: 트랜잭션 단위 (권장)
# statement: 문장 단위 (제한적)
pool_mode = transaction

# 풀 크기
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

# 연결 제한
max_client_conn = 200
max_db_connections = 50

# 타임아웃
server_idle_timeout = 600
client_idle_timeout = 300
query_timeout = 30
client_login_timeout = 60

# 인증
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 로그
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# 관리 인터페이스
listen_addr = 0.0.0.0
listen_port = 6432
admin_users = pgbouncer

-- PgBouncer 상태 확인
SHOW pools;
SHOW stats;
SHOW servers;
SHOW clients;

-- PostgreSQL 연결 수 확인
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- 최대 연결 수 확인
SHOW max_connections;

-- 안티패턴 1: SELECT *
-- BAD
SELECT * FROM users WHERE id = 1;
-- GOOD
SELECT id, name, email FROM users WHERE id = 1;

-- 안티패턴 2: 함수 사용으로 인덱스 무효화
-- BAD (인덱스 사용 불가)
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- GOOD (인덱스 사용 가능)
SELECT * FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

-- 안티패턴 3: LIKE 선행 와일드카드
-- BAD (인덱스 사용 불가)
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD (전문검색 인덱스 활용)
SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('phone');

-- 안티패턴 4: OR 대신 UNION ALL
-- BAD (인덱스 비효율적 사용)
SELECT * FROM orders WHERE user_id = 1 OR status = 'pending';
-- GOOD
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1;

-- 안티패턴 5: 큰 IN 절
-- BAD
SELECT * FROM products WHERE id IN (1, 2, 3, ..., 10000);
-- GOOD (임시 테이블 또는 ANY 사용)
SELECT * FROM products WHERE id = ANY(ARRAY[1, 2, 3, ...]);
-- 또는 임시 테이블 JOIN

-- 안티패턴 6: 불필요한 DISTINCT
-- BAD
SELECT DISTINCT u.name FROM users u JOIN orders o ON u.id = o.user_id;
-- GOOD (EXISTS 사용)
SELECT u.name FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 안티패턴 7: COUNT(*) vs EXISTS
-- BAD (전체 행 카운트)
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
FROM orders WHERE user_id = 1;
-- GOOD (첫 행만 확인)
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 1);

-- 안티패턴 8: 암시적 타입 변환
-- BAD (문자열 컬럼에 숫자 비교 -> 인덱스 무효)
SELECT * FROM users WHERE phone = 01012345678;
-- GOOD
SELECT * FROM users WHERE phone = '01012345678';

-- 안티패턴 9: OFFSET 페이지네이션
-- BAD (큰 OFFSET은 느림)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- GOOD (키셋 페이지네이션)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 안티패턴 10: 과도한 서브쿼리
-- BAD
SELECT * FROM users WHERE id IN (
  SELECT user_id FROM orders WHERE total > (
    SELECT AVG(total) FROM orders
  )
);
-- GOOD (CTE 또는 JOIN)
WITH avg_total AS (SELECT AVG(total) AS avg_val FROM orders)
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
CROSS JOIN avg_total
WHERE o.total > avg_total.avg_val;

-- JOIN 순서 최적화 (작은 테이블을 먼저)
-- PostgreSQL은 자동 최적화하지만, 힌트가 필요한 경우:

-- 테이블 크기 확인
SELECT relname, reltuples::bigint AS row_count
FROM pg_class
WHERE relname IN ('users', 'orders', 'products')
ORDER BY reltuples DESC;

-- JOIN에서 인덱스가 있는 컬럼 사용
-- GOOD
SELECT u.name, o.total
FROM orders o
JOIN users u ON u.id = o.user_id  -- users.id는 PK (인덱스)
WHERE o.status = 'completed';

-- Lateral Join 활용 (상위 N개 패턴)
SELECT u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent_orders;

-- Range 파티셔닝 (시계열 데이터에 적합)
CREATE TABLE orders (
  id bigserial,
  user_id bigint NOT NULL,
  total decimal(10,2),
  status varchar(20),
  created_at timestamp NOT NULL
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE orders_2025_01
  PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02
  PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 자동 파티션 생성 (pg_partman 활용)
-- CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  'public.orders',
  'created_at',
  'native',
  'monthly'
);

-- List 파티셔닝 (카테고리별 분할)
CREATE TABLE products (
  id bigserial,
  name varchar(255),
  category varchar(50),
  price decimal(10,2)
) PARTITION BY LIST (category);

CREATE TABLE products_electronics
  PARTITION OF products
  FOR VALUES IN ('electronics', 'computers', 'phones');

CREATE TABLE products_clothing
  PARTITION OF products
  FOR VALUES IN ('clothing', 'shoes', 'accessories');

-- Hash 파티셔닝 (균등 분배)
CREATE TABLE sessions (
  id uuid,
  user_id bigint,
  data jsonb,
  expires_at timestamp
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE sessions_p1
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE sessions_p2
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE sessions_p3
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- MySQL Range 파티셔닝
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  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
);

쓰기/읽기 분리 아키텍처:

       쓰기 요청          읽기 요청
          |                  |
     [Primary]    ->    [Replica 1]
          |              [Replica 2]
          |              [Replica 3]
     비동기 복제

# Spring Boot - 읽기/쓰기 분리
spring:
  datasource:
    primary:
      url: jdbc:postgresql://primary:5432/myapp
      username: app_write
    replica:
      url: jdbc:postgresql://replica:5432/myapp
      username: app_read

# Django - 읽기/쓰기 라우터
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'primary.db.example.com',
        'NAME': 'myapp',
    },
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'replica.db.example.com',
        'NAME': 'myapp',
    }
}

DATABASE_ROUTERS = ['myapp.routers.ReadReplicaRouter']

# routers.py
class ReadReplicaRouter:
    def db_for_read(self, model, **hints):
        return 'replica'

    def db_for_write(self, model, **hints):
        return 'default'

# Redis 캐싱 패턴
import redis
import json
from functools import wraps

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

# Cache-Aside (Lazy Loading) 패턴
def cache_aside(key_prefix, ttl=3600):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            cache_key = f"{key_prefix}:{args}:{kwargs}"

            # 1. 캐시에서 조회
            cached = r.get(cache_key)
            if cached:
                return json.loads(cached)

            # 2. DB에서 조회
            result = func(*args, **kwargs)

            # 3. 캐시에 저장
            r.setex(cache_key, ttl, json.dumps(result))

            return result
        return wrapper
    return decorator

@cache_aside("user", ttl=1800)
def get_user_profile(user_id):
    return db.query("SELECT * FROM users WHERE id = %s", [user_id])

# Write-Through 패턴
def update_user(user_id, data):
    # 1. DB 업데이트
    db.execute("UPDATE users SET name=%s WHERE id=%s", [data['name'], user_id])

    # 2. 캐시 업데이트 (또는 삭제)
    cache_key = f"user:{user_id}"
    r.delete(cache_key)  # Cache Invalidation
    # 또는
    r.setex(cache_key, 1800, json.dumps(data))  # Cache Update

캐싱 전략 비교:

Cache-Aside:
  장점: 가장 일반적, 유연함
  단점: 첫 요청은 캐시 미스

Write-Through:
  장점: 캐시 항상 최신
  단점: 쓰기 지연 증가

Write-Behind (Write-Back):
  장점: 쓰기 성능 최고
  단점: 데이터 손실 위험

Read-Through:
  장점: 캐시 로직이 분리됨
  단점: 구현 복잡도

-- 수동 VACUUM
VACUUM (VERBOSE) orders;

-- VACUUM FULL (테이블 재작성 - 락 발생!)
VACUUM FULL orders;

-- 통계 갱신
ANALYZE orders;

-- VACUUM + ANALYZE 동시
VACUUM ANALYZE orders;

# postgresql.conf - autovacuum 설정
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60

# 테이블의 20% 변경 시 autovacuum 실행
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50

# 테이블의 10% 변경 시 autoanalyze 실행
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50

# autovacuum 속도 조절 (I/O 부하)
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 200

-- HOT Update 조건:
-- 1. 업데이트된 컬럼에 인덱스가 없음
-- 2. 새 행이 같은 페이지에 저장될 수 있음

-- HOT 비율 확인
SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

-- HOT 비율을 높이려면:
-- 1. 자주 업데이트되는 컬럼에는 인덱스를 최소화
-- 2. fillfactor를 낮추어 같은 페이지에 공간 확보
ALTER TABLE orders SET (fillfactor = 80);

-- 테이블별 I/O 통계
SELECT
  relname,
  seq_scan,
  idx_scan,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 인덱스 사용률
SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 사용되지 않는 인덱스 (삭제 후보)
SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 캐시 적중률
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  round(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;
-- 99% 이상이 이상적

# my.cnf - InnoDB 최적화
[mysqld]
# Buffer Pool 크기 (전체 메모리의 70~80%)
innodb_buffer_pool_size = 8G

# Buffer Pool 인스턴스 (8G 이상일 때 분할)
innodb_buffer_pool_instances = 8

# 로그 파일 크기
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# I/O 스레드
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 동시성
innodb_thread_concurrency = 0  # 자동

# flush 방법
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1  # 안전 (2: 성능 우선)

# 임시 테이블
tmp_table_size = 256M
max_heap_table_size = 256M

-- Buffer Pool 상태 확인
SHOW ENGINE INNODB STATUS\G

-- Buffer Pool 적중률
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_ratio;
-- 99% 이상이 이상적

MySQL 8.0 쿼리 캐시 대안:

1. 애플리케이션 레벨 캐싱 (Redis/Memcached)
2. ProxySQL 쿼리 캐시
3. MySQL Router 캐싱
4. InnoDB Buffer Pool 최적화

-- MySQL 8.0 Performance Schema 활용
-- 느린 쿼리 분석
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 인덱스 힌트 (옵티마이저가 잘못된 선택을 할 때)
SELECT * FROM orders USE INDEX (idx_orders_user_date)
WHERE user_id = 1 AND created_at > '2025-01-01';

SELECT * FROM orders FORCE INDEX (idx_orders_status)
WHERE status = 'pending';

-- MySQL 8.0 옵티마이저 힌트
SELECT /*+ NO_INDEX(orders idx_orders_status) */
  * FROM orders WHERE user_id = 1;

SELECT /*+ JOIN_ORDER(users, orders) */
  u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id;