Jan 28, 2026

MySQL 성능 최적화 및 트러블슈팅 커맨드북: 인덱스, 데드락, 대용량 쓰기 기법

백엔드 애플리케이션 장애 및 성능 저하의 약 80%는 데이터베이스(DB) 연결층 및 쿼리 실행 비효율에서 기인합니다. 데이터가 소량일 때는 잘 작동하던 시스템이 대용량 트래픽 환경으로 이동하면 인덱스 부재, 트랜잭션 경합, 잘못된 PK 설계 등으로 인해 순식간에 마비되곤 합니다.

본 가이드에서는 개발 단계부터 프로덕션 운영까지 필수적으로 인지해야 할 MySQL 성능 최적화 핵심 패턴과 현업 트러블슈팅 방안을 종합 정리합니다.

1. 데이터베이스 인덱스 설계 및 분석 실무

인덱스는 올바르게 설계되었을 때 조회의 성능을 기하급수적으로 단축시켜 주지만, 과도하거나 잘못 설정된 인덱스는 삽입/수정 성능을 떨어뜨립니다.

복합 인덱스 설계와 최적 순서

동등 조건(=) 우선 법칙: = 조건으로 검색되는 컬럼을 인덱스의 가장 처음에 배치하고, 범위 조건(<, >, between, like) 컬럼은 뒤에 배치해야 복합 인덱스의 효용을 100% 누릴 수 있습니다.
카디널리티(Cardinality) 고려: 중복도가 낮고 유니크한 값이 많은 컬럼(예: 이메일, 주민번호 등)을 우선 정렬 컬럼으로 선택합니다.

커버링 인덱스 (Covering Index)

쿼리가 요구하는 모든 컬럼(Select 절 포함)이 인덱스 키에 완전히 포함되어 있어, 실제 테이블 데이터 레코드를 읽으러 가는 디스크 랜덤 I/O(Key Lookup) 과정을 아예 생략하는 기술입니다.

실전 쿼리 예시:

-- id와 name에 복합 인덱스가 걸려 있다면 테이블 참조 없이 인덱스 스캔만으로 종료됨
SELECT id, name FROM users WHERE name = 'Alice';

`EXPLAIN` 실행 계획 분석

쿼리 앞에 EXPLAIN 또는 EXPLAIN ANALYZE를 붙여 실행 계획을 검토합니다.

type 필드 해석: const, eq_ref, ref 유형이 최상이며, range는 좁은 범위여야 안전합니다. ALL(Full Table Scan) 또는 index(Full Index Scan)는 쿼리 튜닝 대상 1순위입니다.
Extra 필드 해석: Using filesort(디스크 정렬 발생) 또는 Using temporary가 보인다면 복합 인덱스 구성이나 정렬 조건을 다듬어야 함을 뜻합니다.

2. 자주 마주하는 쿼리 성능 병목 현상 및 튜닝

N+1 쿼리 문제와 해결 방안

N+1 문제는 부모 엔티티 1번 조회 시 자식 엔티티들을 가져오기 위해 자식 수(N)만큼 루프를 돌며 개별 SELECT 쿼리를 유발하는 현상입니다.

원인: JPA/Hibernate 등 ORM에서 연관 관계 지연 로딩(Lazy Loading) 설정을 해두었거나 쿼리 조인을 명시하지 않았을 때 일어납니다.
해결 방안: ORM 쿼리 시 Fetch Join을 사용해 한 번의 조인 쿼리로 묶어 가져오거나, MyBatis/SQL 환경에서는 서브쿼리 루프 대신 IN 절 조인을 수행하도록 수정합니다.

페이지네이션(Pagination) 성능 튜닝

