MySQL·InnoDB 심화 — 버퍼풀·redo/undo·8.4 LTS·MariaDB

세상에서 가장 많이 쓰이는 오픈소스 DB는 MySQL이고, 그 심장은 InnoDB 스토리지 엔진이다. InnoDB는 트랜잭션·행 락·MVCC·크래시 복구를 책임진다(옛 MyISAM은 트랜잭션·행 락이 없어 사실상 퇴역). 이 장은 InnoDB가 메모리·디스크를 어떻게 쓰는지와 MySQL 8.4 LTS·MariaDB의 갈림길을 다룬다. 기준은 MySQL 8.4 LTS(2024-04 출시). (출처: MySQL 8.4 — InnoDB Architecture / What Is New in MySQL 8.4 since 8.0.)

InnoDB 아키텍처 — 메모리와 디스크의 분업

다이어그램 로딩 중…

버퍼 풀(buffer pool) — InnoDB 성능의 가장 큰 손잡이. 데이터·인덱스 페이지를 메모리에 캐시(변형 LRU — 자주 쓰는 페이지를 young, 한 번 훑는 풀스캔 페이지를 old 영역에 둬 캐시 오염 방지). innodb_buffer_pool_size를 *서버 메모리의 50~75%*로. 읽기·쓰기 대부분이 메모리에서 끝나야 빠르다.
redo log — WAL. 커밋 시 변경을 redo log에 먼저 기록(순차 쓰기)하고 데이터 페이지는 나중에 천천히(체크포인트) 내린다 → 내구성 보장 + 랜덤 쓰기 감소. innodb_flush_log_at_trx_commit이 내구성↔성능을 조절(1=완전 내구성).
undo log — 옛 버전을 보관해 MVCC 읽기·롤백에 쓴다([05]). purge 스레드가 정리.
doublewrite buffer — 페이지를 두 번 쓴다(버퍼→실제). 쓰는 도중 크래시로 페이지가 반쯤 망가지는(torn page) 사고를 복구.
change buffer — 비유니크 보조 인덱스 변경을, 해당 페이지가 버퍼 풀에 없을 때 모았다가 나중에 병합(랜덤 I/O 절감).
adaptive hash index — 자주 찾는 B+Tree 경로를 해시로 단축(자동).

클러스터형 인덱스 — PK가 데이터다

[06]에서 봤듯 InnoDB는 *테이블 자체가 PK 기준 B+Tree(클러스터형)*다. 보조 인덱스 조회는 2단계가 된다.

다이어그램 로딩 중…

그래서 PK 설계가 성능을 좌우한다.

보조 인덱스 리프 = PK 값 → PK가 크면 모든 보조 인덱스가 비대.
PK는 작고 단조 증가(AUTO_INCREMENT BIGINT)가 유리. 랜덤 UUIDv4 PK는 트리 곳곳 삽입 → 페이지 분할·버퍼 풀 효율 저하(대안: 시간순 UUIDv7).
PK를 명시 안 하면 유니크 NOT NULL 키를, 그도 없으면 내부 6바이트 row id를 클러스터 키로.

UPSERT와 MySQL 고유 SQL

-- UPSERT: 충돌 시 갱신 (MySQL 식)
INSERT INTO inventory (product_id, qty) VALUES (42, 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

INSERT IGNORE INTO ...     -- 충돌·에러를 무시하고 건너뜀
REPLACE INTO ...           -- 충돌 시 DELETE 후 INSERT (FK·트리거·AUTO_INC 주의)

-- JSON: MySQL의 JSON은 이미 이진 형식. 특정 경로는 생성 칼럼+인덱스로 색인
ALTER TABLE event
  ADD COLUMN level VARCHAR(10) GENERATED ALWAYS AS (data->>'$.level') STORED,
  ADD INDEX idx_level (level);
SELECT * FROM event WHERE level = 'error';  -- 생성 칼럼 인덱스 사용

AUTO_INCREMENT — 자동 증가. innodb_autoinc_lock_mode(0/1/2)로 동시 INSERT 시 채번 락 강도 조절(2=interleaved, 최고 동시성).
CTE·윈도우 함수(8.0+ — 옛 MySQL엔 없었다).
ALGORITHM=INSTANT DDL — 메타데이터만 바꿔 큰 테이블 칼럼 추가를 즉시(테이블 락 없이).

ALTER TABLE big_table ADD COLUMN note VARCHAR(100), ALGORITHM=INSTANT;

MySQL 8.4 LTS — 무엇이 바뀌었나

MySQL은 Innovation(짧은 주기) + LTS(8.4처럼 장기 지원) 트랙으로 나뉜다. 8.4 LTS의 주요 변화:

InnoDB 기본값 대거 변경 — innodb_buffer_pool_instances·innodb_change_buffering·innodb_doublewrite_files·innodb_io_capacity·innodb_log_buffer_size·innodb_adaptive_hash_index 등 다수 기본값을 현대 하드웨어에 맞게 조정.
--innodb/--skip-innodb 제거 — InnoDB는 항상 켜짐(끌 수 없음).
인증 — caching_sha2_password가 기본, 옛 mysql_native_password는 기본 비활성(필요 시 명시 설치). (옛 쿼리 캐시는 이미 8.0에서 제거.)

그래서 8.0 기준 운영 설정을 8.4에 그대로 옮기면 기본값 차이로 동작이 달라질 수 있다 — 업그레이드 시 기본값 변경표 확인이 필수. (출처: MySQL 8.4 — InnoDB Default Changes.)

MariaDB — 갈라진 사촌

MariaDB는 MySQL 5.x에서 포크돼 점점 독자 노선을 간다 — 초기엔 드롭인 호환이었지만 버전이 갈수록 차이가 커진다.

MariaDB 고유: 시스템 버전 관리 테이블(temporal/AS OF), 시퀀스 객체, RETURNING 절, ColumnStore·Aria·Spider 등 엔진, Galera 동기 클러스터 내장.
옵티마이저·JSON(MariaDB는 내부적으로 LONGTEXT 기반)·일부 함수가 MySQL과 미묘하게 다르다.

실무 함의 — "MySQL과 MariaDB는 호환된다"는 점점 약해지는 가정이다. 드라이버·SQL·복제 구성을 대상 엔진에 맞춰 검증해야 한다.

정리 — 버퍼 풀을 키우고, 로그를 이해하고, PK를 작게

InnoDB 성능 사고의 8할은 버퍼 풀(메모리 적중률)·인덱스·PK 설계·락에서 갈린다. *redo(내구성)·undo(MVCC)·doublewrite(torn page)·change buffer(랜덤 I/O 절감)*가 어떻게 협력하는지 알면 왜 커밋이 빠른데도 안전한지가 보인다. 8.4 LTS는 기본값을 현대화했고, MariaDB는 별도 엔진처럼 다뤄야 한다.

한 줄 요약 — InnoDB = 버퍼 풀 + redo(WAL) + undo(MVCC) + doublewrite + change buffer. 테이블은 PK 클러스터형이라 보조 인덱스는 2단계 조회, 작고 단조증가하는 PK가 유리. UPSERT는 ON DUPLICATE KEY UPDATE, JSON은 생성 칼럼+인덱스, 큰 DDL은 ALGORITHM=INSTANT. 8.4 LTS는 기본값·인증 변경, MariaDB는 독자 분기.

(출처: MySQL 8.4 — InnoDB Architecture / Storage Engine·InnoDB Buffer Pool·MySQL Nutshell 8.4 / MariaDB Documentation.)