인덱스가 쿼리를 느리게 만드는 함정

개발자라면 누구나 한 번쯤 '쿼리 속도가 느리다'는 문제에 직면합니다. 그리고 가장 먼저 떠올리는 해결책은 단연 '인덱스(Index)'입니다. 마치 마법의 은 탄환처럼, 인덱스를 추가하면 거짓말처럼 쿼리 성능이 개선되는 경험을 하곤 합니다. 우리는 흔히 "인덱스 = 성능 향상"이라는 공식을 당연하게 받아들입니다. 하지만 만약 이 공식이 항상 성립하지 않는다면 어떨까요? 오히려 잘 만들어진 인덱스가 시스템 전체의 성능을 갉아먹는 주범이 될 수 있다면 믿으시겠습니까?

이 글은 우리가 맹신했던 데이터베이스 인덱스의 이면을 깊숙이 파헤쳐 봅니다. 단순히 인덱스가 무엇인지 설명하는 것을 넘어, 인덱스가 어떤 원리로 작동하기에 쿼리 성능을 개선하는지, 그리고 어떤 상황에서는 왜 오히려 성능의 발목을 잡는 '함정'이 되는지 그 근본적인 원인을 분석할 것입니다. 이 글을 끝까지 읽고 나면, 여러분은 더 이상 무분별하게 CREATE INDEX를 외치지 않게 될 것입니다. 대신, 데이터의 특성과 워크로드를 꿰뚫어 보는 날카로운 시각으로 데이터베이스의 성능을 자유자재로 조율하는 'SQL 튜닝' 전문가로 거듭나게 될 것입니다.

데이터베이스 인덱스, 정말 빠르기만 할까?

데이터베이스 인덱스를 비유할 때 가장 흔히 사용되는 것이 바로 '책의 맨 뒤에 있는 찾아보기'입니다. 수백, 수천 페이지에 달하는 방대한 책에서 특정 단어가 어느 페이지에 있는지 찾기 위해 첫 페이지부터 마지막 페이지까지 일일이 넘겨보는 사람은 없습니다. 대신, 책 뒤의 '찾아보기'에서 원하는 단어를 찾고, 거기에 적힌 페이지 번호를 바로 펼쳐보는 것이 훨씬 효율적입니다. 데이터베이스 인덱스도 이와 정확히 동일한 역할을 수행합니다.

거대한 테이블에서 특정 조건을 만족하는 데이터를 찾기 위해 모든 데이터를 순차적으로 스캔하는 것(이를 Full Table Scan이라고 합니다)은 매우 비효율적입니다. 인덱스는 특정 컬럼(또는 여러 컬럼)의 값과 해당 값이 저장된 데이터의 물리적 위치 주소(포인터)를 키-값 쌍으로 미리 정렬하여 저장해 둔 별도의 자료구조입니다. 데이터베이스는 쿼리 요청이 들어오면, 이 정렬된 인덱스 구조를 먼저 탐색하여 원하는 데이터의 위치를 빠르게 찾아냅니다. 이는 Full Table Scan에 비해 I/O(Input/Output) 작업을 획기적으로 줄여주므로, 검색(SELECT) 쿼리의 성능을 극적으로 향상시킵니다.

핵심은 '정렬'입니다. 인덱스가 빠른 이유는 데이터가 특정 기준으로 '미리 정렬'되어 있기 때문입니다. 정렬된 데이터 구조는 특정 값을 찾는 데 매우 효율적인 탐색 알고리즘을 적용할 수 있게 해줍니다.

하지만 이 세상에 공짜 점심은 없듯이, 인덱스 역시 대가를 요구합니다. 바로 '쓰기(Write) 성능 저하'와 '추가 저장 공간'이라는 비용입니다. INSERT, UPDATE, DELETE와 같은 데이터 변경 작업이 발생할 때마다, 데이터베이스는 테이블의 데이터를 변경하는 것뿐만 아니라, 해당 테이블에 생성된 모든 인덱스의 내용 또한 일관성을 유지하기 위해 함께 수정해야 합니다. 예를 들어 새로운 데이터가 추가되면, 모든 인덱스 구조에 새로운 키-값 쌍을 정렬된 위치에 삽입해야 합니다. 기존 데이터가 수정되면 인덱스 키 값 자체가 바뀔 수 있고, 이 경우 기존 인덱스 항목을 삭제하고 새로운 위치에 다시 삽입해야 할 수도 있습니다.

인덱스가 많으면 많을수록 이 작업의 부하는 기하급수적으로 늘어납니다. 검색은 빨라지지만, 데이터 변경 작업은 점점 느려지는 '양날의 검'이 되는 것입니다. 따라서 쿼리 최적화와 SQL 튜닝의 핵심은 이 '읽기 성능 향상'과 '쓰기 성능 저하' 사이의 미묘한 균형점을 찾아내는 데 있습니다. 이제부터 그 균형점을 찾기 위한 첫걸음으로, 인덱스의 심장부인 B-Tree 자료구조부터 자세히 살펴보겠습니다.

모든 길은 B-Tree로 통한다: 인덱스의 핵심 동작 원리

