7장. 왕복 — 세컨더리 인덱스는 PK를 들고 다시 내려간다

{/* 삽화 자리 — 색인 카드에서 번호를 받아든 사서가 서가 입구로 되돌아가 처음부터 다시 내려간다. 그 왕복이 행마다 반복된다 */}

5,917행짜리 구간에서 인덱스에 없는 컬럼 하나를 물었더니 페이지 요청이 37회에서 23,700회가 됐습니다. 640배입니다. 행마다 값을 치렀거든요.

면접 실전 질문: ① 커버링 인덱스가 왜 빠른가요? ② 세컨더리 인덱스로 찾은 뒤 무슨 일이 더 일어나나요? ③ 인덱스를 탔는데도 느린 경우가 있나요?

배경 — 6장이 리프에 넣어둔 것

6장에서 세컨더리 인덱스 리프에 무엇이 들어 있는지 봤습니다. 행이 아니라 PK 값이었죠.

그래서 이런 일이 생깁니다.

SELECT txno       FROM t WHERE txno = ?;  -- 인덱스 안에 답이 있다. 끝.
SELECT txno, body FROM t WHERE txno = ?;  -- body 는 인덱스에 없다. 어디로 가야 하나?

두 번째 쿼리는 인덱스에서 PK를 얻은 다음, 그 PK로 클러스터드 인덱스를 처음부터 다시 내려가야 합니다. 서가 입구로 되돌아가서요.

이걸 왕복이라고 부르겠습니다. 그리고 왕복이 아예 없는 경우, 그러니까 조회에 필요한 컬럼이 전부 인덱스 안에 있는 경우를 커버링이라고 합니다.

4장에서 이 값의 옆모습을 이미 봤습니다. 같은 구간을 COUNT(*)로 물으면 옵티마이저가 81%까지 인덱스를 쓰는데 SELECT *로 물으면 13.7%에서 버렸죠. 그 차이가 이 왕복입니다.

그럼 왕복 한 번이 정확히 몇 페이지짜리인지 재봅시다.

스토리 — 재기 전에 예상을 적어둡니다

재기 전에 예상을 세워두는 게 좋습니다. 안 그러면 나온 숫자에 아무 얘기나 갖다 붙이게 되거든요.

클러스터드 인덱스 높이가 3이면 왕복 한 번에 루트·중간·리프 세 페이지를 만져야 합니다. 행당 3회쯤이 상식적인 예상입니다.

그런데 여기서 갈릴 만한 게 하나 더 있어요.

세컨더리 인덱스를 순서대로 훑으면 PK가 어떤 순서로 나올까요? txno가 id를 따라 만들어진 테이블이라면 PK도 나란히 증가합니다. 그럼 왕복이 클러스터드의 같은 페이지를 연달아 두드리게 되죠. 반대로 txno와 id가 아무 관계도 없다면 매번 다른 페이지로 튑니다.

그러면 순서가 맞는 쪽이 훨씬 싸야 합니다. 그럴듯한 가설이죠.

그래서 테이블을 둘 만들었습니다. 값 집합과 행 수는 같고 PK와의 상관만 다릅니다.

-- 순서 일치: txno 가 id 를 따라간다
INSERT INTO rt_ordered (txno, body)
  SELECT LPAD(id,20,'0'), REPEAT('x',100) FROM scan_demo LIMIT 500000;
 
-- 순서 무관: 같은 값 집합을 무작위 순서로 넣는다
INSERT INTO rt_random (txno, body)
  SELECT txno, body FROM rt_ordered ORDER BY RAND();

핵심 — 왕복은 행당 정확히 4회입니다

같은 구간을 두 형태로 물어 페이지 요청 차이를 셌습니다.

• 커버링 — SELECT COUNT(txno). 인덱스 안의 값만 씁니다.
• 비커버링 — SELECT COUNT(body). body는 인덱스에 없어 행까지 가야 합니다.

SELECT * 대신 COUNT를 쓴 건 100바이트짜리 행 5만 개를 클라이언트로 보내는 값이 섞이지 않게 하려는 겁니다. body가 NOT NULL이라 COUNT(body)도 행까지 반드시 내려갑니다.

실행계획으로 그게 확인됩니다.

커버링   : type=range   Extra=Using where; Using index   ← 인덱스만 읽는다
비커버링 : type=range   Extra=Using index condition      ← Using index 가 없다

