데이터베이스 인덱스 생성 원리 및 튜닝 기초

카테고리 없음

데이터베이스 인덱스 생성 원리 및 튜닝 기초

bhey31 2026. 3. 12. 11:00

방대한 데이터베이스 세계에서 쿼리 응답 속도를 좌우하는 가장 핵심적인 기술은 단연 '인덱스(Index)'입니다. 두꺼운 전공서적에서 원하는 단어를 찾을 때 첫 장부터 넘기지 않고 맨 뒤의 색인(Index)을 찾아 페이지를 단숨에 펴는 것과 완벽히 같은 원리입니다. 실무에서 수천만 건의 데이터를 다룰 때 인덱스 없이 단순 조회(SELECT)를 수행하면 테이블 풀 스캔(Full Table Scan)이 발생하여 시스템 전체가 마비될 수 있습니다. 인덱스는 검색 속도를 비약적으로 상승시키는 마법의 도구이지만, 생성 원리를 모르고 남발하면 오히려 시스템을 망가뜨리는 독이 되기도 합니다. 오늘 포스팅에서는 인덱스의 근간이 되는 B-Tree 생성 원리부터 클러스터형/비클러스터형 인덱스의 차이, 결합 인덱스의 순서 설계 규칙, 그리고 실행 계획(Execution Plan) 분석을 통한 기초적인 SQL 튜닝 개념까지 핵심만 압축하여 명쾌하게 정리해 드리겠습니다.

1. 검색의 기적, B-Tree(Balanced Tree) 자료구조의 비밀

대부분의 관계형 데이터베이스(RDBMS) 인덱스는 'B-Tree(Balanced Tree)'라는 트리형 알고리즘 구조로 생성됩니다. B-Tree는 데이터를 최상단의 루트(Root) 노드, 중간 단계인 브랜치(Branch) 노드, 그리고 실제 데이터가 저장된 디스크의 물리적 주소(ROWID)를 품고 있는 최하단의 리프(Leaf) 노드로 계층화하여 정렬된 상태로 저장합니다. 사용자가 데이터를 검색하면 루트에서부터 값의 크기를 비교하며 리프 노드까지 단숨에 찾아 내려갑니다. B-Tree의 가장 위대한 점은 데이터가 아무리 기하급수적으로 늘어나도 트리의 깊이(Depth)가 항상 균형(Balanced) 있게 일정 수준으로 유지된다는 것입니다. 덕분에 수억 건의 데이터 속에서도 단 몇 번의 디스크 I/O만으로 원하는 데이터를 찾아내는 O(log N)의 압도적이고 일관된 탐색 속도를 보장합니다.

요약: 데이터베이스 인덱스는 대부분 B-Tree 구조로 이루어져 있으며, Root-Branch-Leaf 노드의 계층적 탐색을 통해 수억 건의 데이터도 O(log N)의 일관되고 빠른 속도로 스캔할 수 있게 해주는 핵심 알고리즘입니다.

2. 클러스터형 인덱스 vs 비클러스터형 인덱스 완벽 비교

인덱스는 데이터의 물리적 정렬 여부에 따라 크게 두 가지로 나뉩니다. 첫째, '클러스터형 인덱스(Clustered Index)'는 국어사전과 같습니다. 인덱스로 지정된 키 값의 순서에 따라 테이블의 실제 데이터(행) 자체가 물리적으로 완벽하게 정렬되어 디스크에 저장됩니다. 물리적 정렬은 하나만 가능하므로 테이블당 단 1개만 생성할 수 있으며(주로 Primary Key), 범위 검색(BETWEEN, <, >)에 압도적인 성능을 보입니다. 둘째, '비클러스터형 인덱스(Non-Clustered Index)'는 일반 전공서적 맨 뒤의 찾아보기(색인)와 같습니다. 데이터의 물리적 위치는 그대로 둔 채 별도의 인덱스 페이지를 만들어 데이터의 포인터 주소만 연결해 두는 방식입니다. 한 테이블에 여러 개 생성할 수 있지만, 인덱스를 찾은 후 다시 실제 데이터 페이지로 이동하는 과정(랜덤 I/O)이 추가되어 클러스터형보다는 다소 느립니다.

요약: 클러스터형 인덱스는 데이터 자체를 인덱스 순서대로 물리적으로 정렬하여 테이블당 1개만 생성할 수 있으며, 비클러스터형 인덱스는 데이터 위치는 둔 채 별도의 포인터 색인을 만들어 여러 개 생성할 수 있습니다.