대부분의 현대적인 관계형 데이터베이스(MySQL, PostgreSQL, Oracle, SQL Server 등)는 인덱스를 구현하기 위한 기본 자료구조로 B-Tree(Balanced Tree)를 사용합니다. 왜 하필 B-Tree일까요? 컴퓨터 과학에는 수많은 트리 자료구조가 있는데, 데이터베이스는 왜 B-Tree를 선택했을까요? 그 이유는 데이터베이스의 데이터가 주로 어디에 저장되는지를 생각해보면 명확해집니다.

데이터베이스의 데이터는 메모리가 아닌, 하드 디스크나 SSD와 같은 보조 기억 장치에 저장됩니다. 메모리에 비해 디스크는 데이터 접근 속도가 현저히 느립니다. 디스크 I/O는 시스템 전체의 성능에 가장 큰 병목을 유발하는 작업 중 하나입니다. 따라서 데이터베이스 성능을 높이려면 디스크 I/O 횟수를 최소화하는 것이 절대적으로 중요합니다.

B-Tree는 바로 이 '디스크 I/O 최소화'에 최적화된 자료구조입니다. 일반적인 이진 트리(Binary Tree)는 하나의 노드에 하나의 데이터만 저장하지만, B-Tree는 하나의 노드(데이터베이스에서는 '페이지' 또는 '블록'이라고 부릅니다)에 수백, 수천 개의 키-값 쌍을 저장할 수 있습니다. 이렇게 함으로써 트리의 전체 높이(height)를 극단적으로 낮출 수 있습니다.

예를 들어, 1,000만 건의 데이터를 저장한다고 가정해 봅시다.

이진 탐색 트리: 최상의 경우(완전 이진 트리) 트리의 높이는 약 24(log₂(10,000,000))가 됩니다. 최악의 경우(편향 트리)에는 1,000만이 될 수도 있습니다. 데이터를 찾기 위해 평균적으로 수십 번의 노드 탐색, 즉 수십 번의 디스크 I/O가 필요할 수 있습니다.
B-Tree: 하나의 노드에 100개의 자식 노드를 가질 수 있다고 가정하면, 트리의 높이는 단 4(log₁₀₀(10,000,000))에 불과합니다. 이는 데이터를 찾는 데 필요한 디스크 I/O가 단 4번으로 줄어든다는 것을 의미합니다. 이것이 B-Tree가 데이터베이스 인덱스에 채택된 결정적인 이유입니다.

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

루트 노드 (Root Node): 트리의 가장 최상위에 있는 단 하나의 노드입니다. 모든 탐색은 루트 노드에서 시작됩니다.
브랜치 노드 (Branch Node): 루트와 리프 사이의 중간 노드들입니다. 자식 노드를 가리키는 포인터와 키 값의 범위를 가지고 있어, 탐색 경로를 안내하는 역할을 합니다.
리프 노드 (Leaf Node): 트리의 가장 마지막 레벨에 있는 노드들입니다. 실제 인덱스 키 값과 데이터의 위치를 가리키는 포인터(예: ROWID)를 저장하고 있습니다. 또한, 리프 노드들은 서로를 가리키는 양방향 연결 리스트(Doubly Linked List) 형태로 연결되어 있어, 특정 범위의 데이터를 순차적으로 스캔하는 '범위 스캔(Range Scan)'에 매우 효율적입니다.

데이터를 찾는 과정(Index Seek)을 상상해 봅시다.


-- employees 테이블에서 employee_id가 10078인 직원을 찾는 쿼리
SELECT * FROM employees WHERE employee_id = 10078;

데이터베이스는 `employee_id` 인덱스의 루트 노드를 디스크에서 읽어 메모리에 올립니다. (I/O 1회 발생)
루트 노드에 있는 키 값들을 비교하여 10078이 포함될 범위를 찾고, 해당 범위가 가리키는 브랜치 노드의 주소를 확인합니다.
해당 브랜치 노드를 디스크에서 읽어옵니다. (I/O 2회 발생)
다시 브랜치 노드 내에서 키 값들을 비교하여 다음 경로를 결정하고, 다음 레벨의 노드(또는 리프 노드)로 이동합니다.
이 과정을 반복하여 최종적으로 10078이라는 키 값을 가진 리프 노드에 도달합니다. (I/O 3~4회 발생)
리프 노드에서 `employee_id` 10078에 해당하는 데이터의 물리적 주소를 찾아, 최종적으로 테이블의 해당 데이터 블록을 읽어옵니다.

수백만, 수천만 건의 데이터 속에서 단 몇 번의 디스크 I/O만으로 원하는 데이터를 정확히 찾아내는 것, 이것이 바로 B-Tree 인덱스의 힘입니다. 또한 B-Tree는 이름(Balanced Tree)에서 알 수 있듯이, 데이터의 삽입/삭제가 발생해도 트리가 한쪽으로 치우치지 않도록 스스로 균형을 맞추는 알고리즘을 내장하고 있습니다. 이를 통해 어떤 값을 찾더라도 항상 비슷한 탐색 성능을 보장합니다. 이 균형을 맞추는 과정에서 '페이지 분할(Page Split)'이라는 작업이 발생하며, 이것이 쓰기 성능에 부하를 주는 요인 중 하나가 됩니다.

