pkglog
EN

MySQL 엔진 이해하기: InnoDB 페이지와 리두, MVCC, 옵티마이저, 복제와 백업

작성 · · 15분 읽기 · 수정 고급 튜토리얼

이 글의 핵심

복합 인덱스 컬럼 순서가 뒤집혀 p99가 크게 튀었던 사례에서 출발해, 인덱스를 쿼리 모양·정렬·커버링과 함께 봐야 하는 이유를 짚습니다. 무작위 UUID PK가 만드는 페이지 단편화, 긴 트랜잭션이 purge를 밀어내는 현상, 비동기 복제의 지연을 전제로 한 읽기 설계까지 운영에서 자주 부딪히는 판단을 정리합니다.

MySQL이 느리다고 느끼신다면, 실제로는 엔진 자체보다 스키마·인덱스·락·복제 지연 쪽이 원인인 경우가 훨씬 많습니다. 이 글은 교과서식 목차로 정리하는 대신, 제가 고수하는 기준 하나를 먼저 밝히고 시작하겠습니다. InnoDB가 아닌 다른 저장 엔진을 쓰려면 분명한 이유가 있어야 합니다. 2020년대 OLTP에서 별다른 이유 없이 Memory나 MyISAM을 고르는 것은 설득력이 없습니다. 트랜잭션, 행 잠금, FK, 크래시 복구까지 기본으로 갖춘 것이 InnoDB이고, “예전에 그렇게 썼기 때문”이라는 이유는 운영 난이도만 높입니다.

인덱스를 잘못 걸어서 크게 고생한 적이 있습니다. 흔한 사례입니다. 주문 조회에 user_id와 created_at을 함께 쓰는데, 누군가 “일단 created_at에만 인덱스가 있으면 되겠지” 하고 인덱스를 걸어 두었습니다. WHERE user_id = ? AND created_at BETWEEN ? AND ? 패턴이 하루에 수십만 번 실행되는 서비스였는데, 옵티마이저는 앞쪽 컬럼에 동등(=) 조건이 걸리는 복합 인덱스를 가장 잘 활용합니다. 컬럼 순서가 뒤집혀 있으면 range 접근은 나오더라도 스캔 범위가 걷잡을 수 없이 넓어집니다. 그날 p99가 크게 튀어서 슬로 로그를 뒤지며 rows와 Extra를 보다가 “왜 Using filesort/Using temporary까지 함께 뜨지?” 하고 고민했고, 인덱스를 (user_id, created_at) 순서로 정비한 뒤에는 지연이 자릿수 단위로 줄어들었습니다. 그래서 인덱스는 “있으면 된다”가 아니라 쿼리 모양·정렬·커버링과 함께 봐야 한다는 것을 몸으로 배웠습니다. 자주 하는 실수로는 카디널리티가 낮은 컬럼을 앞에 두는 것, SELECT * 습관, 함수로 감싼 컬럼, 그리고 “조회만 빨라 보이는” 쪽에만 집중하다가 쓰기 비용 폭증을 잊는 것이 있습니다.

컬럼 순서가 중요한 이유는 B+Tree 인덱스가 왼쪽 컬럼부터 차례로 정렬된 목록이기 때문입니다. (user_id, created_at) 인덱스는 전화번호부처럼 먼저 user_id로 정렬되고, 같은 user_id 안에서 created_at으로 정렬됩니다. 그래서 user_id = 42로 한 사용자의 구간을 바로 찾은 뒤 그 안에서 날짜 범위를 연속으로 읽으면 됩니다. 순서가 (created_at, user_id)라면 날짜 범위 안의 모든 사용자 행을 훑으면서 user_id를 하나씩 걸러야 합니다. 일반 원칙은 “동등 조건 컬럼을 앞에, 범위 조건과 정렬 컬럼을 뒤에”입니다. 범위 조건 뒤에 오는 컬럼은 인덱스로 탐색 범위를 좁히는 데 쓰이지 못한다는 점도 기억할 만합니다(8.0의 인덱스 컨디션 푸시다운이 일부를 필터링해 주기는 합니다).