OFFSET 페이지네이션의 문제점: LIMIT 1000000, 10과 같이 깊은 오프셋을 사용하면 MySQL은 앞의 1,000,000건의 행을 전부 메모리로 읽은 뒤 버리기 때문에 페이지가 뒤로 갈수록 극도로 느려집니다.
Cursor-based (No-OFFSET) 페이지네이션: 마지막으로 조회한 데이터의 PK 또는 유니크한 정렬 키 값을 기억했다가 다음 페이지 조회 시 WHERE id > last_seen_id 조건으로 탐색하여 오프셋 읽기 작업을 원천 차단합니다.
```
-- 개선된 형태의 페이지네이션 쿼리
SELECT * FROM posts WHERE id < 18402 ORDER BY id DESC LIMIT 20;
```

3. 대용량 쓰기(Write) 및 삽입 최적화

데이터 삽입 속도가 나오지 않거나 쓰기 지연이 걸린다면 아래의 구조적 해법을 도입해야 합니다.

배치 인서트 (Batch Insert)

건별로 커넥션을 맺고 단일 INSERT 문을 날리는 대신, 하나의 쿼리에 다량의 레코드를 담아 전송합니다.

JDBC/Spring 환경 설정:

# Spring Data JPA에서 Bulk insert 활성화 설정
spring.jpa.properties.hibernate.jdbc.batch_size=500
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

주의 사항: JPA에서는 PK 생성 전략이 IDENTITY(Auto-Increment)인 경우 영속성 컨텍스트 쓰기 지연 메커니즘으로 인해 Batch Insert가 자동으로 작동하지 않으므로, Sequence 전략을 취하거나 JdbcTemplate을 이용해 직접 Bulk SQL을 작성해야 합니다.

UUID PK 설계의 함정

무작위로 생성되는 UUID v4는 무작위성이 강해 인덱스 페이지 분할(Page Split)을 빈번히 초래하고 디스크 캐시 효율을 무너뜨립니다.

해결책 (UUID v7 / ULID): 시간 기반의 순차 정렬이 가능한 UUID v7 또는 ULID를 기본키로 도입합니다. 앞부분에 타임스탬프 정보가 포함되어 있어 인덱스 끝부분에 차곡차곡 삽입되므로 성능 저하를 방지합니다.

4. 트랜잭션 격리 수준, 데드락 및 리플리케이션 지연

데이터의 무결성을 지키는 것만큼이나 동시성을 관리하는 인프라 튜닝이 핵심입니다.

트랜잭션 격리 수준 (Isolation Level)

MySQL (InnoDB)의 기본 격리 수준은 REPEATABLE READ입니다.

Phantom Read 방지: InnoDB는 넥스트 키 락(Next-Key Lock, 레코드 락 + 갭 락)을 사용해 트랜잭션 도중 다른 트랜잭션이 새로운 데이터를 끼워 넣지 못하게 원천 방지하여 REPEATABLE READ 수준에서도 팬텀 리드를 막아줍니다.

데드락 (Deadlock) 트러블슈팅

두 트랜잭션이 서로가 획득한 락(Lock)이 해제되기를 무한히 기다리는 현상입니다.

예방 패턴:
- 트랜잭션을 가능한 한 짧게 유지합니다.
- 여러 테이블을 수정할 때 항상 테이블 접근 순서를 동일하게 유지합니다.
- 데드락 발생 로그는 SHOW ENGINE INNODB STATUS; 명령을 터미널에서 실행하여 상세 락 대기 원인을 정확히 추적할 수 있습니다.

리플리케이션 지연 (Replication Lag)

읽기 분산(Read Replica) 환경에서 마스터 서버의 쓰기 작업량이 슬레이브 서버의 반영 속도보다 빨라 두 서버 간 데이터 불일치 시간이 늘어나는 문제입니다.

실무 대책:
- 사용자가 방금 작성한 글을 상세 페이지에서 읽는 것과 같이 실시간성 조회가 극도로 중요한 쿼리는 읽기 전용 복제본(Slave)이 아닌 쓰기 전용 마스터(Master) DB로 강제 라우팅하도록 백엔드 라우터 소스코드를 작성합니다.
- 긴 트랜잭션을 작게 쪼개어 단일 쓰기 잠금이 리플리케이션 스레드를 독점하지 못하게 방지합니다.

먼저 읽어볼 가이드

검색 유입이 많은 핵심 글부터 이어서 보세요.