MySQL(InnoDB) 성능/특성 정리: 인덱스/Buffer Pool/잠금으로 성능을 설명하기

MySQL(InnoDB) 성능/특성 정리: 인덱스/Buffer Pool/잠금으로 성능을 설명하기

이 글은 MySQL(InnoDB)를 성능/운영 관점에서 길게 정리한 개인 노트입니다. 목표는 "지금 느린 이유"를 증거로 좁히고, 재발을 줄이는 체크리스트를 갖추는 것입니다.

전제: 성능은 DB만으로 결정되지 않습니다. 쿼리 패턴, 데이터 분포, 인덱스/스키마, 애플리케이션 트랜잭션 경계, 인프라(IO/네트워크)가 함께 결정합니다. 그래서 이 글은 '성능 모델(어디서 시간이 쓰이나) -> 설계/쿼리 -> 설정 -> 운영' 순서로 정리합니다.

MySQL(InnoDB)은 '버퍼 풀 적중 + 인덱스 설계 + 락/트랜잭션 범위'가 체감 성능을 결정합니다. 튜닝은 설정 전에 쿼리 패턴부터 정리하는 게 빠릅니다.

이 DB를 언제 선택하나

정답은 항상 케이스 바이 케이스지만, 선택을 빠르게 만들기 위해 기준을 먼저 둡니다. 아래에 해당이 많을수록 이 DB를 고려할 만합니다.

- OLTP 중심에서 넓은 생태계와 운영 경험을 활용하고 싶을 때
- 단순 조회/갱신이 많고, 인덱스 튜닝으로 대부분 해결 가능한 서비스
- 스토리지 엔진(InnoDB)의 동작을 이해하고 안정적으로 운영하고 싶을 때

성능 모델: 어디서 시간이 쓰이나

InnoDB의 기본 전제는 데이터도 인덱스도 결국 페이지(page) 단위로 읽고 쓴다는 것입니다. 자주 쓰는 페이지가 buffer pool에 남아 있으면 디스크 IO가 줄어 성능이 급격히 좋아집니다.

쓰기 성능은 redo log, flush 정책, 그리고 트랜잭션이 만드는 잠금/대기(특히 긴 트랜잭션) 영향을 강하게 받습니다. 조회가 느릴 때는 먼저 인덱스/실행 계획을, 쓰기가 느릴 때는 redo/flush/락 대기를 분리해서 봐야 합니다.

또 하나의 흔한 함정은 인덱스를 만들면 끝이라는 믿음입니다. 통계/카디널리티가 흔들리면 인덱스를 잘못 고르거나, 범위 조건+정렬에서 temporary/filesort가 떠서 성능이 깨질 수 있습니다.

핵심 용어 빠른 사전

성능/장애 분석에서 자주 쓰는 단어는 팀마다 다르게 이해되기 쉽습니다. 용어를 짧게 정의하고, 어떤 지표/증상과 연결되는지 같이 적어둡니다.

용어	설명	실무에서 연결되는 것
Buffer Pool	InnoDB 데이터/인덱스 캐시	디스크 read가 많아지면 지연이 급증
Clustered Index	PK 기반으로 레코드가 저장되는 구조	PK 선택이 곧 저장/조회 성능에 영향
Secondary Index	보조 인덱스(리프에서 PK를 통해 레코드 접근)	커버링이 아니면 추가 랜덤 IO가 늘 수 있음
Redo Log	내구성/복구를 위한 로그	쓰기 지연/flush 정책과 연결
Undo	트랜잭션 롤백/일관성에 필요한 정보	긴 트랜잭션/락 대기와 연결
Gap Lock	격리 수준에서 생기는 범위 락	특정 격리 수준에서 동시성 저하/데드락 원인

옵션/핵심 요소(3~6)

자주 부딪히는 핵심 요소를 표로 먼저 고정해 두면, 장애/튜닝 때 팀 커뮤니케이션이 빨라집니다.

