DB 인덱스 개념 정리: 저장 구조, 생성 과정, 동작 원리

0. 배경

DB를 다룰 때 인덱스의 중요성을 모르는 사람은 거의 없을 텐데요.

이상적으로는 테이블 설계 초기 단계에서 인덱스를 설정하는 것이 가장 좋습니다.

하지만, 현실에서는 코드가 배포된 후에야

"DB 부하가 너무 커졌네 ? "

"조회 속도가 왜 이렇게 느리지"

와 같은 문제가 발생한 후 뒤늦게 인덱스를 추가하는 경우가 많았습니다😅 

 

특히, 인덱스를 추가해야 하는 이유와 내부 동작 원리를 정확히 이해하지 못하면

성능 개선은 커녕 오히려 속도가 더 느려지는 경우도 발생합니다. 

 

인덱스는 데이터베이스 성능을 좌우하는 핵심 요소입니다.

하지만, 잘못 사용하면 오히려 성능을 악화시키는 양날의 검이 될 수도 있습니다.

 

오늘은 인덱스가 무엇인지, 어떻게 동작하는지에 대해서 알아보도록 하겠습니다 !

---

1. 인덱스(INDEX) 란 ?

네이버 영어 사전에서 index 란 무슨 의미인지 찾아보도록 하겠습니다.

 

색인, 색인을 만들다 라고 정의하고 있는데요.

DB 에 적용해보자면 ?

인덱스(index) 란 DB 에서 데이터를 빠르게 찾기 위해 사용하는 색인입니다 !

 

예를 들어 만약 1000페이지가 넘는 책에서 특정 단어를 찾으려면 ?

• 인덱스가 없는 경우 -> 처음부터 끝까지 페이지를 한 장씩 모두 뒤져야 합니다. (full scan)
• 인덱스가 있는 경우 -> 목차에서 해당 키워드를 찾아 바로 해당 페이지로 이동할 수 있습니다.

데이터베이스도 마찬가지입니다 !

• 인덱스가 없는 경우 → 테이블의 모든 데이터를 처음부터 끝까지 검색해야 합니다 (full scan)
• 인덱스가 있는 경우 → 정렬된 인덱스를 탐색하여 빠르게 원하는 데이터를 찾을 수 있습니다.

---

2. 인덱스의 저장 구조

책의 목차를 생각해보면 목차는 본문의 내용과 분리되어 있지만, 본문을 빠르게 찾을 수 있도록 돕습니다.

이처럼 인덱스도 논리적, 물리적으로 테이블과 독립적으로 저장되며, 데이터 검색을 빠르게 하기 위해서 사용됩니다.

 

인덱스는 기본적으로 두 개의 컬럼 (검색값, 위치정보) 로 구성됩니다.

	검색값 (KEY)	위치정보 (ROWID or Primary Key)
역할	검색 기준이 되는 값을 저장합니다	해당 데이터의 위치를 저장합니다.
내용	테이블의 특정 열의 값이 저장됩니다. 예를 들어서 name 열에 인덱스를 생성하면 name 의 값들이 KEY 컬럼에 저장됩니다.	KEY 값에 해당하는 실제 데이터가 저장된 위치를 나타냅니다. Oracle 은 ROWID(물리적 주소), MySQL(InnoDB) 는 Primary Key 를 저장합니다.
정렬	인덱스는 일반적으로 B-Tree 구조를 사용하기 때문에 KEY 값도 정렬된 상태로 저장됩니다.	위치정보 로 정렬되진 않고, KEY 값의 정렬 순서에 따라 정렬됩니다.

 

각각은 위와 같은 기능을 가지고 있는데요

검색값 = 실제 값, 위치정보 = 실제 데이터의 위치

라고 생각하시면 간단합니다 !

---

3. 인덱스의 생성 과정

인덱스가 저장되는 구조를 파악했다면 이제는 인덱스의 생성 과정에 대해서 알아봅시다 !

인덱스는 총 4단계를 거쳐서 생성되게 됩니다. 

 

1. 전체 테이블 스캔
   • 인덱스를 생성하려는 컬럼의 데이터를 가져오기 위해 전체 테이블을 스캔합니다.
   • 특정 컬럼에만 인덱스를 생성하는 경우, 해당 컬럼과 위치정보(ROWID, PK) 만 읽습니다.
2. 정렬
   • PGA(Process Global Area, 서버 프로세스가 사용하는 메모리 공간) 내의 Sort Area에서 데이터를 정렬합니다
   • 정렬된 데이터는 저장될 구조(B-tree, Hash 등) 에 맞게 정리됩니다
   • 만약 PGA의 Sort Area가 부족하면, Temporary Tablespace(임시 테이블스페이스)에 저장 후 디스크에서 정렬을 수행하는데 이는 성능 저하를 유발할 수 있습니다
3. Block에 기록 
   • 정렬이 끝난 데이터는 데이터 블록에 저장되며, 이후 B-Tree 또는 Bitmap 인덱스가 생성됩니다
   • B-Tree 인덱스의 경우 루트 노드 - 브랜치 노드 - 리프 노드 구조로 저장됩니다.
   • 리프 노드는 정렬된 ROWID 포인터를 가지고 있습니다.
