왜 변경이 잦은 컬럼에는 인덱스가 독이 될 수 있을까

데이터베이스 성능 최적화를 공부하다 보면 "수정(UPDATE)과 삭제(DELETE)가 잦은 컬럼에는 인덱스를 신중하게 걸어야 한다"는 조언을 자주 듣게 됩니다. 왜 그럴까요? 단순히 인덱스를 관리하는 비용 때문일까요?

 

오늘은 MySQL InnoDB 엔진의 내부 동작 방식을 통해, 실제 데이터는 10만 건인데 인덱스만 비대해져 성능이 갉아먹히는 이유를 '책의 색인' 비유와 함께 파헤쳐 보겠습니다.

---

1. 핵심 개념: DB는 즉시 삭제하지 않는다 (Ghost Record)

데이터베이스는 DELETE나 UPDATE 명령이 들어왔을 때, 해당 데이터를 즉시 물리적으로 지우지 않습니다. 대신 "삭제됨(Deleted Mark)" 표시만 해둡니다.

 

왜 바로 삭제하지 않을까?

• 속도 때문입니다: 즉시 물리적으로 삭제하면 인덱스 구조(B-Tree)를 그 즉시 재조정해야 하므로 작업이 느려집니다.
• 효율성: 표시만 해두고 나중에 백그라운드 스레드(Purge Thread)가 한꺼번에 정리하는 것이 훨씬 효율적입니다.

---

2. 책의 '색인'으로 이해하기 (비유)

책 뒷면의 색인(Index)을 상상해 보세요. 책 본문은 100페이지, 색인은 2페이지 분량입니다.

• 상황: 1,000번의 데이터 삭제/수정이 일어났습니다.
• 결과: 실제 본문 내용은 여전히 100페이지 분량이지만, 색인 페이지는 취소선이 그어진 정보들로 가득 차서 20페이지로 늘어났습니다.
• 문제: 예전에는 '김치'라는 단어를 찾기 위해 2페이지만 보면 됐지만, 이제는 불필요한 취소선이 가득한 20페이지 전체를 다 뒤져야 합니다. 당연히 조회 속도가 느려질 수밖에 없습니다.

---

3. DELETE vs UPDATE 내부 동작 비교

DELETE의 경우: "공간은 그대로, 데이터만 줄어듦"

• 초기: 데이터 100개 (100GB) → Data_length: 100GB
• 20개 삭제 후: 80개 생존, 20개 삭제 표시 → Data_length: 여전히 100GB
• 문제점: SELECT 시 100GB 공간을 전부 스캔하며 삭제 표시된 20GB를 건너뜁니다. 즉, 불필요한 스캔 범위가 남습니다.

 

UPDATE의 경우: "데이터는 그대로, 공간은 늘어남" (더 위험!)

InnoDB에서 UPDATE는 내부적으로 Delete + Insert 방식으로 동작합니다.

• 기존 레코드: 삭제 표시 (Old)
• 새 레코드: 새로운 공간에 삽입 (New)
• 결과: 데이터 100개 중 20개를 수정하면, 전체 점유 공간은 120GB로 늘어납니다.
• 위험성: 수정이 반복될수록 실제 데이터 양은 같은데 스캔해야 할 범위(쓰레기 데이터 공간)만 기하급수적으로 커집니다.

구분	DELETE	UPDATE
실제 데이터	80개(줄어듬)	100개(그대로)
전체 공간	100GB(그대로)	120GB(늘어남)
쓰레기 공간	20GB	20GB
스캔 범위	100GB	120GB

수정이 더 심각한 이유는 데이터는 그대로인데 공간만 계속 늘어나며 스캔해야 할 범위가 계속 커집니다.

이런 걸 고려했을 때 수정이 반복된다면 얼마나 심각해질까요?

100개의 레코드를 100번 update 하면 실제 데이터는 100GB인데 10TB를 스캔해야 하므로 성능은 최악이 됩니다.

---

4. 내 DB 상태 진단하기

MySQL에서는 간단한 쿼리로 우리 테이블에 '쓰레기 데이터(Fragment)'가 얼마나 쌓였는지 확인할 수 있습니다.

SHOW TABLE STATUS LIKE '테이블명';

• Data_length: 전체 데이터가 차지하는 공간
• Data_free: 빈 공간(쓰레기)
• Index_length: 인덱스 크기

최적화가 필요한 시점

Data_free 비율 = Data_free ÷ Data_length이  20% 이상이면 OPTIMIZE TABLE 명령을 통해 파편화된 공간을 정리해 주는 것이 좋습니다. (AI 생각)

---

5. 실무에서의 판단기준

인덱스를 걸어야 하는 경우 

• 조회가 빈번한 컬럼
• where절에 자주 사용되는 컬럼
• INSERT위주이고 수정/삭제가 거의 없는 컬럼

인덱스를 신중해야 하는 경우

• 수정/삭제가 실시간으로 대량 발생하는 컬럼
• 데이터의 중복도(선택도)가 낮은 컬럼 (예: 성별, 진행상태 등)
• 인덱스 크기가 실제 데이터 크기보다 비대해진 경우

---

6. 실제 사례 분석

제가 확인한 실제 테이블 상태입니다.

• Data_length: 6.47GB
• Data_free: 7MB (0.1%)
• Index_length: 6.97GB

Data_free 비율이 0.1%으로 쓰레기가 거의 없습니다. 최적화 불필요하다.

주의할 점

• 인덱스(6.97GB)가 데이터(6.47GB) 보다 큼
• 불필요한 인덱스가 있는지 점검 필요

---

6. 결론

1. DB는 삭제/수정 시 즉시 지우지 않고 표시만 한다.
2. UPDATE는 Delete + Insert이므로 삭제보다 더 빈번하게 공간을 낭비한다.
3. 쓰레기 데이터가 쌓이면 인덱스 스캔 범위가 넓어져 조회 성능이 떨어진다.
4. 정기적으로 Data_free 비율을 모니터링하고 최적화(Optimize)를 진행한다.

결국 좋은 DB 설계란, 무조건 빠른 조회를 위해 인덱스를 남발하는 것이 아니라 데이터의 생애 주기(C.R.U.D)를 고려하여 적절한 균형을 찾는 것입니다.