[DB] 디스크 I/O를 줄이기 위한 인덱스 구조 (B-Tree, Clustered Index) + 실측 검증 (Oracle DBMS_XPLAN)

[DB] 디스크 I/O를 줄이기 위한 인덱스 구조 (B-Tree, Clustered Index) + 실측 검증

목차

1. 인덱스를 사용하는 이유
2. 이진트리
3. B-Tree
4. B+Tree + 실측 검증
5. Clustered Index

1. 인덱스를 사용하는 이유

데이터베이스는 특정 데이터를 조회할 때, 별도의 인덱스가 존재하지 않는 경우 테이블 전체를 순차적으로 탐색하는 Full Scan 방식 수행함. 이 방식은 데이터 건수가 증가할수록 선형적으로 비용이 증가하며, 특히 RDBMS와 같은 디스크 기반 저장 구조에서는 성능 저하가 두드러짐. (데이터베이스의 병목 흐름 : 디스크 I/O 과다 발생 -> Buffer Cache 효율 저하 ->디스크 대기 시간 증가(I/O Queue 쌓임) -> 전체 쿼리 응답 시간 증가 (Latency ↑))

 

※ 디스크 I/O(Input / Output) 는 디스크(SSD/HDD)에서 데이터를 읽거나(wirte / read) 저장하는 모든 작업을 의미함. 

 

데이터베이스는 데이터를 디스크에 페이지(Page) 단위로 저장함(보통 4KB ~ 16KB). 예를 들면, 사용자가 SELECT 요청 -> DB는 Buffer Cache(메모리) 확인 -> 데이터 없으면 디스크에서 페이지 읽어옴(Disk Read I/O 발생). 디스크 I/O의 종류로 1.Read I/O (SELECT 쿼리에서 주로 발생함) 2.Wirte I/O (INSERT, UPDATE, DELETE시 발생함)가 있음.

 

요청된 데이터가 메모리에 존재하지 않아, 디스크 접근이 빈번하게 발생될 시(Cache Hit Ratio가 낮을 시) Cache Miss가 많아지면서(불필요한 디스크 접근 횟수가 증가) 시스템 자원을 비효율적으로 사용하게 됨. 

 

따라서, 디스크 I/O 최소화를 위해 인덱스 구조가 사용됨.

---

2. 이진트리

데이터를 빠르게 찾기 위해 단순 선형 탐색이 아닌 정렬된 트리 구조를 사용할 수 있음. 대표적인 구조가 이진 탐색 트리(Binary Search Tree, BST)임. 이진트리는 자식 노드를 최대 2개 가질 수 있으며, 각 노드는 Key(값), Pointer(주소값)을 가짐. Key를 통해 위치를 찾고, Pointer를 통해 실제 데이터를 조회하는 구조임. 상세 구조는 하기와 같음.

[ Key | Left Pointer | Right Pointer ]

 

다만, 이진트리의 경우 트리가 한쪽으로 치우치거나, 최악의 경우 O(N)으로 탐색 시간이 걸릴 수 있음.(비교적 빠르지만 균형이 깨질 수 있고, 최악의 경우 디스크 I/O 최소화를 하지 못하는 경우 발생함.)

출처: 클로드코드

---

3. B-Tree

이진트리의 한계 해결을 위해, 데이터베이스는 B-Tree 구조의 인덱스를 사용함. B-Tree의 경우 하나의 노드에 여러개의 Key를 저장할 수 있음. 상세 구조는 하기와 같음.(이진트리의 경우 자식 노드가 최대 2개, B-Tree는 수백개 까지 가능함.)

 

내부노드 : Key + Data 저장 가능함. 데이터 위치가 전체 노드에 퍼져있음.

[ Key1 | Key2 | Key3 | ... ]

 

