큰 테이블의 진입점

특정 오류로 미정산 상태에 남은 원장 항목들을 뽑아야 했다. 원장 테이블이 억 단위 행이라 잘못 조회하면 잠금이 걸린다.

Open Table of contents

사전 준비 — 인덱스를 먼저 봤다
판단 기준 — 첫 조건의 선택도
조치 — 조건 순서를 정했다
검증 — 조인 대상의 커버링
결과 — 단계별로 줄어드는 건수
주의 — 전수 스캔과 인덱스 이름
정리

사전 준비 — 인덱스를 먼저 봤다

ledger_entries 에 쓸 만한 인덱스가 있는지부터 봤다.

SHOW INDEX FROM ledger_entries;

둘이 눈에 들어왔다.

(coin, entry_type, settled_at)
(settled_at, reverted_at, fail_status, executed_at)

coin 쪽과 settled_at 쪽 중 어디로 들어갈지를 먼저 정해야 했다.

SHOW INDEX 에 있다는 것만으로는 쓸 수 있는지 알 수 없다. 선행 컬럼이 실제로 얼마나 좁히는지가 그것을 정한다.

판단 기준 — 첫 조건의 선택도

두 번째 인덱스의 선행 컬럼인 settled_at 과 reverted_at 이 미정산 조건이었다.

WHERE settled_at IS NULL AND reverted_at IS NULL

이것으로 얼마나 좁혀지는지 셌다.

억 단위  →  수만

네 자릿수 넘게 줄어든다.

억 단위 행에서 첫 조건이 수만으로 좁히면 나머지는 그 안에서 필터하면 된다. coin 으로 들어가는 것 대신 settled_at IS NULL 쪽을 골랐다. 이유는 coin 은 종류가 열 남짓이라 한 값이 행의 십분의 일을 남기고, 미정산은 만분의 일을 남겨서다.

조치 — 조건 순서를 정했다

진입과 필터와 후처리를 나눠 배치했다.

SELECT ...
  FROM ledger_entries
 WHERE settled_at IS NULL          -- 진입
   AND reverted_at IS NULL         -- 진입
   AND coin = ?                    -- 필터
   AND created_at >= ?             -- 필터
   AND retry_count > ?             -- 필터
   AND last_error LIKE '%...%'     -- 후처리

미정산 조건을 앞에 두고 last_error 부분 일치는 맨 뒤에 뒀다.

LIKE 앞에 % 가 붙으면 인덱스를 못 타므로 가장 좁혀진 뒤에 걸어야 한다. MySQL 5.7 참고 매뉴얼의 B-Tree 인덱스 절이 와일드카드로 시작하지 않는 상수 문자열일 때만 LIKE 에 인덱스를 쓴다고 적는 그대로다. 조건이 어디에 놓이느냐에 따라 읽는 행 수가 자릿수만큼 달라진다.

검증 — 조인 대상의 커버링

이 결과를 다른 표와 조인해야 했다.

(member_no, coin, address)

wallet_addresses 는 수천만 행대인데 필요한 컬럼이 인덱스 안에 다 있었다.

JOIN wallet_addresses a
  ON a.member_no = l.member_no AND a.coin = l.coin

커버링 인덱스라 본체를 안 읽고 처리된다. 같은 매뉴얼의 EXPLAIN 출력 절은 이것을 Extra 의 Using index 로 표시한다고 적는다 — 인덱스 트리만으로 컬럼 정보를 얻어 실제 행을 읽는 추가 탐색이 없다는 뜻이다.

조인 대상의 인덱스가 무엇을 담고 있는지도 조회 설계에 들어간다. 인덱스만 읽고 끝나는 것과 본체까지 읽는 것은 비용이 아주 다르다.

결과 — 단계별로 줄어드는 건수

전체 흐름의 건수 변화를 봤다.

억 단위
  ↓ 미정산 조건
수만
  ↓ 코인 조건
그 코인 몫 (거의 그대로)
  ↓ 나머지 필터 + 부분 일치
수천

억 단위 에서 수천 까지 단계마다 얼마나 줄어드는지가 그대로 나온다.

예상보다 안 줄어드는 단계가 있으면 그 조건의 선택도가 낮다는 뜻이다. 코인 조건이 거의 못 줄인 것은 미정산이 그 코인에 몰려 있어서였고, 각 단계의 건수를 알면 어디가 병목인지가 그 자리에서 갈렸다.

주의 — 전수 스캔과 인덱스 이름

이 확인을 왜 했는지가 중요했다.

ledger_entries 에 전수 스캔이 걸리면 잠금 위험이 있다. 운영 DB 라 체결과 입출금 쿼리들이 그 뒤에 밀리므로 실행 전에 진입점을 확정해야 한다.

인덱스 이름도 힌트를 줬다.

retry_fixable_...
idx_..._settle_queue

retry_fixable 과 settle_queue 라는 말이 그 용도를 그대로 드러낸다.

다만 이름은 짐작이라 실행 계획으로 확인했다. 이름이 그럴듯한데 실제로는 안 쓰이는 경우가 있다.

절차도 적어 두었다.

1. 인덱스 목록을 본다
2. 각 인덱스의 선행 컬럼으로 얼마나 좁혀지나 센다
3. 가장 많이 좁히는 것을 진입점으로 정한다
4. 조건을 진입 / 필터 / 후처리로 나눠 배치한다
5. 조인 대상의 커버링 여부를 본다
6. 실행 계획으로 확인한다

둘째가 핵심인데 인덱스가 있어도 선택도가 낮으면 소용없기 때문이다.

정리

큰 테이블은 진입점을 먼저 확정한다
인덱스가 있어도 선행 컬럼의 선택도가 낮으면 소용없다
가장 많이 좁히는 조건을 진입점으로 정한다
조건을 진입과 필터와 후처리로 나눠 배치한다
부분 일치는 가장 좁혀진 뒤에 건다
조인 대상에 커버링 인덱스가 있으면 본체를 안 읽는다
단계별 건수를 보면 어디가 병목인지 안다
인덱스 이름이 용도의 힌트지만 실행 계획으로 확인한다
운영 DB 의 큰 테이블은 전수 스캔이 잠금 위험이다