MySQL 고급 가이드 | 인덱싱·쿼리 최적화·복제·파티셔닝·성능 튜닝

이 글의 핵심

MySQL 고급 기능을 실전 예제로 완벽 정리합니다. 인덱싱 전략, 쿼리 최적화, 복제, 파티셔닝, 트랜잭션, 성능 튜닝까지 실무에 바로 적용할 수 있는 가이드입니다.

실무 경험 공유: 일 1억 건의 트랜잭션을 처리하는 MySQL 서버를 운영하면서, 인덱싱으로 쿼리 속도를 100배 향상시키고 복제로 읽기 성능을 5배 높인 경험을 공유합니다.

들어가며: “MySQL이 느려요”

실무 문제 시나리오

시나리오 1: 쿼리가 10초 걸려요

1억 건 테이블에서 쿼리가 느립니다. 인덱스로 0.1초로 단축합니다. 시나리오 2: 읽기 부하가 너무 커요

단일 서버로 감당 안 됩니다. 복제로 분산합니다. 시나리오 3: 테이블이 너무 커요

수억 건 데이터로 관리가 어렵습니다. 파티셔닝으로 해결합니다.

1. MySQL이란?

핵심 특징

MySQL은 가장 인기 있는 오픈소스 RDBMS입니다. 주요 장점:

• 빠른 성능: 읽기 성능 우수
• 안정성: 검증된 안정성
• 확장성: 복제, 샤딩 지원
• 생태계: 풍부한 도구
• 무료: 오픈소스

---

2. 인덱싱 전략

단일 컬럼 인덱스

-- 인덱스 생성
CREATE INDEX idx_email ON users(email);
-- 복합 인덱스
CREATE INDEX idx_user_created ON users(user_id, created_at);
-- 유니크 인덱스
CREATE UNIQUE INDEX idx_email_unique ON users(email);
-- 인덱스 확인
SHOW INDEX FROM users;
-- 인덱스 삭제
DROP INDEX idx_email ON users;

Full-Text 인덱스

-- Full-Text 인덱스 생성
CREATE FULLTEXT INDEX idx_content ON articles(title, content);
-- 검색
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql tutorial' IN NATURAL LANGUAGE MODE);
-- Boolean 모드
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

---

3. 쿼리 최적화

EXPLAIN

-- 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- 더 자세한 정보
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';

인덱스 사용 확인

-- ❌ 느린 쿼리 (인덱스 미사용)
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- ✅ 빠른 쿼리 (인덱스 사용)
SELECT * FROM users 
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

JOIN 최적화

-- ❌ 느린 쿼리
SELECT u.*, p.*
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.is_active = 1;
-- ✅ 빠른 쿼리 (필요한 컬럼만)
SELECT u.id, u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.is_active = 1;

---

4. 파티셔닝

Range 파티셔닝

CREATE TABLE orders (
    id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    created_at DATE NOT NULL
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

List 파티셔닝

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(100),
    country VARCHAR(2)
)
PARTITION BY LIST COLUMNS(country) (
    PARTITION p_us VALUES IN ('US'),
    PARTITION p_kr VALUES IN ('KR'),
    PARTITION p_jp VALUES IN ('JP'),
    PARTITION p_others VALUES IN (DEFAULT)
);

---

5. 복제 (Replication)

Master 설정

# /etc/mysql/my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb

Slave 설정

# /etc/mysql/my.cnf
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin

복제 시작

-- Master에서
-- 실행 예제
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
-- Slave에서
CHANGE MASTER TO
  MASTER_HOST='master-host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G

---

6. 트랜잭션

ACID

-- 실행 예제
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 성공 시
COMMIT;
-- 실패 시
ROLLBACK;

격리 수준

-- 격리 수준 설정
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 확인
SELECT @@transaction_isolation;

---

7. 성능 튜닝

설정 최적화

