taeknology/notes

[MySQL] InnoDB 스토리지 엔진: 기본 엔진의 ACID·MVCC·clustered index 정리

한 줄 요약: InnoDB 는 MySQL 9.7 에서도 default 스토리지 엔진 임 (SHOW ENGINES 의 Support 컬럼이 DEFAULT). DML 이 ACID 모델을 따라 commit/rollback/crash recovery 가 갖춰져 있고, row-level locking + MVCC 로 동시성을 떠받치며, 모든 테이블이 PK 기준 clustered index 로 디스크에 떨어짐. 외래 키·체크섬·buffer pool 캐싱까지 한 엔진이 묶어 제공해서 다른 엔진은 특수 목적이 아닌 한 사용 이유가 없음.

개요

InnoDB 는 MySQL 5.5 부터 default 엔진이며, 8.0 부터는 데이터 딕셔너리·시스템 테이블까지 InnoDB 위에 얹혀 있음. 즉 server layer 와 storage engine 의 경계인 handler API 아래쪽이 사실상 InnoDB 만 가정하고 동작함 (mysql-architecture-overview 참조). 다른 엔진은 같은 handler API 뒤에 끼울 수 있지만, 운영 환경에서 일반 OLTP 테이블에 InnoDB 외 엔진을 쓰는 경우는 거의 없음.

이 글은 공식 매뉴얼의 Introduction to InnoDB 페이지를 기준으로 InnoDB 가 묶어 제공하는 핵심 기능 — ACID, row-level lock + MVCC, clustered index, 외래 키, crash recovery, buffer pool — 을 한 페이지로 정리하고, MySQL 9.7.0 컨테이너에서 동작을 직접 확인함.

항목InnoDB
9.7 default 여부YES (SHOW ENGINES Support = DEFAULT)
트랜잭션 / XA / 세이브포인트YES / YES / YES
잠금 단위row
MVCCYES (undo log 기반, 일관된 스냅샷 읽기)
인덱스 구조B+Tree clustered index (PK 가 곧 디스크 물리 배치)
외래 키YES (InnoDB 만 지원)
Full-text 인덱스YES (5.6 부터)
데이터 캐시YES — buffer pool
Storage 상한64 TB (테이블스페이스 기준)
데이터 파일*.ibd (file-per-table) · mysql.ibd · ibdata1 · #innodb_redo/

결론

왜 InnoDB 가 default 가 되었나

질문답변
왜 ACID 가 기본 요건인가OLTP 운영의 거의 모든 시나리오 — 결제·예약·재고 — 가 commit / rollback / crash 후 일관성 복구를 요구함. 트랜잭션 없는 엔진은 OLTP 에서 못 씀.
왜 row-level lock 인가동시 사용자가 같은 테이블의 서로 다른 row 를 동시에 수정하는 워크로드에서 table-level lock 은 직렬화돼 throughput 이 무너짐.
왜 PK 가 곧 데이터 배치 인가clustered index 는 PK 조회·범위 스캔의 I/O 를 최소화함. 보조 인덱스 leaf 가 PK 를 들고 있어 PK 설계가 인덱스 비용 전체를 좌우함.
왜 8.0 부터 시스템 테이블도 InnoDB 인가데이터 딕셔너리를 트랜잭셔널 테이블로 옮겨 DDL 이 atomic 해짐. ALTER 도중 크래시가 나도 메타·파일이 어긋나지 않음.

운영 시 기억할 것

  • CREATE TABLE 에 ENGINE= 절을 빼면 자동으로 InnoDB 가 됨. default_storage_engine 시스템 변수가 InnoDB 임.
  • PK 설계가 곧 storage 설계 임. clustered index 라 PK 가 단조 증가가 아니거나 너무 길면 페이지 분할·인덱스 비대화로 비용이 큼. PK 를 명시하지 않으면 InnoDB 가 GIPK 로 my_row_id 를 자동 추가함 (generated-invisible-primary-keys 참조).
  • buffer pool 이 처리량을 결정 함. 디스크 I/O 가 메모리 접근보다 훨씬 비싸므로 hot 데이터·인덱스가 메모리에 머무는 비율이 곧 응답 시간임. 산정·설정 절차는 innodb-buffer-pool-size-config 참조.
  • 외래 키는 InnoDB 만 강제 함. 같은 DDL 을 MyISAM 에 넣으면 문법은 통과되나 제약은 적용되지 않음 — 엔진 선택 자체가 데이터 무결성 정책을 정함.
  • crash recovery 는 자동임. 비정상 종료 후 재기동 시 redo / undo 가 commit 된 변경을 finalize 하고 진행 중이던 트랜잭션은 되돌림. 운영자가 따로 호출할 명령은 없음.