항목	의미	언제 쓰는지(실무 상황)
EXPLAIN	실행 계획을 확인(접근 방식/추정 rows)	인덱스가 제대로 쓰이는지 1차 확인
slow_query_log	느린 쿼리를 기록	체감 성능 이슈를 재현 없이 추적
innodb_buffer_pool_size	버퍼 풀 크기	읽기 IO가 많은데 메모리 여유가 있을 때
innodb_flush_log_at_trx_commit	redo flush/commit 동기화 정책	쓰기 지연과 내구성 요구를 조정할 때
ANALYZE TABLE	통계 갱신	계획이 갑자기 바뀌거나 인덱스 선택이 이상할 때
SHOW ENGINE INNODB STATUS	락/트랜잭션/버퍼 상태 요약	락 대기/데드락/쓰기 지연을 빠르게 훑을 때

실무 튜닝 포인트(설정/옵션별)

설정에는 정답이 없습니다. 워크로드(읽기/쓰기/동시성/데이터 크기) 기준으로 관측하고 조정합니다. 아래는 실무에서 가장 자주 만지는 레버를 '항목별'로 정리한 것입니다.

innodb_buffer_pool_size
의미: 데이터/인덱스 캐시의 핵심
언제 만지나: 디스크 read가 많고 캐시 미스가 잦을 때
주의/트레이드오프: 너무 크게 잡으면 OS/다른 프로세스 메모리를 압박

innodb_buffer_pool_instances
의미: 버퍼 풀을 여러 인스턴스로 분할
언제 만지나: 동시성이 높아 버퍼 풀 경합이 보일 때
주의/트레이드오프: 과도하게 늘려도 효과가 제한적

innodb_log_file_size
의미: redo log 파일 크기
언제 만지나: 쓰기 burst에서 flush/checkpoint가 잦을 때
주의/트레이드오프: 너무 크게 잡으면 crash recovery 시간이 길어질 수 있음

innodb_flush_log_at_trx_commit
의미: commit 내구성/지연 정책
언제 만지나: 내구성/지연을 정책으로 정해야 할 때
주의/트레이드오프: 값에 따라 장애 시 유실 가능성이 달라짐

innodb_flush_method
의미: OS 캐시/직접 IO 방식
언제 만지나: 이중 버퍼링이 의심되거나 IO 패턴이 비효율적일 때
주의/트레이드오프: 환경/파일시스템에 따라 최적이 달라 테스트 필요

max_connections
의미: 동시 연결 상한
언제 만지나: 스레드 폭증으로 컨텍스트 스위칭이 병목일 때
주의/트레이드오프: 상한을 늘리면 문제를 숨기고 더 느려질 수 있음

tmp_table_size
의미: 내부 임시 테이블 메모리 한도
언제 만지나: temporary/filesort가 잦을 때
주의/트레이드오프: 동시 쿼리에서 메모리 급증

sort_buffer_size
의미: 정렬 버퍼(세션 단위)
언제 만지나: 대형 정렬이 잦고 비용이 클 때
주의/트레이드오프: 세션 단위라 크게 잡으면 위험

innodb_lock_wait_timeout
의미: 락 대기 타임아웃
언제 만지나: 락 경합 시 빠르게 실패/재시도 전략을 쓰고 싶을 때
주의/트레이드오프: 너무 짧으면 정상 트랜잭션도 실패

transaction_isolation
의미: 격리 수준
언제 만지나: 일관성/성능 균형을 설계할 때
주의/트레이드오프: 격리 수준이 올라갈수록 락/대기 영향 증가 가능

쿼리/스키마 설계에서 성능이 갈리는 지점

InnoDB에서 성능이 갈리는 지점은 어떤 인덱스가 레코드를 얼마나 빨리 좁혀주느냐와, 그 다음에 추가로 읽는 row가 얼마나 많느냐입니다. 커버링 인덱스(인덱스만으로 SELECT 컬럼을 충족)가 되면 랜덤 IO를 크게 줄일 수 있습니다.

복합 인덱스는 좌측(prefix) 규칙을 지키고, 범위 조건이 먼저 나오면 그 뒤 컬럼은 정렬/추가 필터에 잘 못 쓰는 경우가 많습니다. 실제 쿼리 형태에 맞춰 인덱스를 설계하고, 불필요 인덱스(쓰기 비용만 올리는)를 줄이는 것도 튜닝의 일부입니다.

-- 계획 확인
EXPLAIN SELECT id, total_amount FROM orders WHERE user_id = 42 AND status = 'PAID' ORDER BY created_at DESC LIMIT 50;

