성능 최적화 STEP 5 - 인덱스 설계와 튜닝으로 쿼리 속도 개선하기

성능 최적화 5편 - 인덱스 설계와 튜닝으로 쿼리 속도 개선하기

지금까지는 Fetch Join, @BatchSize, Redis 캐싱처럼 애플리케이션 레벨에서 쿼리 횟수를 줄이는 방법을 다뤘습니다. 이번 글에서는 한 단계 더 아래로 내려가, 쿼리 자체의 실행 속도를 좌우하는 인덱스 설계와 튜닝을 정리합니다.

1. 인덱스가 없으면 생기는 일

인덱스가 없는 컬럼으로 조회하면, 데이터베이스는 테이블 전체를 처음부터 끝까지 훑는 풀 스캔(Full Scan)을 수행합니다.

select * from orders where member_id = 12345;

member_id에 인덱스가 없다면, 주문 테이블이 100만 건이어도 100만 건을 전부 확인해야 원하는 행을 찾을 수 있습니다. 데이터가 쌓일수록 이 조회는 점점 느려집니다.

2. 실행 계획으로 문제 확인하기

쿼리가 인덱스를 타는지 확인하려면 실행 계획을 조회합니다.

explain analyze
select * from orders where member_id = 12345;

실행 계획에서 Seq Scan(또는 Table Scan)이 나온다면 인덱스를 타지 않고 있다는 신호입니다. 인덱스를 정상적으로 타면 Index Scan 또는 Index Seek로 표시됩니다.

-- 인덱스 없이 조회한 경우
Seq Scan on orders (cost=0.00..18334.00 rows=50 width=120)
  Filter: (member_id = 12345)

-- 인덱스 생성 후 조회한 경우
Index Scan using idx_orders_member_id on orders (cost=0.43..8.45 rows=50 width=120)
  Index Cond: (member_id = 12345)

3. 인덱스 생성하기

create index idx_orders_member_id
on orders (member_id);

단일 컬럼 인덱스를 생성하면, 위 조회처럼 member_id 하나로 필터링하는 쿼리의 속도가 크게 개선됩니다.

4. 복합 인덱스와 컬럼 순서

조건이 여러 개 걸리는 쿼리에는 복합 인덱스(Composite Index)를 고려합니다. 이때 컬럼 순서가 중요합니다.

select * from orders
where member_id = 12345
  and status = 'PENDING'
order by created_at desc;

create index idx_orders_member_status_created
on orders (member_id, status, created_at);

인덱스는 왼쪽 컬럼부터 순서대로 사용됩니다. 그래서 where member_id = ?처럼 첫 번째 컬럼만 사용하는 쿼리부터, member_id + status, member_id + status + created_at까지 모두 이 인덱스를 활용할 수 있습니다. 반대로 status만으로 조회하는 쿼리는 이 인덱스를 타지 못합니다. 선택도(카디널리티)가 높은 컬럼, 자주 조건절에 등장하는 컬럼을 앞쪽에 두는 것이 일반적인 설계 원칙입니다.

5. 인덱스가 오히려 독이 되는 경우

인덱스는 조회를 빠르게 해주지만 공짜가 아닙니다.

• 쓰기(INSERT/UPDATE/DELETE) 성능 저하 : 데이터가 변경될 때마다 인덱스도 함께 갱신해야 합니다. 인덱스가 많을수록 쓰기 작업이 느려집니다.
• 저장 공간 증가 : 인덱스 자체도 별도의 자료구조로 디스크 공간을 차지합니다.
• 카디널리티가 낮은 컬럼 : 성별, 상태값처럼 값의 종류가 적은 컬럼은 인덱스를 타도 큰 효과가 없는 경우가 많습니다. 오히려 옵티마이저가 인덱스를 안 타는 게 나은 계획을 선택하기도 합니다.

그래서 조회 성능만 보고 인덱스를 무조건 추가하기보다, 조회 빈도와 쓰기 빈도의 균형을 함께 고려해야 합니다.

6. LIKE 검색과 인덱스

-- 인덱스를 타지 못하는 경우 (앞에 와일드카드)
select * from members where name like '%철수%';

-- 인덱스를 탈 수 있는 경우 (뒤에만 와일드카드)
select * from members where name like '철수%';

LIKE '%검색어%'처럼 앞쪽에 와일드카드가 붙으면 일반 B-Tree 인덱스는 활용되지 못하고 풀 스캔이 발생합니다. 이런 검색이 잦다면 인덱스 튜닝만으로는 한계가 있고, 검색 전용 엔진(Elasticsearch 등)이나 Full-Text 인덱스 도입을 고려하는 것이 일반적입니다.

7. 실무에서의 튜닝 순서

1. 실행 계획으로 느린 쿼리가 풀 스캔을 하는지 확인한다
2. 조건절에 자주 쓰이는 컬럼을 파악한다
3. 카디널리티가 높은 컬럼 순으로 복합 인덱스를 설계한다
4. 인덱스 적용 전후로 실행 계획과 실제 응답 속도를 비교해 효과를 검증한다
5. 쓰기 성능에 미치는 영향도 함께 모니터링한다

8. 정리

• 인덱스가 없으면 조회 시 풀 스캔이 발생하고, 데이터가 쌓일수록 느려진다
• 실행 계획(EXPLAIN)으로 인덱스 사용 여부를 먼저 확인하는 것이 튜닝의 출발점이다
• 복합 인덱스는 컬럼 순서가 핵심이며, 카디널리티가 높은 컬럼을 앞에 둔다
• 인덱스는 조회를 빠르게 하는 대신 쓰기 성능과 저장 공간을 희생하므로, 무분별하게 추가하지 않는다

애플리케이션 레벨의 최적화(Fetch Join, 캐싱)와 데이터베이스 레벨의 최적화(인덱스)는 서로 다른 층위에서 작동합니다. 두 가지를 함께 이해하고 있어야, 느린 쿼리를 마주쳤을 때 어느 지점에서 문제를 해결해야 할지 정확히 판단할 수 있습니다.

다음 글에서는 여러 서버에서 동시에 같은 자원에 접근할 때 발생하는 문제를 다루는 분산 락(Distributed Lock) 전략을 정리해보겠습니다.