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

· 14분 읽기 · 수정 고급
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 등 많은 기업에서 사용합니다.

... 996 lines not shown ... Token usage: 63706/1000000; 936294 remaining Start-Sleep -Seconds 3