결과적으로 모든 Leaf 노드는 같은 깊이를 가지며, 트리 높이가 일정하게 유지됨. (한 노드에 많은 Key를 저장하여 탐색 시 접근단계를 최소화 시킬 수 있음.) 다만 B-Tree의 경우 데이터가 중간노드, 리프노드에 퍼져있기 때문에 특정 값을 찾으려면 위 아래로 탐색하는 경우가 생겨 범위 검색의 경우 비효율적임.

 

※ Leaf 노드 : 트리의 맨 아래에 있어서, 자식이 없는 노드 / 중간 노드 : Root 와 Leaf를 제외한 것. 위 아래 모두 연결됨.

※ 범위 검색 : 예) SELECT * FROM Table WHERE price BETWEEN 100 AND 200;

 

결과적으로 디스크 B-Tree 또한 디스크 I/O 발생 가능성이 있음.(범위 검색의 경우)

출처: 클로드코드

---

4. B+Tree

B+Tree의 경우 중간 노드에는 Key만 저장하고, 실제 데이터는 리프 노드에만 저장하는 구조임. 리프 노드 간 연결 구조를 통해 범위 검색 시 순차 접근이 가능함.(위아래가 아니라 옆으로 읽게 만든 구조. 같은 높이의 노드끼리 연결되어 있기 때문에 Root노드 까지 갈 필요 없이 바로 옆으로 탐색이 가능함.) 결과적으로 디스크 I/O를 최소화하고 조회 성능을 향상시킬 수 있음. 

 

내부 노드 : Key 값만 저장함.

[ Key1 | Key2 | Key3 | ... ]

 

리프 노드 : 데이터는 오직 리프 노드에만 존재함.

[ Key1 | ROWID ] [ Key2 | ROWID ]

 

B+Tree는 데이터를 리프 노드에 삽입하며, 노드가 가득 찰 경우 분할(Split)이 발생함. 이 때 일부 키는 상위 노드로 전달되어 트리 구조를 유지하게 되며, 이러한 과정을 통해 항상 균형 잡힌 상태를 유지함. 

MySQL이나 Oracle에서 인덱스의 경우 B+Tree의 구조를 가짐.

출처 : 클로드코드

 

B+Tree의 실 예시를 파이널 프로젝트에서 들어볼 수 있음.

호텔 예약 시 기간 조회를 하는 경우 (재고 테이블에서 범위 검색을 함.(BETWEEN)) --> DB 조회시 왼쪽 컬럼부터 조건을 타는 경우.

SELECT *
FROM room_stock
WHERE room_type_id = 101
AND stay_date BETWEEN '2026-04-01' AND '2026-04-05';

 

Full Scan의 경우 : 4/1 -> 4/2 -> ... -> 전체 테이블(재고) 처음부터 끝까지 트리를 타면서 다 탐색해야 함. (시간 복잡도 : O(N))

B+Tree의 경우 : 인덱스 구조가 room_type_id, stay_date(복합인덱스)로 묶여 있어 [101, 2026-04-01] , [101, 2026-04-02] 리프 노드가 이런식으로 구성됨. 맨 처음 시작점을 찾고(2026-04-01) 옆으로 쭉 읽으면 끝. (시간 복잡도 : (log N + K) -> 시작점 찾고 + 결과 개수만큼 읽음)

 

※ 복합 인덱스 : WHERE 절에 있는 컬럼 기준으로 묶임. 복합 인덱스는 선두 컬럼부터 조건이 사용될 때 가장 효율적으로 동작함. 두 번째 컬럼만 사용되거나, 컬럼에 함수를 적용할 경우 인덱스 효율이 떨어질 수 있음.

예) WHERE room_type_id = 101 AND DATE(stay_date) BETWEEN '2026-04-01' AND '2026-04-05';

 

즉, B+Tree 는 연속된 데이터를 복합 인덱스를 통해 조회하고, 리프 노드끼리 연결되어 있으므로 한 번에 읽을 수 있다고 정리하면 됨. (시작점 찾고, 옆으로 쭉).

---

+ 실측 검증

이론으로 정리한 B+Tree 인덱스 효과를 CIEL 프로젝트 테이블에서 Oracle DBMS_XPLAN과 SET TIMING을 통해 직접 검증함.

 