정렬된 책장 vs 별도의 색인: 클러스터형과 비클러스터형 인덱스

B-Tree라는 기본 원리는 같지만, 데이터베이스 인덱스는 크게 두 가지 종류로 나뉩니다: 클러스터형 인덱스(Clustered Index)와 비클러스터형 인덱스(Non-Clustered Index)입니다. 이 둘의 차이를 이해하는 것은 효과적인 인덱스 설계를 위한 가장 중요한 열쇠 중 하나입니다. 많은 개발자들이 이 차이를 명확히 알지 못해 잘못된 인덱스를 설계하는 실수를 저지릅니다.

이 둘의 차이를 비유하자면, 클러스터형 인덱스는 '내용 자체가 사전 순으로 정렬된 영어사전'과 같고, 비클러스터형 인덱스는 '일반 책의 맨 뒤에 있는 찾아보기'와 같습니다.

클러스터형 인덱스 (Clustered Index)

영어사전에서 'database'라는 단어를 찾을 때, 우리는 'd'로 시작하는 부분을 펼치고, 그 안에서 'da', 'dat' 순서로 쉽게 찾아 들어갈 수 있습니다. 단어 자체가 이미 정렬되어 있기 때문입니다. 클러스터형 인덱스는 이와 같이 테이블의 데이터 자체를 인덱스 키 컬럼을 기준으로 물리적으로 정렬하여 저장합니다.

물리적 정렬: 이것이 가장 핵심적인 특징입니다. 데이터 행(Row)들이 인덱스 키의 순서대로 디스크에 저장됩니다.
테이블 당 하나만 존재: 책의 내용을 두 가지 기준으로 동시에 정렬할 수 없듯이, 테이블의 데이터도 단 하나의 기준으로만 물리적으로 정렬될 수 있습니다. 따라서 클러스터형 인덱스는 테이블 당 오직 하나만 생성할 수 있습니다.
리프 노드가 곧 데이터 페이지: B-Tree의 리프 노드가 별도의 포인터를 가지는 것이 아니라, 데이터가 저장된 데이터 페이지 그 자체입니다. 인덱스를 통해 리프 노드를 찾으면, 추가적인 I/O 없이 바로 원하는 데이터를 얻을 수 있습니다.
성능 특성:
- 장점: 인덱스 키를 이용한 범위 검색(예: `WHERE id BETWEEN 100 AND 200`)에 압도적으로 빠른 성능을 보입니다. 데이터 자체가 정렬되어 있어, 시작점만 찾으면 그 이후로는 디스크에서 순차적으로 읽기만 하면 되기 때문입니다.
- 단점: 데이터의 `INSERT`, `UPDATE`, `DELETE` 시 성능 저하가 비클러스터형 인덱스보다 심할 수 있습니다. 새로운 데이터가 중간에 삽입될 경우, 기존 데이터들의 물리적 위치를 뒤로 밀어내거나, 공간이 부족하면 페이지를 나누는 '페이지 분할'이 발생하여 상당한 부하를 유발합니다.

대부분의 데이터베이스는 `PRIMARY KEY`를 지정하면 자동으로 해당 컬럼에 클러스터형 인덱스를 생성합니다. (MySQL의 InnoDB 엔진이 대표적입니다.)

비클러스터형 인덱스 (Non-Clustered Index)

일반 책의 '찾아보기'에서 '데이터베이스'라는 단어를 찾으면 '15, 48, 120 페이지 참조'와 같이 실제 내용이 있는 페이지 번호가 적혀 있습니다. 비클러스터형 인덱스는 이처럼 데이터 자체는 정렬되지 않은 상태(Heap 또는 클러스터형 인덱스 순서)로 두고, 별도의 공간에 인덱스 키와 실제 데이터의 위치 주소를 담은 B-Tree를 생성합니다.

논리적 정렬: 데이터의 물리적 순서와는 무관하게, 인덱스 페이지만 키 값을 기준으로 정렬되어 있습니다.
테이블 당 여러 개 존재: 책에 '용어 찾아보기', '인명 찾아보기' 등 여러 종류의 찾아보기를 만들 수 있듯이, 비클러스터형 인덱스는 하나의 테이블에 여러 개를 생성할 수 있습니다. `CREATE INDEX` 구문으로 생성하는 대부분의 인덱스가 바로 비클러스터형 인덱스입니다.
리프 노드가 포인터를 가짐: 리프 노드에는 실제 데이터가 아닌, 데이터의 위치를 가리키는 포인터(RID - Row ID 또는 클러스터형 인덱스 키 값)가 저장되어 있습니다.
성능 특성:
- 장점: 데이터 변경 작업 시 클러스터형 인덱스보다 부하가 적습니다. 데이터의 물리적 위치를 변경할 필요 없이, 인덱스 페이지만 수정하면 되기 때문입니다.
- 단점: 데이터를 조회할 때 항상 두 단계의 과정을 거칩니다. 먼저 비클러스터형 인덱스를 탐색하여 데이터의 위치 포인터를 찾고(Index Seek), 그 포인터를 이용해 다시 테이블의 데이터 페이지에 접근(Key Lookup 또는 RID Lookup)해야 합니다. 이 과정에서 추가적인 디스크 I/O가 발생하여 클러스터형 인덱스보다 성능이 떨어질 수 있습니다.

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

