좋은 인덱스를 이용한 조회 성능 개선

보통의 웹 서비스에서 쓰기 작업 보단 읽기 작업이 비교적 빈번하게 일어난다. 그렇기 때문에 웹 서비스를 운영하며 읽기 작업 개선은 매우 중요하다고 생각한다. 조회 성능 개선 방법 중 인덱스를 이용하는 방법에 대해 알아보았다.

1줄 요약(TL;DR)

인덱스 선택은 사용 빈도, 사용 컬럼, 카디널리티 분석 등 다방면으로 고려할 줄 알아야 한다.

들어가며

가상의 이커머스 서버를 설정으로 진행했다. 브랜드, 상품 테이블이 존재하고 상품 목록 조회에 대한 성능 비교를 진행했다.

브랜드 테이블

이름이 중복되지 않은 가상의 브랜드를 2000개 생성하였다.

상품 테이블

브랜드 id, 이름, 판매 상태, 삭제 여부, 좋아요 카운트를 가지고 있고 총 1000만개의 데이터를 준비했다.
브랜드 당 5000개의 상품을 생성하였다.

조회 성능이 왜 중요한가?

쿼리가 느려 응답시간이 지연되면 실질적으로 페이지 조회수 자체가 감소하고 사용자의 이탈을 유발할 수 있다.
또한 DB 커넥션이 고갈되거나 메모리 등 서버 자원을 많이 사용하게 되므로 성능적인 이슈가 발생할 수 있다.

인덱스 선택 방법

1. 카디널리티 분석

카디널리티는 특정 컬럼에서 고유한 값의 개수이다. DB 옵티마이저의 실행 계획에서 인덱스를 사용여부에서 굉장히 중요한 역할을 한다.

상품 테이블의 카디널리티 분석

컬럼	고유값 수	선택도	인덱스 효율성
brand_id	2,000개	0.02%	좋음
name	10,000,000개	0.00001%	매우 좋음
status	2개	50%	낮음

결과

인덱스는 카디널리티가 높은 컬럼을 우선으로 생성하는 것이 좋다.
예를 들어 브랜드 id 와 상태에 대한 인덱스를 만든다고 해보자.

-- 좋은 인덱스
CREATE INDEX idx_product_brand_status 
ON product(name, brand_id);

-- 나쁜 인덱스
CREATE INDEX idx_product_status_brand 
ON product(brand_id, name);

이름을 먼저 정렬 시킨 후 브랜드 id 를 찾는 것이 효과적이다.

2. 유즈케이스 분석

실제 사용 되는 쿼리를 분석해 인덱스를 설계해야 한다.

트래픽 비교에 따른 인덱스 전략

-- 1. 브랜드별 특정 이름 상품 조회 (1% 트래픽)
SELECT * FROM product 
WHERE brand_id = ? AND name = ?;

-- 2. 브랜드별 활성 상품 조회 (99% 트래픽)  
SELECT * FROM product 
WHERE brand_id = ? AND status = 'ACTIVE' ;

----------------------------------------------

-- 메인 인덱스: 99% 트래픽 커버
CREATE INDEX idx_product_brand_like_count 
ON product(brand_id, status DESC);

-- 서브 인덱스: 1% 트래픽
CREATE INDEX idx_product_brand_status 
ON product(name, brand_id);

사용되는 컬럼에 따른 인덱스 전략

-- 정렬에 사용되는 조인 컬럼
SELECT p.*
FROM product AS p
JOIN product_like_count AS plc
ON p.id = plc.product_id
WHERE brand_id = 1
ORDER BY plc.count DESC

----------------------------------------------

-- product 테이블에서 사용되는 컬럼 커버링 인덱스
CREATE INDEX idx_product_brand_status_deleted
    ON product (brand_id);

-- 정렬에 사용되는 product_like_count 컬럼 커버링 인덱스
CREATE INDEX idx_product_like_count_composite
    ON product_like_count (product_id, count desc);

테이블	접근방식 (type)	접근 방식(key)	스캔 행수 (rows)	필터링	추가 정보(Extra)
product	ref	idx_product_brand_like_count	5,000행	100%	-
정렬	-	-	-	-	Using index

3. 쿼리 실행계획 분석

EXPLAIN을 통해 실제 성능을 검증한다.

분석 포인트

• type: 접근 방식 (ALL < index < range < ref < eq_ref < const)
• rows: 스캔할 예상 행 수
• Extra: 추가 작업 (Using filesort, Using temporary 등)
• key: 실제 사용된 인덱스

인덱스 전후 성능 비교

테스트 환경

• 데이터: 상품 1천만 건, 브랜드 2천 개
• 쿼리: 브랜드별 상품 조회 + 좋아요 순 정렬
• DB: MySQL 8.0

SELECT p.*
FROM product as p
WHERE brand_id = 1
ORDER BY p.like_count DESC;

성능 비교

시나리오	실행시간	스캔 행수	정렬방식
인덱스 전	2-3초	9,699,404행	Filesort
인덱스 후	0.05초	5,000행	Index

인덱스 없을 때의 문제점

테이블	접근방식	스캔 행수	추가 정보
product	ALL	9,699,404행	Using where
정렬	-	969,940행	Using filesort

• 전체 테이블 스캔: 970만 행 전체 읽기
• 메모리 정렬: 97만 행을 메모리에서 정렬 (디스크 사용)

2. 인덱스 적용 후 개선

CREATE INDEX idx_product_brand_like_count 
ON product(brand_id, like_count DESC);

테이블	접근방식	스캔 행수	추가 정보
product	ref	5,000행	-
정렬	-	-	Using index

• 인덱스 스캔: 필요한 5,000행만 접근
• 인덱스 정렬: 별도 정렬 작업 없음 (이미 정렬된 상태)

마무리

좋은 인덱스는 단순히 쿼리에 자주 사용되는 것이 아니라 얼마나 빈번하게 조회하고, 어떤 형태로 사용되는 컬럼인지 잘 파악하는 것이 중요하다고 생각한다.