슬로우 쿼리 분석 — pg_stat_statements · MySQL slow log

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

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

-- 설정 확인
SHOW shared_preload_libraries;

# postgresql.conf 수정
sudo nano /etc/postgresql/16/main/postgresql.conf

# 추가:
# shared_preload_libraries = 'pg_stat_statements'
# pg_stat_statements.track = all
# pg_stat_statements.max = 10000

sudo systemctl restart postgresql

-- 평균 실행 시간 기준 Top 10
SELECT
  round(mean_exec_time::numeric, 2) AS avg_ms,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 총 실행 시간 기준 (가장 영향 큰 쿼리)
SELECT
  round(total_exec_time::numeric / 1000, 2) AS total_sec,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- I/O 많은 쿼리
SELECT
  left(query, 80),
  shared_blks_read,
  shared_blks_hit,
  round(shared_blks_hit * 100.0 / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_rate
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- 통계 초기화
SELECT pg_stat_statements_reset();

-- 기본 실행 계획
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 실제 실행 + 통계
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name
ORDER BY COUNT(o.id) DESC;

Seq Scan     → 순차 스캔 (느림, 인덱스 필요)
Index Scan   → 인덱스 사용 (빠름)
Bitmap Scan  → 중간 정도
Hash Join    → 대용량 조인
Nested Loop  → 소용량 조인

Actual Rows >> Estimated Rows → 통계 오래됨
cost=0..1234 → 실행 비용 추정
actual time=0.1..500 ms → 실제 시간

-- 통계 갱신
ANALYZE users;
ANALYZE VERBOSE users;

# /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log       = 1
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1          # 1초 이상
log_queries_not_using_indexes = 1 # 인덱스 안 쓰는 쿼리도 기록

sudo systemctl restart mysql

# 실시간 모니터링
sudo tail -f /var/log/mysql/slow.log

# mysqldumpslow로 분석
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log   # 총 시간 기준 Top 10
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log   # 호출 횟수 기준

EXPLAIN SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- 주요 컬럼
-- type: ALL(풀스캔) < index < range < ref < eq_ref < const (좋아짐)
-- key: 사용된 인덱스
-- rows: 검사 예상 행 수
-- Extra: Using filesort, Using temporary → 성능 문제

-- 1. 인덱스 없는 WHERE 조건 → 인덱스 추가
CREATE INDEX idx_users_status_email ON users(status, email);

-- 2. SELECT * → 필요한 컬럼만
SELECT id, name FROM users WHERE status = 'active';

-- 3. N+1 문제 → JOIN으로 해결
-- 나쁜 예: 루프에서 각 유저마다 쿼리
-- 좋은 예:
SELECT u.id, u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 4. OFFSET 대신 커서 페이징
-- 나쁜 예: LIMIT 10 OFFSET 10000
-- 좋은 예:
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;