인덱스를 추가하기 전에 먼저 확인했던 조회 패턴

쿼리가 느려지기 시작하면 가장 먼저 떠오르는 해결책은 보통 인덱스입니다.
저도 한동안은 그랬습니다. 실행 시간이 길면 "이 컬럼에 인덱스 하나 걸면 되겠지"라고 생각했습니다.

그런데 실무에서는 그렇게 단순하게 풀리지 않는 경우가 많았습니다.
인덱스를 추가했는데도 기대만큼 빨라지지 않거나, 반대로 쓰기 성능만 나빠지는 경우도 있었습니다.

결국 느린 쿼리를 볼 때는 인덱스 자체보다, 어떤 조회 패턴으로 데이터가 읽히는지를 먼저 보는 쪽으로 습관이 바뀌었습니다. 그 과정에서 가장 도움이 됐던 건 EXPLAIN 출력을 인덱스 추가 전후로 직접 비교해 보는 일이었습니다.

처음엔 where 절 컬럼만 보고 인덱스를 생각했다

예전에는 이런 식으로 봤습니다.

사용자별 최근 주문 목록 쿼리

select *
from orders
where user_id = ?
order by created_at desc
limit 20;

그러면 바로 user_id에 인덱스를 추가하려고 했습니다.
물론 틀린 접근은 아니지만, 실제로는 그것만으로 부족한 경우가 많았습니다.

이 쿼리는 단순히 where user_id = ?만 보는 게 아니라,

어떤 조건이 함께 들어오는지
정렬은 무엇인지
limit이 붙는지
조인 뒤에 필터링하는지

를 같이 봐야 했습니다.

느린 쿼리 한 사이클을 그대로 따라가 봤다

말로만 "실행 계획을 본다"라고 하면 와닿지 않아서, 위 쿼리 하나를 인덱스 추가 전후로 한 바퀴 돌려본 흐름을 그대로 적어 둡니다.
먼저 인덱스가 없는 상태에서 EXPLAIN을 찍어 봤습니다.

실행 계획 확인

EXPLAIN
select *
from orders
where user_id = ?
order by created_at desc
limit 20;

출력은 대략 아래와 같은 형태였습니다. 행 수 같은 값은 환경마다 다르니, 숫자보다는 컬럼이 어떤 모양으로 나오는지만 봐 주시면 됩니다.

EXPLAIN 출력 형태 (인덱스 추가 전, 값은 예시)

+----+-------------+--------+------+---------------+------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | rows | Extra                       |
+----+-------------+--------+------+---------------+------+------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL |  ... | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+------+-----------------------------+

여기서 제 눈에 먼저 들어온 건 type = ALL과 Extra의 Using filesort였습니다.
풀 테이블 스캔으로 다 읽은 뒤, 정렬을 위해 별도의 정렬 단계까지 한 번 더 거친다는 신호였기 때문입니다.

실제로 읽히는 양과 시간이 궁금할 때는 EXPLAIN ANALYZE를 함께 봤습니다. 이건 추정이 아니라 실제로 한 번 실행해 보고 시간을 측정해 주는 쪽입니다.

EXPLAIN ANALYZE 출력 형태 (인덱스 추가 전, 시간·행수는 환경별로 다름)

-> Limit: 20 row(s)  (actual time=... rows=... loops=1)
    -> Sort: orders.created_at DESC, limit input to 20 row(s)  (actual time=... rows=... loops=1)
        -> Filter: (orders.user_id = ?)  (cost=... rows=...)
            -> Table scan on orders  (cost=... rows=...) (actual time=... rows=... loops=1)

Table scan on orders 아래에서 위로 읽으면, 전체 스캔 → 필터 → 정렬 → limit 순서로 비용이 쌓이는 구조가 보였습니다.
이 모양을 보고 나서야 "필터 컬럼 하나에만 인덱스"가 아니라, 정렬까지 같이 처리할 인덱스가 필요하다는 걸 받아들였습니다.

복합 인덱스를 만들고 다시 실행 계획을 봤다

그래서 user_id로 거른 뒤 created_at 순서로 바로 읽을 수 있도록, 두 컬럼을 묶은 복합 인덱스를 만들었습니다.

복합 인덱스 추가

CREATE INDEX idx_orders_user_created
    ON orders (user_id, created_at);

그리고 같은 쿼리로 다시 EXPLAIN을 찍었습니다.

EXPLAIN 출력 형태 (인덱스 추가 후, 값은 예시)

+----+-------------+--------+------+-------------------------+-------------------------+------+-------------+
| id | select_type | table  | type | possible_keys           | key                     | rows | Extra       |
+----+-------------+--------+------+-------------------------+-------------------------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_orders_user_created | idx_orders_user_created |  ... | Using where |
+----+-------------+--------+------+-------------------------+-------------------------+------+-------------+

달라진 지점은 세 군데였습니다.