3. 인덱스의 양면성: DML 오버헤드와 페이지 분할(Page Split)

인덱스는 SELECT 조회 속도를 날개 돋친 듯 높여주지만, 공짜로 얻어지는 마법은 아닙니다. 데이터가 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)되는 이른바 DML 작업이 발생할 때마다, 원본 테이블뿐만 아니라 연결된 모든 인덱스 트리도 함께 업데이트되고 재정렬되어야 합니다. 특히 인덱스 데이터가 꽉 찬 블록(페이지)에 중간 순서의 새로운 값이 강제로 끼어들어 오면, 공간을 확보하기 위해 블록을 두 개로 쪼개는 '페이지 분할(Page Split)' 연산이 발생합니다. 이는 디스크에 엄청난 I/O 부하를 일으키고 시스템 전반의 성능을 크게 갉아먹는 치명적인 병목 현상입니다. 따라서 조회가 잦고 데이터 변경이 적은 핵심 컬럼에만 선별적으로 인덱스를 생성하는 튜닝의 지혜가 필요합니다.

요약: 인덱스가 많을수록 INSERT, UPDATE, DELETE 작업 시 인덱스를 재정렬하고 쪼개는 '페이지 분할' 부하가 가중되어 입력 성능이 심각하게 저하되므로, 무분별한 인덱스 생성은 피해야 합니다.

4. 튜닝의 꽃: 결합 인덱스(Composite Index)의 선행 컬럼 배치 순서

실무에서는 단일 컬럼보다 두 개 이상의 컬럼을 묶어 하나의 인덱스로 만드는 '결합 인덱스(Composite Index)'를 훨씬 더 자주 사용합니다. 이때 가장 중요한 튜닝 포인트는 바로 괄호 안에 들어가는 컬럼의 '배치 순서'입니다. 결합 인덱스는 명시된 선행 컬럼 순서대로 먼저 정렬된 후, 그다음 컬럼이 정렬되는 구조를 가집니다. 따라서 WHERE 절에서 항상 고정적으로 조건에 사용되면서, 데이터 중복도가 낮아 변별력(Selectivity)이 높은 컬럼을 무조건 1순위(가장 앞쪽)에 배치해야 합니다. 만약 인덱스의 첫 번째로 지정된 선행 컬럼이 쿼리의 WHERE 조건식에서 누락되어 버리면, 데이터베이스는 인덱스 트리를 타지 못하고 테이블 전체를 스캔하는 불상사를 겪게 됩니다.

요약: 여러 컬럼을 묶는 결합 인덱스 생성 시, 조건절에 자주 쓰이면서 변별력(선택도)이 높은 컬럼을 맨 앞쪽에 배치해야 인덱스 스캔 효율이 극대화되며, 선행 컬럼 누락 시 인덱스를 타지 못합니다.

5. 옵티마이저의 배신: 인덱스 스캔 vs 풀 테이블 스캔

인덱스를 만들어두었다고 해서 데이터베이스가 무조건 인덱스를 사용하는 것은 아닙니다. 쿼리를 실행할 때 최적의 경로를 계산하는 두뇌인 '옵티마이저(CBO)'는 추출해야 할 데이터의 양을 통계 정보로 미리 예측합니다. 만약 쿼리 조건이 전체 테이블 데이터의 약 15~20% 이상을 읽어와야 한다고 판단되면, 옵티마이저는 한 건씩 인덱스를 들렀다 가는 방식(랜덤 I/O)이 오히려 손해라고 판단하여 '테이블 풀 스캔(Full Table Scan)'을 선택합니다. 한 번에 디스크에서 대량의 데이터를 순차적으로 퍼올리는 순차 I/O가 더 빠르기 때문입니다. 따라서 맹목적인 인덱스 맹신을 버리고, 실행 전 반드시 실행 계획(EXPLAIN PLAN)을 띄워 옵티마이저가 올바른 인덱스를 타는지 검증하는 것이 데이터베이스 튜닝의 완벽한 첫걸음입니다.

요약: 추출할 데이터가 전체의 15~20%를 초과할 경우 옵티마이저는 인덱스 스캔보다 풀 테이블 스캔이 효율적이라고 판단합니다. 따라서 쿼리 작성 후에는 반드시 실행 계획(EXPLAIN)을 확인하여 실제 작동 방식을 점검해야 합니다.