구분	클러스터형 인덱스 (Clustered Index)	비클러스터형 인덱스 (Non-Clustered Index)
핵심 특징	데이터 행을 인덱스 키 기준으로 물리적으로 정렬	인덱스 페이지만 논리적으로 정렬, 데이터는 별도 저장
비유	영어 사전 (내용 자체가 정렬됨)	책의 찾아보기 (색인과 본문이 분리됨)
생성 개수	테이블 당 1개만 가능	테이블 당 여러 개 생성 가능
리프 노드	데이터 페이지 그 자체	데이터 위치를 가리키는 포인터 (ROWID, 클러스터 키 등)
데이터 조회 과정	인덱스 탐색으로 데이터 즉시 접근	인덱스 탐색 → 포인터 획득 → 데이터 페이지 접근 (Key/RID Lookup)
조회 성능	범위 검색(Range Scan)에 매우 유리	단일 값 검색(Point Lookup)에 효율적
쓰기(CUD) 성능	데이터 재정렬, 페이지 분할로 인해 부하가 큼	인덱스 페이지만 수정하므로 상대적으로 부하가 적음
저장 공간	별도의 추가 공간이 거의 없음 (인덱스 구조 자체만)	인덱스 구조를 위한 별도의 저장 공간이 필요
주요 사용처	PK, 순차적으로 증가하며 범위 검색이 잦은 컬럼 (주문일시, 순번 등)	FK, 검색 조건(WHERE)에 자주 사용되는 대부분의 컬럼

이 두 인덱스의 특성을 이해하면, 어떤 상황에 어떤 인덱스를 선택해야 할지 전략을 세울 수 있습니다. 예를 들어, 순차적으로 증가하는 `AUTO_INCREMENT` PK는 클러스터형 인덱스의 좋은 후보입니다. 새로운 데이터가 항상 마지막에 추가되므로 페이지 분할이 거의 발생하지 않기 때문입니다. 반면, 이름이나 상태 코드처럼 검색 조건에 자주 쓰이지만, 범위 검색보다는 특정 값 검색이 많은 컬럼에는 비클러스터형 인덱스가 더 적합합니다.

두 마리 토끼 잡기: 복합 인덱스 제대로 활용하기

실제 애플리케이션에서는 하나의 컬럼만으로 데이터를 검색하는 경우보다 여러 컬럼을 조합하여 검색하는 경우가 훨씬 많습니다. 예를 들어, "개발팀(department) 소속의 시니어(seniority) 등급인 직원(employee)을 찾아라"와 같은 쿼리가 일반적입니다. 이럴 때 사용하는 것이 바로 복합 인덱스(Composite Index) 또는 다중 컬럼 인덱스(Multi-column Index)입니다.

복합 인덱스는 이름 그대로 두 개 이상의 컬럼을 묶어 하나의 인덱스로 만드는 것입니다.


CREATE INDEX idx_dept_seniority ON employees (department, seniority);

위와 같이 인덱스를 생성하면, 인덱스는 먼저 `department` 컬럼 값으로 정렬되고, 같은 `department` 내에서는 `seniority` 컬럼 값으로 다시 정렬됩니다. 마치 전화번호부가 '성(Last Name)'으로 먼저 정렬되고, 같은 성 안에서 '이름(First Name)'으로 다시 정렬되는 것과 같습니다.

가장 중요한 것은 '컬럼의 순서'

복합 인덱스를 사용할 때 가장 중요하고, 또 가장 많은 개발자가 실수하는 부분이 바로 '컬럼의 순서'입니다. 인덱스를 구성하는 컬럼의 순서는 쿼리 옵티마이저가 해당 인덱스를 사용할 수 있는지 여부를 결정하는 절대적인 기준이 됩니다.

위에서 생성한 `idx_dept_seniority` 인덱스(`department`, `seniority` 순서)가 어떤 쿼리에 효과적일지 살펴봅시다.

CASE 1: WHERE department = '개발팀'
✅ 인덱스 사용 가능. 인덱스의 첫 번째 컬럼인 `department`를 조건으로 사용했으므로, 옵티마이저는 이 인덱스를 효율적으로 탐색하여 '개발팀'으로 시작하는 인덱스 블록을 빠르게 찾을 수 있습니다.
CASE 2: WHERE department = '개발팀' AND seniority = '시니어'
✅ 인덱스 사용 매우 효율적. 인덱스를 구성하는 모든 컬럼을 순서대로 사용했습니다. 옵티마이저는 '개발팀' 블록을 찾고, 그 안에서 '시니어' 값을 찾아 정확한 위치를 집어낼 수 있습니다.
CASE 3: WHERE seniority = '시니어'
❌ 인덱스 사용 비효율적 (또는 사용 불가). 인덱스는 `department`로 먼저 정렬되어 있습니다. `seniority` 값은 각 부서별로 흩어져 있기 때문에, '시니어'라는 값만으로는 인덱스 탐색의 시작점을 잡을 수 없습니다. 이 경우 옵티마이저는 인덱스 전체를 훑는 '인덱스 풀 스캔(Index Full Scan)'을 하거나, 차라리 인덱스를 포기하고 '테이블 풀 스캔'을 선택할 수도 있습니다.
CASE 4: WHERE department = '개발팀' ORDER BY seniority
✅ 매우 효율적. `WHERE` 절에서 인덱스 선행 컬럼을 사용하고, `ORDER BY` 절에서 그 다음 컬럼을 사용했습니다. 인덱스 자체가 이미 이 순서로 정렬되어 있기 때문에, 데이터베이스는 별도의 정렬(Sort) 작업을 수행할 필요 없이 인덱스를 순서대로 읽기만 하면 됩니다. 이는 쿼리 성능에 큰 향상을 가져옵니다.