SELECT *가 문제인 이유도 같은 구조에서 나옵니다. 필요한 컬럼이 모두 인덱스 안에 있으면 InnoDB는 인덱스만 읽고 끝내는데(EXPLAIN의 Using index, 커버링 인덱스), *로 모든 컬럼을 요구하면 인덱스에서 찾은 PK로 클러스터 인덱스를 다시 조회해야 합니다. 결과 행이 많을수록 이 추가 조회가 랜덤 I/O로 쌓입니다.

InnoDB에서는 데이터와 1차 인덱스(클러스터 인덱스)가 함께 붙어 있다고 보면 됩니다. PK를 대충 설계하면(무작위 UUID를 그대로 넣어 페이지 단편화와 쓰기 핫스팟이 생기는 경우 등) 보조 인덱스를 거쳐 조회할 때 I/O에 발목이 잡힙니다. 버퍼 풀(innodb_buffer_pool_size)이 워킹 세트를 담지 못하면 랜덤 읽기가 폭증합니다. 리두/언두는 “왜 히스토리가 길어질수록 읽기가 무거워지는가?”에 대한 답이며, 트랜잭션이 길어지면 purge가 밀리면서 행 버전이 쌓이는 현상이 커집니다. 단순히 “쿼리가 느리다”가 아니라 동시에 돌고 있는 락·버전·복제가 얽힌 문제이므로, 격리 수준이나 SELECT FOR UPDATE의 범위 같은 부분도 함께 봐야 합니다. 기본값인 RR에서는 gap·next-key 락을 자세히 살펴볼 필요가 있습니다.

긴 트랜잭션의 영향은 숫자로 확인할 수 있습니다. SHOW ENGINE INNODB STATUS의 TRANSACTIONS 부분에 나오는 History list length는 아직 purge되지 못한 언두 기록의 양으로, 평소에는 수천 이하로 유지되다가 누군가 트랜잭션을 열어 둔 채 방치하면 계속 늘어납니다. 원인은 대개 커밋을 잊은 배치 작업, autocommit=0 상태로 방치된 관리 도구 세션, 또는 결과를 스트리밍하며 오래 읽는 리포트 쿼리입니다. information_schema.innodb_trx에서 trx_started가 오래된 트랜잭션을 찾아 종료하면 수치가 다시 줄어듭니다. 버퍼 풀 크기는 DB 전용 서버라면 물리 메모리의 절반에서 4분의 3 정도가 흔한 출발점이지만, 커넥션마다 쓰는 정렬·조인 버퍼와 OS 파일 캐시 몫을 남겨 두지 않으면 스왑이나 OOM으로 이어지므로 max_connections와 함께 계산해야 합니다.

-- 인덱스 컬럼에 함수: 자주 죽는 패턴
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- 이런 쪽이 현실적
SELECT * FROM users
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- type / key / rows / Extra 같이 봐야 함, 추정 row가 허튼짓 중이면 그게 첫 수술 대상

첫 번째 쿼리가 느린 이유는 YEAR(created_at)이 인덱스에 저장된 값이 아니라 계산 결과를 비교하기 때문입니다. 옵티마이저는 모든 행의 created_at에 함수를 적용해 봐야 하므로 인덱스를 탐색에 쓰지 못합니다. 같은 이유로 WHERE amount + 0 = 100, 문자열 컬럼을 숫자와 비교하는 WHERE phone = 01012345678(암시적 형변환), 컬레이션이 다른 컬럼끼리의 JOIN도 인덱스를 무력화합니다. 함수 조건이 꼭 필요하다면 8.0.13부터 지원하는 함수 기반 인덱스(INDEX ((YEAR(created_at))))를 만들 수 있습니다.

EXPLAIN을 읽을 때 type은 const/eq_ref/ref/range가 좋고 index(인덱스 전체 스캔)와 ALL(테이블 전체 스캔)은 경고 신호입니다. rows는 추정치라 통계가 오래되면 실제와 크게 어긋나므로, 8.0.18 이상에서는 EXPLAIN ANALYZE로 쿼리를 실제로 실행해 단계별 실제 행 수와 시간을 함께 보는 편이 정확합니다. 단, EXPLAIN ANALYZE는 쿼리를 정말 실행하므로 무거운 UPDATE나 운영 트래픽이 몰린 시간에는 조심해야 합니다.

