SQL_22) 건초더미에서 바늘 찾기 싫다면 인덱스를 쓰세요

인덱스(Index)

인덱스(Index) 는 데이터 검색 속도를 높이기 위해 별도로 관리하는 자료구조입니다. 일반적으로 B-Tree 구조를 사용하며, 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 저장합니다. 인덱스가 있으면 테이블 전체를 읽는 'Full Table Scan' 대신 특정 범위만 탐색하는 'Index Scan'이 가능해져 SELECT 성능이 비약적으로 향상됩니다.

---

Why (왜 사용하는가? 왜 중요한가?)

• 인덱스 없이는 대용량 테이블에서 데이터를 검색할 때 전체 테이블을 스캔해야 하므로 성능이 크게 저하됩니다. 1억 개의 데이터가 섞여 있을 때 특정 값을 찾는 것은 '모래사장에서 바늘 찾기'와 같지만, 인덱스를 통해 미리 정렬해두면 업다운 게임처럼 빠르게 찾을 수 있습니다.
• 인덱스는 항상 정렬된 상태를 유지해야 하므로 INSERT, UPDATE, DELETE 시에는 추가적인 오버헤드가 발생하며, 별도의 저장 공간이 필요하다는 트레이드오프가 있습니다. 따라서 꼭 필요한 컬럼에만 전략적으로 인덱스를 만드는 것이 핵심입니다.
• Full Table Scan과 Index Scan의 차이, B-Tree와 B+Tree의 구조와 동작 원리, 인덱스의 장단점, 그리고 인덱스를 사용해야 하는 경우와 사용하지 말아야 하는 경우를 이해해야 합니다.

---

1. 인덱스(Index)란?

인덱스의 정의

인덱스(Index)는 데이터 검색 속도를 높이기 위해 별도로 관리하는 자료구조입니다. 인덱스는 한 마디로 '검색용 복사본'입니다.

인덱스의 필요성

대용량 테이블에서 특정 데이터를 찾을 때, 인덱스가 없으면 전체 테이블을 스캔해야 합니다.

→ 최대 100까지의 숫자 업다운 게임을 예시로로 인덱스가 없을 때와 있을 때의 경우를 비교를 해봅시다.

순서대로 찾기

너 1이야? 
너 2야?
... 
...
... 
너 97이야? >> 응 >> 97번의 조회를 통해 찾았다.

나누어서 유추

너 50보다 커?
너 75보다 커?
... 
...
...
96보다 크고 98보다 작구나.  97 >> 대략 6~8회의 조회를 통해 찾았다. 

인덱스의 구조

인덱스는 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 저장합니다.

인덱스 구조:
키(컬럼 값) → 값(레코드 주소)

예시:
이름: "홍길동" → 주소: 0x1234
이름: "김철수" → 주소: 0x5678
이름: "이영희" → 주소: 0x9ABC

---

2. Full Table Scan vs Index Scan

Full Table Scan (전체 테이블 스캔)

Full Table Scan은 인덱스를 사용하지 않고 테이블의 모든 행을 순차적으로 읽는 방식입니다.

특징:

• 테이블의 모든 데이터를 읽음
• 인덱스가 없거나 인덱스를 사용할 수 없는 경우 발생
• 대용량 테이블에서는 매우 느림

비유: 처음부터 끝까지 다 뒤지는 '노가다'

예시:

-- 인덱스가 없는 컬럼으로 검색
SELECT * FROM users WHERE email = 'user@example.com';
-- 전체 테이블을 스캔하여 email 컬럼을 하나씩 확인

Index Scan (인덱스 스캔)

Index Scan은 인덱스를 사용하여 필요한 데이터만 빠르게 찾는 방식입니다.

특징:

• 인덱스를 통해 특정 범위만 탐색
• 정렬된 인덱스에서 이진 탐색 가능
• 대용량 테이블에서도 빠른 검색

비유: 미리 정렬된 '지름길'로 가는 방식

예시:

-- 인덱스가 있는 컬럼으로 검색
SELECT * FROM users WHERE id = 12345;
-- 인덱스를 통해 id=12345인 레코드의 주소를 바로 찾음

성능 비교

구분	Full Table Scan	Index Scan
스캔 범위	전체 테이블	인덱스 + 필요한 행만
시간 복잡도	O(n)	O(log n)
대용량 테이블	매우 느림	빠름
비유	노가다	지름길