4-1. RESERVATION 테이블 - EXPLAIN PLAN 비교

날짜 기반 예약 조회 시 CHECKIN_DATE + CHECKOUT_DATE 복합 인덱스 적용 전후 실행계획을 비교함.

 

■ 복합 인덱스 생성

CREATE INDEX IDX_RESERVATION_DATE
ON RESERVATION(CHECKIN_DATE, CHECKOUT_DATE);

 

■ 실행계획 비교 (DBMS_XPLAN.DISPLAY) --> 실측 결과

 

항목	인덱스 적용 전	인덱스 적용 후
Operation	TABLE ACCESS FULL	INDEX RANGE SCAN
Cost	68	2
개선율	-	약 97% 감소

 

※ Cost : Oracle 옵티마이저가 쿼리 실행 전 예측한 I/O + CPU 비용의 상대적 비용임. 실제 ms 단위 실행시간이 아닌 상대값이므로, 절대적 수치보다 전후 비교 맥락으로 해석해야 함.

 

※ 예약 테이블 조회 시, 10,115건 데이트 중 인덱스로 6월 위치 찾고 해당 건만 읽느냐, 전체를 Full Scan 하고 필터를 씌우느 그 차이.

 

이 경우 CHECKIN_DATE, CHECKOUT_DATE 둘 다 BETWEEN 조건이므로, 선두 컬럼도 범위 조건임. 이 경우 복합 인덱스의 두 번째 컬럼(CHECKOUT_DATE)은 인덱스를 타지 못하며, CHECKIN_DATE 단일 인덱스로 동작하는 것과 유사함. 하기의 ROOM_STOCK의 등치 + 범위 패턴과는 다른 케이스로 봐야 함.

---

4-2. ROOM_STOCK 테이블 - EXPLAIN PLAN + SET TIMING 비교

기간 별 재고 조회시 (ROOM_TYPE_ID, STAY_DATE) 복합 Unique 인덱스 (UK_ROOM_DATE) 적용 효과를 실행계획과 실제 실행 시간 두 가지로 검증함.

 

■ 테스트 쿼리

1) Full Scan 강제(힌트 사용)

SELECT /*+ FULL(ROOM_STOCK) */ *
FROM ROOM_STOCK
WHERE ROOM_TYPE_ID = 14
AND STAY_DATE BETWEEN DATE '2026-06-01' AND DATE '2026-12-31';

 

※ 힌트 사용 : 오라클 옵티마이저에게 무조건 full scan 쓰라고 강제 지시하는 것.

 

2) Index Scan (자연 실행)

SELECT *
FROM ROOM_STOCK
WHERE ROOM_TYPE_ID = 14
AND STAY_DATE BETWEEN DATE '2026-06-01' AND DATE '2026-12-31';

 

- EXPLAIN PLAN

 

- SET TIMING

 

※ 실측 결과 (ROOM_STOCK 전체 9,855건 테이블 기준 / 조건 필터링 결과 214건 조회 기준)

항목	Full Scan (강제)	Index Scan
Operation	TABLE ACCESS FULL	INDEX RANGE SCAN
Cost	13	2
실행시간 (SET TIMING)	0.115초	0.042초
개선율	-	Cost 85% / 실행시간 63% 감소

 

- Cost는 옵티마이저의 추정값이므로, SET TIMING을 통한 실제 실행시간을 함께 측정하여 두 지표 모두에서 유의미한 성능 개선을 검증함.

 

※ room_stock 테이블에 9,855건의 데이터가 있고 room_type_id=14 + 날짜 범위에 맞는 건만 조회하느냐, full scan으로 전체를 읽고 필터를 씌우느냐 그 타이임. Full Scan은 9,855건 전부 읽고 필터를 씌우지만 Index Scan은 인덱스로 [14, 2026-06-01] 위치 찾고 옆으로 쭉 읽음. -> 결과 214건 반환.

 

