[ 보고서 ] 정렬 인덱스, 필터 인덱스, 그 조합이 만드는 쿼리의 운명
🔍 이 문서를 작성한 목적
조회 성능 저하가 우려되는 API를 분석하여 원인을 파악하고
개선 방안을 도출하기 위해 작성하였습니다.
🧭 성능 저하 가능성이 있는 API
`/api/v1/products`는 상품 목록을 조회하는 API로,
다음과 같은 조건을 기반으로 필터링합니다.
| 조건 항목 | 설명 |
| 가격 정렬 |
가격 오름차순 / 내림차순 정렬 |
| 최신순 정렬 | 최신순으로 상품을 정렬 |
| category | 문자열 필드, 정규화되지 않음 |
| 내부 조인: product_inventory | 재고 정보와의 조인 |
| 페이징 + COUNT(*) | 페이지네이션과 총 개수 동시 조회 |
정렬 및 필터 조건이 인덱스를 활용하지 못하는 경우
옵티마이저가 전체 테이블을 스캔하거나 대량의 정렬 연산을 수행하게 되어
데이터량이 증가할수록 쿼리 조회 비용이 증가할 수 있습니다.
🧱 문제 정의 및 원인
클라이언트로부터 `/api/v1/products` API에서
특정 카테고리를 선택한 후
`가격 낮은 순`으로 정렬하여
첫 페이지(20개)를 요청하는 경우가 빈번히 발생합니다.
이때 필터 조건인 `category`는 정규화되지 않은 문자열 필드이며,
분포가 특정 값에 치우쳐 있어 옵티마이저가 정확한 필터링 비용을 예측하기 어렵습니다.
또한 정렬 기준인 `price`, `created_at` 필드는 인덱스가 없을 경우 전량 스캔 후 정렬이 필요하므로
데이터가 많을수록 정렬 비용이 급증하게 됩니다.
✅ 결론
이번 실험은 동일한 조건의 조회 쿼리를 대상으로
다양한 인덱스 구성 방식과 적용 순서를 비교했습니다.
특히 정렬 방향 설정 유무,
그리고 인덱스를 과도하게 보유했을 때 성능에 어떤 영향을 미치는지를 함께 분석했습니다.
그 결과는 아래와 같습니다.
| No. | 인덱스 구성 | 설명 | ⏰ 실행 시간 |
| 1 | ❌ | 인덱스 없음 | 14.4초 |
| 2 | 복합 인덱스 [ price ASC, created_at DESC ] | 정렬 최적화만 적용 | 0.09초 |
| 3 | 복합 인덱스 [ price, created_at ] | 정렬 방향 미지정 | 14.7초 |
| 4 | 단일 인덱스 [ category ] | 필터만 최적화 | 16.3초 |
| 5 | 복합 인덱스 [ price ASC, created_at DESC, category ] | 정렬 + 필터 완전 최적화 | 0.0037초 |
| 6 | 복합 인덱스 [ category, price ASC, created_at DESC ] | 필터는 빠르나, 정렬은 인덱스 순서 불일치로 비용 발생 |
3.61초 |
| 7 | 총 6개 인덱스 (복합 + 단일 혼합) | 과도한 인덱스 → 옵티마이저 판단 복잡 | 9.38초 |
📈 인덱스별 성능 개선 효과 비교
| 비교 대상 | 기존 응답 속도 | 개선 후 속도 | 성능 향상률 |
| 🔴 인덱스 없음 vs 복합 인덱스 | 14.4초 | 0.0037초 | ✅ 99.97% 개선 |
🧠 핵심 인사이트
- 정렬 방향(ASC/DESC)을 명시하지 않은 복합 인덱스는 정렬 생략 효과가 제한적일 수 있습니다.
- 필터 조건만 최적화된 경우, 정렬 단계에서 성능 병목이 발생할 가능성이 높습니다.
- 정렬 필드를 앞에 배치한 복합 인덱스가 가장 안정적인 성능을 보여주는 경향이 있었습니다.
- category처럼 분포가 편향된 필드는 옵티마이저가 필터링 비용을 정확히 예측하지 못할 수 있습니다.
- 자주 사용되는 조건 조합을 기반으로 인덱스를 구성하는 것이 효율적입니다.
- 인덱스가 과도하게 많을 경우, 옵티마이저가 비효율적인 실행 계획을 선택할 가능성이 있습니다.
🔬 성능 측정 방식
EXPLAIN ANALYZE를 활용해 쿼리의 처리 방식과 응답 시간을 분석하고
개선 방안 적용 전후의 성능 차이를 비교합니다.
📊 테스트 데이터 설계
총 1,000,000건의 테스트 데이터를 활용하여,
다양한 조건에서 쿼리 성능을 분석합니다.
📌 Category 분포
| 카테고리 | 비율 | 건수 | 테스트 목적 |
| BOOK | 60% | 600,000건 | 정렬 대상이 많은 조건 테스트용 |
| GAME | 20% | 200,000건 | 중간 분포 |
| TECH | 10% | 100,000건 | 희소 |
| FOOD | 5% | 50,000건 | 극희소 |
| TOY | 5% | 50,000건 | 극희소 |
카테고리 중 'BOOK'은 전체의 과반 이상을 차지하는 편중된 값으로 세팅하였습니다.
🧪 분석 내용
[ 테스트 환경 ]
- DBMS: MySQL 8.0.41
- 데이터 기간: 2025년 1월 18일 ~ 4월 17일
- 총 상품 수: 1,000,000건
- 테스트 방식: EXPLAIN ANALYZE 기반 쿼리 성능 측정
[ 분석 대상 쿼리 ]
조건 요약
=> category (가장 분포도가 높은 'BOOK' 으로 필터링)
=> price 오름차순 (가격 낮은 순)
=> created_at 내림차순 (최신순)
1. 인덱스 적용 전
⏰ 약 14.4초
-> Limit: 20 row(s) (cost=299420 rows=20) (actual time=14406..14408 rows=20 loops=1)
-> Nested loop inner join (cost=299420 rows=994779) (actual time=14406..14407 rows=20 loops=1)
-> Sort: p.price, p.created_at DESC (cost=100893 rows=994779) (actual time=14406..14406 rows=20 loops=1)
-> Filter: (p.category = 'BOOK') (cost=100893 rows=994779) (actual time=10.9..12971 rows=200069 loops=1)
-> Table scan on p (cost=100893 rows=994779) (actual time=10.9..6183 rows=1e+6 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.0409..0.0461 rows=1 loops=20)
2. 복합 인덱스 [ 가격 ASC(낮은순), 기간 DESC(최신순) ]
⏰ 약 0.09초
CREATE INDEX idx_price_created ON product(price ASC, created_at DESC);
-> Limit: 20 row(s) (cost=99051 rows=2) (actual time=5.88..90.8 rows=20 loops=1)
-> Nested loop inner join (cost=99051 rows=2) (actual time=5.83..90.1 rows=20 loops=1)
-> Filter: (p.category = 'BOOK') (cost=1.85 rows=2) (actual time=5.31..81.5 rows=20 loops=1)
-> Index scan on p using idx_price_created (cost=1.85 rows=20) (actual time=5.16..79.6 rows=86 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.356..0.366 rows=1 loops=20)
3. 복합 인덱스 [ 가격, 기간 ]
⏰ 약 14.7초
* 특징 : ASC나 DESC 조건을 걸지 않음
CREATE INDEX idx_price_created ON product(price, created_at);
-> Limit: 20 row(s) (cost=300586 rows=20) (actual time=14692..14694 rows=20 loops=1)
-> Nested loop inner join (cost=300586 rows=994779) (actual time=14692..14694 rows=20 loops=1)
-> Sort: p.price, p.created_at DESC (cost=102060 rows=994779) (actual time=14692..14692 rows=20 loops=1)
-> Filter: (p.category = 'BOOK') (cost=102060 rows=994779) (actual time=3.34..13278 rows=200069 loops=1)
-> Table scan on p (cost=102060 rows=994779) (actual time=3.27..6415 rows=1e+6 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.0385..0.0447 rows=1 loops=20)
4. 단일 인덱스 [ Category(문자열) ]
⏰ 약 16.3초
CREATE INDEX idx_category ON product(category);
-> Limit: 20 row(s) (cost=490373 rows=20) (actual time=16218..16312 rows=20 loops=1)
-> Nested loop inner join (cost=490373 rows=405248) (actual time=16218..16311 rows=20 loops=1)
-> Sort: p.price, p.created_at DESC (cost=48013 rows=405248) (actual time=16211..16212 rows=20 loops=1)
-> Index lookup on p using idx_category (category='BOOK') (cost=48013 rows=405248) (actual time=8.02..13945 rows=200069 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.992 rows=1) (actual time=4.93..4.94 rows=1 loops=20)
5. 복합 인덱스 [ 가격 ASC(낮은순), 기간 DESC(최신순), Category 순서대로 적용 시 ]
⏰ 약 0.0037초
CREATE INDEX idx_price_created_category ON product(price ASC, created_at DESC, category);
-> Limit: 20 row(s) (cost=99051 rows=2) (actual time=0.242..3.68 rows=20 loops=1)
-> Nested loop inner join (cost=99051 rows=2) (actual time=0.218..3.42 rows=20 loops=1)
-> Filter: (p.category = 'BOOK') (cost=1.86 rows=2) (actual time=0.0944..1.84 rows=20 loops=1)
-> Index scan on p using idx_price_created_category (cost=1.86 rows=20) (actual time=0.0568..1.14 rows=86 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.0444..0.0497 rows=1 loops=20)
6. 복합 인덱스 [ Category, 가격 ASC(낮은순), 기간 DESC(최신순) ]
⏰ 약 3.61초
CREATE INDEX idx_category_price_created ON product(category, price, created_at);
-> Limit: 20 row(s) (cost=487645 rows=20) (actual time=3609..3611 rows=20 loops=1)
-> Nested loop inner join (cost=487645 rows=404560) (actual time=3609..3611 rows=20 loops=1)
-> Sort: p.price, p.created_at DESC (cost=44374 rows=404560) (actual time=3609..3609 rows=20 loops=1)
-> Index lookup on p using idx_category_price_created (category='BOOK') (cost=44374 rows=404560) (actual time=0.506..2219 rows=200069 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.0421..0.0471 rows=1 loops=20)
7. 복합 인덱스 / 단일 인덱스 총 6개
⏰ 약 9.38초
-- 정렬용
CREATE INDEX idx_price ON product(price);
CREATE INDEX idx_created_at ON product(created_at);
CREATE INDEX idx_price_created ON product(price, created_at);
-- 필터용
CREATE INDEX idx_category ON product(category);
-- 필터 + 정렬 혼합
CREATE INDEX idx_category_price_created ON product(category, price, created_at);
CREATE INDEX idx_price_created_category ON product(price, created_at, category);
-> Limit: 20 row(s) (cost=493561 rows=20) (actual time=9379..9382 rows=20 loops=1)
-> Nested loop inner join (cost=493561 rows=404560) (actual time=9379..9382 rows=20 loops=1)
-> Sort: p.price, p.created_at DESC (cost=50290 rows=404560) (actual time=9378..9378 rows=20 loops=1)
-> Index lookup on p using idx_category_price_created (category='BOOK') (cost=50290 rows=404560) (actual time=25.1..7834 rows=200069 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.174..0.179 rows=1 loops=20)
❓정렬 방향이 반대여도 인덱스가 동작할까?
기존에는 `price ASC`(저가순), `created_at DESC`(최신순) 정렬 기준으로 인덱스를 구성하였습니다.
일반적으로 E-Commerce에서는 이렇게 검색을 하는 경우가 더 많기 때문이었습니다.
그렇다면 반대로,
`price DESC`(고가순), `created_at ASC`(오래된 순)처럼
설정한 인덱스의 정렬 방향과 완전히 반대인 경우에도 인덱스가 실제로 동작할 수 있을지 확인해 보았습니다.
이를 확인하기 위해 동일한 필터 조건(`category = 'BOOK'`)에
반대 정렬 조건을 적용하여 테스트를 진행하였습니다.
CREATE INDEX idx_price_created_category ON product(price ASC, created_at DESC, category);
EXPLAIN ANALYZE
SELECT p.id, p.name, p.price, p.created_at, pi.stock
FROM product p
JOIN product_inventory pi ON p.id = pi.product_id
WHERE p.category = 'BOOK'
ORDER BY p.price DESC, p.created_at ASC
LIMIT 20 OFFSET 0;
-> Limit: 20 row(s) (cost=99051 rows=2) (actual time=0.601..5.15 rows=20 loops=1)
-> Nested loop inner join (cost=99051 rows=2) (actual time=0.573..4.86 rows=20 loops=1)
-> Filter: (p.category = 'BOOK') (cost=1.85 rows=2) (actual time=0.463..3.08 rows=20 loops=1)
-> Index scan on p using idx_price_created_category (reverse) (cost=1.85 rows=20) (actual time=0.097..2.05 rows=122 loops=1)
-> Single-row index lookup on pi using UK_e6vup57dqij0evpnn6mum40lu (product_id=p.id) (cost=0.996 rows=1) (actual time=0.0459..0.0522 rows=1 loops=20)
실험 결과 해당 쿼리의 실행 시간은 약 0.00515초(5.15ms)로
설정한 인덱스 방향과 정렬 방향이 정반대임에도 불구하고
예상 외로 매우 빠른 성능을 보였습니다.
이는 MySQL 8.0 이상에서 지원하는 인덱스 역방향 탐색(reverse index scan) 기능 덕분입니다.
B-Tree 인덱스는 원래 구성된 정렬 방향과 반대일 경우에도
옵티마이저가 인덱스를 역순으로 탐색하여
풀스캔 없이 빠르게 결과를 추출할 수 있도록 동작합니다.
다만 이 방식은 항상 적용되는 것이 아니며,
상황에 따라 옵티마이저가 인덱스를 선택하지 않을 수도 있다는 점은 주의가 필요합니다.
따라서 실제 운영 환경에서는 실제 사용 빈도에 따라 주요 정렬 조합별 인덱스를 설계하고,
로그 기반 분석을 통한 인덱스 튜닝 전략을 병행하는 것이 바람직합니다.
❓인덱스 말고 더 개선할 수 있는 방법은 없을까?
단순히 인덱스 설계만으로 모든 성능 문제를 해결하기는 어렵습니다.
특히 category처럼 문자열 기반이며 값의 분포가 편향된 필드는
옵티마이저의 실행 계획 선택이 불안정할 수 있습니다.
이 경우, 다음과 같은 구조적인 개선 방향도 함께 고려해볼 수 있습니다.
category 필드 정규화
현재 `category` 컬럼은 문자열로 관리되고 있습니다.
이로 인해 정렬 및 필터링 성능이 불안정하며
값의 종류가 제한적임에도 문자열 비교 비용이 발생합니다.
[ 개선 방향 ]
- `category`를 별도 테이블로 분리하여 정규화
- `product.category_id`로 외래키 매핑
- 문자열 대신 정수 기반 필터링 및 인덱싱 가능
이렇게 하면 필터링 성능이 향상되고
카테고리 관리(수정, 다국어 지원 등)도 구조적으로 유연해질 수 있다는 장점이 있습니다.
📝 필요 시, 캐싱 전략, 테이블 파티셔닝 등도 다음 단계로 고려할 수 있습니다.
🚧 실험의 한계 및 향후 개선 방향
이번 실험은 정렬 조건과 인덱스 구성에 따른 실행 성능과
옵티마이저의 선택 우선순위를 분석하는데 목적을 두었습니다.
아래와 같은 요소들은 실험 범위에 포함되지 않았으며
향후 추가적인 실험을 한다면 더 정교한 분석이 가능할 것으로 예상됩니다.
- 희소 조건(category = 'FOOD' 등)에서의 옵티마이저 판단 정확도 측정
- 인덱스 선택 시
- 옵티마이저가 통계 정보를 기반으로 잘못된 실행 계획(예: 풀스캔)을 선택하는 상황은 실험하지 않았음
- LIKE, BETWEEN 등 범위 조건이 인덱스 사용 및 정렬 생략 여부에 미치는 영향
- 범위 조건 적용 시
- 인덱스 정렬 순서와 충돌하거나 인덱스 범위 스캔(Index Range Scan)으로 인해 정렬이 생략되지 않는 문제는 포함하지 않음
🔗 해당 프로젝트 깃헙
GitHub - developerOlive/hhplus-e-commerce: [항해플러스] 이커머스 서비스
[항해플러스] 이커머스 서비스. Contribute to developerOlive/hhplus-e-commerce development by creating an account on GitHub.
github.com