좋은 서버 쓰면 되잖아? - 인덱스 성능 설계 및 분석 (1)

TLDR;

좋은 인덱스 설계 방법을 통해 성능을 개선할수 있다.

 

개요

왜 DB 최적화가 필요한가? (why-optimization)

왜 DB 최적화를 해? 좋은 서버 쓰면 되잖아

 

 

많은 개발자들이 서버 성능을 높이는 것(scale-up)으로 성능 문제를 해결하려 합니다. 하지만...

서버 성능 업그레이드의 현실

• 비용 효율성: 서버 성능 향상은 비용이 기하급수적으로 증가
• 물리적 한계: 아무리 좋은 서버도 결국 한계 존재
• 근본적 해결 아님: 비효율적인 쿼리는 여전히 비효율적
• 확장성: 데이터 증가 시 문제는 더욱 심화

 

현재 상황 살펴보기

상품 테이블 구조

create table products
(
    id         bigint auto_increment primary key,
    name       varchar(255) null,
    brand_id   bigint       null,
    like_count bigint       null,
    price      bigint       null,
    quantity   bigint       null,
    created_at datetime(6)  not null,
    deleted_at datetime(6)  null,
    updated_at datetime(6)  not null
);

 테스트 데이터 규모

• 📦 전체 상품: 100,000개
• 🏷️ 브랜드 수: 1,000개

 

가장 많이 사용되는 쿼리들

상품 목록 조회

-- 1. 브랜드별 상품 조회
SELECT * FROM products WHERE brand_id = ?;

-- 2. 최신순 정렬
SELECT * FROM products ORDER BY created_at DESC;

-- 3. 인기순 정렬  
SELECT * FROM products ORDER BY like_count DESC;

-- 4. 가격순 정렬
SELECT * FROM products ORDER BY price;

-- 5. 복합 조건 (실제 비즈니스 로직)
SELECT * FROM products WHERE brand_id = ? ORDER BY price ASC;

 

상품 테이블은 조회 시 브랜드 필터링, 정렬등 쿼리 조합이 다양한데 이걸 다 만들어야 되나??

---

카디널리티 분석

처음에는 "카디널리티가 뭐지?" 했는데,  특정 컬럼에서 고유한 값의 개수를 뜻합니다.

 

카디널리티 측정 해보기

SELECT
    COUNT(DISTINCT brand_id)   as brand_id,
    COUNT(DISTINCT price)      as price,
    COUNT(DISTINCT like_count) as like_count,
    COUNT(DISTINCT quantity)   as quantity,
    COUNT(DISTINCT created_at) as created_at
FROM products;

 

카디널리티별 상세 분석

컬럼 명	카디널리티 등급	고유값 개수	고유값 비율	평균 중복도	인덱스 효율성
created_at	최고 🔴🔴	100,000개	100%	1개	최고 (1:1)
price	높음 🔴	5,000개	5%	20개	매우 좋음 (1/20)
brand_id	중상 🟠	1,000개	1%	100개	좋음 (1/100)
like_count	중간 🟡	500개	0.5%	200개	보통 (1/200)
quantity	낮음 🟢	50개	0.05%	2,000개	나쁨 (1/2,000)

 

 

카디널리티 분석 결과, created_at과 price가 가장 효율적인 인덱스 효과가 가장 좋았고, quantity는 재고가 비슷비슷해서 인덱스를 걸어도 별 의미가 없었습니다.

---

인덱스 최적화 전략

 

✅  인덱스 설계 권장사항

1. 높은 카디널리티 우선: 고유값이 많은 컬럼부터
2. 쿼리 패턴 분석: 자주 사용되는 WHERE, ORDER BY 고려
3. 복합 인덱스 순서: WHERE 조건 → ORDER BY 순으로 배치
4. 저장공간 vs 성능: 적절한 균형점 찾기

 

단일 인덱스 

먼저, 높은 카디널리티를 가진 컬럼들의 단일 인덱스를 우선 생성하였습니다.

-- 가장 확실해 보이는 것부터
CREATE INDEX idx_created_at ON products (created_at);

-- 가격 정렬이 자주 있으니까
CREATE INDEX idx_price ON products (price);

-- 브랜드 필터링 많이 사용하니까
CREATE INDEX idx_brand_id ON products (brand_id);

 

복합 인덱스

그 다음으로, 비즈니스 로직에서 자주 사용되는 쿼리 패턴을 고려한 복합 인덱스을 생성하였습니다.

-- 브랜드별 최신 상품 조회
CREATE INDEX idx_brand_created ON products (brand_id, created_at);

-- 브랜드별 가격 정렬
CREATE INDEX idx_brand_price ON products (brand_id, price);