---

3. B-Tree와 B+Tree

B-Tree 구조

B-Tree는 인덱스에서 가장 많이 사용되는 자료구조입니다. 균형 잡힌 트리 구조로 데이터를 저장합니다.

B-Tree의 특징:

• 각 노드는 여러 개의 키를 가질 수 있음
• 모든 리프 노드가 같은 레벨에 있음 (균형 트리)
• 각 노드의 키는 정렬된 상태

B+Tree 구조

B+Tree는 B-Tree의 개선된 형태로, 대부분의 RDBMS에서 사용합니다.

B+Tree의 특징:

• 내부 노드(Internal Node): 키만 저장 (가이드 역할)
• 리프 노드(Leaf Node): 키와 실제 데이터 주소 저장
• 리프 노드 연결: 리프 노드끼리 선형으로 연결되어 범위 검색에 유리

B+Tree 구조 예시:

왜 B+Tree인가?

1. 범위 검색에 유리

리프 노드끼리 연결되어 있어 "10살부터 20살까지 다 가져와!" 같은 범위 검색에서 압도적인 성능을 냅니다.

범위 검색: WHERE age BETWEEN 10 AND 20
1. 인덱스에서 10을 찾음
2. 리프 노드 연결을 따라 20까지 순차적으로 읽음
3. 필요한 데이터만 빠르게 가져옴

2. 디스크 I/O 최소화

내부 노드는 키만 저장하므로 메모리에 더 많은 노드를 올릴 수 있어 디스크 접근을 줄입니다.

3. 순차 접근 최적화

리프 노드가 연결되어 있어 순차 스캔이 효율적입니다.

---

4. 인덱스의 동작 원리

인덱스 생성

-- 단일 컬럼 인덱스 생성
CREATE INDEX idx_name ON users(name);

-- 복합 인덱스 생성
CREATE INDEX idx_name_age ON users(name, age);

인덱스를 사용한 검색 과정

1. 인덱스에서 검색 키 찾기
   - B+Tree를 따라 내려가며 키를 찾음

2. 리프 노드에서 데이터 주소 확인
   - 찾은 키에 해당하는 레코드 주소 확인

3. 실제 테이블에서 데이터 읽기
   - 주소를 통해 실제 데이터를 가져옴

예시:

SELECT * FROM users WHERE name = '홍길동';

동작 과정:

1. idx_name 인덱스에서 '홍길동' 검색
2. B+Tree를 따라 내려가며 '홍길동' 찾기
3. 리프 노드에서 '홍길동'에 해당하는 레코드 주소 확인 (예: 0x1234)
4. 실제 테이블의 0x1234 주소에서 데이터 읽기

---

5. 인덱스의 장점과 단점

장점

1. 검색 속도 향상
   • Full Table Scan 대신 Index Scan 사용
   • 대용량 테이블에서도 빠른 검색
2. 정렬 작업 최소화
   • 인덱스가 이미 정렬되어 있어 ORDER BY 성능 향상
3. 조인 성능 향상
   • 외래 키에 인덱스가 있으면 조인 성능 향상

단점 (Trade-off)

1. 추가 저장 공간 필요
   • 인덱스는 별도의 저장 공간을 차지함
   • 테이블 크기의 약 10~20% 추가 공간 필요
2. INSERT, UPDATE, DELETE 성능 저하
   • 데이터를 새로 넣거나 바꿀 때마다 인덱스도 매번 다시 정렬해야 함
   • 인덱스를 남발하면 INSERT 할 때마다 DB가 "잠깐만, 인덱스 줄 세우기 다시 해야 해"라며 멈칫거림
3. 인덱스 유지 비용
   • 인덱스는 항상 정렬된 상태를 유지해야 함
   • 데이터 변경 시 인덱스도 함께 업데이트해야 함

세상에 공짜는 없다 (Trade-off)

인덱스는 조회(SELECT)와 수정(DML) 사이의 밀당이 필요합니다.

• SELECT가 많으면: 인덱스가 유리
• INSERT/UPDATE/DELETE가 많으면: 인덱스가 부담

따라서 꼭 필요한 컬럼에만 전략적으로 인덱스를 만드는 것이 핵심입니다.

---