type이 ALL → ref로 바뀌었다: 전체 스캔이 아니라 인덱스로 해당 사용자 행만 찾아 들어간다는 뜻입니다.
key에 idx_orders_user_created가 잡혔다: 실제로 그 인덱스를 탔다는 의미입니다.
Extra에서 Using filesort가 사라졌다: 인덱스가 이미 created_at 순서대로 정렬돼 있어서 별도 정렬 단계가 필요 없어졌습니다.

order by created_at desc인데도 오름차순 인덱스로 정렬이 풀리는 건, 엔진이 인덱스를 역방향으로 스캔할 수 있기 때문입니다.
"인덱스가 있다"와 "그 인덱스를 잘 탄다"가 다른 이야기라는 걸, 이 key와 Extra 컬럼에서 매번 확인하게 됐습니다.

EXPLAIN 출력 컬럼은 이렇게 읽었다

표가 익숙해지기 전까지는 어떤 컬럼을 먼저 봐야 할지 헷갈렸습니다. 제가 실제로 의지하는 컬럼은 네 개 정도였습니다.

type: 접근 방식입니다. 대략 ALL(풀 스캔) → index → range → ref → eq_ref → const 순으로 오른쪽으로 갈수록 좋습니다. ALL이 보이면 일단 의심했습니다.
key: 옵티마이저가 실제로 선택한 인덱스입니다. possible_keys에는 후보가 있는데 key가 NULL이면, 인덱스는 있는데 안 탄다는 신호였습니다.
rows: 이 단계에서 읽을 것으로 추정한 행 수입니다. 추정치라 정확하진 않지만, 인덱스 전후로 자릿수가 줄어드는지를 보는 용도로 충분했습니다.
Extra: 부가 동작입니다. Using filesort, Using temporary, Using index 같은 문구가 병목과 직접 연결돼서, 사실 제일 먼저 보게 되는 컬럼이었습니다.

복합 인덱스 순서는 선택도와 카디널리티로 정했다

복합 인덱스에서 가장 자주 틀렸던 건 컬럼 순서였습니다.
(user_id, created_at)과 (created_at, user_id)는 이름만 비슷할 뿐 전혀 다르게 동작했습니다.

제가 정리한 기준은 단순합니다.

등치(=) 조건 컬럼을 앞에 둔다. where user_id = ?처럼 한 값으로 콕 찍는 컬럼을 선두에 둬야, 그다음 컬럼이 정렬된 상태로 따라옵니다.
정렬·범위 컬럼을 뒤에 둔다. order by created_at 또는 created_at > ? 같은 컬럼은 선두 컬럼으로 좁힌 다음 순서대로 읽도록 뒤에 둡니다.
선택도(카디널리티)가 높은 컬럼을 우선 고려한다. 같은 등치 조건이 여러 개라면, 중복 값이 적어 한 번에 더 많이 걸러 주는 컬럼을 앞쪽에 두는 편이 유리했습니다.

user_id는 한 값으로 등치 비교하고, created_at은 그 안에서 정렬하니 자연스럽게 (user_id, created_at) 순서가 됐습니다.
순서를 거꾸로 뒀다면 선두 컬럼인 created_at이 정렬엔 쓰여도 user_id 필터를 좁혀 주지 못해, 다시 Using filesort나 넓은 스캔으로 돌아갔을 겁니다.

커버링 인덱스로 테이블 접근까지 줄였다

여기서 한 걸음 더 들어가면 커버링 인덱스가 있습니다.
쿼리가 필요로 하는 컬럼이 전부 인덱스 안에 들어 있으면, 엔진은 인덱스만 읽고 실제 테이블 행은 들여다보지 않아도 됩니다. 이때 Extra에 Using index가 뜹니다.

예를 들어 목록에서 id와 created_at만 보여 주면 된다면, 굳이 select *로 모든 컬럼을 끌어오지 않아도 됩니다.

커버링 인덱스를 노린 가벼운 조회

select id, created_at
from orders
where user_id = ?
order by created_at desc
limit 20;

InnoDB에서는 보조 인덱스 리프에 기본키(id)가 함께 들어 있어서, (user_id, created_at) 인덱스만으로도 위 컬럼들이 모두 커버됐습니다.
그래서 select *를 줄이는 것만으로 Extra가 Using where에서 Using index로 바뀌고, 테이블로 한 번 더 들어가는 비용이 사라지는 경우가 있었습니다.

Using filesort와 Using temporary는 그냥 넘기지 않았다

Extra에 자주 등장하면서 비용이 큰 두 신호는 따로 외워 뒀습니다.

Using filesort: 인덱스 순서와 order by가 맞지 않아, 결과를 별도로 정렬한다는 뜻입니다. 디스크 정렬을 항상 의미하진 않지만, 정렬 컬럼을 인덱스 뒤쪽에 포함시키면 사라지는 경우가 많았습니다.
Using temporary: 임시 테이블을 만든다는 신호입니다. group by나 distinct, 또는 group by와 다른 컬럼으로 정렬할 때 자주 나왔고, 이게 보이면 인덱스보다 쿼리 구조 자체를 다시 봐야 할 때가 많았습니다.

