데이터베이스 쿼리 속도를 높이는 인덱스 활용법

이 글은 IT 전문 번역가 David가 마네슈와르 홀라(Maneshwar C Holla)의 <Speed Up DB Queries Like a Pro> 글을 번역하였습니다. 필자는 소프트웨어 엔지니어로 DEV Community와 Forem에서 활동하고 있으며, 현재 Antino에 근무 중입니다.

이번 글에서는 데이터베이스 쿼리 성능 향상을 위한 인덱스 사용법과 중요성에 대해 다룹니다. PostgreSQL의 B-트리 인덱스를 예시로, 인덱스 작동 방식과 EXPLAIN ANALYZE를 통한 성능 측정 기법을 소개합니다.

필자로부터 허락받아 번역했으며, 각주(*)는 ‘번역자주’를 나타냅니다.

데이터베이스 쿼리 기다림이 얼마나 고통스러운지 아는 사람이라면 인덱스의 필요성을 이해할 수 있습니다. PostgreSQL은 강력하지만 적절한 인덱싱 없이는 느릴 수 있습니다.

인덱스는 쿼리 속도를 향상시키지만, 동시에 트레이드오프가 존재합니다.

인덱스란 무엇인가?

PostgreSQL의 인덱스는 책의 색인처럼 작동하여 필요한 부분으로 바로 이동할 수 있도록 돕습니다. 인덱스가 없으면 순차 검색을 하게 되어 성능이 저하됩니다.

인덱스는 다음에 유용합니다:

• ‘WHERE’ 절이 있는 쿼리 속도 개선
• JOIN 성능 개선

인덱스를 통해 쿼리 실행 시간이 O(n)에서 O(log n)으로 개선되지만, 저장 공간과 쓰기 성능에 영향을 줄 수 있습니다.

인덱스 작동 원리: B-트리

PostgreSQL의 기본 인덱스는 B-트리로, 데이터를 정렬된 상태로 유지하는 트리 구조입니다.

• 루트 노드: 검색 시작점
• 분기 노드: 리프 노드로 안내
• 리프 노드: 데이터 포인터 저장

예를 들어 “김철수” 검색 시, B-트리는 루트에서 시작해 현재 노드와 비교하여 일치하는 항목을 찾을 때까지 진행합니다.

이 방식으로 검색 속도가 로그 시간으로 빨라집니다.

인덱스를 사용해야 하는 경우

읽기 성능을 크게 향상시킬 수 있을 경우 인덱스를 사용합니다.

인덱스를 사용하지 말아야 하는 경우

인덱스는 관리가 필요합니다. 쓰기 작업이 잦은 경우 성능 저하를 초래할 수 있어 사용을 피해야 합니다:

• 작은 테이블
• 인덱스된 열로 필터링을 하지 않는 경우
• 쓰기가 빈번한 경우
• 트랜잭션 위주이지 빠른 삽입이 필요한 경우

PostgreSQL의 MVCC* 메커니즘은 성능에 영향을 줄 수 있습니다.

*MVCC: 여러 사용자가 동시에 데이터를 안전하게 수정할 수 있도록 관리하는 기술

인덱스 성능 측정

인덱스 추가 전, PostgreSQL의 EXPLAIN ANALYZE로 테스트할 수 있습니다:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Seq Scan과 Index Scan을 확인하고, 쿼리 시간이 줄지 않는다면 인덱스가 불필요합니다.

불필요한 인덱스 제거는 다음 명령어로 합니다:

DROP INDEX index_name;

PostgreSQL의 인덱스 유형들

PostgreSQL은 여러 인덱스 유형을 제공합니다.

B-트리 인덱스(기본값)

B-트리는 동등 비교와 범위를 효율적으로 처리하며, 모든 데이터 타입에 사용할 수 있습니다.

CREATE INDEX idx_users_email ON users(email);

해시 인덱스

해시 인덱스는 동등 비교에 최적화되어 있으며 B-트리보다 장점이 적습니다.

CREATE INDEX idx_users_hash_email ON users USING hash(email);

GIN(일반화된 역 인덱스)

GIN은 배열 값 인덱싱 및 전체 텍스트 검색에 유용합니다.

CREATE INDEX idx_users_bio ON users USING gin(to_tsvector('korean', bio));

GiST

GiST 인덱스는 일반적인 균형 트리 구조를 사용하며, 기하 및 텍스트 검색에 사용됩니다.

CREATE INDEX idx_locations ON places USING gist(location);

BRIN(블록 범위 인덱스)

BRIN 인덱스는 대용량 순차 데이터에 효율적입니다.

CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

고급 인덱싱 전략

복합 인덱스

하나의 쿼리에서 자주 조회되는 열에 대해 인덱스를 생성합니다.

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

부분 인덱스

조건에 의해 정의된 데이터 부분만 인덱싱합니다.

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

커버링 인덱스

추가 열을 저장하여 메인 테이블 접근을 방지합니다.

CREATE INDEX idx_orders_covering ON orders(user_id, order_date) INCLUDE (total_price);

고유 인덱스

열의 고유성을 보장합니다.

CREATE UNIQUE INDEX idx_unique_email ON users(email);

트레이드오프: 읽기 vs 쓰기 성능

정리하며

인덱스는 PostgreSQL 성능 개선의 핵심 요소입니다. 다음을 고려하여 전략적으로 사용하세요:

• 인덱스 사용시 필터링, 정렬, 조인 고려
• 자주 업데이트되는 테이블에는 인덱스 사용을 피하세요.
• EXPLAIN ANALYZE로 성능 테스트 후 인덱스 추가하세요.
• 쿼리 패턴에 맞는 인덱스 유형 선택하세요.

<원문>

Speed Up DB Queries Like a Pro