# /etc/mysql/my.cnf
[mysqld]
# InnoDB 버퍼 풀 (RAM의 70-80%)
innodb_buffer_pool_size = 8G
# 연결 수
max_connections = 500
# 쿼리 캐시 (MySQL 5.7 이하)
query_cache_type = 1
query_cache_size = 256M
# 로그
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Slow Query 분석

# Slow Query 로그 분석
mysqldumpslow /var/log/mysql/slow.log
# 가장 느린 쿼리 10개
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

---

8. 백업

mysqldump

# 전체 백업
mysqldump -u root -p --all-databases > backup.sql
# 특정 데이터베이스
mysqldump -u root -p mydb > mydb.sql
# 복원
mysql -u root -p mydb < mydb.sql

자동 백업

# backup.sh
#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -p${MYSQL_PASSWORD} mydb > /backups/mydb_${DATE}.sql
find /backups -name "*.sql" -mtime +7 -delete

---

정리 및 체크리스트

핵심 요약

• MySQL: 가장 인기 있는 RDBMS
• 인덱싱: B-Tree, Full-Text 인덱스
• 쿼리 최적화: EXPLAIN 분석
• 파티셔닝: 대용량 데이터 관리
• 복제: 읽기 성능 향상
• 트랜잭션: ACID 보장

프로덕션 체크리스트

• 적절한 인덱스 생성
• Slow Query 분석
• 복제 구성
• 파티셔닝 (필요 시)
• 백업 자동화
• 성능 튜닝
• 모니터링 설정

---

같이 보면 좋은 글

• PostgreSQL 고급 가이드
• MongoDB 고급 가이드
• Redis 고급 가이드

---

이 글에서 다루는 키워드

MySQL, Database, SQL, Indexing, Optimization, Replication, Performance

자주 묻는 질문 (FAQ)

Q. MySQL vs PostgreSQL, 어떤 게 나은가요?

A. MySQL은 읽기 성능이 좋습니다. PostgreSQL은 복잡한 쿼리와 JSON 지원이 좋습니다. 읽기 위주는 MySQL, 복잡한 쿼리는 PostgreSQL을 권장합니다.

Q. InnoDB vs MyISAM, 어떤 걸 사용하나요?

A. InnoDB를 사용하세요. 트랜잭션과 외래 키를 지원합니다. MyISAM은 레거시입니다.

Q. 파티셔닝은 언제 사용하나요?

A. 테이블이 수억 건 이상일 때 사용합니다. 초기에는 인덱싱으로 충분합니다.

Q. 프로덕션에서 사용해도 되나요?

A. 네, Facebook, Twitter, YouTube 등 많은 기업에서 사용합니다.

심화 부록: 구현·운영 관점

이 부록은 앞선 본문에서 다룬 주제(「MySQL 고급 가이드 | 인덱싱·쿼리 최적화·복제·파티셔닝·성능 튜닝」)를 구현·런타임·운영 관점에서 다시 압축합니다. 도메인별 세부 구현은 글마다 다르지만, 입력 검증 → 핵심 연산 → 부작용(I/O·네트워크·동시성) → 관측의 흐름으로 장애를 나누면 원인 추적이 빨라집니다.

내부 동작과 핵심 메커니즘

flowchart TD
  A[입력·요청·이벤트] --> B[파싱·검증·디코딩]
  B --> C[핵심 연산·상태 전이]
  C --> D[부작용: I/O·네트워크·동시성]
  D --> E[결과·관측·저장]

sequenceDiagram
  participant C as 클라이언트/호출자
  participant B as 경계(런타임·게이트웨이·프로세스)
  participant D as 의존성(API·DB·큐·파일)
  C->>B: 요청/이벤트
  B->>D: 조회·쓰기·RPC
  D-->>B: 지연·부분 실패·재시도 가능
  B-->>C: 응답 또는 오류(코드·상관 ID)

