시스매

데이터베이스 인덱스 완벽 정복 가이드 A to Z

19 min read

2025-09-01 00:41

  • 데이터베이스 인덱스는 책의 목차처럼 데이터 검색 속도를 비약적으로 향상시키는 자료 구조입니다.

  • 인덱스는 빠른 검색(SELECT)을 가능하게 하지만, 데이터 변경(INSERT, UPDATE, DELETE) 시에는 성능 저하를 일으키는 트레이드오프 관계를 가집니다.

  • 따라서 무분별한 인덱스 생성은 피하고, 데이터의 카디널리티와 조회 패턴을 분석하여 전략적으로 설계하고 적용해야 합니다.

데이터베이스 인덱스 완벽 정복 가이드 A to Z

데이터베이스를 다루다 보면 ‘인덱스를 추가했더니 쿼리 속도가 100배 빨라졌어요’ 혹은 ‘인덱스를 잘못 사용해서 오히려 성능이 느려졌어요’와 같은 이야기를 흔히 듣게 됩니다. 데이터베이스 인덱스는 이처럼 시스템 성능에 지대한 영향을 미치는 핵심적인 요소입니다. 하지만 많은 개발자들이 인덱스를 ‘빠르게 만들어주는 마법’ 정도로만 인식하고 그 내부 동작 원리나 대가에 대해서는 깊이 있게 이해하지 못하는 경우가 많습니다.

이 핸드북은 데이터베이스 인덱스가 왜 필요한지, 어떤 구조로 이루어져 있는지, 그리고 어떻게 현명하게 사용해야 하는지에 대한 모든 것을 담았습니다. 이 글을 끝까지 읽으신다면, 더 이상 인덱스를 두려워하지 않고 시스템의 성능을 자유자재로 튜닝할 수 있는 개발자로 거듭날 수 있을 것입니다.

1. 인덱스는 왜 만들어졌을까? 거대한 도서관에서 책 찾기

데이터베이스 인덱스를 이해하는 가장 좋은 방법은 거대한 도서관을 상상하는 것입니다. 만약 수백만 권의 책이 아무런 규칙 없이 뒤섞여 꽂혀 있는 도서관에서 ‘데이터베이스의 역사’라는 책을 찾아야 한다면 어떻게 해야 할까요? 아마 도서관의 첫 번째 책장 첫 번째 칸부터 마지막 책까지 한 권 한 권 제목을 확인하는 끔찍한 방법을 사용해야 할 것입니다.

데이터베이스에서 인덱스가 없는 테이블을 조회하는 것은 이와 똑같습니다. 이러한 방식을 풀 테이블 스캔(Full Table Scan) 이라고 부릅니다. 데이터가 몇백 건 수준일 때는 문제가 없겠지만, 수백만, 수천만 건으로 늘어난다면 원하는 데이터를 찾는 데 몇 분, 혹은 몇 시간이 걸릴 수도 있습니다. 서비스의 응답 속도가 이렇게 느리다면 어떤 사용자도 기다려주지 않을 것입니다.

이 문제를 해결하기 위해 도서관 사서들은 ‘도서 목록 카드’를 만들었습니다. 책의 제목, 저자, 주제별로 카드를 만들어 가나다순으로 정리해 둔 것이죠. 이제 우리는 ‘데이터베이스의 역사’라는 책을 찾기 위해 모든 책장을 뒤질 필요 없이, ‘ㄷ’ 섹션에서 ‘데이터베이스’로 시작하는 카드만 빠르게 찾으면 됩니다. 그 카드에는 책이 실제로 꽂혀있는 위치(예: 3층 B-2 구역 5번째 책장)가 적혀 있습니다.

데이터베이스 인덱스가 바로 이 ‘도서 목록 카드’와 같은 역할을 합니다. 인덱스는 특정 컬럼(들)의 데이터와 해당 데이터가 저장된 행의 물리적 주소를 키-값 쌍으로 저장하는 별도의 자료 구조입니다. 데이터베이스는 테이블 전체를 뒤지는 대신, 잘 정렬된 인덱스 구조를 먼저 탐색하여 원하는 데이터의 위치를 신속하게 찾아냅니다.

2. 인덱스의 구조 파헤치기: B-Tree를 중심으로

그렇다면 인덱스는 내부적으로 어떤 모습일까요? 가장 대표적으로 사용되는 인덱스 자료 구조는 B-Tree(Balanced Tree) 입니다. 이름에서 알 수 있듯, 어느 한쪽으로 치우치지 않고 균형을 유지하는 트리 구조입니다.