상세

1. ACID — commit · rollback · crash recovery

InnoDB 의 DML 은 ACID 모델을 따름. 정상 경로에서는 commit / rollback 으로 원자성·일관성을 보장하고, 비정상 종료 후에는 redo / undo 로 같은 보장을 복구함.

  • redo log — 내구성. 커밋 시 redo 만 디스크에 있으면 buffer pool 에 변경이 남아 있어도 크래시 후 복구 가능. 로그 용량은 innodb_redo_log_capacity (8.0.30+) 로 관리하고, 9.7 기본값은 104857600 바이트 (= 100 MiB) 임.
  • undo log — 원자성 + MVCC 의 양면. 롤백용 이전 이미지를 보관하면서, 다른 트랜잭션의 시점 일관 읽기에 그 이전 이미지를 재료로 줌.

MySQL 9.7.0 에서 직접 확인 — START TRANSACTION; INSERT...; ROLLBACK; 후 SELECT 결과에서 INSERT 된 row 가 보이지 않음.

2. Row-level lock + MVCC — 동시성 모델

table-level lock 은 같은 테이블의 서로 다른 row 를 동시에 만지는 워크로드를 직렬화시킴. InnoDB 는 lock 단위를 row 까지 내려서 같은 테이블이라도 다른 행이면 서로 막히지 않게 함. 동시에 MVCC 로 SELECT 와 UPDATE 가 서로를 잠그지 않음.

graph LR
    T1[Transaction 1<br/>UPDATE row A]
    T2[Transaction 2<br/>UPDATE row B]
    T3[Transaction 3<br/>SELECT row A]

    T1 -. row-lock A .-> RowA[(row A)]
    T2 -. row-lock B .-> RowB[(row B)]
    T3 -- "스냅샷 read<br/>(undo log)" --> RowA

UPDATE 가 잡고 있는 row 라도 다른 트랜잭션의 SELECT 는 잠금 없이 일관된 스냅샷 을 봄 — undo log 가 변경 직전 row 이미지를 들고 있어 시점에 맞는 버전을 재구성해 주기 때문임. 이게 MVCC 의 본질임.

3. Clustered index — PK 가 곧 디스크 물리 배치

InnoDB 테이블은 PK 기준 B+Tree clustered index 한 그루 안에 데이터가 들어가 있음. PK 가 디스크 row 위치를 결정하므로 PK 조회·범위 스캔이 가장 빠르고, 보조 인덱스의 leaf 에는 PK 값이 들어감 — 즉 보조 인덱스로 진입한 뒤 데이터를 가져오려면 PK 로 한 번 더 lookup 이 일어남.

인덱스 타입leaf 에 들어가는 것I/O 특성
Clustered (PK)행 데이터 전체PK 조회·범위 스캔에 가장 유리
Secondary index인덱스 컬럼 + PK 값데이터 접근에 PK lookup 한 번 더 필요

PK 설계가 단조 증가가 아니면 (UUID 같은 랜덤 값) 페이지 분할이 잦아지고 인덱스가 비대해짐. 운영 환경에서 자주 권장되는 패턴이 BIGINT AUTO_INCREMENT PK 인 이유가 이것임.

4. 외래 키 — InnoDB 만 강제

FOREIGN KEY 제약은 InnoDB 에서만 실제로 강제 됨. 같은 DDL 을 다른 엔진에 넣으면 파서는 통과시키지만 제약은 사라지거나 주석처럼 무시됨. 따라서 엔진 선택 자체가 데이터 무결성 정책을 결정함.

MySQL 9.7.0 에서 직접 확인 — InnoDB 두 테이블 사이 FK 위반 INSERT 는 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ... 로 거부됨.

5. Buffer pool — InnoDB 의 메인 메모리 캐시

server layer 의 query cache 는 8.0 에서 제거됐고, 현재 MySQL 의 데이터 캐싱은 InnoDB buffer pool 한 곳 에 집중돼 있음. 페이지 단위로 데이터·인덱스를 메모리에 들고 있고, LRU 변형 알고리즘으로 hot 페이지를 우선 유지함. 매뉴얼은 “On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool” 을 기준으로 권장함.

산정·정합 규칙·운영 SQL 은 innodb-buffer-pool-size-config 에서 자세히 다룸.

6. Crash recovery — 자동·무인

InnoDB 는 비정상 종료 시 자동으로 복구함. 매뉴얼 설명을 그대로 옮기면 — 하드웨어/소프트웨어 문제로 서버가 종료되더라도 재기동 시 InnoDB crash recovery 가 commit 된 변경을 finalize 하고 진행 중이던 트랜잭션은 되돌림. 운영자가 별도로 호출할 명령은 없음.