복합 인덱스 컬럼 순서의 황금률: 카디널리티(Cardinality, 값의 고유성/분포도)가 높고, 등호(=) 조건으로 자주 사용되는 컬럼을 앞쪽에 배치하라.

쿼리 최적화의 꽃, 커버링 인덱스 (Covering Index)

복합 인덱스의 활용법 중 가장 강력한 기술은 바로 커버링 인덱스입니다. 앞서 비클러스터형 인덱스는 인덱스 탐색 후 실제 데이터를 찾기 위해 테이블에 한 번 더 접근하는 'Key Lookup'이 발생한다고 했습니다. 커버링 인덱스는 이 추가적인 접근 자체를 없애는 기법입니다.

만약 쿼리에서 요구하는 모든 데이터(SELECT, WHERE, `ORDER BY` 절 등)가 인덱스 안에 모두 포함되어 있다면, 데이터베이스는 굳이 테이블까지 찾아갈 필요 없이 인덱스 스캔만으로 쿼리를 완료할 수 있습니다. 이것이 바로 '커버링 인덱스'입니다.


-- employees 테이블에는 employee_id(PK), department, name, salary 컬럼이 있다.
-- "개발팀 소속 직원의 이름과 연봉을 알고 싶다"는 쿼리
SELECT name, salary FROM employees WHERE department = '개발팀';

-- CASE 1: department에만 인덱스가 있는 경우
CREATE INDEX idx_dept ON employees (department);
-- 1. idx_dept에서 '개발팀'인 데이터의 포인터(PK)를 찾는다. (Index Seek)
-- 2. 찾은 포인터(PK)를 가지고 employees 테이블로 가서 name, salary를 가져온다. (Key Lookup)
--    -> '개발팀' 직원 수만큼 Key Lookup 발생

-- CASE 2: 커버링 인덱스를 사용하는 경우
CREATE INDEX idx_dept_name_salary ON employees (department, name, salary);
-- 1. idx_dept_name_salary에서 '개발팀'인 데이터를 찾는다. (Index Seek)
-- 2. 인덱스 안에 이미 name과 salary 정보가 모두 포함되어 있다!
-- 3. 테이블에 접근할 필요 없이, 인덱스에서 바로 결과를 반환하고 쿼리 종료.
--    -> Key Lookup이 발생하지 않음!

커버링 인덱스는 디스크 I/O를 획기적으로 줄여주기 때문에, 적절하게 사용하면 쿼리 최적화에 매우 큰 효과를 볼 수 있습니다. 하지만, 인덱스에 컬럼을 많이 추가할수록 인덱스의 크기가 커지고, 쓰기 작업(INSERT, UPDATE) 시의 부하도 함께 증가한다는 점을 반드시 기억해야 합니다. 모든 쿼리를 커버링 인덱스로 만들려고 시도하는 것은 오히려 전체적인 시스템 성능을 저하시키는 지름길이 될 수 있습니다.

옵티마이저의 속마음 엿보기: 실행 계획 분석

우리가 인덱스를 아무리 잘 설계했다고 생각해도, 실제로 그 인덱스를 사용할지 말지를 최종적으로 결정하는 것은 데이터베이스의 쿼리 옵티마이저(Query Optimizer)입니다. 옵티마이저는 우리가 작성한 SQL 쿼리를 실행하는 가장 효율적인 방법, 즉 '실행 계획(Execution Plan)'을 수립하는 두뇌와 같은 역할을 합니다.

우리가 만든 인덱스가 실제로 쿼리에 사용되고 있는지, 혹은 왜 사용되지 않는지를 확인하려면 이 실행 계획을 반드시 분석할 줄 알아야 합니다. 실행 계획은 데이터베이스에게 "이 쿼리를 어떻게 실행할 거니?"라고 묻고 그 대답을 듣는 것과 같습니다. 대부분의 데이터베이스는 `EXPLAIN`, `EXPLAIN ANALYZE` (PostgreSQL), `SET SHOWPLAN_TEXT ON` (SQL Server) 등의 명령어를 통해 특정 쿼리의 실행 계획을 보여주는 기능을 제공합니다.

실행 계획을 보면 수많은 연산자(Operator)들이 트리 구조로 나타나는데, 성능 튜닝 관점에서 우리가 주목해야 할 핵심적인 연산자는 다음과 같습니다.

