인덱스가 있는데도 느린 쿼리와 복합·커버링 인덱스

같은 목록 쿼리가 계약 2천 건짜리 사이트에선 2.2ms, 20만 건짜리 사이트에선 226ms가 걸렸습니다. site_id 단일 인덱스로 사이트의 계약을 전부 읽은 뒤 상태·유형으로 거르고, 남은 4만 8천 건을 정렬해서 100건만 쓰고 있었기 때문입니다. (site_id, status, contract_type, created_at) 복합 인덱스 하나로 정렬까지 인덱스에 맡기자 큰 사이트가 226ms에서 0.8ms가 됐고, 읽는 행도 20만 건에서 정확히 100건이 됐습니다. 커버링 인덱스로 한 단계 더 가 봤지만 0.6ms에서 0.6ms로 차이가 없었고, 인덱스 크기만 131MB에서 199MB로 늘었습니다.

시작하며

목록 화면이 느리다는 이야기를 들으면 “인덱스는 걸려 있는데요”에서 대화가 멈추는 경우가 많습니다. 인덱스가 있는데도 느린 쿼리는 어떤 모양인지, 그리고 흔히 다음 단계로 꼽는 커버링 인덱스가 실제로 얼마나 도움이 되는지 궁금했습니다.

그래서 임대 계약 목록과 비슷한 모양의 테이블을 직접 만들어 재 봤습니다. 이 글의 숫자는 전부 아래 환경에서 측정한 값입니다.

운영 환경처럼 디스크에서 처음 읽는 경우라면 숫자가 더 커집니다. 여기서는 인덱스 구성 사이의 차이를 봐 주시면 됩니다.

같은 쿼리, 사이트에 따라 100배

SELECT c.*, r.name, r.phone
FROM contract c
JOIN resident r ON c.resident_id = r.id
WHERE c.site_id = ?
  AND c.status = 'ACTIVE'
  AND c.contract_type = 'LONG_TERM'
ORDER BY c.created_at DESC
LIMIT 100;

처음에는 site_id 단일 인덱스만 있습니다.

같은 쿼리인데 100배 차이가 납니다. 작은 사이트만 보고 테스트했다면 “빠른 쿼리”로 넘어갔을 겁니다.

EXPLAIN은 괜찮아 보였습니다

type=ref  key=idx_site_id  rows=350352  filtered=1.0
Extra: Using where; Using filesort

type이 ALL이 아니라 ref입니다. “인덱스는 탄다”는 뜻이라 처음 보면 문제가 없어 보입니다. 단서는 나머지 칸에 있었습니다.

• rows=350352: 이만큼 읽을 거라는 추정입니다. 실제로는 20만 건이었지만, 어느 쪽이든 100건을 얻으려고 읽기엔 너무 많습니다
• Using filesort: 인덱스 순서로 꺼낼 수 없어서 따로 정렬합니다

EXPLAIN ANALYZE로 실제 실행을 찍어 보면 시간이 어디서 나가는지 보입니다.

-- 조인 줄은 빼고 c 테이블 쪽만 남겼습니다
-> Limit: 100 row(s)                                          (actual time=228..228 rows=100)
    -> Sort: c.created_at DESC                               (actual time=228..228 rows=100)
        -> Filter: (contract_type = 'LONG_TERM' and status = 'ACTIVE')
                                                             (actual time=0.09..212 rows=47922)
            -> Index lookup on c using idx_site_id (site_id=42)
                                                             (actual time=0.09..194 rows=200000)

인덱스로 42번 사이트 20만 건을 찾아 행을 읽는 데 194ms, 상태·유형으로 4만 8천 건까지 거르는 데까지 212ms가 걸렸고, 그걸 정렬해서 100건만 씁니다. 시간의 대부분은 버릴 행을 읽는 데 들어갔습니다.

복합 인덱스: 등호 조건 먼저, 정렬 컬럼 마지막

CREATE INDEX idx_contract_composite
ON contract (site_id, status, contract_type, created_at);

세 조건이 모두 등호(=)라서 앞에 두고, ORDER BY에 쓰는 created_at을 마지막에 뒀습니다. 이렇게 하면 “42번 사이트의 ACTIVE·LONG_TERM 계약”이 인덱스 안에서 이미 created_at 순으로 줄지어 있습니다. MySQL은 그 줄의 끝에서부터 100건만 읽고 멈춥니다.