같은 동작이 MyISAM 에는 없음 — MyISAM 은 비정상 종료 시 인덱스 파일이 깨질 수 있고, myisam_recover_options 를 켜야 기동 시 자동 점검·복구가 시도됨.

테스트

테스트 환경: MySQL 9.7.0 (mysql:9.7 이미지). 9.7 은 2026-04-21 출시된 현재 LTS 라인이며, 8.4 LTS 와 병행 지원됨.

1. default 엔진 확인

SELECT VERSION();
SELECT @@default_storage_engine, @@default_tmp_storage_engine;
SHOW ENGINES;
VERSION()
9.7.0
 
@@default_storage_engine    @@default_tmp_storage_engine
InnoDB                       InnoDB
 
Engine              Support     Comment
ndbcluster          NO          Clustered, fault-tolerant tables
MEMORY              YES         Hash based, stored in memory, useful for temporary tables
InnoDB              DEFAULT     Supports transactions, row-level locking, and foreign keys
PERFORMANCE_SCHEMA  YES         Performance Schema
MyISAM              YES         MyISAM storage engine
FEDERATED           NO          Federated MySQL storage engine
ndbinfo             NO          MySQL Cluster system information storage engine
MRG_MYISAM          YES         Collection of identical MyISAM tables
BLACKHOLE           YES         /dev/null storage engine (anything you write to it disappears)
CSV                 YES         CSV storage engine
ARCHIVE             YES         Archive storage engine

InnoDB 행의 Support 컬럼이 DEFAULT 로 나옴 — ENGINE= 절을 생략한 CREATE TABLE 은 모두 InnoDB 가 됨. 임시 테이블의 default 도 InnoDB 임.

2. ACID — ROLLBACK 이 INSERT 를 되돌리는지

CREATE TABLE innodb_demo (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO innodb_demo VALUES (1, 'a'), (2, 'b');
 
START TRANSACTION;
INSERT INTO innodb_demo VALUES (3, 'c');
ROLLBACK;
 
SELECT * FROM innodb_demo;
id  name
1   a
2   b

id=3 row 가 commit 되지 않은 상태에서 ROLLBACK 으로 사라짐. 같은 시나리오를 MyISAM 에 넣으면 row 가 그대로 남음 (myisam-storage-engine-overview 의 동일 시나리오 참조).

3. 외래 키 — 위반 INSERT 거부

CREATE TABLE fk_parent (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE fk_child  (id INT PRIMARY KEY, p_id INT,
  FOREIGN KEY (p_id) REFERENCES fk_parent(id)) ENGINE=InnoDB;
 
INSERT INTO fk_parent VALUES (1);
INSERT INTO fk_child  VALUES (10, 1);   -- OK
INSERT INTO fk_child  VALUES (11, 99);  -- 부모에 99 없음
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(`testdb`.`fk_child`, CONSTRAINT `fk_child_ibfk_1`
 FOREIGN KEY (`p_id`) REFERENCES `fk_parent` (`id`))

SHOW CREATE TABLE fk_child 결과에도 CONSTRAINT ... FOREIGN KEY ... REFERENCES ... 가 그대로 남아 있음 — InnoDB 는 FK 정의를 보존하고 강제함.

4. 디스크 파일 — file-per-table

innodb_file_per_table 기본값(ON) 에서는 테이블마다 .ibd 파일이 한 개씩 생김.

ls -la /var/lib/mysql/testdb/
-rw-r----- 1 mysql mysql 114688 May  5 14:58 innodb_demo.ibd

mysql.ibd (데이터 딕셔너리 + 시스템 테이블), ibdata1 (시스템 테이블스페이스 — undo · doublewrite buffer 등), #innodb_redo/ 디렉토리(redo log 그룹) 가 별도로 존재함. MyISAM 의 .MYD / .MYI 분리 구조와 비교되는 점.

5. clustered index — PK 가 정렬 순서 결정

CREATE TABLE clustered_demo (id INT PRIMARY KEY, v INT);
INSERT INTO clustered_demo VALUES (3, 30), (1, 10), (5, 50), (2, 20), (4, 40);
SELECT * FROM clustered_demo;  -- ORDER BY 없이도 PK 순으로 보임
id  v
1   10
2   20
3   30
4   40
5   50

별도 정렬 없이도 PK 오름차순으로 row 가 반환됨 — 디스크에 PK 순서로 저장돼 있어 풀 스캔이 PK 정렬 결과를 그대로 줌. 보조 인덱스의 leaf 에는 PK 값이 들어가는 점도 같은 구조의 결과임.

참고

관련 글