B-Tree는 다음과 같은 노드들로 구성됩니다.

  • 루트 노드 (Root Node): 트리의 가장 상위에 있는 시작 노드입니다.

  • 브랜치 노드 (Branch Node): 루트와 리프 사이의 중간 노드로, 다른 노드를 가리키는 포인터를 가집니다.

  • 리프 노드 (Leaf Node): 트리의 가장 말단에 있는 노드입니다. 실제 인덱스 데이터(컬럼 값)와 해당 데이터가 저장된 테이블 행의 주소 값을 가지고 있습니다. 리프 노드들은 보통 이중 연결 리스트(Doubly Linked List) 구조로 서로 연결되어 있어, 특정 범위의 데이터를 순차적으로 검색하는 ‘범위 스캔’에 매우 효율적입니다.

왜 ‘균형(Balanced)‘이 중요할까요?

B-Tree가 ‘균형’ 트리라는 점은 성능에 매우 중요합니다. 루트 노드에서 어떤 리프 노드로 찾아가든 그 경로의 길이가 모두 동일하다는 의미이기 때문입니다. 이 덕분에 데이터가 수백만, 수천만 건이 되어도 항상 일관되고 예측 가능한 검색 성능을 보장합니다. B-Tree의 탐색 시간 복잡도는 O(log N)으로, 풀 테이블 스캔의 O(N)에 비해 압도적으로 빠릅니다. 데이터가 100만 건일 때, 풀 스캔은 100만 번의 연산을 해야 하지만, B-Tree는 약 20번(log₂(1,000,000) ≈ 20) 정도의 연산만으로 데이터를 찾아낼 수 있습니다.

3. 인덱스 사용법과 동작 원리: 옵티마이저의 선택

우리가 직접 인덱스를 사용하라고 데이터베이스에 명령하는 경우는 거의 없습니다. 인덱스를 생성해 두면, 옵티마이저(Optimizer) 라는 데이터베이스의 두뇌가 쿼리를 분석하고 가장 효율적인 실행 계획을 세우면서 인덱스 사용 여부를 스스로 결정합니다.

인덱스 생성 기본 문법

CREATE INDEX idx_user_name ON users (name);

위 SQL은 users 테이블의 name 컬럼을 기준으로 idx_user_name이라는 이름의 인덱스를 생성합니다.

옵티마이저는 어떤 경우에 인덱스를 사용할까?

옵티마이저는 비용 기반(Cost-based)으로 동작합니다. 즉, 풀 테이블 스캔을 하는 비용과 인덱스를 사용하는 비용을 비교하여 더 저렴한 쪽을 선택합니다. 일반적으로 다음과 같은 쿼리에서 인덱스가 효과적으로 사용됩니다.

  • WHERE 절을 사용한 특정 데이터 조회: 가장 일반적인 경우입니다. WHERE user_id = 123과 같이 특정 조건을 만족하는 데이터를 찾을 때 매우 빠릅니다.

  • JOIN 연산: 테이블 간 조인에 사용되는 컬럼(주로 Foreign Key)에 인덱스가 없으면, 한 테이블의 모든 행을 다른 테이블의 모든 행과 비교하는 끔찍한 일이 발생할 수 있습니다. 조인 컬럼 인덱스는 필수적입니다.

  • ORDER BY 절을 사용한 정렬: 인덱스는 이미 데이터가 정렬된 상태로 저장되어 있습니다. 만약 ORDER BY 절의 정렬 기준이 인덱스와 같다면, 데이터베이스는 별도의 정렬 과정을 거치지 않고 인덱스를 순서대로 읽기만 하면 되므로 속도가 매우 빠릅니다.

  • MIN() / MAX() 함수: 인덱스의 가장 첫 번째 값(MIN)이나 마지막 값(MAX)을 바로 가져올 수 있으므로 빠르게 처리됩니다.

4. 심화 학습: 인덱스를 현명하게 사용하기 위한 고려사항

인덱스는 만능 해결책이 아닙니다. 오히려 잘못 사용하면 시스템에 독이 될 수 있습니다. 인덱스를 설계할 때는 다음과 같은 점들을 반드시 고려해야 합니다.

인덱스의 대가: 쓰기 성능 저하와 저장 공간

인덱스는 ‘읽기(SELECT)’ 성능을 향상시키는 대신, ‘쓰기(INSERT, UPDATE, DELETE)’ 성능을 희생합니다.

  • INSERT: 새로운 데이터가 추가되면, 테이블뿐만 아니라 인덱스에도 새로운 데이터가 추가되어야 합니다. 이 과정에서 B-Tree의 정렬 상태를 유지하기 위해 페이지 분할(Page Split)과 같은 복잡한 작업이 발생할 수 있습니다.

  • UPDATE: 인덱싱된 컬럼의 값이 변경되면, 인덱스에서도 기존 값을 삭제하고 새로운 값을 추가하는 작업이 필요합니다.

  • DELETE: 데이터가 삭제되면, 테이블뿐만 아니라 인덱스에서도 해당 데이터를 삭제해야 합니다.

즉, 인덱스가 많을수록 쓰기 작업은 더 느려집니다. 또한, 인덱스는 테이블과는 별개의 저장 공간을 차지하는 객체이므로 디스크 공간을 추가로 사용하게 됩니다.

