이주호 · 개발 블로그

10. 5만 행을 1행으로 — EXPLAIN 읽기와 인덱스

9 min read

핵심 한 줄

쿼리가 느리면 추측하지 않는다. **실행계획(EXPLAIN)**을 읽는다. 상품 하나를 찾으려 5만 행을 다 훑던 쿼리는 type=ALL, rows=49998로 자백한다. 인덱스 하나를 얹자 type=ref, rows=1 — 5만 행이 1행이 됐다. 성능은 “그럴 것이다”가 아니라 실행계획의 숫자로 판단한다.

시리즈 — 커머스 백엔드를 카타로 쌓다 (계속 연재)

00.카타 서문 · 01.돈을 primitive로 두면 언젠가 샌다 · 02.불변식은 누가 지키나 · 03.동시에 사면 초과판매된다 · 04.두 번 취소해도 한 번만 · 05.save가 INSERT가 아니었다 · 06.기다리게 하지 말고 거절하라 · 07.트랜잭션 안에서 HTTP를 부르지 마라 · 08.응답을 못 받았다는 실패가 아니다 · 09.요청 하나에 쿼리 1+N · 10.5만 행을 1행으로 · 11.같은 걸 두 번 묻지 않기 · 12.가짜는 내가 시킨 답만 한다 · 13.분명히 잡았는데 터졌다 · 14.5개 주세요 했는데 5만 개를 읽었다 · 15.순수하게 만들었더니 저장이 사라졌다 · 16.설정 한 줄 바꿨더니 세 번 터졌다 · … 그리고 계속

1. 쿼리 수를 1로 줄였는데도 느리다면

앞 편에서 fetch join으로 N+1을 잡았다. 요청 하나에 쿼리가 1+N개 나가던 것을 1개로 줄였다. 그런데 그 1개가 느리면? 목록은 한 번에 불러왔지만 그 한 방이 5만 행을 훑고 있다면?

여기서 흔한 실수는 코드를 노려보며 “인덱스를 걸면 빨라지겠지”라고 추측하는 것이다. 하지만 어떤 인덱스가 실제로 쓰이는지, 정말 쓰이긴 하는지는 코드가 말해주지 않는다. DB에게 직접 물어봐야 한다. 그게 EXPLAIN이다.

order_line 테이블에 5만 행(정확히는 49,998행 남짓)을 심어두고, 상품 이름으로 한 줄을 찾는 쿼리를 세워봤다.

EXPLAIN SELECT * FROM order_line WHERE product_name = 'prod-025000';

2. type 사다리 — DB가 자백하는 나쁨의 등급

EXPLAIN 결과에서 가장 먼저 볼 칸은 type이다. 이 쿼리가 데이터를 얼마나 무식하게 찾는지를 등급으로 알려준다. 왼쪽이 최악, 오른쪽이 최선이다.

ALL < index < range < ref < eq_ref < const
(풀스캔)              (범위)  (=매칭)        (상수)
  • ALL — 풀 테이블 스캔. 조건에 맞는 행을 찾으려 테이블 전체를 처음부터 끝까지 읽는다. 최악이다.
  • range — 인덱스를 타되 범위로 훑는다(>, BETWEEN, 접두 LIKE).
  • ref — 인덱스로 = 매칭. 원하는 값이 있는 자리로 바로 점프한다.
  • eq_ref / const — 유니크·PK로 한 건을 콕 집는다. 가장 빠르다.

type 옆에서 같이 봐야 할 세 칸:

  • key — 실제로 사용한 인덱스. NULL이면 인덱스를 못 썼다는 뜻이다.
  • rows — 이 쿼리가 읽을 것으로 추정한 행 수. 낮을수록 좋다.
  • Extra — 부가 정보. Using where(읽고 나서 거른다) / Using index(커버링 인덱스, 좋다) / Using filesort·Using temporary(정렬·임시테이블, 경고 신호).

3. 인덱스 없이 — 5만 행을 다 훑는다

인덱스를 걸지 않은 상태로 실행한 결과다.

typepossible_keyskeyrowsExtra
ALLNULLNULL49998Using where