Table Scan (또는 Full Table Scan):
가장 피해야 할 최악의 연산. 테이블의 처음부터 끝까지 모든 데이터를 읽어서 조건에 맞는지 검사하는 방식입니다. 데이터가 많을수록 성능은 선형적으로 저하됩니다. 실행 계획에 이 연산자가 보인다면, 적절한 인덱스가 없거나, 인덱스가 있더라도 옵티마이저가 사용하지 않기로 결정했다는 신호입니다.
Index Scan (또는 Full Index Scan):
인덱스의 리프 노드 전체를 처음부터 끝까지 스캔하는 방식입니다. 테이블 전체를 스캔하는 것보다는 일반적으로 빠르지만(인덱스가 테이블보다 작으므로), 여전히 비효율적일 수 있습니다. `WHERE` 절 없이 `ORDER BY`만 인덱스 컬럼으로 수행하는 경우 등에 나타날 수 있습니다.
Index Seek:
우리가 가장 원하는 이상적인 연산. B-Tree를 효율적으로 타고 내려가 원하는 데이터가 있는 지점을 정확히 '찾아내는' 방식입니다. 대량의 데이터에서 소수의 특정 데이터만 추출할 때 나타나며, 매우 빠른 성능을 보장합니다.
Key Lookup (또는 RID Lookup, Bookmark Lookup):
비클러스터형 인덱스에서 Index Seek 이후에 발생하는 연산. 인덱스에서 찾은 포인터를 가지고 실제 테이블 데이터에 접근하는 과정입니다. 이 연산이 너무 많이 발생하면(수천, 수만 번) 심각한 성능 저하를 유발할 수 있으며, 이 경우 커버링 인덱스를 고려해봐야 합니다.

실행 계획 분석 예시


-- 쿼리: SELECT * FROM orders WHERE customer_id = 123;

-- [BAD] customer_id에 인덱스가 없을 때의 실행 계획 (가상)
-> Table Scan on orders (cost=1052.34 rows=10)

-- [GOOD] customer_id에 인덱스가 있을 때의 실행 계획 (가상)
-> Nested Loops
  -> Index Seek on idx_customer_id (cost=0.43 rows=10)
  -> Key Lookup on orders (cost=2.87 rows=10)

위 예시에서 볼 수 있듯이, 인덱스가 없을 때는 `Table Scan`이 발생하며 예상 비용(cost)이 1052로 매우 높습니다. 반면 인덱스를 생성한 후에는 `Index Seek`와 `Key Lookup`으로 바뀌면서 전체 비용이 3.3 정도로 획기적으로 줄어든 것을 볼 수 있습니다. 이처럼 실행 계획을 분석하면 내가 만든 인덱스가 의도대로 동작하는지, 쿼리의 병목 지점이 어디인지 명확하게 파악하고 성능 개선의 방향을 잡을 수 있습니다.

함정 카드 발동! 인덱스가 성능을 저하시키는 5가지 시나리오

지금까지 인덱스의 작동 원리와 긍정적인 측면을 깊이 있게 살펴보았습니다. 이제 이 글의 핵심 주제인 '인덱스가 오히려 성능을 저하시키는 함정'에 대해 본격적으로 알아보겠습니다. 다음 시나리오들은 현업에서 빈번하게 발생하는 문제들이며, 이를 이해하면 불필요한 인덱스를 만들거나 잘못 사용하는 실수를 피할 수 있습니다.

1. 카디널리티(Cardinality)가 극도로 낮은 컬럼

카디널리티란 특정 컬럼에 포함된 값의 고유성(uniqueness) 정도를 나타내는 지표입니다. 예를 들어, 주민등록번호 컬럼은 모든 값이 고유하므로 카디널리티가 매우 높습니다. 반면, '성별' 컬럼은 '남', '여', '기타' 등 몇 가지 값만 반복되므로 카디널리티가 매우 낮습니다.

함정: 카디널리티가 낮은 컬럼에 인덱스를 생성하는 것은 거의 대부분의 경우 무의미하거나 오히려 해가 됩니다. 예를 들어 100만 명의 회원이 있는 테이블의 'is_active' (활성/비활성, Y/N) 컬럼에 인덱스를 걸었다고 가정해 봅시다. `WHERE is_active = 'Y'` 라는 쿼리를 실행하면, 옵티마이저는 어떤 선택을 할까요?

활성 회원이 전체의 80%라고 가정하면, 인덱스를 통해 'Y'인 데이터의 주소를 찾아도 결국 테이블 전체 데이터의 80%에 접근해야 합니다. 이럴 경우, 인덱스를 읽고(I/O), 다시 테이블을 읽는(I/O) 이중 작업을 하느니, 그냥 처음부터 테이블 전체를 순차적으로 쭉 읽는(Full Table Scan) 것이 오히려 더 빠릅니다. 옵티마이저는 이러한 통계 정보를 기반으로 판단하기 때문에, 이 인덱스를 무시하고 테이블 스캔을 선택할 가능성이 매우 높습니다. 결국, 이 인덱스는 쓰기 작업 시 부하만 발생시키는 '쓰레기 인덱스'가 되고 맙니다.