클러스터형 인덱스 vs 비클러스터형 인덱스

  • 클러스터형 인덱스 (Clustered Index): 테이블의 데이터 행 자체가 인덱스에 의해 지정된 순서대로 물리적으로 정렬되어 저장됩니다. 마치 영어 사전처럼, 단어(데이터)들이 알파벳 순서(인덱스)로 이미 정렬되어 있는 것과 같습니다. 이 때문에 테이블당 단 하나만 존재할 수 있으며, 보통 Primary Key가 클러스터형 인덱스로 생성됩니다. 범위 검색에 매우 강력한 성능을 보입니다.

  • 비클러스터형 인덱스 (Non-Clustered Index): 데이터 행의 물리적 순서는 그대로 둔 채, 별도의 공간에 인덱스 구조를 만듭니다. 책 뒤의 ‘찾아보기’와 같습니다. 찾아보기가 여러 개 있을 수 있듯, 비클러스터형 인덱스는 테이블당 여러 개 생성할 수 있습니다.

구분클러스터형 인덱스비클러스터형 인덱스
개수테이블당 1개테이블당 여러 개
물리적 정렬데이터 자체가 인덱스 순서로 정렬됨데이터는 정렬되지 않고, 인덱스만 정렬됨
데이터 저장리프 노드가 실제 데이터 페이지리프 노드가 데이터 위치를 가리키는 포인터
비유영어 사전책의 찾아보기 (인덱스)
성능범위 검색에 매우 빠름단일 행 검색에 빠름

복합 인덱스와 컬럼 순서의 중요성

두 개 이상의 컬럼을 묶어 하나의 인덱스로 만들 수 있는데, 이를 복합 인덱스(Composite Index) 라고 합니다.

CREATE INDEX idx_lastname_firstname ON users (last_name, first_name);

복합 인덱스에서 가장 중요한 것은 컬럼의 순서입니다. 위 인덱스는 last_name으로 검색하거나, last_name과 first_name으로 함께 검색할 때 효과적으로 사용됩니다. 하지만 first_name만으로 검색하는 경우에는 인덱스를 제대로 활용할 수 없습니다. 이는 인덱스가 (last_name, first_name) 순서로 정렬되어 있기 때문입니다. 따라서 쿼리에서 자주 사용되는 조회 조건의 순서를 고려하여 복합 인덱스를 설계해야 합니다.

카디널리티(Cardinality)가 낮은 컬럼을 주의하라

카디널리티란 특정 컬럼의 데이터 값들의 중복 정도를 나타내는 지표입니다. 주민등록번호나 email처럼 모든 값이 고유한 컬럼은 카디널리티가 ‘높다’고 하고, 성별(‘남’, ‘여’)이나 주문상태(‘결제완료’, ‘배송중’, ‘배송완료’)처럼 몇 가지 값만 반복되는 컬럼은 카디널리티가 ‘낮다’고 합니다.

카디널리티가 낮은 컬럼에 인덱스를 거는 것은 대부분의 경우 비효율적입니다. 예를 들어, ‘성별’ 컬럼에 인덱스를 걸고 WHERE gender = '남' 이라는 쿼리를 실행한다고 가정해 봅시다. 이 쿼리는 전체 데이터의 약 50%를 가져올 것입니다. 이 경우, 데이터베이스 옵티마이저는 인덱스를 통해 데이터 위치를 일일이 찾아가는 것보다 그냥 테이블 전체를 스캔하는 것이 더 빠르다고 판단할 확률이 높습니다. 인덱스는 좁은 범위를 선택적으로 조회할 때 효과적인 도구이지, 넓은 범위를 조회할 때는 오히려 부담이 될 수 있습니다.

5. 결론: 인덱스는 예술이자 과학이다

데이터베이스 인덱스는 단순히 추가하면 빨라지는 마법이 아니라, 명확한 동작 원리와 트레이드오프를 가진 정교한 도구입니다.

  • 인덱스는 읽기 성능을 극대화하지만 쓰기 성능을 저하시킨다.

  • B-Tree 구조 덕분에 데이터 양에 관계없이 일관되고 빠른 검색이 가능하다.

  • WHERE, JOIN, ORDER BY 등 특정 쿼리 패턴에서 효과적이다.

  • 카디널리티, 복합 인덱스의 컬럼 순서, 클러스터링 여부 등을 종합적으로 고려하여 전략적으로 설계해야 한다.

훌륭한 개발자는 무분별하게 인덱스를 추가하는 대신, 서비스의 쿼리 패턴을 분석하고 데이터의 특성을 파악하여 꼭 필요한 곳에, 최적의 형태로 인덱스를 설계합니다. 이 핸드북이 여러분의 시스템을 더 빠르고 효율적으로 만드는 데 훌륭한 길잡이가 되기를 바랍니다.

레퍼런스(References)

인덱스

그래프 뷰

백링크