✅ 실측 (MySQL 8.0.46 / Docker / Apple M1 / macOS, 2026-08. 각 50만 행, 버퍼풀 1GB. Innodb_buffer_pool_read_requests 델타이고 전역 카운터라 유휴 컨테이너에서 쟀습니다. 적응형 해시 인덱스는 끈 상태입니다 — 이유는 아래에. 옵티마이저가 중간에 풀스캔으로 갈아타지 않도록 FORCE INDEX로 고정했습니다(4장에서 본 그 경계 때문입니다). 재현: AHI=OFF ./ch07/roundtrip.sh. 전체 소스: docs/book/code/db-index/ch07/roundtrip.sh)

하마터면 또 속을 뻔했습니다

표에 “구간 경계”와 “실제 행 수”가 따로 있는 이유부터 말해야겠습니다.

처음엔 경계값을 그대로 행 수로 적었습니다. 그러자 행당 값이 2.0에서 2.6까지 흘러 다녔고, 저는 그걸 설명하려 애쓰고 있었어요.

틀린 건 값이 아니라 나눗셈의 분모였습니다. 시드 원본의 auto_increment에 구멍이 있어서(4장에서 겪은 그 함정입니다) 경계 100이 실제로는 64행이었거든요. COUNT(*)로 세어 다시 나누니 흔들림이 사라졌습니다.

4장에서 똑같이 당하고 이 장에서 또 당했습니다. 폭과 개수는 다른 것이고, 헷갈리면 반드시 나중에 잘못된 이야기를 만들어냅니다.

세 가지가 읽힙니다

첫째, 왕복은 행당 정확히 4회입니다. 64행이든 32,768행이든 3.98~4.00이에요. 512배 차이 나는 구간에서 소수 둘째 자리까지 같습니다.

예상은 3회였습니다. 클러스터드 높이가 3이니까요. 하나가 더 붙은 셈인데, 이건 설명하지 못했습니다.

둘째, 커버링도 행 수를 따라 늡니다. “건수와 무관하다”가 아니에요. 기울기가 다를 뿐입니다.

커버링   : (111 − 19) ÷ (32,768 − 512) = 행 351개마다 페이지 1장
비커버링 : 행 1개마다 4장

행 351개당 1장이라는 값은 이 인덱스의 리프 한 장에 엔트리가 470개 들어간다는 사실과 같은 자릿수입니다. 리프를 옆으로 걷는 값이 그겁니다. 1,400배 차이가 여기서 옵니다.

셋째, 왕복 몫이 두 테이블에서 사실상 같습니다. 네 구간 중 셋은 값이 아예 같고(255·2,039·131,050), 나머지 하나도 23,663 대 23,664 — 1 차이입니다.

가설은 틀린 게 아니라, 이 실험이 답할 수 없는 질문이었습니다

순서 가설 말인데요. 확인도 반증도 못 했습니다. 지표를 잘못 골랐거든요.

우리가 센 건 페이지를 몇 번 달라고 했는가입니다. 버퍼풀에 있든 없든 요청 횟수는 안 변해요. 미스도 요청으로 세니까요. 순서가 갈리는 건 그 요청 중 몇 개가 디스크까지 내려가느냐 — 물리 read 쪽입니다.

그리고 이 실험에서는 물리 read가 전 구간 0이었습니다. 갈릴 자리가 아예 없었어요.

그러니 이 표는 “순서는 상관없다”가 아니라 “메모리에 다 올라와 있는 동안에는 순서가 요청 횟수를 안 바꾼다”까지만 말합니다. 버퍼풀이 데이터를 다 못 담으면 어떻게 되는지가 바로 다음 장입니다.

순서가 바꾼 게 하나 있긴 합니다

왕복은 안 바꿨지만 인덱스 크기는 바꿨습니다.

✅ 실측 (mysql.innodb_index_stats의 n_leaf_pages)

같은 50만 행인데 인덱스가 1.56배입니다. 무작위 순서로 넣으니 페이지가 쪼개지며 64%만 찬 거예요. 표에서 rt_random의 커버링 값이 조금씩 더 컸던 것도(37 대 42, 111 대 157) 이 때문입니다.

2장에서 “정렬해서 한 번에 부으면 페이지가 꽉 찬다”고 했는데, 그 반대 경우를 여기서 처음 봅니다. 얼마나 비싼지는 13·14장입니다.

적응형 해시 인덱스가 하나를 깎습니다

행당 4회 중 하나는 깎을 수 있습니다. InnoDB는 자주 쓰이는 동등 검색 패턴을 관찰해 해시로 지름길을 냅니다. 트리를 내려가는 대신 해시 한 번으로 레코드에 닿는 거죠.

✅ 실측 (같은 환경, rt_ordered. 5,917행·32,768행 두 구간 모두 3.02)

24.5%가 깎입니다. 범위 스캔 자체에는 안 걸리기 때문에 커버링 열은 거의 안 변하고, 깎이는 건 왕복 쪽뿐이에요.

