1. Index, 인덱스란?
인덱스(Index)란 데이터베이스 테이블에서 데이터를 빠르게 검색할 수 있도록 도와주는 자료구조이다.
인덱스는 테이블의 특정 컬럼에 대해 별도의 데이터 구조(주로 B-Tree, Hash 등)를 만들어, 전체 테이블을 일일이 탐색하지 않고도 원하는 데이터를 빠르게 찾을 수 있게 해준다.
SQL에서 인덱스는 다음처럼 생성, 삭제, 재구성이 가능하다.
sql
CREATE INDEX 인덱스명 ON 테이블명(컬럼명); -- 인덱스 생성
DROP INDEX 인덱스명; -- 인덱스 삭제
ALTER INDEX 인덱스명 REBUILD; -- 인덱스 재구성데이터베이스는 기본적으로 전체 테이블을 순차 탐색(Full Table Scan) 한다.
인덱스를 사용하면 특정 조건(Where)에 맞는 데이터를 테이블을 풀 스캔하지 않고 INDEX → ROWID → TABLE 순으로 빠르게 접근하기 때문에 데이터들을 빠르게 찾을 수 있다.
(여기서 ROWID란 테이블에서 하나의 Row(행)가 물리적으로 저장된 주소 값이다.)
2. Index와 Table의 구조적 차이
| 구분 | Table | Index |
| 목적 | 데이터를 실제로 저장 | 데이터를 빠르게 찾기 위한 포인터 목록 |
| 저장형태 | 테이블스페이스 내 데이터블록에 로우(행) 단위 저장 | 테이블과 별도 공간에 Key + RowID 쌍 저장 |
| 구조 | Heap Table, Cluster Table 등 다양한 구조 | 주로 B-Tree, Bitmap, Hash 구조 |
| 읽는 방식 | 전체 스캔 또는 인덱스를 통해 접근 | 인덱스 트리 탐색 → RowID로 테이블 접근 |
Table
이름, 컬럼, 제약조건 같은 구조를 설명하는 틀로, 데이터베이스 내부적으로 스키마 객체(USER_TABLES, DBA_TABLES) 같은 메타 정보로 관리된다. 실제로 디스크에 저장되는 테이블 데이터로 테이블 스페이스 -> 데이터파일 -> 데이터 블록으로 실제 값이 저장된다.
Index
별도의 저장 공간에 생성되며(B-Tree / Bitmap, Hash) 같은 구조를 사용하여 Key + RowID를 쌍으로 하는 포인터 형태로 테이블과 분리된 파일 안에 저장된다. DB가 데이터를 검색할 때 인덱스를 먼저 탐색하고 RowID를 통해 테이블에서 실제 데이터를 읽는다.
따라서 비유로 쉽게 Table이 책이라면, Index는 책의 목차라고 표현하는 것이다.
책은 실제 내용을 담고 있고, 목차는 찾기 위한 주소 목록이다.
인덱스가 없으면 전체 책을 처음부터 끝까지 읽어야 하지만, 인덱스가 있으면 바로 찾아갈 수 있다.
3. 인덱스의 종류와 동작 원리
🔹인덱스 종류
| 종류 | 설명 | 특징 |
| B-Tree Index | 가장 일반적인 인덱스. 범위 검색 가능 | WHERE 조건에 적합 |
| Unique Index | 중복을 허용하지 않음 | PK, Unique 제약조건에 사용 |
| Composite Index | 여러 컬럼을 묶어서 만든 인덱스 | 다중 조건 WHERE 최적화 |
| Hash Index | 해시값 기반. 정확한 값 검색에 적합 | NoSQL에서 자주 사용 |
| Bitmap Index | 값 종류가 적고 중복 많은 컬럼용 | DW, OLAP 환경에서 유용 |
| Full-Text Index |
텍스트 검색 특화 | 검색엔진, 게시판 검색 등 |
🔹인덱스 스캔
| 방식 | 설명 |
| Full Scan | 인덱스 전체 탐색 |
| Range Scan | 범위 조건에 맞는 값만 탐색 |
| Unique Scan | 고유 인덱스를 이용해 단일 Row 조회 |
| Skip Scan | 선두 컬럼 없이도 인덱스 탐색 가능 |
🔹B-Tree 동작 원리
인덱스 유형 중에서 가장 많이 사용하는 인덱스의 구조는 B-Tree(균형 트리, Balanced Tree) 구조이다.
B-Tree 기반으로 B+Tree 또는 B*Tree 형태로 변형하여 사용하기도 하며, B*Tree 인덱스는 오라클을 비롯한 대부분의 DBMS에서 사용하고 있는 보편적인 인덱스이다.
B-Tree는 루트 노드 → 브랜치 노드 → 리프 노드로 구성되어 있으며, 각 노드에는 데이터값과 해당 데이터가 실제 저장된 위치를 가리키는 포인터가 함께 저장된다.
검색 시, 루트 노드에서 시작해 조건에 맞는 방향으로 브랜치를 따라 내려가고, 최종적으로 리프 노드에서 포인터를 통해 실제 데이터를 찾아내는 방식이다.
B-Tree는 BST(Binary Search Tree, 이진 탐색 트리) 와 비슷해 보이지만, BST는 노드당 자식이 최대 2개인 반면, B-Tree는 노드 안에 데이터가 n개 존재하면 자식 노드의 수는 항상 n+1개가 되는 구조이다. (초기 root의 경우나 leaf노드 제외)
따라서 B-Tree 자료구조가 균형 트리라고 불리는 이유는 값이 추가될 때마다 트리는 스스로 노드 분할(페이지 분할)을 통해 균형을 맞추고, 모든 리프 노드는 항상 동일한 깊이를 가지게 되기 때문이다.
덕분에 데이터가 아무리 많아도 탐색 시 전체 테이블 스캔을 피하고 log₂N 시간 복잡도로 일정한 검색 성능을 보장한다.
하지만 인덱스 파일은 생성 후 삽입, 수정, 삭제 등이 오랫동안 일어나다 보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어지게 되고 검색 속도가 떨어지므로 주기적으로 리빌딩 작업을 거치는 것이 좋다.
B+Tree는 B-Tree의 확장 개념으로, 특정 키값이 한 노드에만 존재하는 것이 아닌 leaf노드와 부모노드에 공존하게 되어있는 형태이다.
B*Tree또한 B-Tree의 문제점인 '구조를 유지하기 위해 추가적으로 연산을 해야한다는 것' 을 보안하기 위해 모든 리프는 같은 레벨에 있으며 노드 분할 시 인접 노드와 공간을 먼저 공유한 뒤 분할하여 노드 분할을 최소화하는 방식의 형태이다.
그 외에 B+-Tree도 존재한다.
B-Tree Visualization 과 B+Tree Visualization 에서 생성과정을 눈으로 확인해볼 수 있다.
4. 인덱스의 타입
인덱스는 크게 두 가지로 나뉘며, 대부분 B-Tree 저장 구조로 이루어져 있다.
- 클러스터형 인덱스(Clustered Index) = 오라클에서는 Index Organized Table(IOT)
- 보조 인덱스(Non-Clustered Index)
🔹 클러스터형 인덱스 (Clustered Index, IOT)
일반적으로 Index와 Table은 분리형이다. 인덱스에 먼저 접근해서 rowid를 찾고 rowid를 이용해 테이블에서 데이터를 다시 조회한다.
반면 Clustered Index(IOT)는 Table과 Index가 결합된 형태로, 인덱스에 따로 접근할 필요 없이 테이블이 인덱스 순서대로 물리적으로 정렬된다.
테이블당 1개만 존재할 수 있으며 PK를 기준으로 자동 생성된다.
테이블 자체가 인덱스 구조로 만들어지기 때문에 검색 속도가 빠르지만 데이터를 삽입/수정/삭제할 때, 기존 데이터를 밀어내며 위치를 유지해야 하므로 성능에 영향을 준다.
예를 들어 ID 컬럼을 PK로 사용해 클러스터형 인덱스를 만들었다고 가정해보자, 이미 ID = 1,3,4 가 존재하는 테이블에 ID = 2를 삽입하면, 2보다 큰 모든 데이터는 한 칸씩 밀리고 ID = 2가 그 자리를 차지해야 한다. 데이터가 수십, 수백만 건이라면 이 과정은 엄청난 비용을 발생시킨다.
따라서 보통 Primary Key로는 순차적으로 증가하는 정수형 ID + Auto Increment 를 사용하는 이유이며, 새로운 데이터가 항상 맨 뒤에 추가되어 정렬 변경을 피할 수 있어 성능 저하를 방지할 수 있다.
⚠️ 오라클에서 사용하는 Cluster Index (Table)와는 다른 개념이다.
Cluster Index는 인덱스와 테이블이 느슨하게 연결된 구조로 인덱스와 데이터가 1:1이 아닌 1:N의 관계이다.
기존의 분리형 Index ↔ Table관계와, 일체형 Clurstered Index의 중간 역할을 하는 구조이다.
🔹 보조 인덱스 (Non-Clustered Index)
Non-Clustered Index는 이름 그대로 클러스터형 인덱스가 아닌 일반 인덱스를 의미한다.
테이블 데이터와 인덱스가 분리된 형태로 저장되고, 인덱스의 리프 노드에는 실제 데이터를 찾기 위한 포인터(rowid) 가 저장된다.
보조 인덱스의 특징은 다음과 같다.
- 테이블 당 여러 개 생성 가능하다
- 데이터는 테이블에 저장되고, 인덱스는 데이터 위치(RowID)를 참조한다
- 조회 시 인덱스 + 테이블을 두 번 접근하므로 I/O가 발생한다
- 데이터가 삽입/수정/삭제될 때마다 인덱스도 별도로 갱신해야 한다
- 데이터 정렬을 보장하지 않는다
- 클러스터형 인덱스보다 삽입/수정/삭제 성능이 좋다
5. 장단점 및 주의 사항
장점
1️⃣ 검색 성능 향상
WHERE, JOIN, ORDER BY, GROUP BY 등에서 빠른 데이터 접근이 가능하다. 인덱스를 사용하면 선형 탐색(O(N)) 대신 로그 탐색(O(log N)) 또는 해시 기반 탐색(O(1))이 가능하다.
2️⃣ 정렬/그룹핑 최적화
인덱스 자체가 정렬된 구조라서 별도의 ORDER BY 없이도 정렬된 결과를 반환 가능하다.
단점
1️⃣ 쓰기 성능 저하
INSERT, UPDATE, DELETE 시 테이블 뿐 아니라 인덱스도 갱신해야 하기 때문에 오버헤드 발생
2️⃣ 저장 공간 증가
인덱스는 테이블과 별개의 물리적 저장 공간을 차지한다. 컬럼 값이 길거나 Row 수가 많을수록 인덱스 크기도 커진다. 인덱스를 관리하기 위해 추가 관리가 필요하며 DB의 약 10%에 해당하는 저장 공간이 필요하다.
⚠️ 주의사항
인덱스는 잘 활용하면 성능을 크게 향상시키지만, 무분별하게 사용하거나 구조를 잘못 잡으면 오히려 성능을 떨어뜨릴 수 있다.
1️⃣ 인덱스를 과도하게 생성하면, DML(INSERT, UPDATE, DELETE) 작업 시 인덱스까지 함께 관리해야 하므로 쓰기 성능이 저하된다.
2️⃣ WHERE 조건으로 대부분의 데이터를 반환하는 경우, 옵티마이저는 인덱스를 사용하지 않고 Full Table Scan을 선택한다. 즉, 인덱스를 타지 않고 테이블 전체를 읽는 것이 더 빠를 수 있다.
3️⃣ WHERE조건에 함수가 포함되면 인덱스를 사용할 수 없다.
sql
WHERE UPPER(NAME) = 'JOHN' // → 인덱스 무시
4️⃣ 인덱스는 내부적으로 정렬된 상태를 유지하기 때문에, 후방 일치 검색(LIKE '%abc')처럼 정렬 정보를 활용할 수 없는 경우 인덱스를 사용할 수 없다.
sql
WHERE JOB LIKE '%ist' -- 후방 검색, 인덱스 사용 불가
5️⃣복합 인덱스(Composite Index) 순서 주의
INDEX (A, B) 는 WHERE A = ? 일 때 효율적이나,WHERE B = ? 만 사용하면 비효율적이다.
INDEX (A, B)가 다음과 같은 형태로 저장될 때
| A | B | ROWID |
| 1 | a | ... |
| 1 | b | ... |
| 1 | c | ... |
| 2 | a | ... |
| 2 | d | ... |
| 3 | b | ... |
sql
SELECT * FROM 테이블명 WHERE B = 'b'B 값은 인덱스 내에서 A에 묶여있기 때문에 인덱스를 처음부터 끝까지 Full Scan해야 한다.
따라서 인덱스를 사용할 땐, 인덱스를 쓰는 목적과 기준을 다시 한번 생각해볼 필요가 있다.
6. 목적과 선택 기준
인덱스 쓰는 목적
- 조회 속도 개선: WHERE, JOIN, ORDER BY 속도 향상
- 정렬 최소화: 이미 인덱스가 정렬되어 있어
ORDER BY시 별도 정렬 불필요 - 중복 체크: Unique Index를 통해 데이터 무결성 유지
인덱스 선택 기준
- 자주
WHERE,JOIN,ORDER BY,GROUP BY에 등장하는 컬럼 - 데이터 분포가 넓은 컬럼 <예: 성별(X), 사용자 ID(O)>
- 데이터가 10~20% 이상 WHERE로 추출될 경우 → Full Scan이 더 빠름
- 자주 검색/조인/정렬되는 컬럼에만 인덱스 생성: 불필요한 인덱스는 피함
- 저카디널리티 컬럼은 인덱스 비추천: 중복값이 많은 컬럼은 인덱스 효과가 적음
- 너무 넓은(large) 컬럼 인덱스는 피함: 인덱스 크기 증가로 오히려 성능 저하
- 적절한 인덱스 개수 유지: 읽기/쓰기 성능 균형 고려
인덱스를 사용할 때는 단순히 생성하는 것보다, 실제 쿼리가 어떻게 동작하는지를 확인하는 과정이 중요하다.
이를 위해 실행계획(Execution Plan) 을 반드시 확인해야 하며, 실행계획을 통해 쿼리가 인덱스를 제대로 사용하는지,
또는 Full Scan을 수행하는지를 사전에 파악하고 튜닝하는 습관이 필요하다.
'DataBase' 카테고리의 다른 글
| [Oracle] ROW_NUMBER() OVER(PARTITION BY) (0) | 2025.04.11 |
|---|---|
| [Database] PL/SQL 이란? Procedure와 Java 프로그래밍의 차이와 생각 (0) | 2025.04.08 |
| [Database] Spring Boot에서 MongoDB Atlas 연결하기 (0) | 2025.02.11 |
| [Database] 채팅 시스템 NoSQL 특성 및 비교 분석(CAP, PACELC) (1) | 2025.02.07 |