읽어보자. type=ALL은 풀 테이블 스캔, key=NULL은 쓸 인덱스가 없음, rows=49998은 이 한 줄을 찾으려 5만 행 가까이를 전부 읽는다는 뜻이다. Using where는 그렇게 다 읽은 뒤에 조건으로 걸러낸다는 말이다.

상품 하나(prod-025000) 찾자고 창고 전체를 뒤지는 셈이다. 행이 500만이었으면 500만을 훑었을 것이다.

4. 인덱스 하나 — 5만이 1행이 된다

product_name에 인덱스를 건다.

CREATE INDEX idx_order_line_product_name ON order_line (product_name);

같은 EXPLAIN을 다시 돌린다.

typekeyrowsfiltered
refidx_order_line_product_name1100

type이 ALL에서 **ref**로, rows가 49998에서 **1**로 떨어졌다. key에 인덱스 이름이 찍혔으니 실제로 탔다는 증거다. 49,998행을 읽던 쿼리가 이제 1행만 읽는다.

인덱스는 책 뒤의 전화번호부다. 이름을 왼쪽부터 정렬해 두었기에, ‘ㄱ’을 찾을 때 첫 장부터 넘기지 않고 곧장 해당 자리로 점프한다. DB도 똑같이 product_name을 정렬해 두고, 원하는 값의 위치로 바로 뛴다.

5. 인덱스를 무력화하는 세 가지 — 왜 안 타는가

인덱스를 걸었다고 항상 타는 건 아니다. 인덱스는 컬럼의 원본값을 왼쪽부터 정렬한 것이다. 이 전제를 깨는 순간 정렬을 못 타고 다시 풀스캔으로 떨어진다.

(1) 값을 함수로 감싸면 — 원본이 아니다

EXPLAIN SELECT * FROM order_line WHERE UPPER(product_name) = 'PROD-025000';
typekey
ALLNULL

인덱스는 product_name의 원본값으로 정렬돼 있는데 UPPER(...)는 그 값을 변형해 버린다. 정렬된 원본과 대조할 수가 없으니 인덱스를 버리고 전부 읽는다.

(2) 앞을 비운 LIKE — 시작을 모른다

EXPLAIN SELECT * FROM order_line WHERE product_name LIKE '%025000';
typekey
ALLNULL

전화번호부는 앞 글자로 찾는다. 그런데 %025000은 앞이 무엇인지 모른 채 끝만 안다. 시작점을 못 잡으니 처음부터 다 넘기는 수밖에 없다.

(3) 접두 LIKE — 앞이 고정이면 탄다

EXPLAIN SELECT * FROM order_line WHERE product_name LIKE 'prod-025%';
typekeyExtra
rangeidx_order_line_product_nameUsing index condition

같은 LIKE인데 이번엔 앞(prod-025)이 고정이다. 시작점을 잡을 수 있으니 type=range로 인덱스를 탄다. 와일드카드가 어디 붙느냐가 전부를 가른다.

실무 처방:

  • 함수 조건(UPPER, DATE() 등)은 값을 애초에 맞춰 넣거나 함수 기반 인덱스를 쓴다.
  • 앞 와일드카드 검색(%키워드)은 인덱스로 안 된다. 풀텍스트 인덱스나 검색엔진(Elasticsearch 등)의 영역이다.

6. 원리 — 숫자로 판단한다

이 편의 핵심은 인덱스 문법이 아니다. 성능을 추측하지 않는 태도다.

“인덱스 걸면 빨라지겠지”는 가설이다. type=ALL → ref, rows=49998 → 1은 증거다. EXPLAIN은 DB가 실제로 어떻게 움직이는지 자백을 받아내는 도구고, 우리는 그 자백의 type과 rows로 판단한다. 서문에서 말한 “그럴 것이다가 아니라 이렇게 나왔다”가 여기서도 그대로다.

인덱스로 개별 조회를 5만 행에서 1행으로 줄였다. 그런데 만약 같은 값을 계속 다시 묻는다면? 아무리 1행짜리 조회여도 초당 수천 번이면 DB는 지친다. 가장 빠른 쿼리는 아예 던지지 않는 쿼리다. 다음 편은 안 묻는 법 — 캐시다.

→ 11.같은 걸 두 번 묻지 않기

그래프 뷰

백링크