■ 결론

예약 테이블, 객실 재고 테이블 조회의 경우 복합 인덱스 적용 시 TABLE ACCESS FULL -> INDEX RANGE SCAN 전환이 실측으로 확인됨. B+Tree 인덱스는 이론상 선두 컬럼부터 조건을 타고, 리프 노드끼리 연결되어 범위 검색 시 순차 접근이 가능한 구조임. 이를 CIEL 프로젝트 실제 데이터(RESERVATION 10,115건 / STOCK 9,855건)에서 Oracle DBMS_XPLAN으로 직접 검증함.

 

---

5. Clustered Index

클러스터링 인덱스는 B+Tree를 사용하는 인덱스 방식임. (B+Tree = 자료구조 / 클러스터링 인덱스 = 사용방식) 리프 노드에 실제 데이터(Row)를 저장하는 방식. 클러스터링 인덱스는 데이터 자체를 정렬하는 방식이라서 한 테이블에 1번만 정렬이 가능함. (ex: PK 기준 정렬)

 

일반 인덱스의 Leaf 노드 : 인덱스(Key) -> 주소(RowID) -> 실제 데이터 ==> 디스크 I/O 2번 

[Key | RowID] 

 

클러스터링 인덱스의 Leaf 노드 : RowID가 없거나, 있더라도 의미 없음. 실제 데이터 자체가 들어감. ==> 디스크 I/O 1번

[Key | 실제 데이터(Row)]

 

Clustered Index 또한 실 예시를 파이널 프로젝트에서 들어볼 수 있음.

 

1. 예약 테이블 생성시 Primary KEY 선언 => 클러스터링 인덱스 생성됨.

CREATE TABLE reservation (
  reservation_id INT PRIMARY KEY, #PK
  checkin_date DATE,
  checkout_date DATE,
  member_no INT,
  ...
);

 

1-2. SELECT 시 내부 동작 흐름 : PK 기반 조회 이므로 클러스터링 인덱스를 통해 바로 데이터 접근이 가능함. (<-> 일반 인덱스의 경우 1) 인덱스에서 PK 찾고 -> 2) PK로 다시 테이블 검색하므로 2번 접근함.)  

SELECT *
FROM reservation
WHERE reservation_id = 1002;

 

2. 복합PK를 통해 빠른 조회 가능함. 상기 B+Tree의 예시로 내부동작은 room_type_id = 101 로 위치를 읽고, 그 안에 쭉 날짜를 읽는 식으로 처리됨. 

CREATE TABLE room_stock (
  room_type_id INT,
  stay_date DATE,
  available_count INT,
  PRIMARY KEY (room_type_id, stay_date) #PK
);

 

room_stock 테이블에서는 (room_type_id, stay_date)를 복합 Primary Key로 설정하여, 객실 타입별 날짜 데이터를 유일하게 식별함과 동시에 클러스터링 인덱스를 구성함. 이를 통해 특정 객실에 대한 기간 조회 시 정렬된 데이터 접근이 가능해 조회 성능을 높일 수 있었음.

 

+ 정리 : B+Tree는 자료구조이고, Clustered Index는 방식임. 차이는 리프노드에 무얼 저장하느냐. 일반 인덱스는 RowID를 저장하고 Clustered Index는 실제 row 데이터를 저장함. 

 

 

+ PK로 조회할 때 (where reservation_id = 1002) Clustered 인덱스가 유리함. 리프노드에 바로 데이터가 있기 때문에 1번 접근함. PK가 아닌 컬럼으로 조회할 때 (where member_no = 5)는 PK를 찾고 -> PK로 Clustered Index 조회하기 때문에 2번 접근. 

 

즉, PK 기반으로 조회할 때(단건 조회 + PK기준 범위 조건 조회 where id BETWEEN 100 AND 200 같은..) Clustered Index가 유리하다. 매번 유리한 것은 아님.

 

 참고 : https://www.youtube.com/watch?v=MuZ-Mx0N-dA&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=61