6. 인덱스를 사용해야 하는 경우

인덱스 생성이 유리한 경우

1. WHERE 절에서 자주 사용되는 컬럼
2. -- name 컬럼으로 자주 검색 SELECT * FROM users WHERE name = '홍길동';
3. 조인에 자주 사용되는 컬럼
4. -- 외래 키에 인덱스 SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
5. ORDER BY에서 자주 사용되는 컬럼
6. -- 정렬 성능 향상 SELECT * FROM users ORDER BY created_at DESC;
7. 고유한 값을 가진 컬럼
8. -- PRIMARY KEY, UNIQUE 제약조건 CREATE UNIQUE INDEX idx_email ON users(email);
9. 카디널리티가 높은 컬럼
   • 카디널리티: 컬럼의 고유한 값의 개수
   • 높은 카디널리티 = 많은 고유 값 = 인덱스 효과 큼

인덱스 생성이 불리한 경우

1. 카디널리티가 낮은 컬럼
   • 예: 성별(남/여), 상태(활성/비활성)
   • 인덱스 효과가 거의 없음
2. 자주 변경되는 컬럼
   • INSERT/UPDATE/DELETE가 빈번한 컬럼
   • 인덱스 유지 비용이 큼
3. 작은 테이블
   • 데이터가 적으면 Full Table Scan이 더 빠를 수 있음
4. NULL 값이 많은 컬럼
   • 인덱스 효과가 제한적

---

7. 복합 인덱스 (Composite Index)

복합 인덱스란?

복합 인덱스(Composite Index)는 여러 컬럼을 조합하여 만든 인덱스입니다.

CREATE INDEX idx_name_age ON users(name, age);

복합 인덱스의 사용 규칙

복합 인덱스는 왼쪽부터 순서대로 사용됩니다.

예시:

-- 인덱스: (name, age)

-- ✅ 인덱스 사용 가능
SELECT * FROM users WHERE name = '홍길동';
SELECT * FROM users WHERE name = '홍길동' AND age = 25;

-- ❌ 인덱스 사용 불가 (age만 사용)
SELECT * FROM users WHERE age = 25;

이유: 인덱스는 (name, age) 순서로 정렬되어 있어, name 없이 age만으로는 인덱스를 효율적으로 사용할 수 없습니다.

복합 인덱스 설계 원칙

1. 자주 함께 사용되는 컬럼 조합
2. 카디널리티가 높은 컬럼을 앞에 배치
3. WHERE 절에서 자주 사용되는 컬럼을 앞에 배치

---

요약

• 인덱스는 데이터 검색 속도를 높이기 위해 별도로 관리하는 자료구조로, 한 마디로 '검색용 복사본'입니다. 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 저장합니다.
• Full Table Scan은 전체 테이블을 읽는 방식이고, Index Scan은 인덱스를 통해 특정 범위만 탐색하는 방식입니다. 인덱스가 있으면 SELECT 성능이 비약적으로 향상됩니다.
• B+Tree는 대부분의 RDBMS에서 사용하는 인덱스 자료구조로, 리프 노드끼리 연결되어 있어 범위 검색에 유리합니다. 가이드 노드를 따라가서 실제 데이터가 있는 리프 노드에 도달하는 방식입니다.
• 인덱스의 트레이드오프: 검색 속도는 향상되지만, 추가 저장 공간이 필요하고 INSERT/UPDATE/DELETE 시 오버헤드가 발생합니다. 따라서 꼭 필요한 컬럼에만 전략적으로 인덱스를 만드는 것이 핵심입니다.

---

참고 자료

• MySQL 공식 문서 - 인덱스
• PostgreSQL 공식 문서 - 인덱스
• Database Indexing Explained

---

예상 꼬리질문 정리

1. 인덱스가 없으면 왜 느린가?

Full Table Scan의 문제점:

인덱스가 없으면 데이터베이스는 테이블의 모든 행을 순차적으로 읽어야 합니다.

1억 개의 데이터에서 특정 값 찾기
→ 전체 테이블을 처음부터 끝까지 스캔
→ 최악의 경우 1억 개를 모두 확인해야 함
→ 시간 복잡도: O(n)

Index Scan의 장점:

인덱스가 있으면 정렬된 데이터에서 이진 탐색이 가능합니다.

