20장. 인덱스로 못 푸는 것 — 보관정책·파티셔닝·스키마

{/* 삽화 자리 — 넘치는 창고 안에서 물건 찾는 지도를 열심히 그리는 사람. 지도는 훌륭한데 창고는 계속 차오른다 */}

로그 테이블에서 30만 행을 DELETE로 지웠습니다. 테이블스페이스 파일은 568.0MiB에서 568.0MiB, 한 장도 안 줄었어요. OPTIMIZE까지 27.8초를 더 써도 2.8%만 돌아옵니다. 같은 행을 DROP PARTITION으로 버리면 236밀리초에 16.0%가 그대로 빠집니다.

면접 실전 질문: ① 로그 테이블이 계속 커지면 어떻게 하나요? ② 파티셔닝은 언제 쓰나요? ③ DELETE했는데 왜 용량이 안 줄어드나요?

배경 — 인덱스가 안 하는 일

열아홉 장 동안 인덱스가 무엇을 하는지 봤습니다. 덜 읽게 만드는 장치요. 45초짜리 조회가 밀리초가 됩니다.

그런데 여는 글의 그 테이블은 조회가 느린 게 문제의 전부가 아니었습니다. 계속 자라고 있었어요. 결제 요청마다 요청·응답 두 건이 쌓이는 테이블이니까요.

인덱스를 걸어도 그건 안 멈춥니다. 인덱스는 덜 읽게 하지 덜 쌓이게 하지 않습니다. 오히려 4부에서 봤듯 인덱스 자체가 자리를 더 먹어요.

이 장은 인덱스 밖의 세 가지입니다 — 지우는 것(보관정책), 나누는 것(파티셔닝), 애초에 덜 쌓는 것(스키마).

스토리 — 지웠는데 안 줄어듭니다

로그 테이블에 보관정책을 넣는다고 해봅시다. “3개월 지난 건 지운다”요.

DELETE FROM request_log WHERE logged_on < '2026-02-01';

이러면 될 것 같습니다. 그런데 두 가지가 어긋납니다.

첫째, 파일이 안 줄어듭니다. InnoDB는 지운 행의 자리를 재사용 가능하다고 표시할 뿐 파일을 되돌려주지 않아요. 디스크 사용량을 보면 그대로입니다.

둘째, 지우는 것도 쓰기입니다. 30만 행을 지우면 클러스터드 인덱스와 모든 세컨더리 인덱스를 그만큼 고쳐야 해요. 4부에서 본 그 비용이 그대로 붙습니다. 그리고 긴 DELETE는 그동안 공유 MDL을 쥐고 있으니, 뒤따르는 ALTER가 18장 ③구간에서 물릴 수 있고요.

그래서 파티셔닝을 못 쓰는 테이블이라면 — 아래에서 볼 유니크 제약 때문에 흔합니다 — 청크로 나눠 지웁니다. LIMIT 10000을 붙여 반복하고 사이사이 커밋하면, 한 번의 트랜잭션이 길어지는 걸 막을 수 있어요.

-- 한 번에 다 지우지 않는다. 지울 게 없을 때까지 반복한다
DELETE FROM request_log WHERE logged_on < '2026-02-01' LIMIT 10000;

파티셔닝은 다른 방법을 씁니다. 테이블을 날짜 구간별로 따로 저장해두고, 지울 때는 그 구간을 통째로 버려요. 행을 하나씩 지우는 게 아니라 파일을 하나 버리는 겁니다.

얼마나 다른지 재봤습니다.

핵심 — 236밀리초와 29.6초

같은 200만 행 로그 테이블을 두 벌 만들었습니다. 한쪽은 평범한 테이블, 한쪽은 월별 RANGE 파티션이고요. 둘 다에서 첫 달치(30만 3,413행)를 지웠습니다.

크기는 실제 테이블스페이스 파일(INNODB_TABLESPACES.FILE_SIZE)로 쟀습니다. 처음엔 DATA_LENGTH를 썼는데 그건 영속 통계에서 나온 근사값이라 실행마다 흔들려요 — “1바이트도 안 줄었다”를 주장하려면 파일을 재야 합니다.

