슬로우 쿼리 개선하기 - index

서론

실무에서 인덱스/캐시/MV(Materialized View) 등의 성능 개선을 고민하게 되는 지점은
대부분 슬로우 쿼리 발생 시점이다.

이번 글에서는 상품 데이터 20만 건을 기준으로,
브랜드 필터 + 좋아요 순 정렬 조회 쿼리의 실행 계획을 분석하고, 인덱스 최적화를 수행한 과정을 정리한다.

EXPLAIN은 DB 옵티마이저가 '해당 쿼리를 어떤 방식으로 처리할 예정인지' 보여주는 도구다.

1. 실행 계획 확인 및 분석

// 상품 목록 조회 쿼리
SELECT * FROM products                                                                               
WHERE deleted_at IS NULL AND visibility = 'VISIBLE' AND brand_id = 1                                                          
ORDER BY like_count DESC LIMIT 20; 

해당 쿼리로 explain 실행

결과는 이런식으로 읽으면 된다.

• possible_keys = NULL → 지금 쿼리에 쓸 만한 인덱스가 없음
• type = ALL → 풀 테이블 스캔
• rows = 199176
  → MySQL 옵티마이저가 읽어야 할 것으로 예상하는 row수(현재 product 테이블 전체 row수와 비슷함)
• key = NULL → 사용한 인덱스 없음
• filtered = 0.50% → 필터링되고 남은 레코드 비율(읽은 것 대비 실제 필요한 데이터가 극히 적음)
• Extra = Using where; Using filesort → 조건 걸러내고, 정렬을 위해 별도 작업(비용) 필요
  (위 두 개가 같이 나오면 '읽기도 비효율, 정렬도 비효율'인 것)

특히, Extra 컬럼에 Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다. 해당 코멘트는 order by 처리가 적절한 인덱스를 사용하지 못 할때만 표시된다. (Real MySQL 8.0 참고)

따라서, 조회 성능 개선을 위해 적절한 인덱스 설계가 필요하다는 것을 알 수 있다.

보통 실행 계획 확인시, Extra 컬럼에서 중요하게 봐야할 네 가지가 있다.
1. Using where
2. Using index
3. Using filesort
4. Using temporary

1, 2번은 위에서 살펴봤다.

3번 Using index는 인덱스만 읽어서 쿼리를 처리했다는 의미이며 보통 성능에 좋은 신호이다.
하지만, type=index이고, rows수가 크다면 index full scan일 수도 있다는 점도 알아두자.

4번 Using temporary는 쿼리 처리 과정에서 임시 테이블을 생성해 작업을 수행했다는 의미이다.
보통 GROUP BY, DISTINCT, ORDER BY 등이 적절한 인덱스를 활용하지 못할 때 발생하며, 이 경우 추가적인 메모리 사용이나 디스크 I/O가 발생할 수 있다. 따라서 Using temporary가 나타나는 경우에도 쿼리 구조나 인덱스 설계를 다시 점검해보는 것이 좋다.

즉, 현재 조회 쿼리는 대충 이런 흐름인 것이다:

전체 상품 레코드를 거의 다 읽음
→ deleted_at, visibility, brand_id로 걸러냄
→ 남은 결과를 like_count DESC로 다시 정렬 → 20개만 잘라서 반환

그렇다면 어떤 인덱스를 추가하면 좋을까?

2. 인덱스 후보 도출

이 쿼리의 핵심은 visibility, deleted_at, brand_id 조건으로 대상을 좁힌 뒤,
그 결과를 like_count DESC로 정렬해 상위 20건만 빠르게 가져오는 것이다.

즉, 단순히 WHERE 조건만 처리하는 인덱스가 아니라
정렬 비용까지 함께 줄일 수 있는 방향으로 인덱스를 설계해야 한다.

우선 WHERE 절에 사용된 조건들을 보면 모두 동등 비교(=, IS NULL) 조건이다.

• brand_id = 1
• visibility = 'VISIBLE'
• deleted_at IS NULL

반면 like_count DESC는 정렬 조건이다.

일반적으로 복합 인덱스를 설계할 때는 동등 조건 컬럼 -> 정렬 컬럼 순서로 배치하는 경우가 많다.
동등 조건은 인덱스 탐색 단계에서 읽어야 할 레코드 범위를 먼저 좁혀줄 수 있기 때문이다.
이렇게 범위를 줄인 뒤 정렬 컬럼을 활용하면 ORDER BY 처리 비용까지 함께 줄일 수 있다.