-- 가격대별 최신 상품 조회
CREATE INDEX idx_price_created ON products (price, created_at);

---

성능 비교 및 측정

테스트  쿼리

-- 브랜드별 가격순 정렬
SELECT * FROM products 
WHERE brand_id = 18843 
ORDER BY price ASC;

 

 

실행 계획 분석

🐌  인덱스 사용 전

-> Sort: products.price  (cost=20227 rows=199142) (actual time=80.3..80.3 rows=2 loops=1)
    -> Filter: (products.brand_id = 18843)  (cost=20227 rows=199142) (actual time=26.5..80.3 rows=2 loops=1)
        -> Table scan on products  (cost=20227 rows=199142) (actual time=5.02..72.1 rows=200000 loops=1)

 

🚀  인덱스 사용 후

-> Index lookup on products using idx_brand_price (brand_id=18843)  (cost=0.7 rows=2) (actual time=0.106..0.109 rows=2 loops=1)

 

성능 개선 결과

지표	인덱스 전	인덱스 후	개선율
실행 시간	80.3ms	0.109ms	99.86% ⬇️
스캔 방식	Full Table Scan	Index Lookup	최적화
처리 행수	200,000행	2행	99.9% ⬇️
Cost	20,227	0.7	99.997% ⬇️

 

단순히 인덱스 하나 추가했을 뿐인데 거의 1000배 빨라졌습니다.

---

데이터 규모별 성능 테스트 (단일 인덱스 VS 복합 인덱스 )

데이터가 늘어나면 어떻게 될까 궁금해서 테스트해봤습니다.

데이터 규모 (상품/브렌드)	단일 인덱스	복합 인덱스	승자	개선 율
10만개 / 1000개	0.31ms	0.307ms	복합	+1%
100만개 / 4000개	3.73ms	1.77ms	복합	+52.5%
1000만개 / 15000개	30.6ms	40.5ms	단일	-32.4%

 

어? 뭔가 이상한데 100만개에서는 복합 인덱스에서는 성능이 잘나오는데, 1000만개에서 갑자기 느려졌습니다.

 

❓ 왜 이런 일이 일어났을까?

 

중간 데이터 규모 (10만~500만)

브랜드당 100~500개 정도까지는 복합 인덱스가 확실히 좋다:

• 적당한 데이터 크기
• 인덱스가 메모리에 잘 올라감 
• B-Tree 탐색이 효율적
• Sort 작업 완전 생략

대규모 (1000만+): 복합 인덱스 한계점 

브랜드당 500개 넘어가면 단일 인덱스 + 애플리케이션 레벨 최적화가 필요:

• 인덱스 크기가 너무 커짐
• 메모리 캐시 미스 빈발
• B-Tree 깊이 증가로 탐색 비용 ↑
• 디스크 I/O 증가

---

💡인덱스 설계 가이드라인

1. 카디널리티부터 체크하세요.

• 데이터부터 제대로 분석하고 시작하세요.

2. 실제 쿼리 패턴을 보세요.

• 개발할 때 예상했던 쿼리와 실제 운영에서 나오는 쿼리가 달랐어요. 로그 분석이 중요해요.

3. 복합 인덱스 순서가 생명이에요.

• (brand_id, price)와 (price, brand_id)는 완전히 다른 성능을 보여줘요. WHERE 조건을 앞에 두세요.

4. EXPLAIN은 친구입니다.

• 인덱스 적용 전후로 꼭 실행계획을 확인하세요. 숫자로 확인해야 확실히 분석할수 있습니다.

---

인덱스 사용 시 장 단점

장점 

• 조회 성능: 말이 필요 없습니다.
• 서버 부하: CPU 사용률이 확연히 줄어듭니다.

단점

• 저장 공간: 인덱스도 공간을 차지하기 때문에 테이블의 크기가 늘어납니다.
• 쓰기 성능: INSERT/UPDATE가 살짝 느려졌어요. 하지만 Select가 워낙 많아서 상쇄합니다.

---

결론

단순히 서버 스펙을 확장하는 것보다 데이터베이스 인덱스를 최적화하는 것이 장기적인 비용 절감과 성능 향상에 훨씬 효과적입니다. 인덱스 최적화는 쿼리 효율을 극대화하고 데이터베이스 구조를 강화함으로써, 대규모 트래픽이 발생하는 이커머스 환경 등에서 안정적이고 빠른 서비스를 제공하는 기반이 됩니다.

 

 

🚀 다음 편 예고

이어서 인덱스 성능 분석에 대한 내용은 2편에서 이어서 진행해보겠습니다.