-- 패턴에 맞춘 복합/커버링 인덱스(예시)
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC, id, total_amount);

-- 통계가 의심될 때
ANALYZE TABLE orders;

진단 명령/쿼리 모음(바로 실행용)

장애 때는 문서 찾을 시간이 없습니다. 아래는 현장에서 자주 쓰는 '첫 5분' 진단 명령/쿼리만 모은 것입니다. 환경/권한에 따라 일부는 제한될 수 있습니다.

-- 1) 현재 실행 중인 쿼리/세션
SHOW FULL PROCESSLIST;

-- 2) 락/대기/데드락 힌트(출력 큼)
SHOW ENGINE INNODB STATUS\G

-- 3) 실행 계획 확인
EXPLAIN SELECT 1;

-- 4) 통계 갱신(테이블 단위)
ANALYZE TABLE orders;

자주 하는 실수(운영/튜닝)

실수 체크리스트:
- slow query 없이 체감만으로 튜닝을 시작한다
- 인덱스를 추가하기 전에 EXPLAIN으로 계획/스캔량을 확인하지 않는다
- 커버링 인덱스가 가능한데도 불필요하게 테이블 랜덤 IO를 유발한다
- 긴 트랜잭션(외부 API 호출 포함)을 그대로 두고 락/대기가 커진다
- max_connections를 늘려서 연결 폭주를 숨기고, 풀링/백프레셔를 도입하지 않는다
- tmp_table_size/sort_buffer_size를 크게 잡아 동시성에서 메모리 폭발을 만든다

운영/모니터링 체크리스트

운영에서는 1) slow query 상위, 2) buffer pool hit, 3) redo/flush 지연, 4) row lock time, 5) deadlock 빈도, 6) replica lag(복제 사용 시)를 같이 봅니다.

락 문제는 로그만 보면 감이 안 오는 경우가 많아, 대기 트랜잭션/락을 먼저 확인하고 애플리케이션의 트랜잭션 범위를 줄이는 게 큰 효과를 냅니다.

공통 체크리스트:
- 느린 쿼리 top N을 지속 수집(시간/호출수/총시간)
- 지연(p95/p99)과 오류율을 애플리케이션 지표와 함께 본다
- 캐시/메모리/디스크 IO 중 무엇이 병목인지 먼저 분리한다
- 복제/HA를 쓰면 일관성 요구가 높은 기능을 분리한다
- 튜닝/변경 후에는 동일 조건으로 재측정해 회귀를 막는다

트러블슈팅 루틴(순서 고정)

장애 때는 선택지가 너무 많아서 흔히 길을 잃습니다. 아래 순서를 팀 표준으로 정해두면, 원인 추적이 빨라지고 '감으로 설정만 만지는' 일을 줄일 수 있습니다.

1) slow_query_log/Performance Schema로 상위 쿼리를 고른다
2) EXPLAIN으로 인덱스/rows 추정을 본다
3) temporary/filesort 여부를 확인하고 정렬/범위 조건을 재검토한다
4) 커버링/복합 인덱스를 설계하고 불필요 인덱스는 정리한다
5) 쓰기 지연이면 redo/flush 설정과 디스크 지연, 락 대기를 분리해서 본다
6) 변경 후 동일 쿼리를 재측정하고 회귀 방지를 추가한다

문제 상황(정확히 1개)

상황 -> 특정 API가 간헐적으로 3~10초까지 느려지고, 서버 자원은 남아 있는데도 응답이 밀린다.
원인 -> 긴 트랜잭션이 행 락을 오래 잡아 다른 트랜잭션이 lock wait에 갇혔고, slow query는 대기 포함으로 길어졌다.
해결 -> 락 대기 원인을 찾고 트랜잭션 범위를 줄이며, 필요한 인덱스를 추가해 잠금 범위를 축소한다.
예방 팁 -> 트랜잭션 경계를 명확히 하고 lock wait/deadlock을 지표/알람으로 운영한다.

참고/출처

정확한 동작/버전별 차이는 공식 문서를 기준으로 확인합니다. 실무에서는 버전 차이가 곧 성능/장애 차이입니다.

• MySQL 8.0 Reference Manual
• MySQL: EXPLAIN Output Format
• InnoDB Buffer Pool