✅ 실측 (MySQL 8.0.46 / Docker / Apple M1 / macOS, 2026-08. 200만 행을 4개월치 연속 구간으로 넣고 첫 달을 지웠습니다. 두 비율 모두 적재 직후 크기 기준이에요. 재현: ./ch20/retention.sh. 전체 소스: docs/book/code/db-index/ch20/retention.sh)

DELETE + OPTIMIZE가 29,596밀리초, DROP PARTITION이 236밀리초입니다. 125배예요.

그리고 결과도 다릅니다. DROP PARTITION은 지운 행 비율(15.2%)만큼 파일이 그대로 줄었는데(16.0%), DELETE + OPTIMIZE는 2.8%만 돌아왔습니다.

(파티션 테이블이 600.0MiB로 32MiB 큽니다 — 파티션마다 세그먼트를 따로 잡기 때문이에요. 5.6% 차이고, 위 비교는 각자의 적재 직후를 기준으로 하니 영향은 없습니다. 다만 이것도 파티셔닝의 고정 비용입니다.)

”한 장도 안 줄었다”를 따로 봅시다

첫 줄이 이 장에서 제일 중요합니다. 30만 행이 사라졌는데 568.0MiB가 568.0MiB예요.

InnoDB는 지운 행의 공간을 같은 테이블 안에서 재사용합니다. 새 행이 들어오면 그 자리를 쓰죠. 그러니 “지웠으니 디스크가 여유로워졌겠지”는 틀립니다 — 운영체제가 보는 파일 크기는 그대로고, 모니터링의 디스크 사용량도 그대로입니다.

OPTIMIZE가 2.8%만 돌려준 이유는 못 밝혔습니다

15.2%를 지웠는데 파일은 2.8%만 줄었습니다. 그래서 계측 문제인지부터 배제해봤어요.

✅ 실측 (같은 환경)

마지막 줄이 답을 좁힙니다. 같은 행 수를 처음부터 쌓으면 484.0MiB인데, 지우고 재구성하면 552.0MiB예요. 68MiB, 14% 차이입니다.

purge도 아니고 반복으로도 안 줄고, 그 크기가 자연스러운 크기도 아닙니다. 왜 그만큼 남는지는 못 밝혔습니다. 확실한 건 DROP PARTITION이 16.0%를 그대로 돌려줬다는 것뿐이에요.

📄 OPTIMIZE는 17장에서 본 그 재구성입니다. 17장이 인덱스 추가 중에 잰 처리량 감소가 31%인데, OPTIMIZE 중의 처리량은 따로 재지 않았습니다.

파티셔닝의 값 — partitions 열

파티셔닝이 공짜는 아닙니다. EXPLAIN에 partitions 열이 있는데, 거기가 계산서예요.

조건 있음:          partitions=p03            type=index  rows=404416
파티션 키 없는 조건: partitions=p02,p03,p04    type=ref    rows=1

✅ 실측 (같은 환경. retention.sh의 실제 출력입니다)

위가 날짜 조건, 아래가 transaction_no = ? 조건입니다.

날짜 조건이 있으면 한 파티션만 봅니다. 이걸 파티션 프루닝이라고 해요. 나머지 파티션은 아예 안 열립니다.

그런데 조건에 날짜가 없으면 모든 파티션을 다 봅니다. 위 예시는 transaction_no = ?로 찾는 건데, type=ref에 rows=1이라 인덱스는 잘 탔습니다. 다만 그 인덱스 탐색을 파티션마다 한 번씩 해요.

이게 파티셔닝의 트레이드오프입니다.

그리고 도입을 막는 제약이 둘 더 있습니다. 실무에서 먼저 부딪히는 쪽이에요.

그래서 파티션 키는 조회 패턴이 정합니다. 로그 테이블에서 날짜로 나누는 게 잘 맞는 건, 조회가 대개 “이 기간의” 로그를 찾기 때문이에요. 거래번호로만 찾는 패턴이 주류라면 날짜 파티셔닝은 조회를 느리게 만듭니다.

(여기 실험에서 월별 행 수가 434,469 / 410,694 / 851,424로 안 맞습니다. 4장에서 본 auto_increment 구멍 때문에 id 구간이 균등하게 안 나뉘어서예요. 실제 로그 테이블은 시간으로 나뉘니 이보다 고를 겁니다.)

애초에 덜 쌓기 — 1장으로 돌아갑니다

