데이터베이스 인덱스 동작방식 그리고 최적화

데이터베이스를 사용하다 보면 성능 최적화를 위해 인덱스를 생성하게 됩니다. 하지만 “느린 쿼리에 인덱스를 추가하면 되겠지”라고 생각하다가는 오히려 성능이 저하될 수도 있습니다. 다년간의 개발 경험을 바탕으로 인덱스 설계 시 알아두면 좋은 핵심 원칙들을 정리해봤습니다.

인덱스로 추가할 필드는 어떻게 선택할까?

1. 카디널리티를 고려하세요

카디널리티(Cardinality)란 특정 필드의 고유한 값의 개수를 의미합니다. 중복된 데이터가 많은 필드는 인덱스를 생성해도 성능 향상이 크지 않습니다.

예시:

나쁜 인덱스: 성별 필드 (M/F 두 가지 값만 존재)
좋은 인덱스: 이메일 필드 (거의 모든 값이 고유함)

인덱스는 다음과 같은 경우에 효과적입니다:

WHERE 절에 자주 사용되는 필드
JOIN 조건에 사용되는 필드
ORDER BY나 GROUP BY에 사용되는 필드

복합 인덱스, 필드 순서가 중요합니다

복합 인덱스를 생성할 때는 필드의 순서가 매우 중요합니다. 잘못된 순서로 생성하면 인덱스가 제대로 활용되지 않을 수 있습니다.

원칙 1: 카디널리티가 높은 필드를 앞에

중복이 적은(고유한 값이 많은) 필드를 앞에 배치하면 초기 필터링 단계에서 더 많은 데이터를 걸러낼 수 있습니다.

-- 좋은 예: 이메일(고유) → 상태(중복 많음)
CREATE INDEX idx_user ON users(email, status);

-- 나쁜 예: 상태(중복 많음) → 이메일(고유)
CREATE INDEX idx_user ON users(status, email);

원칙 2: Equal → Range 순서로 배치

Equal(=) 조건과 Range(>, <, BETWEEN) 조건을 함께 사용할 경우, Equal 조건 필드를 앞에 위치시켜야 합니다.

이유: 데이터베이스는 Equal 조건으로 먼저 필터링한 후 Range 검색을 진행합니다. Range 조건 이후의 필드는 인덱스의 정렬 순서를 활용할 수 없어 인덱스 효율이 크게 떨어집니다.

-- 쿼리
SELECT \* FROM orders WHERE user_id = 123 AND created_at > ‘2025–01–01‘;

-- 올바른 인덱스
CREATE INDEX idx_orders ON orders(user_id, created_at);

-- 비효율적인 인덱스
CREATE INDEX idx_orders ON orders(created_at, user_id);

원칙 3: Equal → Range → Order By 순서

정렬(ORDER BY)까지 사용하는 쿼리라면 다음 순서로 인덱스를 생성합니다:

-- 쿼리
SELECT \*
FROM products
WHERE category = ‘electronics’
AND price > 10000
ORDER BY created_at DESC;

-- 인덱스
CREATE INDEX idx_products ON products(category, price, created_at);

원칙 4: Equal → Group By → Range → Order By 순서

GROUP BY까지 포함된 복잡한 쿼리의 경우:

-- 쿼리
SELECT category, AVG(price)
FROM products
WHERE status = ‘active’
AND price > 1000
GROUP BY category
ORDER BY category;

-- 인덱스
CREATE INDEX idx_products ON products(status, category, price);

인덱스 관리 팁

1. 중복 인덱스를 제거하세요

이미 (a, b, c) 필드로 구성된 인덱스가 있다면, (a)나 (a, b) 인덱스는 일반적으로 불필요합니다. (a, b, c) 인덱스가 이들의 역할을 모두 수행할 수 있기 때문입니다.

단, 예외 상황:

(a, b) 쿼리가 매우 빈번하고 (a, b, c) 인덱스가 크기가 큰 경우
메모리 효율을 위해 작은 인덱스가 필요한 경우

-- 이미 존재하는 인덱스
CREATE INDEX idx_user_full ON users(email, status, created_at);

-- 불필요한 인덱스 (제거 가능)
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_email_status ON users(email, status);

2. 쿼리 실행 계획을 확인하세요

인덱스를 생성한 후에는 반드시 쿼리 실행 계획(EXPLAIN)을 확인해야 합니다.

-- MySQL
EXPLAIN SELECT \* FROM users WHERE email = ‘test@example.com’;

-- PostgreSQL
EXPLAIN ANALYZE SELECT \* FROM users WHERE email = ‘test@example.com’;
생성한 인덱스가 실제로 사용되는지, 어떤 방식으로 활용되는지 점검한 후 배포하는 것을 권장합니다.

3. 성능 문제가 발생했을 때 추가하세요

미리 과도하게 인덱스를 생성하는 것보다, 실제 성능 이슈가 발생했을 때 분석 후 추가하는 것이 좋습니다.

인덱스의 단점:

INSERT, UPDATE, DELETE 시 성능 저하
추가 저장 공간 필요
너무 많은 인덱스는 옵티마이저의 판단을 어렵게 만듦

데이터가 적으면 인덱스가 불필요할까?

일반적으로 10,000건 이하의 데이터라면 인덱스 없이도 성능상 큰 문제가 없다고 알려져 있습니다.

하지만 이는 절대적인 기준이 아니며 다음 요소들에 따라 달라집니다:

각 레코드의 크기
쿼리의 복잡도
시스템의 성능 요구사항
동시 접속자 수

따라서 데이터 건수만으로 판단하기보다는 실제 쿼리 성능을 측정하고 필요시 인덱스를 추가하는 것이 현명합니다.

마치며

인덱스는잘못 사용하면 오히려 독이 될 수 있습니다.

핵심은 쿼리 패턴을 분석하고 적절한 필드를 선택하고 올바른 순서로 배치하고 실행 계획으로 검증하고 불필요한 인덱스는 제거하는 것 입니다.

과도한 인덱스 추가로 성능 이슈가 발생하지 않도록 신중하게 설계하시길 바랍니다. 데이터베이스의 종류(MySQL, PostgreSQL, MongoDB 등)에 따라 세부 동작이 다를 수 있으니, 사용 중인 데이터베이스의 공식 문서도 함께 참고하시면 좋습니다.