2. 무분별하게 추가된 과도한 인덱스

느린 쿼리가 발견될 때마다 해당 `WHERE` 절에 있는 컬럼들을 모두 인덱스로 추가하는 것은 가장 흔히 저지르는 실수입니다.

함정: 앞서 강조했듯이, 인덱스는 `SELECT`의 성능을 높이는 대신 `INSERT`, `UPDATE`, `DELETE`의 성능을 희생시킵니다. 테이블에 인덱스가 10개 있다면, 데이터 1건을 추가할 때 테이블 자체의 변경 작업 1번과 함께 10개의 인덱스에 대한 추가, 정렬, 페이지 분할 작업이 발생합니다. 특히 쓰기 작업이 매우 빈번한(Write-Heavy) 테이블, 예를 들어 실시간 로그 테이블, IoT 센서 데이터 테이블 등에 수많은 인덱스를 추가하는 것은 시스템에 재앙을 초래할 수 있습니다. 읽기 성능을 조금 개선하려다 전체적인 쓰기 처리량을 심각하게 떨어뜨려 시스템 전체가 마비될 수 있습니다.

인덱스의 법칙: 꼭 필요한 곳에, 최소한으로. 인덱스는 비용입니다. 추가하기 전에 항상 그로 인한 쓰기 부하와 저장 공간 비용을 함께 고려해야 합니다.

3. 데이터가 매우 적은 작은 테이블

당연해 보이지만 의외로 간과하기 쉬운 부분입니다. 테이블에 데이터가 수백, 수천 건 정도로 매우 적다면 인덱스는 필요 없습니다.

함정: 데이터가 적은 테이블은 대부분의 경우 데이터 페이지 몇 개만으로 디스크에 저장됩니다. 이런 테이블을 스캔하는 것은 메모리 위에서 빠르게 처리되므로 Full Table Scan의 비용이 매우 낮습니다. 오히려 B-Tree 인덱스를 탐색하는 오버헤드(루트 노드부터 리프 노드까지 이동)가 Full Table Scan보다 더 클 수 있습니다. 옵티마이저 역시 테이블의 크기가 작다는 것을 알고 있으므로, 인덱스가 있더라도 사용하지 않고 테이블 스캔을 선택합니다.

4. 쿼리 조건절에 함수나 연산을 가하는 경우 (SARGable)

인덱스가 있는 컬럼이라도, 쿼리에서 해당 컬럼을 가공하면 옵티마이저는 인덱스를 활용할 수 없게 됩니다.

함정: 인덱스는 컬럼의 '원본 값'을 기준으로 정렬되어 있습니다. 만약 쿼리에서 함수를 사용하거나 연산을 수행하면, 그 결과 값은 인덱스에 저장된 값과 다르기 때문에 인덱스를 이용한 검색이 불가능해집니다. 이를 SARGable(Search ARGument-able)하지 않다고 표현합니다.


-- `order_date` 컬럼에 인덱스가 존재함

-- [BAD] 인덱스 사용 불가 (non-SARGable)
-- order_date에 SUBSTRING 함수를 적용하여 원본 값을 변경했음
SELECT * FROM orders WHERE SUBSTRING(order_date, 1, 4) = '2025';

-- [GOOD] 인덱스 사용 가능 (SARGable)
-- order_date 컬럼 자체는 변경하지 않고, 비교 대상을 범위로 지정
SELECT * FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

`LIKE` 구문에서도 마찬가지입니다. `WHERE name LIKE '%길동'` 처럼 와일드카드(%)가 앞에 붙으면 인덱스를 사용할 수 없습니다. 인덱스는 '김'으로 시작하는 단어는 빠르게 찾을 수 있지만, '길동'으로 끝나는 단어는 찾을 수 없기 때문입니다.

5. 암시적 형변환 (Implicit Type Conversion)

이것은 가장 찾아내기 어렵고 미묘한 성능 저하의 원인 중 하나입니다. 컬럼의 데이터 타입과 쿼리의 비교 값의 데이터 타입이 다를 때 발생합니다.

함정: `user_phone`이라는 컬럼이 `VARCHAR(20)` 타입으로 정의되어 있고, 인덱스도 걸려있다고 가정해 봅시다. 그런데 애플리케이션 코드의 실수로 숫자형으로 쿼리를 전송했습니다.


-- user_phone 컬럼은 VARCHAR 타입
SELECT * FROM users WHERE user_phone = 01012345678; -- 숫자로 비교

이 경우, 데이터베이스는 문자열인 `user_phone` 컬럼의 모든 값을 숫자로 변환하여 비교하려고 시도합니다. 이는 결과적으로 4번 시나리오처럼 인덱스 컬럼에 함수(`TO_NUMBER()`)를 적용한 것과 동일한 효과를 낳습니다. 결국 옵티마이저는 인덱스를 포기하고 Full Table Scan을 수행하게 됩니다. 개발자는 코드 상의 작은 실수 하나 때문에 인덱스가 동작하지 않는다는 사실을 인지하지 못한 채 시스템 성능 저하의 원인을 찾아 헤매게 될 수 있습니다.