지우고 나누는 건 이미 쌓인 걸 다루는 방법입니다. 그 앞에 하나가 더 있어요.

1장에서 같은 25만 행 테이블이 63MB일 수도 653MB일 수도 있다는 걸 봤습니다. 차이는 컬럼 하나였어요 — CHAR(180)이냐 JSON(약 2.1KB)이냐.

10.4배입니다. 그리고 이건 인덱스로 못 줄여요. 인덱스는 읽는 양을 줄이지 쌓이는 양을 안 줄입니다.

로그 테이블이라면 물어볼 게 있습니다. 그 2.1KB를 전부 DB에 넣어야 하나요?

정답은 없습니다. 다만 “인덱스를 걸었는데도 느리다”의 답이 인덱스가 아닐 수 있다는 걸 알고 고르는 것과 모르고 고르는 건 다릅니다.

그래서 순서는 이렇습니다

이 표의 순서가 중요합니다. 인덱스는 첫 줄이지 전부가 아닙니다.

정리

• 인덱스는 덜 읽게 하지 덜 쌓이게 하지 않습니다. 오히려 인덱스 자체가 자리를 더 먹어요(4부).
• DELETE는 파일을 안 줄입니다. 30만 행을 지웠는데 테이블스페이스 파일이 568.0MiB에서 568.0MiB였어요. InnoDB가 그 자리를 같은 테이블 안에서 재사용하기 때문입니다.
• 크기는 DATA_LENGTH가 아니라 **INNODB_TABLESPACES.FILE_SIZE**로 재야 합니다. 앞엣것은 영속 통계에서 나온 근사값이라 실행마다 흔들려요.
• 되돌리려면 OPTIMIZE가 필요한데 27.8초가 들고, 15.2%를 지운 자리에서 2.8%만 돌아왔습니다.
• 그게 계측 문제인지 배제해봤습니다. purge를 기다려도, 두 번 돌려도 552.0MiB 그대로예요. 그리고 같은 행 수를 처음부터 넣으면 484.0MiB입니다 — 68MiB, 14% 차이고 왜 그만큼 남는지는 못 밝혔습니다.
• DROP PARTITION은 236밀리초에 16.0%를 그대로 돌려줍니다. DELETE + OPTIMIZE(29,596ms) 대비 125배예요.
• 파티셔닝의 값은 EXPLAIN의 partitions 열에 찍힙니다. 조건에 파티션 키가 있으면 한 파티션만 보고, 없으면 전부 봅니다.
• 도입을 막는 제약이 둘 있어요. 파티션 키가 모든 유니크 제약에 들어가야 하고, 다음 구간 파티션을 미리 만들어둬야 합니다.
• 파티셔닝을 못 쓰면 청크로 나눠 지웁니다. 한 트랜잭션이 길어지면 18장이 따라옵니다.
• 그 앞에 스키마가 있습니다. 1장에서 같은 25만 행이 63MB와 653MB로 10.4배 갈렸고, 차이는 JSON 컬럼 하나였습니다. 인덱스로 못 줄이는 종류의 비용이에요.
• 순서: 특정 조회가 느리면 인덱스, 테이블이 커지면 보관정책, 주기적으로 버려야 하면 파티셔닝, 행이 크면 스키마.

생각해볼 질문

1. DELETE가 파일을 안 줄이는 건 그 자리를 재사용하기 때문입니다. 그럼 매일 지우고 매일 쌓는 테이블은 파일이 계속 커질까요, 어느 선에서 멈출까요?
2. 파티션 키가 조건에 없으면 모든 파티션을 봅니다. 그럼 파티션을 100개로 나눈 테이블에서 transaction_no = ? 조회는 안 나눈 테이블보다 얼마나 느려질까요? 무엇에 비례할까요?
3. 이 책은 “읽는 양”을 페이지로 셌습니다. 보관정책은 그 페이지 수 자체를 줄이는 일이고요. 그럼 인덱스와 보관정책 중 어느 쪽이 먼저여야 할까요? 어떤 조건에서 답이 갈릴까요?

여기까지가 이 책의 스무 장입니다.

인덱스 이야기는 여기서 끝입니다. 남은 건 처음 그 장면으로 돌아가는 일이고요.

▶ 닫는 글 — 다시, 읽는 양 앞에서