[DB] MySQL / InnoDB: clustered index, redo log, MVCC

MySQL, InnoDB, clustered index, secondary index, redo log, undo log, buffer pool, binlog, doublewrite buffer, adaptive hash index

정의

MySQL + InnoDB (기본 스토리지 엔진) 의 clustered index + redo log 기반 행 기반 RDBMS. 웹 / SaaS 의 가장 흔한 backbone.

아키텍처

flowchart TB
    Client --> Server["MySQL Server<br/>(parser, optimizer, query cache)"]
    Server --> InnoDB
    subgraph InnoDB["InnoDB Storage Engine"]
        direction LR
        BP["Buffer Pool<br/>(메모리 캐시)"]
        AHI["Adaptive Hash Index"]
        CL["Change Buffer"]
        RedoLog["Redo Log<br/>(WAL)"]
        UndoLog["Undo Log<br/>(MVCC)"]
        DWB["Doublewrite Buffer"]
        DataFiles[("ibd 데이터 파일")]
        BP --> DWB --> DataFiles
    end
    Server -->|writes| Binlog[("Binlog<br/>replication")]

Clustered Index (vs PostgreSQL)

	PostgreSQL	MySQL InnoDB
Primary 데이터 저장	heap + 별도 인덱스	primary key 의 B-tree leaf
Secondary index → 데이터	tid (heap 위치)	primary key 값
Secondary lookup	1 hop	2 hop (인덱스 → PK → 데이터)
PK 영향	작음	큼 (변경 시 secondary index 도 갱신)

flowchart TB
    subgraph InnoDB
        PK["Primary Key B-tree<br/>leaf = 실제 row 데이터"]
        Sec["Secondary Index B-tree<br/>leaf = PK 값"]
        Sec -->|"PK lookup (2 hop)"| PK
    end

Redo Log + Undo Log

sequenceDiagram
    Client->>InnoDB: UPDATE ...
    InnoDB->>UndoLog: 옛 값 보관 (MVCC + rollback)
    InnoDB->>BufferPool: page dirty
    InnoDB->>RedoLog: 변경 기록 (WAL)
    InnoDB-->>Client: COMMIT (redo fsync 후)
    Note over InnoDB: Checkpoint 시점에 page → disk

로그	역할
Redo Log	crash recovery, WAL
Undo Log	MVCC + rollback
Binlog	replication, point-in-time recovery

MySQL 8.0.30+ Redo Log 개선

• Redo log 는 순환 파일 (기본 2개) → 동적 크기 로 변경.
• innodb_redo_log_capacity 단일 파라미터로 제어 (옛 innodb_log_file_size × innodb_log_files_in_group).

Doublewrite Buffer

페이지 손상 (partial write) 방지:

sequenceDiagram
    InnoDB->>DWB: dirty page 먼저 doublewrite buffer 에 기록
    DWB->>SystemTablespace: flush
    InnoDB->>DataFile: 실제 데이터 파일에 쓰기
    Note over InnoDB: crash 시 DWB 로 복구 가능

• OS 가 4KB 단위로 쓰지만 InnoDB page 는 16KB → partial write 발생 가능.
• Doublewrite buffer 는 atomic write 보장 SSD (FusionIO 등) 에서 비활성화 가능.

-- 8.0.20+ 별도 파일로 분리됨
SHOW VARIABLES LIKE 'innodb_doublewrite%';

Adaptive Hash Index (AHI)

flowchart LR
    Query["반복 동일 조건 쿼리"] --> AHI["Adaptive Hash Index<br/>(메모리 해시)"]
    AHI -->|"hit (O(1))"| Row["Row (Buffer Pool)"]
    AHI -.->|"miss"| BTree["B-tree 탐색 (O(log N))"]

• 자동으로 핫 B-tree 패턴을 해시 테이블 로 캐시.
• 동일 쿼리 패턴 반복 시 B-tree depth 생략.
• write-heavy 환경에서 오히려 경합 → 비활성화 검토.

SHOW ENGINE INNODB STATUS\G  -- AHI 사용률 확인
SET GLOBAL innodb_adaptive_hash_index = OFF;  -- 비활성화

Change Buffer (Insert Buffer)

보조 인덱스 페이지 가 메모리에 없을 때 나중에 일괄 적용:

flowchart LR
    Insert["INSERT / UPDATE"] --> Check{보조 인덱스 페이지<br/>Buffer Pool에?}
    Check -->|있음| Direct["즉시 적용"]
    Check -->|없음| CB["Change Buffer<br/>(시스템 테이블스페이스)"]
    CB -.->|"페이지 로드 시 merge"| Direct

• 대량 INSERT 시 random I/O 감소 효과.
• innodb_change_buffer_max_size (기본 25% of buffer pool).

Isolation Level (InnoDB 기본)

Level	InnoDB 기본	의미
READ UNCOMMITTED		dirty read
READ COMMITTED		다른 트랜잭션의 commit 된 것만
REPEATABLE READ	기본	같은 쿼리는 같은 결과 (snapshot)
SERIALIZABLE		완전 격리

InnoDB 의 REPEATABLE READ 는 next-key lock 으로 phantom read 방지. PostgreSQL 의 Read Committed 기본과 기본값이 다름.

자세한 건 transaction-isolation-levels.

Buffer Pool

flowchart LR
    Disk[("ibd files")] --> BP["Buffer Pool<br/>(innodb_buffer_pool_size)"]
    BP -->|hit| Client
    BP -.->|"LRU eviction"| Disk
    BP --> Young["Young 리스트<br/>(최근 접근)"]
    BP --> Old["Old 리스트<br/>(eviction 후보)"]

• 메모리에 page 단위 (16KB) 캐시.
• innodb_buffer_pool_size = 총 RAM 의 70-80% 가 운영 표준.
• Hit ratio 확인: Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads.
• 대용량 서버: innodb_buffer_pool_instances = 8 로 경합 분산.

-- 버퍼 풀 히트율 확인
SELECT FORMAT((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2)
  AS hit_ratio
FROM (
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r, (
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

Lock

Lock	의미
Shared (S)	다른 S 와 호환, X 와 충돌
Exclusive (X)	단독
Intention (IS, IX)	table-level 의도 표시
Record lock	row 단일
Gap lock	row 사이 간격
Next-key lock	record + gap (phantom 방지)
Auto-inc lock	AUTO_INCREMENT 동기

Replication

flowchart LR
    Primary --> Binlog[("binlog")]
    Binlog --> R1["Replica 1"]
    Binlog --> R2["Replica 2"]
    Binlog --> R3["Replica 3"]

• Async (기본): primary 가 즉시 OK
• Semi-sync: 적어도 1개 replica 가 receive 후 OK
• Group Replication: 다수 노드 합의

MySQL vs PostgreSQL

항목	MySQL	PostgreSQL
스토리지 엔진	InnoDB (+ MyISAM legacy)	단일
인덱스	clustered (PK = data)	heap + index
확장성 (extension)	적음	매우 많음
JSON 인덱싱	OK	JSONB + GIN 우수
복제	binlog 기반	streaming WAL
트랜잭션 DDL	8.0+ 부분	대부분 트랜잭션
운영 도구	percona, mysql workbench	psql + 풍부한 ecosystem
라이센스	GPL (commercial 도)	PostgreSQL License

흔한 함정

관련 위키

• postgresql (비교)
• mvcc
• btree-indexing
• transaction-isolation-levels
• wal-write-ahead-log
• query-explain-plan