• 불변 조건(Invariant): 버퍼 경계, 프로토콜 상태, 트랜잭션 격리, FD 상한 등 단계별로 문장으로 적어 두면 디버깅 비용이 줄어듭니다.
• 결정성: 순수 층과 시간·네트워크·스케줄에 의존하는 층을 분리해야 테스트와 장애 분석이 쉬워집니다.
• 경계 비용: 직렬화, 인코딩, syscall 횟수, 락 경합, 할당·GC, 캐시 미스를 의심 목록에 둡니다.
• 백프레셔: 생산자가 소비자보다 빠를 때 버퍼·큐·스트림에서 속도를 줄이는 신호를 어디에 둘지 정의합니다.

프로덕션 운영 패턴

영역	운영 관점 질문
관측성	요청 단위 상관 ID, 에러율·지연 p95/p99, 의존성 타임아웃·재시도가 대시보드에 보이는가
안전성	입력 검증·권한·비밀·감사 로그가 코드 경로마다 일관적인가
신뢰성	재시도는 멱등 연산에만 적용되는가, 서킷 브레이커·백오프·DLQ가 있는가
성능	캐시·배치 크기·커넥션 풀·인덱스·백프레셔가 데이터 규모에 맞는가
배포	롤백 룬북, 카나리/블루그린, 마이그레이션·피처 플래그가 문서화되어 있는가
용량	피크 트래픽·디스크·FD·스레드 풀 상한을 주기적으로 검증하는가

스테이징은 데이터 양·네트워크 RTT·동시성을 프로덕션에 가깝게 맞출수록 재현율이 올라갑니다.

확장 예시: 엔드투엔드 미니 시나리오

앞선 본문 주제(「MySQL 고급 가이드 | 인덱싱·쿼리 최적화·복제·파티셔닝·성능 튜닝」)를 배포·운영 흐름에 맞춰 옮긴 체크리스트입니다. 도메인에 맞게 단계 이름만 바꿔 적용할 수 있습니다.

1. 입력 계약 고정: 스키마·버전·최대 페이로드·타임아웃·에러 코드를 경계에 둔다.
2. 핵심 경로 계측: 요청 ID, 단계별 지연, 외부 호출 결과 코드를 로그·메트릭·트레이스에서 한 흐름으로 본다.
3. 실패 주입: 의존성 타임아웃·5xx·부분 데이터·락 대기를 스테이징에서 재현한다.
4. 호환·롤백: 설정/마이그레이션/클라이언트 버전을 되돌릴 수 있는지 확인한다.
5. 부하 후 검증: 피크 대비 p95/p99, 에러율, 리소스 상한, 알림 임계값을 점검한다.

handle(request):
  ctx = newCorrelationId()
  validated = validateSchema(request)
  authorize(validated, ctx)
  result = domainCore(validated)
  persistOrEmit(result, idempotentKey)
  recordMetrics(ctx, latency, outcome)
  return result

문제 해결(Troubleshooting)

증상	가능 원인	조치
간헐적 실패	레이스, 타임아웃, 외부 의존성, DNS	최소 재현 스크립트, 분산 트레이스·로그 상관관계, 재시도·서킷 설정 점검
성능 저하	N+1, 동기 I/O, 락 경합, 과도한 직렬화, 캐시 미스	프로파일러·APM으로 핫스팟 확인 후 한 가지씩 제거
메모리 증가	캐시 무제한, 구독/리스너 누수, 대용량 버퍼, 커넥션 미반납	상한·TTL·힙/FD 스냅샷 비교
빌드·배포만 실패	환경 변수, 권한, 플랫폼 차이, lockfile	CI 로그와 로컬 diff, 런타임·이미지 버전 핀
설정 불일치	프로필·시크릿·기본값, 리전	스키마 검증된 설정 단일 소스와 배포 매트릭스 표준화
데이터 불일치	비멱등 재시도, 부분 쓰기, 캐시 무효화 누락	멱등 키·아웃박스·트랜잭션 경계 재검토

권장 순서: (1) 최소 재현 (2) 최근 변경 범위 축소 (3) 환경·의존성 차이 (4) 관측으로 가설 검증 (5) 수정 후 회귀·부하 테스트.

배포 전에는 git add → git commit → git push 후 npm run deploy 순서를 권장합니다.