visibility = 'VISIBLE', deleted_at IS NULL은 전체 데이터에서 대부분을 차지할 가능성이 높아 단독 조건으로는 범위를 크게 줄이지 못할 수 있다.
반면에 brand_id는 특정 브랜드 상품만 조회하는 경우, 조회 범위를 비교적 효과적으로 좁혀줄 가능성이 있다.

따라서 첫 번째 인덱스 후보로는 아래와 같은 형태를 생각해볼 수 있다.

CREATE INDEX idx_products_brand_visible_deleted_like_count
ON products (brand_id, visibility, deleted_at, like_count DESC);

Q. 인덱스 생성시 DESC 붙이면 뭐가 다른가?

MySQL의 B-Tree 인덱스는 기본적으로 오름차순(ASC)으로 저장되지만,
쿼리에서 ORDER BY ... DESC가 사용되더라도 인덱스를 역방향으로 스캔(backward index scan)할 수 있다. 따라서, 단일 정렬 컬럼의 경우 DESC 키워드를 붙인 인덱스를 별도로 생성하지 않아도 정렬을 위해 filesort가 발생하지 않을 수 있다.
MySQL 8.0부터는 INDEX(column DESC) 문법이 지원되지만, 이는 주로 복합 인덱스에서 정렬 방향이 혼합된 경우에 의미가 있다.

---

2-1. 인덱스 내 brand_id 위치 결정

그런데, 여기서 한 가지 고민이 생겼다.
상품 조회 쿼리에서 brand_id 파라미터는 필수가 아닌 선택 조건이다.
사실 실제 이커머스 환경을 떠올려봐도, 사용자가 상품 조회 시 처음부터 브랜드 필터를 거는 경우보다는 다음과 같은 패턴이 훨씬 자주 사용된다.

• (브랜드 상관없이) 전체 상품 조회
• 카테고리 필터(아우터, 상의, 신발 등)
• 정렬 옵션 변경

즉, brand_id를 선두에 둔 인덱스는 브랜드 필터가 포함된 조회에는 유리하지만
브랜드 조건이 없는 조회에서는 인덱스를 아예 활용하지 못할 수 있다.

그렇다고 인덱스에 brand_id를 배제하기에는,

• 어드민 상품 관리 화면
• 특정 브랜드 상품 조회
  처럼 브랜드 필터가 자주 사용되는 맥락도 분명히 존재한다.

그럼 선택지는 두 가지로 정리할 수 있다.

Option A. brand_id를 앞에 위치시켜, 브랜드 필터가 포함된 조회에서 강력하게 범위를 좁힌다.
Option B. brand_id를 복합 인덱스 뒤 쪽에 위치시켜, 브랜드 필터가 있을 때만 추가 활용하도록 한다.

---

2-2. brand_id를 선두에 둘 때의 트레이드오프

brand_id를 선두에 두면 한 가지 문제가 생긴다.

MySQL의 B-Tree 인덱스는 선두 컬럼부터 순서대로만 활용할 수 있기 때문에,
brand_id 조건이 없는 쿼리에서는 해당 인덱스를 사용할 수 없다.

그런데 중요한 점은, 상품을 조회하는 모든 쿼리에는 항상 다음 조건이 포함된다는 것이다.

	WHERE deleted_at IS NULL AND visibility = 'VISIBLE'

사용자에게는 삭제되지 않았고, 노출 중인 상품만 반환해야 하기 때문이다.
이건 선택적 필터가 아닌 필수 기본 필터다.

결국 Option A를 선택한다면, 이 기본 필터를 처리할 인덱스를 별도로 추가해야 한다.

  (brand_id, deleted_at, visibility)  -- brand 필터 있는 쿼리용
  (deleted_at, visibility)            -- brand 필터 없는 쿼리용 (추가 필요)

반면 Option B는 인덱스 하나로 두 가지 조회 패턴을 모두 처리할 수 있다.
brand_id가 없으면 앞선 두 컬럼의 프리픽스(deleted_at, visibility)를, 있으면 세 컬럼 모두를 활용한다.
인덱스를 두 개로 늘리지 않고도 두 케이스를 모두 커버할 수 있으므로 Option B를 선택했다.

---

2-3. 최종 인덱스 생성 후보

	(deleted_at, visibility, brand_id)  -- brand 필터 유무 무관하게 커버
    (deleted_at, visibility, price)     -- 가격순 정렬
  	(deleted_at, visibility, like_count DESC)  -- 좋아요순 정렬(생성 X)