옵티마이저는 통계(8.0의 히스토그램 포함)와 비용을 기준으로 실행 계획을 고르는데, 대량 로드 후에 ANALYZE TABLE을 하지 않고 “왜 갑자기 풀스캔이지?” 하는 경우도 흔합니다. 힌트는 재현과 측정이 끝난 뒤 “임시 조치”로만 쓰기를 권합니다. 힌트가 고정되어 있으면 버전을 올릴 때 유지보수가 괴로워집니다.

복제 이야기는 짧게 하겠습니다. ROW 포맷과 GTID를 쓰는 편이 실무에서 덜 꼬이며, 비동기 복제라면 지연은 정상이라는 전제를 머릿속에 두십시오. 쓰고 바로 읽는 흐름은 프라이머리로 보내거나, “오래된 데이터 읽기 허용”을 제품 요구사항으로 명시해야 합니다. 세미싱크는 데이터 손실을 줄이는 대신 지연이 늘 수 있고, GR/Cluster는 HA 목표에는 좋지만 운영 난이도가 상당히 올라갑니다. 이 역시 “팀이 감당할 수 있는가?”를 먼저 따져야 합니다.

복제 지연을 볼 때 SHOW REPLICA STATUS의 Seconds_Behind_Source만 믿으면 안 됩니다. 이 값은 복제본이 지금 적용 중인 이벤트의 타임스탬프와 현재 시각의 차이라서, 복제 I/O 스레드가 멈춰 새 이벤트를 받지 못하는 상황에서는 0으로 보이기도 하고, 큰 트랜잭션 하나를 적용하는 동안에는 갑자기 크게 튀었다가 사라지기도 합니다. 프라이머리에 주기적으로 타임스탬프를 쓰고 복제본에서 읽어 비교하는 하트비트 방식(pt-heartbeat 등)이 더 믿을 만합니다. 지연의 흔한 원인은 프라이머리에서 여러 스레드로 병렬 실행된 쓰기를 복제본이 적은 수의 스레드로 적용하는 것과, 수백만 행을 한 번에 바꾸는 큰 트랜잭션입니다. 대량 UPDATE·DELETE를 수천 행 단위로 나눠 커밋하는 것만으로도 복제 지연이 크게 줄어드는 경우가 많습니다.

트랜잭션 격리 수준도 한 번은 짚고 넘어가야 합니다. InnoDB의 기본값은 REPEATABLE READ인데, 이 수준에서는 범위 조건으로 잠글 때 레코드 사이의 간격까지 막는 갭 락·넥스트 키 락이 걸립니다. 그래서 WHERE created_at > ? 같은 범위 UPDATE나 SELECT ... FOR UPDATE가 동시에 몰리면, 서로 겹치는 간격을 잡으려다 데드락이 나는 일이 생각보다 잦습니다. 팬텀 읽기 방지가 꼭 필요하지 않다면 세션이나 서버를 READ COMMITTED로 낮추는 팀이 많고, 그러면 갭 락이 대부분 사라져 경합이 크게 줄어듭니다. 대신 같은 트랜잭션 안에서 같은 쿼리를 두 번 읽으면 결과가 달라질 수 있다는 점을 코드가 감당해야 합니다. 데드락이 나면 SHOW ENGINE INNODB STATUS의 LATEST DETECTED DEADLOCK부터 확인하는 것이 순서입니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;

파티셔닝을 “큰 테이블이면 나눈다”는 생각으로 접근하면 실망하기 쉽습니다. 조회 성능은 WHERE 절에 파티션 키 조건이 있어서 파티션 프루닝이 될 때만 좋아지고, 그 조건이 없으면 모든 파티션을 다 훑습니다. 효과가 확실한 곳은 오래된 로그·주문 데이터를 DELETE 대신 ALTER TABLE ... DROP PARTITION으로 순식간에 지우는 보존 정책 쪽입니다. 제약도 알아 두어야 합니다. 파티션 키는 PK를 포함한 모든 유니크 키에 들어가야 하므로 PRIMARY KEY (id)만 있는 테이블은 created_at 기준으로 나눌 수 없어 PRIMARY KEY (id, created_at)로 바꿔야 합니다. 또한 MySQL의 LIST 파티셔닝에는 다른 DB처럼 “나머지 전부”를 받는 DEFAULT 파티션이 없어서, 목록에 없는 값이 들어오면 INSERT가 실패합니다.