📄 문서 기반 (미검증) — 동시성이 아주 높으면 해시 래치 경합으로 되레 느려져 끄고 운영하는 곳도 있습니다. 직접 재보지는 않았습니다.

옵티마이저는 왕복을 1장으로 봅니다

4장의 비용 표를 다시 꺼내 왕복 몫만 뽑아보면 이렇습니다.

폭 200,000 : (148,218 − 43,470) ÷ 423,422 = 0.24738
폭 400,000 : (236,668 − 69,411) ÷ 676,102 = 0.24738
memory_block_read_cost                    = 0.25

옵티마이저는 왕복을 행당 페이지 딱 한 장 값으로 모델링합니다. 두 지점에서 소수 다섯째 자리까지 같아요.

그런데 실측은 행당 4회를 요청했습니다. 비용 모델의 단위와 요청 횟수는 같은 것이 아니니 “그러니 모델이 4배 틀렸다”고 말할 수는 없습니다. 다만 방향은 분명합니다 — 모델은 왕복을 싸게 봅니다. 그래서 4장의 13.7%라는 경계도 실제보다 늦게 잡혀 있을 여지가 있습니다. 옵티마이저가 언제 어떻게 틀리는지는 12장에서 따로 다룹니다.

값이 페이지 밖으로 빠졌다면

5장에서 행이 8,106바이트를 넘으면 값이 오버플로 페이지로 나간다고 했습니다. 그런 테이블에서는 왕복이 어떻게 될까요.

📄 문서 기반 (미검증) — 한 번 더 붙습니다. 클러스터드 리프에 도착해도 값이 거기 없으니 오버플로 페이지를 또 읽어야 하거든요. 대신 SELECT id처럼 값을 안 건드리는 조회는 5장에서 본 대로 리프가 7장뿐이라 오히려 싸집니다. 이 케이스는 직접 재지 않았습니다.

정리

• 세컨더리 인덱스 리프에는 PK만 있습니다(6장). 인덱스에 없는 컬럼을 물으면 그 PK로 클러스터드 인덱스를 처음부터 다시 내려가야 합니다. 이게 왕복입니다.
• 실측: 왕복은 행당 정확히 4회의 페이지 요청을 붙입니다. 64행부터 32,768행까지 3.98~4.00으로 흔들리지 않았어요. 예상이던 3회(클러스터드 높이)보다 하나 많은데, 그건 설명하지 못했습니다.
• 커버링도 행 수를 따라 늡니다. 다만 기울기가 행 351개당 1장이라 비커버링(행당 4장)과 1,400배 벌어집니다.
• 메모리에 다 올라와 있는 동안에는 세컨더리 순서가 PK 순서와 맞는지가 요청 횟수를 안 바꿨습니다. 물리 read가 전 구간 0이라 갈릴 자리가 없었어요(8장).
• 순서가 바꾼 건 인덱스 크기입니다. 무작위로 넣은 쪽이 충전율 64%로 1.56배 컸습니다(13·14장).
• 적응형 해시 인덱스는 왕복을 4.00에서 3.02로 깎습니다.
• 4장에서 옵티마이저는 왕복을 행당 페이지 한 장 값(0.2474)으로 모델링했습니다. 실측은 4회 요청이고요. 모델은 왕복을 싸게 봅니다.
• 왕복을 없애려면 조회에 필요한 컬럼을 전부 인덱스에 넣으면 됩니다. 공짜는 아니에요 — 넣는 만큼 인덱스가 커지고(6장) 쓰기가 비싸집니다(13장). 그 계산이 16장입니다.

생각해볼 질문

1. 왕복이 행당 4회라면, 조건에 맞는 행이 1건일 때와 10만 건일 때 인덱스의 이득은 어떻게 달라질까요?
2. 이 장은 순서 가설을 확인하지 못했습니다. 지표를 무엇으로 바꾸고 조건을 어떻게 만들어야 그 가설을 시험할 수 있을까요? (8장이 그 조건을 만듭니다)
3. 적응형 해시 인덱스가 왕복을 24.5% 깎았습니다. 그럼 이걸 끄는 게 이득인 상황도 있을까요?

이 장의 모든 물리 read가 0이었다는 게 결국 핵심입니다. 데이터가 전부 메모리에 있으면 페이지를 몇 번 달라고 하든 값이 비슷하니까요.

그럼 메모리에 다 안 들어가면 무슨 일이 벌어질까요. 무엇이 남고 무엇이 밀려날까요.

▶ 8장. 무엇이 메모리에 남는가 — 버퍼풀과 Young/Old LRU