결과적으로 마지막 인덱스는 추가하지 않았다.
like_count는 좋아요/취소가 발생할 때마다 갱신되는 쓰기 빈도가 높은 컬럼이라,
조회 성능 향상보다 인덱스 유지 비용이 더 크다고 판단했기 때문이다.

like_count 정렬에 대한 추가 최적화는 별도로 다룰 예정이다.

---

3. 인덱스 추가 후 실행 계획 변화

두 개의 인덱스를 추가하고, 조회 패턴별로 실행 계획이 어떻게 달라지는지 확인했다.

	CREATE INDEX idx_products_deleted_visibility_brand 
		ON products (deleted_at, visibility, brand_id);

	CREATE INDEX idx_products_deleted_visibility_price 
    	ON products (deleted_at, visibility, price);

3-1. brand_id 필터 + 좋아요순 정렬

1번 도입부와 동일한 쿼리로 실행계획을 확인해본다.

	EXPLAIN SELECT * FROM products
    WHERE deleted_at IS NULL AND visibility = 'VISIBLE' AND brand_id = 1
    ORDER BY like_count DESC LIMIT 20;

인덱스 추가 전과 비교하면:

• key = idx_products_deleted_visibility_brand → 추가한 인덱스 사용
• type = ref → 풀스캔(ALL)에서 인덱스 탐색으로 개선
• rows → 18810 -> 이전 199176에서 약 1/10 수준으로 대폭 감소
• Extra = Using index condition 확인
  (참고: Extra에 Using filesort가 남아있는 이유는, 앞서 인덱스 생성 단계에서 언급했던 대로 like_count를 인덱스에 포함하지 않았기 때문)

개선된 조회 흐름을 정리하면 다음과 같다.

인덱스 탐색
→ deleted_at IS NULL, visibility = 'VISIBLE' 조건으로 범위 축소
→ brand_id 조건이 있다면 추가 범위 축소
→ 남은 결과를 like_count DESC로 정렬 후 20건 반환

3-2. brand_id 없는 기본 조회

2번에서 (deleted_at, visibility, brand_id) 인덱스 하나로 brand_id 유무와 무관하게 두 케이스를 모두 커버할 수 있다고 판단했다. 실제로 그런지 확인해보았다.

  EXPLAIN SELECT * FROM products
  WHERE deleted_at IS NULL AND visibility = 'VISIBLE'
  ORDER BY like_count DESC LIMIT 20;

• key = idx_products_deleted_visibility_brand → brand_id 없이도 동일 인덱스 사용 확인
• type = ref -> 인덱스 탐색
• rows = 99546 -> 위 brand_id 조건 18810 보다 많음

(deleted_at, visibility) prefix만으로 인덱스를 활용하고 있다. brand_id 조건이 추가될수록 읽어야 할 범위가 더 좁아지며, 인덱스 하나로 두 케이스가 모두 커버됨을 확인했다.

3-3. 가격순 정렬

  EXPLAIN SELECT * FROM products
  WHERE deleted_at IS NULL AND visibility = 'VISIBLE'
  ORDER BY price ASC LIMIT 20;

• key = idx_products_deleted_visibility_price → price 인덱스 사용 확인
• Extra = Using index condition → 별도 정렬 작업 없음

인덱스가 (deleted_at, visibility, price) 순서로 구성되어 있어, WHERE 조건 필터 후 남은 결과가 이미 price 순으로 정렬된 상태다. 별도의 정렬 작업 없이 인덱스 순서대로 읽어 LIMIT 20을 바로 적용할 수 있다.

마무리

실행 계획 상의 주요 지표만 보더라도 인덱스 생성을 통해 조회 성능이 크게 향상되었음을 확인할 수 있다.

그러나 슬로우 쿼리가 발생할 때마다 인덱스를 추가하는 방식이 항상 베스트 프랙티스는 아니다.
인덱스는 특정 쿼리 하나가 아니라 서비스에서 반복적으로 사용되는 조회 패턴을 기준으로 설계해야 하고,

인덱스가 많아질수록 다음과 같은 비용이 함께 증가함을 인지해야 한다.

• 쓰기 성능 저하 (INSERT, UPDATE, DELETE)
• 저장 공간 증가
• 중복 인덱스 관리 문제

따라서 먼저 해당 쿼리가 얼마나 자주, 중요한 경로에서 사용되는지 확인하고,
인덱스 추가로 얻는 이점과 유지 비용을 함께 비교해야 한다.

그럼에도 조회 비용이 여전히 크다면 상황에 따라 캐시(Cache), Materialized View, 별도 집계 테이블과 같은 접근 방식도 함께 고려할 수 있다. (다음 편에 계속..)