그렇다면 우리는 어떻게 해야 할까? 현명한 인덱스 설계 전략

지금까지 인덱스의 명과 암을 모두 살펴보았습니다. 인덱스가 성능 저하의 함정이 될 수 있다는 것을 알았으니, 이제는 그 함정을 피해 가는 현명한 전략을 세울 차례입니다. 성공적인 SQL 튜닝과 쿼리 최적화를 위한 인덱스 설계 원칙은 다음과 같습니다.

`WHERE`, `JOIN`, `ORDER BY`를 주목하라:
인덱스 생성의 제1원칙은 "자주 사용되는 검색 조건에 인덱스를 걸어라"입니다. 쿼리의 `WHERE` 절에 자주 등장하는 컬럼, `JOIN`의 연결고리가 되는 컬럼(FK), 그리고 정렬의 기준이 되는 `ORDER BY` 절의 컬럼이 인덱스 생성의 최우선 고려 대상입니다.
추측하지 말고, 측정하고 분석하라:
"이 쿼리가 느릴 것 같으니 인덱스를 걸자"는 식의 접근은 금물입니다. 항상 데이터베이스가 제공하는 모니터링 툴(예: MySQL의 Slow Query Log, PostgreSQL의 `pg_stat_statements`)을 통해 실제로 느린 쿼리를 식별하고, `EXPLAIN`을 통해 실행 계획을 분석하여 병목 지점을 정확히 찾아내야 합니다. 데이터에 기반한 결정만이 올바른 최적화로 이어집니다.
읽기 vs 쓰기 워크로드의 균형을 고려하라:
여러분이 다루는 테이블의 특성을 파악해야 합니다. 하루에 몇 번 업데이트되지 않지만 조회는 수백만 번 일어나는 '마스터 데이터' 테이블이라면 여러 개의 인덱스가 효과적일 수 있습니다. 반면, 초당 수천 건의 데이터가 쌓이는 '로그' 테이블이라면 인덱스를 최소화하고, 꼭 필요한 인덱스만 신중하게 추가해야 합니다. OLTP(온라인 트랜잭션 처리) 시스템인지, OLAP(온라인 분석 처리) 시스템인지에 따라 인덱스 전략은 완전히 달라져야 합니다.
복합 인덱스를 적극적으로 활용하되, 순서에 유의하라:
개별 컬럼에 여러 개의 단일 인덱스를 만드는 것보다, 함께 자주 사용되는 컬럼들을 묶어 하나의 복합 인덱스로 만드는 것이 더 효율적입니다. 이때, 카디널리티가 높고 `=` 조건으로 사용되는 컬럼을 앞에 배치하는 것을 잊지 마십시오.
커버링 인덱스의 힘을 이용하라:
자주 실행되는 핵심 쿼리가 있다면, 해당 쿼리가 요구하는 모든 컬럼을 포함하는 커버링 인덱스를 설계하는 것을 고려해 보세요. Key Lookup을 제거하는 것만으로도 놀라운 성능 향상을 경험할 수 있습니다.
지속적으로 관리하라:
인덱스는 한 번 만들고 끝이 아닙니다. 데이터가 계속 쌓이고 변경되면서 인덱스도 조각화(fragmentation)가 발생하여 성능이 저하될 수 있습니다. 데이터베이스에 따라 주기적으로 인덱스를 재구성(Rebuild)하거나 재정리(Reorganize)하는 유지보수 작업이 필요합니다.

결론: 인덱스는 만병통치약이 아닌 정교한 도구

우리는 '인덱스가 쿼리를 느리게 만드는 함정'이라는 주제로 긴 여정을 함께했습니다. 이제 여러분은 데이터베이스 인덱스가 단순히 쿼리 속도를 높여주는 마법이 아니라, 읽기와 쓰기 성능 사이에서 신중하게 균형을 맞춰야 하는 정교한 도구임을 이해하셨을 것입니다.

훌륭한 개발자는 단순히 코드를 작성하는 것을 넘어, 자신이 사용하는 기술의 내부 동작 원리를 깊이 이해하고 그에 따른 트레이드오프를 인지하는 사람입니다. B-Tree의 구조부터 옵티마이저의 실행 계획, 그리고 인덱스가 오히려 독이 되는 시나리오까지, 오늘 다룬 내용들은 여러분이 데이터베이스의 성능을 한 단계 높은 수준에서 제어할 수 있는 튼튼한 기반이 되어줄 것입니다.

이제 `CREATE INDEX`를 실행하기 전에 스스로에게 질문해 보십시오.
"이 인덱스는 정말 필요한가? 이로 인해 발생하는 비용은 무엇이며, 감당할 수 있는 수준인가? 더 나은 대안은 없는가?"

이 질문에 자신 있게 대답할 수 있을 때, 여러분은 비로소 데이터베이스를 진정으로 다룰 줄 아는 개발자로 거듭나게 될 것입니다. 데이터베이스의 성능은 애플리케이션 전체의 사용자 경험을 좌우하는 핵심 요소이며, 그 중심에 바로 '현명한 인덱스 활용'이 자리 잡고 있습니다.