type=ref  key=idx_contract_composite  filtered=100.0
Extra: Backward index scan

-> Index lookup on c using idx_contract_composite (site_id=42, status='ACTIVE', contract_type='LONG_TERM') (reverse)
                                                             (actual time=0.046..0.098 rows=100)

Using filesort가 사라지고 Backward index scan이 생겼습니다. 읽은 행은 정확히 100건입니다.

사이트 크기와 상관없이 1ms 안쪽이 됐습니다. 읽는 양이 사이트 크기가 아니라 LIMIT에 묶였기 때문입니다.

순서가 왜 중요한지는 반대로 생각하면 보입니다. created_at을 맨 앞에 두면 날짜 순으로는 정렬돼 있지만, 사이트·상태·유형이 섞여 있어서 조건에 맞는 100건이 나올 때까지 훑어야 합니다.

커버링 인덱스까지 가 봤습니다

조인과 화면에 필요한 컬럼(resident_id, contract_no)까지 인덱스에 넣으면 테이블을 아예 안 읽을 수 있습니다. 조회 컬럼도 SELECT c.* 대신 필요한 것만 남겼습니다.

CREATE INDEX idx_contract_covering
ON contract (site_id, status, contract_type, created_at, resident_id, contract_no);

Extra: Backward index scan; Using index
-> Covering index lookup on c using idx_contract_covering (...) (reverse)  (actual time=0.017..0.044 rows=100)

Using index가 떴으니 의도대로 됐습니다. 그런데 숫자는 거의 그대로였습니다.

이유는 LIMIT 100입니다. 복합 인덱스로도 테이블에서 읽는 행이 100건뿐이라, 그 100번의 조회를 아껴 봐야 아낄 게 거의 없습니다. 커버링 인덱스가 힘을 쓰는 건 인덱스로 많은 행을 읽은 뒤 테이블까지 가야 하는 경우입니다. 이번 쿼리는 이미 그 문제가 사라진 상태였습니다.

인덱스의 비용

인덱스는 쓰기마다 같이 갱신됩니다. 같은 조건에서 계약 2만 건을 500건씩 나눠 넣어 봤습니다(3회 중앙값).

7%는 크지 않지만, 이번 쿼리에선 아무것도 얻지 못하고 내는 비용입니다. 디스크도 68MB를 더 씁니다.

site_id 단일 인덱스만으로는 데이터가 한쪽에 몰리면 무너집니다(큰 사이트 226ms). 복합 인덱스(등호 → 정렬)는 0.5~0.8ms로 충분했습니다. 커버링 인덱스는 필요한 컬럼만 조회할 때 0.4~0.6ms가 나왔지만 인덱스가 68MB 늘고 쓰기가 7% 느려져서, 이 쿼리에는 과했습니다. 복합 인덱스와 커버링 인덱스를 둘 다 두는 것은 한쪽이 다른 쪽의 앞부분이라 중복입니다.

중복 인덱스는 실제로 자주 보는 실수입니다. (a, b, c, d) 인덱스가 있는데 (a, b, c, d, e, f)를 하나 더 만들면, 앞의 것은 뒤의 것으로 대체됩니다. 둘 다 두면 쓰기 비용만 두 배로 냅니다.

마무리

2천 건짜리 사이트에서는 문제가 없던 쿼리가 20만 건짜리 사이트에서 100배 느려졌으니, 목록 쿼리는 데이터가 가장 몰린 사이트로 재 봐야 합니다. type=ref여도 rows가 크고 Using filesort가 있으면 버릴 행을 읽고 있는 것이고, 어느 단계에서 몇 건을 읽는지는 EXPLAIN ANALYZE로 확인할 수 있습니다. 커버링 인덱스는 LIMIT이 작으면 효과가 거의 없을 수 있어서, 복합 인덱스로 226ms가 0.8ms가 된 뒤에는 인덱스를 하나 더 만들기 전에 한 번 더 재 보는 편이 쌌습니다.

이번에는 버퍼 풀에 데이터가 올라온 상태에서만 쟀습니다. 디스크에서 처음 읽는 경우는 재 보지 않았습니다.

읽어주셔서 감사합니다.🖐

참고:

MySQL 8.0 EXPLAIN 출력 형식
MySQL 8.0 ORDER BY 최적화
Use The Index, Luke