#5. 인덱스는 언제 반영될까: 즉시 vs 지연

# 인덱스는 언제 반영될까: 즉시 vs 지연 > 📘 **학습자료 5편** | 연결 퀴즈: [**인덱스 기초2 풀러가기**](quiz.html?set=B) > INSERT/DELETE 한 번에도 인덱스마다 동작이 다릅니다. > 즉시 반영되는 인덱스, 지연 반영되는 인덱스, 그리고 삭제조차 "논리적으로만" 일어나는 보조 인덱스 — 그 차이의 이유를 깊이 있게 다룹니다. > 📚 **이전 편**: [4편 - 자동으로 생기는 인덱스들] > 4편에서 인덱스가 어떻게 생성되는지를 봤다면, 이번 편에서는 인덱스가 어떻게 **변경되고 삭제되는지**를 다룹니다. ## 이 자료를 다 읽으면 알게 되는 것 - **PK 인덱스**가 왜 즉시 반영되어야 하는지 - **유니크 보조 인덱스**가 왜 즉시 반영되어야 하는지 - **일반 보조 인덱스**는 왜 지연 반영되어도 괜찮은지 - InnoDB의 핵심 최적화 기법 **Change Buffer**의 동작 원리 (버퍼풀, dirty page, 병합 시점) - 보조 인덱스 삭제가 왜 "논리적 삭제"로 처리되는지 (MVCC) --- ## 📑 목차 - [1. 한 눈에 보기: 인덱스별 반영 시점](#1-한-눈에-보기-인덱스별-반영-시점) - [2. PK 인덱스: 즉시 반영해야 하는 이유](#2-pk-인덱스-즉시-반영해야-하는-이유) - [3. 유니크 보조 인덱스: 무결성을 위한 즉시 반영](#3-유니크-보조-인덱스-무결성을-위한-즉시-반영) - [⭐ 4. 일반 보조 인덱스: Change Buffer로 지연 반영](#4-일반-보조-인덱스-change-buffer로-지연-반영) - [5. 삭제도 즉시 일어나지 않는다: MVCC와 논리적 삭제](#5-삭제도-즉시-일어나지-않는다-mvcc와-논리적-삭제) - [핵심 요약](#핵심-요약) - [이제 퀴즈에 도전하기](#이제-퀴즈에-도전하기) --- ## 1. 한 눈에 보기: 인덱스별 반영 시점 본격적으로 들어가기 전에, 이번 편의 결론을 먼저 보고 시작합시다. | 인덱스 종류 | 삽입/수정 시 | 삭제 시 | |---|---|---| | **PK (클러스터링)** | 즉시 반영 | 논리적 삭제 → Purge | | **유니크 보조 인덱스** | 즉시 반영 | 논리적 삭제 → Purge | | **일반 보조 인덱스** | **지연 반영 (Change Buffer)** | 논리적 삭제 → Purge | 같은 `INSERT` 한 줄에도, 인덱스마다 처리되는 방식이 다릅니다. 왜 그럴까요? 하나씩 살펴봅시다. --- ## 2. PK 인덱스: 즉시 반영해야 하는 이유 3편에서 PK는 **클러스터링 인덱스**라고 배웠습니다. 클러스터링 인덱스의 리프 노드는 **실제 데이터 레코드 그 자체**가 들어있는 곳입니다. ```sql INSERT INTO products (id, name, price) VALUES (1001, '당근', 3000); ``` 이 한 줄이 실행될 때 PK 인덱스에는 즉시 다음이 일어납니다: 1. InnoDB가 PK B+Tree의 루트 페이지로 진입 2. 가이드를 따라 `id = 1001`이 들어갈 리프 페이지를 찾음 3. 그 자리에 새 레코드 `(1001, '당근', 3000)`를 **즉시 삽입** **왜 즉시 반영해야 할까요?** 두 가지 이유가 있습니다. ### 이유 1: 클러스터링 인덱스 = 실제 데이터 저장소 PK 인덱스의 리프 노드 자체가 데이터의 물리적 저장 공간입니다. **PK 삽입을 지연시키는 것 = 데이터 저장을 지연시키는 것**과 같은 의미가 됩니다. 데이터를 어디에 저장할지조차 결정이 미뤄지면 다른 모든 동작도 막혀버립니다. ### 이유 2: PK는 UNIQUE + NOT NULL 제약을 가진다 PK는 중복이 허용되지 않습니다. 새 행이 들어올 때마다 **이 PK 값이 이미 존재하는지** 검사해야 합니다. ```sql INSERT INTO pk_test VALUES (1, 'apple'); -- 성공 INSERT INTO pk_test VALUES (1, 'banana'); -- 실패: Duplicate entry '1' for key 'PRIMARY' ``` 이 동작의 내부를 들여다보면: 1. `(1, 'banana')` 삽입 시도 → InnoDB가 PK B+Tree 루트 페이지를 탐색 2. B+Tree 경로를 따라가며 키 = 1이 이미 있는지 확인 - 존재함 → **즉시 에러 반환** (`Duplicate entry`) - 없음 → 적절한 리프 페이지에 삽입 **중복 검사를 하려면 인덱스가 즉시 반영되어 있어야 합니다.** 미뤄두면 이미 들어간 중복 값을 검사할 방법이 없으니까요. --- ## 3. 유니크 보조 인덱스: 무결성을 위한 즉시 반영 `UNIQUE` 제약이 걸린 보조 인덱스도 PK와 같은 이유로 **즉시 반영**됩니다. ```sql CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255) UNIQUE ); INSERT INTO users VALUES (1, 'a@test.com'); INSERT INTO users VALUES (2, 'a@test.com'); -- 실패: 중복 검사 통과 못 함 ``` `email`은 PK가 아닌 **보조 인덱스**입니다. 그래도 UNIQUE 제약이 걸려 있으니 **새 값이 들어올 때마다 중복 여부를 즉시 확인**해야 합니다. 지연 반영하면 두 번째 INSERT가 일단 성공하고 나서야 중복임을 발견하게 되니, 데이터 무결성이 깨집니다. 이는 MariaDB 공식 문서에도 명시되어 있습니다. > **Inserts to UNIQUE secondary indexes cannot be buffered** > (UNIQUE 보조 인덱스의 삽입은 버퍼링될 수 없다) 여기서 "버퍼링"이라는 단어가 나오는데, 이게 다음 섹션의 핵심 개념입니다. > 💡 여기까지 읽었다면 즉시 반영의 이유를 충분히 이해한 것입니다. [**퀴즈로 확인해보세요!**](quiz.html?set=B) --- ## 4. 일반 보조 인덱스: Change Buffer로 지연 반영 > ⭐ **이 자료에서 가장 중요한 섹션입니다.** 인덱스 동작을 이해하는 핵심 개념입니다. 이번엔 **유니크 제약이 없는 일반 보조 인덱스**입니다. ```sql CREATE INDEX idx_name ON products(name); -- 일반 보조 인덱스 ``` `name` 컬럼은 중복이 허용됩니다. 데이터 무결성을 검사할 필요가 없죠. 그렇다면 **굳이 즉시 반영할 이유가 없습니다.** InnoDB는 이 점을 이용해서 **쓰기 성능을 크게 끌어올리는 최적화 기법**을 씁니다. 이름하여 **Change Buffer**. ### 4-1. Change Buffer란  Change Buffer는 **일반 보조 인덱스의 변경사항을 메모리에 잠시 모아두는 캐시**입니다. INSERT/UPDATE/DELETE로 보조 인덱스를 변경해야 할 때, 즉시 디스크에 반영하지 않고 일단 Change Buffer에 쌓아두는 거죠. ### 4-2. 왜 필요한가 — 디스크 I/O의 함정 Change Buffer가 왜 필요한지 이해하려면, 먼저 InnoDB의 **버퍼풀(Buffer Pool)** 개념을 알아야 합니다. InnoDB는 디스크에 저장된 인덱스 페이지를 **메모리에 올려놓고 작업**합니다. 이 메모리 영역이 버퍼풀입니다. ``` [ 디스크 ] [ 메모리: 버퍼풀 ] 인덱스 페이지 1 ←→ 페이지 1 사본 인덱스 페이지 2 (디스크에만) 인덱스 페이지 3 ←→ 페이지 3 사본 인덱스 페이지 4 (디스크에만) ... ``` 작업할 페이지가 버퍼풀에 있으면 빠릅니다 (메모리 접근). 없으면 **디스크에서 읽어와야** 합니다 (랜덤 I/O - 느림). 문제는 보조 인덱스 페이지가 **버퍼풀에 잘 안 올라온다**는 점입니다. 보조 인덱스는 보통: - 크기가 큼 (테이블 데이터의 일부 컬럼만 있어도 컬럼 수가 많거나 인덱스 종류가 많으면 누적됨) - 임의의 키로 변경이 자주 일어남 (예: `name` 컬럼은 가나다 순으로 분포가 흩어짐) - 그래서 변경할 때마다 매번 다른 페이지가 필요 만약 1000번의 INSERT가 있고, 각 INSERT가 보조 인덱스의 **다른 페이지**를 건드린다면? 디스크에서 1000번 페이지를 읽어와서 변경해야 합니다. 어마어마한 랜덤 I/O가 발생합니다. ### 4-3. Change Buffer의 동작 흐름 Change Buffer는 이 문제를 이렇게 해결합니다. 1. **INSERT 발생** → 보조 인덱스 페이지를 변경해야 함 2. 그 페이지가 버퍼풀에 있는지 확인 - 있으면 → 그냥 거기서 즉시 변경 (Change Buffer 안 씀) - **없으면 → 디스크에서 읽어오지 않고, 변경 내용을 Change Buffer에 기록만 해둠** 3. 나중에 그 페이지가 어떤 이유든 버퍼풀에 올라올 때, Change Buffer 변경사항들을 한꺼번에 반영 ### 4-4. Dirty Page와 병합 시점 **병합(Merge)**: Change Buffer에 쌓인 변경사항을 실제 인덱스 페이지에 반영하는 것을 병합이라고 합니다. 병합이 일어나는 시점은 세 가지입니다. 1. **해당 페이지가 버퍼풀에 올라올 때** (가장 일반적) - SELECT 쿼리 등으로 그 페이지를 읽어야 할 일이 생김 → 페이지가 디스크에서 버퍼풀로 올라옴 → 그 직후 Change Buffer의 내용을 즉시 병합 - 이때 페이지는 메모리에 있지만 디스크와 내용이 다른 상태가 됨 — 이를 **dirty page**라고 부름 - dirty page는 백그라운드에서 주기적으로 디스크에 기록됨 2. **백그라운드 스레드의 주기적 병합** - InnoDB는 시스템이 한가할 때 Change Buffer 내용을 천천히 디스크에 반영 3. **Crash Recovery 시** - 서버 재시작 시 Change Buffer 내용이 손실되지 않도록, 시스템 테이블스페이스에 기록되어 있다가 복구 과정에서 병합됨 ### 4-5. 정리: 누가 Change Buffer를 쓰고 안 쓰는가 | | Change Buffer 사용? | |---|---| | 클러스터링 인덱스 (PK) | ❌ 안 씀 (즉시 반영) | | 유니크 보조 인덱스 | ❌ 안 씀 (즉시 반영, 무결성 검사 필요) | | **일반 보조 인덱스** | ✅ **사용 (지연 반영)** | > 💡 **그래서 Change Buffer 덕분에 어떤 이득이 있나?** > - **랜덤 I/O가 순차 I/O로 변환**되는 효과: 여러 INSERT가 같은 페이지를 건드릴 때 한 번에 모아서 처리 > - **디스크 I/O 자체가 줄어듦**: 페이지가 버퍼풀에 올라올 때까지 디스크 접근 자체를 미룸 > - **트랜잭션 처리량(TPS) 증가**: 특히 보조 인덱스가 많은 테이블에서 효과가 큼 --- ## 5. 삭제도 즉시 일어나지 않는다: MVCC와 논리적 삭제 지금까지는 INSERT/UPDATE 이야기였습니다. 이제 **DELETE**를 봅시다. ```sql DELETE FROM products WHERE id = 1001; ``` 이 쿼리가 실행되면 행이 즉시 사라질 것 같지만, **실제 InnoDB의 동작은 다릅니다.** 보조 인덱스는 **논리적 삭제**만 일어납니다. ### 5-1. 논리적 삭제란 InnoDB는 보조 인덱스 항목을 즉시 물리적으로 제거하지 않고, **삭제 플래그(delete-mark)** 만 설정합니다. ``` 삭제 전: [name='당근', PK=1001] 삭제 후: [name='당근', PK=1001, ❌ 삭제됨] ← 데이터는 그대로, 마크만 추가 ``` 이렇게 마크된 항목은 일반 쿼리에는 보이지 않지만, 페이지 안에는 그대로 존재합니다. 나중에 **백그라운드의 Purge 스레드**가 더 이상 어떤 트랜잭션에서도 참조되지 않는다고 판단되면 비로소 물리적으로 제거합니다. ### 5-2. 왜 이렇게 복잡하게 — MVCC 때문 이 동작을 이해하려면 **MVCC**(Multi-Version Concurrency Control, 다중 버전 동시성 제어)를 알아야 합니다. InnoDB는 트랜잭션을 지원합니다. 그리고 여러 트랜잭션이 **동시에** 같은 데이터를 읽고 쓸 수 있습니다. 다음 시나리오를 생각해봅시다: ``` 시각 t1: 트랜잭션 A 시작 → SELECT * FROM products WHERE id = 1001 (당근 행을 읽음) 시각 t2: 트랜잭션 B 시작 → DELETE FROM products WHERE id = 1001 시각 t3: 트랜잭션 B 커밋 시각 t4: 트랜잭션 A가 다시 같은 행을 읽으려 함 ``` 만약 t3 시점에 행을 **물리적으로 즉시 삭제**해버리면, t4 시점의 트랜잭션 A는 데이터를 못 읽거나, 더 나쁜 경우 깨진 페이지를 읽게 됩니다. 트랜잭션 A는 t1 시점의 데이터를 보고 있었기 때문에, 동일한 데이터에 일관되게 접근할 수 있어야 합니다. **MVCC는 이 문제를 "여러 버전을 동시에 유지하기"로 해결합니다.** - 삭제는 즉시 일어나지 않고 마크만 됨 - 여전히 데이터를 참조 중인 트랜잭션이 있으면 그 데이터는 살아있음 - 아무도 참조하지 않게 되면 그제서야 Purge 스레드가 물리적으로 제거 ### 5-3. MySQL 공식 문서의 설명 > **InnoDB MVCC** treats secondary indexes differently than clustered indexes. > When a secondary index column is updated: > - Old secondary index records are **delete-marked** > - New records are inserted > - Delete-marked records are eventually **purged** 즉, 보조 인덱스에서 행을 수정하면: 1. 기존 항목에 삭제 마크 2. 새 항목을 따로 삽입 3. 나중에 Purge 스레드가 마크된 항목을 정리 ### 5-4. 실무에서 이게 왜 중요한가 > 💡 **"DELETE 했는데도 디스크 사용량이 안 줄어요"** > 운영 중 자주 보이는 현상입니다. 대량 DELETE 직후에도 테이블 사이즈가 그대로인 이유는 바로 이 **논리적 삭제 → Purge 대기** 흐름 때문입니다. > Purge가 따라잡지 못하면 실제 디스크 공간 회수가 한참 미뤄질 수 있고, 이걸 모니터링하는 게 DBA의 일 중 하나입니다. > > 신입 개발자 입장에서는 "DELETE 했는데 왜 빠르지 않지?", "왜 용량이 그대로지?" 같은 의문이 생길 때 MVCC를 떠올리면 됩니다. --- ## 핵심 요약 이번 편에서 꼭 가져가야 할 한 가지: > 🎯 **인덱스의 반영 시점은 "무결성 검사가 필요한가"로 갈린다** > - 무결성 검사 필요 (PK, 유니크 보조 인덱스) → **즉시 반영** > - 무결성 검사 불필요 (일반 보조 인덱스) → **지연 반영 (Change Buffer)** > - 삭제는 모든 인덱스에서 **논리적으로 먼저** 일어남 (MVCC) 체크리스트: - [x] PK 삽입이 **즉시 반영**되어야 하는 두 가지 이유를 설명할 수 있다 - [x] 유니크 보조 인덱스가 **Change Buffer를 쓰지 않는** 이유를 설명할 수 있다 - [ ] Change Buffer가 무엇이고, **어떤 인덱스에만 적용**되는지 안다 - [ ] Change Buffer 병합이 일어나는 **세 가지 시점**을 안다 - [ ] 보조 인덱스 삭제가 **왜 논리적 삭제로 처리**되는지 (MVCC) 설명할 수 있다 - [ ] **Purge 스레드**의 역할을 안다 > 📝 체크리스트를 다 채울 자신이 있다면? [**인덱스 기초2 퀴즈 도전하기**](quiz.html?set=B) --- ## 이제 퀴즈에 도전하기 2편에 걸쳐 인덱스 기초2의 모든 개념을 다뤘습니다. - 자동으로 생기는 인덱스 — GEN_CLUST_INDEX, UNIQUE/FK 자동 인덱스 (4편) - 인덱스별 반영 시점 — PK·유니크는 즉시, 일반 보조는 지연 (5편) - Change Buffer의 동작 원리 — 버퍼풀, dirty page, 병합 시점 (5편) - MVCC와 논리적 삭제 — 왜 DELETE가 즉시 일어나지 않는가 (5편) > 🎯 [**인덱스 기초2 퀴즈 풀어보기**](quiz.html?set=B) > 6문제 중 막히는 게 있다면, 해당 학습자료의 섹션으로 돌아와 다시 읽어보세요. > 특히 Change Buffer와 MVCC는 한 번 이해해두면 인덱스뿐 아니라 InnoDB의 다른 동작들도 함께 이해되는 핵심 개념입니다. --- ## 다음 학습자료 인덱스 기초2를 모두 마쳤습니다. 다음 학습자료에서는 **인덱스 기초3** 주제를 다룹니다. --- ## Reference - MySQL 공식 문서: [InnoDB Change Buffer](https://dev.mysql.com/doc/refman/8.4/en/innodb-change-buffer.html) - MySQL 공식 문서: [FAQ: InnoDB Change Buffer](https://dev.mysql.com/doc/refman/8.4/en/faqs-innodb-change-buffer.html) - MySQL 공식 문서: [InnoDB Multi-Versioning (MVCC)](https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html) - MariaDB 공식 문서: [InnoDB Change Buffering](https://mariadb.com/kb/en/innodb-change-buffering/)