왜 랜덤 I/O가 문제인가

InnoDB에서 세컨더리 인덱스는 리프 노드에 인덱스 컬럼과 함께 프라이머리 키(PK) 값만 저장한다. 인덱스에 없는 컬럼을 조회하면 각 행마다 PK로 클러스터드 인덱스를 다시 찾아가는데, 이 과정을 테이블 룩업 또는 북마크 룩업이라 부른다. 매칭되는 행이 수천 개면 수천 번의 PK 조회가 발생하고, 이는 디스크상에서 흩어진 위치를 읽는 랜덤 I/O로 이어진다.

버퍼 풀에 데이터가 없으면 랜덤 I/O 비용은 순차 I/O 대비 수십 배까지 커진다. 인덱스로 대상 행을 빠르게 골랐더라도, 룩업 단계에서 전체 쿼리 시간의 대부분을 소모하는 경우가 흔하다.

커버링 인덱스란

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼(SELECT, WHERE, ORDER BY, GROUP BY)을 인덱스 자체가 포함하는 상태를 말한다. 필요한 데이터가 전부 인덱스 리프에 있으면 클러스터드 인덱스로 되돌아갈 필요가 없어 테이블 룩업이 사라진다. 실행 계획의 Extra에 Using index가 뜨면 커버링이 적용된 것이다.

문제 상황 재현

주문 테이블에서 특정 사용자의 최근 주문 금액을 집계하는 쿼리를 예로 든다.

-- 인덱스: idx_user (user_id)
EXPLAIN
SELECT user_id, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 100;

-- Extra: Using where; Backward index scan
-- amount, created_at 를 얻으려 매 행마다 PK 룩업 발생

인덱스는 user_id만 커버하므로 amount와 정렬용 created_at을 얻으려 룩업이 발생하고, 정렬을 위한 파일소트까지 겹칠 수 있다.

커버링 인덱스로 해결

WHERE, ORDER BY, SELECT에 쓰이는 컬럼을 한 인덱스에 순서대로 담는다. 등호 조건 컬럼을 앞에, 정렬 컬럼을 그다음, 조회 전용 컬럼을 뒤에 둔다.

ALTER TABLE orders
  ADD INDEX idx_cover (user_id, created_at, amount);

EXPLAIN
SELECT user_id, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 100;

-- Extra: Using index   <- 커버링 성공, 룩업/파일소트 제거

user_id로 범위를 좁히고 created_at 순서가 인덱스에 이미 정렬돼 있어 정렬 비용도 사라진다. 마지막의 amount는 조건에 쓰이지 않지만 리프에 값을 실어 룩업을 없애는 역할을 한다.

확인 방법

커버링 여부는 EXPLAIN의 Extra와 실제 읽은 페이지 수로 검증한다.

-- 상태 초기화 후 논리적 읽기량 비교
SET @a := 0;
FLUSH STATUS;
SELECT user_id, amount FROM orders
  WHERE user_id = 42 ORDER BY created_at DESC LIMIT 100;
SHOW STATUS LIKE 'Handler_read%';

-- Handler_read_next 는 인덱스 순차 읽기,
-- Handler_read_rnd 계열이 크면 룩업이 남아있다는 신호

커버링 vs 일반 인덱스

구분일반 세컨더리 인덱스커버링 인덱스
SELECT 컬럼 획득PK 룩업 필요인덱스에서 직접
랜덤 I/O매칭 행 수만큼 발생거의 없음
Extra 표시Using where 등Using index
인덱스 크기작음포함 컬럼만큼 증가
쓰기 부담낮음상대적으로 높음

주의점

커버링은 만능이 아니다. 조회 컬럼을 인덱스에 넣을수록 인덱스가 커지고, INSERT/UPDATE/DELETE 시 갱신 비용과 버퍼 풀 점유가 늘어난다. SELECT *를 커버하려는 시도는 사실상 테이블 복제와 같으니 피한다. 대상은 자주 실행되고 반환 컬럼이 좁은 핵심 쿼리로 한정하는 것이 좋다.

또한 LIKE '%x'처럼 인덱스를 못 타는 조건이나, TEXT/BLOB 컬럼을 SELECT에 포함하면 커버링이 깨진다. 컬럼 순서도 중요해서 정렬·범위 조건을 무시하고 배치하면 Using index가 뜨더라도 정렬 비용이 남는다. 인덱스를 추가한 뒤에는 반드시 EXPLAIN과 실제 지연 시간, 쓰기 처리량을 함께 측정해 트레이드오프를 확인한다.