CREATE TABLE orders (
  id BIGINT NOT NULL,
  created_at DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, created_at)          -- 파티션 키를 PK에 포함
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
  PARTITION p2026 VALUES LESS THAN ('2027-01-01'),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE orders DROP PARTITION p2025;  -- 오래된 데이터 즉시 제거

검색 기능을 LIKE '%키워드%'로 버티다가 FULLTEXT 인덱스로 넘어가는 경우도 흔한데, 기본 파서는 공백 기준으로 단어를 자르기 때문에 한국어에는 거의 맞지 않습니다. 한국어라면 WITH PARSER ngram으로 인덱스를 만들어야 하고(ngram_token_size 기본값 2), 그래도 검색 품질이 중요한 서비스라면 별도 검색 엔진으로 분리하는 편이 결국 편합니다.

CREATE FULLTEXT INDEX ft_title ON articles(title, content) WITH PARSER ngram;
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('인덱스 설계' IN BOOLEAN MODE);

프로덕션 운영은 지루하게 말하면 커넥션 풀, 백업, 슬로 로그, 복제 지연 네 갈래가 기본입니다. 요청마다 커넥션을 새로 열면 금방 한계에 부딪히므로, 애플리케이션 풀과 (환경에 따라) ProxySQL·RDS Proxy 같은 도구를 씁니다. 백업은 mysqldump든 XtraBackup이든 복원 연습을 하지 않았다면 “백업이 있다”가 아니라 “디스크에 파일이 있다”에 가깝습니다. 8.0 이상이라면 쿼리 캐시는 제거되었으니 잊으셔도 됩니다.

mysqldump를 쓴다면 --single-transaction 옵션이 사실상 필수입니다. 이 옵션은 덤프 전체를 하나의 일관된 스냅샷(REPEATABLE READ 트랜잭션)으로 읽어 InnoDB 테이블을 잠그지 않고도 특정 시점의 데이터를 얻게 해 줍니다. 빠뜨리면 테이블마다 다른 시점의 데이터가 섞여 외래 키 관계가 깨진 백업이 만들어질 수 있습니다. 다만 이 스냅샷 트랜잭션도 긴 트랜잭션이라 덤프가 도는 동안 History list length가 늘어난다는 점, 그리고 수백 GB 이상에서는 복원에 몇 시간이 걸린다는 점 때문에 큰 DB는 물리 백업(XtraBackup, MySQL Shell의 dump 유틸리티)과 바이너리 로그를 조합한 특정 시점 복구 체계로 가는 것이 일반적입니다. “복원에 걸리는 시간”이 곧 장애 시 서비스가 멈추는 시간이므로, 복원 연습에서는 성공 여부와 함께 걸린 시간을 기록해 두는 것이 좋습니다.

아래 설정의 long_query_time = 0.5도 한 번 생각해 볼 값입니다. 0.5초 이상만 기록하면 한 번에 느린 쿼리는 잡히지만, 10ms짜리 쿼리가 초당 수천 번 실행되어 DB를 가장 많이 점유하는 경우는 보이지 않습니다. 실제 부하의 주범을 찾으려면 performance_schema.events_statements_summary_by_digest로 쿼리 패턴별 누적 실행 시간을 보는 편이 효과적입니다.

[mysqld]
innodb_buffer_pool_size = 8G
max_connections = 500
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

정리하자면, 저는 InnoDB가 아니라면 그 이유부터 확인하고, 인덱스는 쿼리·정렬·쓰기 세 가지를 모두 보며, 복제는 지연을 제품 요구사항에 반영하는지부터 정합니다. 목차를 빈틈없이 채우는 문서를 쓰려는 것은 아니며, 느릴 때는 EXPLAIN·슬로 로그·버퍼 히트율·락·복제 lag를 한꺼번에 의심해 보면 절반은 해결됩니다. 실행 계획을 더 깊이 읽는 방법은 MySQL EXPLAIN과 쿼리 최적화 글에서 이어서 다룹니다.

같이 보면 좋은 글