4. 데이터 변경 제한 
   • 인덱스 생성 중 DML(INSERT, UPDATE, DELETE)이 발생하면 일관성이 깨질 위험이 있으므로, 데이터 변경을 제한하는 조치가 필요합니다.
   • 이를 방지하기 위해 일반적인 인덱스 생성 방식에서는 테이블을 Lock 하여 데이터 변경을 제한합니다.
   • Online Indexing 이 지원되는 경우 테이블을 읽으면서 인덱스를 생성하되, 데이터 변경을 허용하면서도 인덱스를 생성할 수 있습니다 (새롭게 발생하는 DML 작업을 추적하여 변경 내용을 반영)

 

---

4. 인덱스가 동작하는 예시

name 을 기준으로 인덱스를 만들었고, name 에 PARK, KIM, LEE 가 있다고 합시다.

B-Tree 인덱스 구조를 사용하는 Oracle 기준 으로 인덱스에는 아래와 같이 저장되게 됩니다.

검색값	위치정보 (ROWID)
PARK	1001 (실제 PARK 의 데이터 위치)
KIM	1002 (실제 KIM 의 데이터 위치)
LEE	1003 (실제 LEE 의 데이터 위치)

 

이렇게 저장되면 인덱스는 

인덱스 탐색 -> 데이터 위치 확인 -> 실제 데이터 조회 의 과정으로 데이터를 찾습니다.

 

사용자가 'PARK' 이라는 데이터를 검색할 때

 

📌 1️⃣ B-Tree 인덱스를 탐색

• name='PARK'을 검색하면, B-Tree 인덱스에서 정렬된 순서로 탐색이 시작됩니다.
• 이진 탐색(Binary Search) 을 수행하여 빠르게 PARK를 찾습니다.

📌 2️⃣ 인덱스에서 데이터 위치 확인

• Oracle: PARK에 해당하는 ROWID(1001) 를 찾습니다
• MySQL (InnoDB): PARK에 해당하는 프라이머리 키 를 찾습니다

📌 3️⃣ 테이블에서 실제 데이터 조회

• Oracle: ROWID를 사용하여 테이블에서 해당 데이터를 직접 조회합니다.
• MySQL (InnoDB): 프라이머리 키를 사용하여 클러스터드 인덱스를 다시 검색한 후 데이터를 조회합니다.

의 프로세스로 동작합니다. 

이런 구조를 가지고 있기에 데이터를 훨씬 빠르게 찾을 수 있습니다 🙌

---

5. 인덱스를 남발하면 안되는 이유

💬 : "인덱스를 추가하면 조회 속도가 빨라지니깐 모든 테이블의 모든 컬럼에 인덱스를 걸어야겠군 ?! "

🙅‍♀️ 땡 그렇지 않습니다 !

 

인덱스는 데이터를 빠르게 조회할 수 있도록 도와주지만, 마구잡이로 생성하면 오히려 전체적인 성능이 나빠질 수 있습니다.

인덱스를 남발하면 성능이 저하되는 이유는 다음과 같습니다. 

 

1. INSERT, DELETE, UPDATE 속도 저하 
   • 새로운 데이터를 추가하거나 수정할 때마다, 모든 인덱스도 함께 갱신해야 합니다.
   • 즉, 인덱스가 많아질수록 변경 작업(쓰기 작업, Write Operation)이 느려집니다.
2. 인덱스의 저장 공간 증가
   • 인덱스도 결국 디스크(스토리지)에 저장되는 데이터 구조
   • 인덱스가 많아지면 디스크 사용량이 증가하고, 캐싱 효율이 떨어집니다.
3. 인덱스 선택이 비효율적으로 이루어질 가능성
   • 인덱스가 많아지면, DB 옵티마이저 가 어떤 인덱스를 사용할지 판단하는 데 더 많은 시간이 필요합니다.
   • 경우에 따라 잘못된 인덱스를 선택해서 성능이 더 떨어질 수도 있습니다.

그렇기 때문에 인덱스를 추가하기 전에 SQL 을 먼저 최적화 해야 합니다. 

비효율적인 SQL을 최적화하면 인덱스 없이도 성능을 개선할 수 있습니다 !!

---

6. 결론

인덱스는 DB 성능을 최적화하는 강력한 도구입니다.

하지만 무조건 많이 추가한다고 좋은 것은 아닙니다.

 

🅾️ 적절한 인덱스를 사용하면 빠른 데이터 검색이 가능하지만

🙅‍♀️ 불필요한 인덱스는 오히려 성능을 저하시킬 수 있습니니다.

그렇기 때문에 인덱스를 추가하기 전에 반드시 SQL 을 먼저 최적화하고, 필요한 곳에만 신중하게 적용해야 한다는 사실을 잊지 마세요 !!

 

다음 글에서는 인덱스의 종류에 대해서 알아보도록 하겠습니다. 🚀

---

레퍼런스

[Database] 인덱스(index)란?