1억 개의 데이터에서 특정 값 찾기
→ 인덱스를 통해 이진 탐색
→ 최대 약 27번의 비교로 찾을 수 있음 (log₂(1억) ≈ 27)
→ 시간 복잡도: O(log n)

2. B-Tree와 B+Tree의 차이는?

B-Tree:

• 내부 노드와 리프 노드 모두 데이터를 저장할 수 있음
• 범위 검색 시 비효율적일 수 있음

B+Tree:

• 내부 노드는 키만 저장 (가이드 역할)
• 리프 노드에만 실제 데이터 주소 저장
• 리프 노드끼리 연결되어 범위 검색에 유리
• 대부분의 RDBMS에서 사용

왜 B+Tree를 사용하는가:

• 범위 검색 성능이 뛰어남
• 디스크 I/O 최소화
• 순차 접근 최적화

3. 인덱스를 많이 만들면 좋은가?

아니요. 인덱스를 남발하면 안 됩니다.

문제점:

1. 저장 공간 낭비
   • 인덱스는 별도의 저장 공간을 차지함
   • 불필요한 인덱스는 공간만 낭비
2. INSERT/UPDATE/DELETE 성능 저하
   • 데이터 변경 시 모든 인덱스를 업데이트해야 함
   • 인덱스가 많을수록 쓰기 성능 저하
3. 인덱스 선택 비용 증가
   • 옵티마이저가 어떤 인덱스를 사용할지 결정하는 비용 증가

권장 사항:

• 꼭 필요한 컬럼에만 인덱스 생성
• 자주 사용되는 쿼리 패턴을 분석하여 인덱스 설계
• 인덱스 사용 여부를 모니터링하여 불필요한 인덱스 제거

4. 복합 인덱스에서 순서가 중요한 이유는?

복합 인덱스는 왼쪽부터 순서대로 사용됩니다.

예시:

CREATE INDEX idx_name_age ON users(name, age);

인덱스 구조:

(name, age) 순서로 정렬됨
- ('김철수', 20)
- ('김철수', 25)
- ('홍길동', 30)
- ('홍길동', 35)

사용 가능한 쿼리:

-- ✅ name만 사용 (인덱스 사용 가능)
WHERE name = '홍길동'

-- ✅ name과 age 모두 사용 (인덱스 사용 가능)
WHERE name = '홍길동' AND age = 30

사용 불가능한 쿼리:

-- ❌ age만 사용 (인덱스 사용 불가)
WHERE age = 30

이유: 인덱스는 (name, age) 순서로 정렬되어 있어, name 없이 age만으로는 인덱스를 효율적으로 사용할 수 없습니다.

5. 카디널리티가 낮은 컬럼에 인덱스를 만들면?

카디널리티가 낮은 컬럼은 고유한 값의 개수가 적은 컬럼입니다.

예시:

• 성별: 남/여 (카디널리티: 2)
• 상태: 활성/비활성/대기 (카디널리티: 3)

문제점:

1. 인덱스 효과가 거의 없음
   • 인덱스를 사용해도 많은 행을 스캔해야 함
   • Full Table Scan과 성능 차이가 거의 없음
2. 인덱스 유지 비용만 발생
   • INSERT/UPDATE/DELETE 시 인덱스 업데이트 비용
   • 저장 공간 낭비

권장 사항:

• 카디널리티가 낮은 컬럼에는 인덱스를 만들지 않는 것이 좋음
• 단, 복합 인덱스의 일부로 사용되는 경우는 예외

6. 인덱스가 SELECT 성능에만 영향을 주나?

아니요. 인덱스는 여러 작업에 영향을 줍니다.

SELECT:

• 인덱스를 사용하여 검색 속도 향상

INSERT:

• 인덱스 업데이트로 인한 오버헤드 발생
• 인덱스가 많을수록 INSERT 속도 저하

UPDATE:

• 인덱스 컬럼을 변경하면 인덱스도 업데이트 필요
• 인덱스가 많을수록 UPDATE 속도 저하

DELETE:

• 인덱스에서도 해당 항목 삭제 필요
• 인덱스가 많을수록 DELETE 속도 저하

ORDER BY:

• 인덱스가 이미 정렬되어 있어 정렬 작업 최소화

JOIN:

• 외래 키에 인덱스가 있으면 조인 성능 향상