두 문구는 "인덱스를 탔는지"와는 별개로 추가 비용이 붙는 자리라서, type이 괜찮아 보여도 Extra까지 확인하는 습관이 생겼습니다.

Before/After를 한 표로 정리해 둔다

같은 쿼리를 인덱스 전후로 비교한 내용은 매번 이런 표로 남겨 둡니다. 정성적인 변화를 먼저 적고, 실제 수치는 측정해서 따로 채우는 식입니다.

항목	인덱스 전	인덱스 후
접근 방식(type)	풀 테이블 스캔에 가까움	user_id로 ref 접근
정렬 처리(Extra)	Using filesort 발생	인덱스 순서로 정렬 해소
스캔 행 수(rows)	조건과 무관하게 크게 잡힘	해당 사용자 주문 범위로 축소
체감 응답	목록이 늦게 떴음	목록이 바로 떴음

수치 자리를 비워 두는 건, 환경마다 데이터 양과 분포가 달라서 한 번 잰 숫자를 일반화하면 오히려 오해를 부르기 때문입니다.
그래서 표에는 방향성만 적고, 실제 ms와 rows는 직접 측정한 환경에서만 채우도록 남겨 둡니다.

정렬 조건을 같이 보지 않으면 체감이 작았다

조회가 느린데 필터 컬럼만 보고 인덱스를 넣으면, 정렬 단계에서 다시 비용이 커지는 경우가 있었습니다.

예를 들어 사용자별 최근 주문 목록이라면,

where user_id = ?
order by created_at desc

를 같이 보게 됩니다.

이 경우 실무에서는 단일 인덱스보다 복합 인덱스를 먼저 검토하는 편이 낫습니다.
필터와 정렬이 같이 자주 쓰이는 패턴이라면, 조회 체감 차이가 꽤 컸습니다.
이런 정렬 조건이 붙는 목록은 페이징 API에서 정렬 기준을 고정하며 고민했던 부분과도 자연스럽게 이어졌습니다.

select 절과 조인 구조도 같이 본다

인덱스를 추가해도 여전히 느릴 때는, 결국 읽어오는 데이터 양이 너무 많았던 경우가 많았습니다.

select *로 불필요한 컬럼까지 읽고 있는지
조인이 너무 이른 단계에서 일어나는지
조건 필터보다 조인이 먼저 커지는지

실무에서는 인덱스를 넣기 전에 쿼리 자체를 더 가볍게 만들 수 있는 경우가 꽤 있었습니다.
오히려 이쪽이 더 큰 효과를 내는 경우도 많았습니다.
조인이 많은 구조라면 JPA N+1 문제를 찾고 해결한 과정에서 정리한 것처럼, 인덱스보다 쿼리 구조를 먼저 손보는 편이 효과적일 때가 있었습니다.

중복 인덱스도 생각보다 자주 생긴다

급하게 대응하다 보면 비슷한 인덱스가 여러 개 생기기도 합니다.

idx_orders_user_id
idx_orders_user_id_created_at

이런 식으로 추가하다 보면, 실제로는 하나로 충분한데 관리 대상만 늘어나는 경우가 있었습니다.
복합 인덱스 (user_id, created_at)이 있으면 선두 컬럼만 쓰는 user_id 단독 조회도 대부분 커버되기 때문에, idx_orders_user_id는 중복일 가능성이 높았습니다.
쓰기 비용과 저장 비용도 무시할 수 없어서, 인덱스는 늘릴수록 좋다는 생각은 위험했습니다.

지금은 이렇게 확인한다

인덱스를 추가하기 전에는 보통 아래 순서로 봅니다.

느린 쿼리가 어느 화면에서 반복되는가
where, order by, limit 조합이 어떻게 되는가
EXPLAIN의 type·key·Extra에서 어디가 병목인가
복합 인덱스라면 등치·정렬·선택도 기준으로 컬럼 순서를 잡았는가
인덱스보다 쿼리 구조(select 범위, 조인 시점)를 먼저 줄일 수 있는가
새 인덱스가 기존 인덱스와 겹치지 않는가

이 순서를 거치고 나면 "무조건 인덱스 추가"보다 훨씬 덜 흔들리게 됩니다.

마무리

실무에서 인덱스는 느린 쿼리를 고치는 도구이기도 했지만, 그보다 조회 패턴을 이해했는지 확인하는 지표에 가까웠습니다.

결국 쿼리를 빠르게 만드는 건 인덱스 하나를 추가하는 행위보다,
어떤 화면이 어떤 방식으로 데이터를 읽는지 먼저 이해하고, 그 이해를 EXPLAIN 출력으로 확인하는 데서 시작된다고 느꼈습니다.