큰 테이블의 진입점

특정 오류가 난 작업들을 뽑아야 했다. 작업 테이블이 2억 행이라 잘못 조회하면 잠금이 걸린다.

Open Table of contents

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

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

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

SHOW INDEX FROM distribution_product_jobs;

둘이 눈에 들어왔다.

(channel, job, completed_at)
(completed_at, deleted_at, fail_status, executed_at)

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

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

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

두 번째 인덱스의 선행 컬럼인 completed_at 과 deleted_at 이 미완료 조건이었다.

WHERE completed_at IS NULL AND deleted_at IS NULL

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

2억  →  약 4.5만

4천 배 넘게 줄어든다.

2억 행에서 첫 조건이 4.5만으로 좁히면 나머지는 그 안에서 필터하면 된다. completed_at IS NULL 쪽이 이 조회의 최적 진입점이었다.

조치 — 조건 순서를 정했다

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

SELECT ...
  FROM distribution_product_jobs
 WHERE completed_at IS NULL        -- 진입
   AND deleted_at IS NULL          -- 진입
   AND channel = ?                 -- 필터
   AND created_at >= ?             -- 필터
   AND attempt_count > ?           -- 필터
   AND last_log LIKE '%...%'       -- 후처리

미완료 조건을 앞에 두고 last_log 부분 일치는 맨 뒤에 뒀다.

LIKE 앞에 % 가 붙으면 인덱스를 못 타므로 가장 좁혀진 뒤에 걸어야 한다. 조건이 어디에 놓이느냐에 따라 읽는 행 수가 자릿수만큼 달라진다.

검증 — 조인 대상의 커버링

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

(product_id, channel_product_id, deleted_at)

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

JOIN distribution_products dp
  ON dp.product_id = j.product_id AND dp.channel = j.channel

커버링 인덱스라 본체를 안 읽고 처리된다.

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

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

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

2억
  ↓ 미완료 조건
4.5만
  ↓ 채널 조건
약 4.6만 (그 채널)
  ↓ 나머지 필터 + 부분 일치
약 2,900

2억 에서 2,900 까지 단계마다 얼마나 줄어드는지가 그대로 나온다.

예상보다 안 줄어드는 단계가 있으면 그 조건의 선택도가 낮다는 뜻이다. 각 단계의 건수를 알면 어디가 병목인지가 그 자리에서 갈렸다.

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

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

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

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

retry_fixable_...
idx_..._queue

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

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

절차도 적어 두었다.

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

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

정리

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