(데이터베이스) 인덱스(Index) 개념 정리 (Oracle 중심)

인덱스(Index) 개념 정리 (Oracle 중심)

1. 인덱스란?

• 목적: 테이블에서 조건을 만족하는 튜플을 빠르게 조회하기 위해 사용됨.
• 기본 원리: 테이블과는 별도로 정렬된 구조를 유지하여, 검색 시 테이블을 모두 탐색하는 Full Table Scan을 피할 수 있음.
• 활용 예시: WHERE, ORDER BY, JOIN, GROUP BY, DISTINCT 절 등에서 사용.

---

2. 인덱스의 자동 생성

• PK (Primary Key): 자동으로 Unique B-Tree 인덱스가 생성됨.
• Unique 제약 조건: 고유성 보장을 위해 Unique Index 생성됨.
• Foreign Key: 자동 생성되지 않음. → 외래키로 자주 JOIN하거나 부모 삭제 시 참조 무결성 체크가 빈번할 경우, 수동 인덱스 권장.

---

3. 인덱스의 종류

1) 일반 B-Tree 인덱스

• 가장 기본적인 인덱스
• 정렬된 구조를 유지하며, Binary Search 방식으로 탐색
• WHERE column = 'X', column > 10 같은 등가/범위 검색에 최적

 

 

1단계: 중간값 기준 분할 (Binary Search 시작)    

 

 

2단계: 다시 중간값 비교 

 

 

3단계: 마지막 값 확인

* 요약 표현

1. 인덱스 테이블에서 절반 기준으로 중위 값 찾음
2. 중위 값과 조건 값(9) 비교 → 방향 결정 → 반쪽 제거
3. 남은 절반에서 다시 중위 값 비교 → 반복
4. 일치하는 값 찾으면 해당 ROWID로 매핑

 

2) Unique Index

• 고유한 값만 허용됨 (e.g., 주민등록번호, 이메일 등)
• 제약조건과 함께 자주 사용됨 (UNIQUE, PRIMARY KEY)

3) 복합 인덱스 (Composite / Multi-column Index)

• 2개 이상의 컬럼으로 구성
• 왼쪽에서 오른쪽 방향으로 정렬됨
  → 인덱스 (a, b) 생성 시 WHERE a = ... 또는 WHERE a = ... AND b = ... 조건에만 사용 가능
  → WHERE b = ... 단독으로는 인덱스 사용되지 않음

-- 사용되는 경우
SELECT * FROM user WHERE a = 1;           -- OK
SELECT * FROM user WHERE a = 1 AND b = 2; -- OK

-- 사용 안 되는 경우
SELECT * FROM user WHERE b = 2;           -- 인덱스 미사용

4) 커버링 인덱스 (Covering Index)

• 인덱스에 조회 대상 컬럼이 모두 포함되어 있어, 테이블 접근 없이 인덱스만으로 결과 반환 가능
• 인덱스 I/O만 발생 → 속도 향상
• 오라클에서 인덱스 포함 여부는 EXPLAIN PLAN의 ACCESS PATH에 TABLE ACCESS 없음으로 확인 가능

5) 함수 기반 인덱스 (Function-Based Index)

• 컬럼 값에 함수를 적용한 결과에 인덱스 생성

CREATE INDEX idx_lower_name ON users(LOWER(name));

• TO_CHAR, TRIM, NVL 등을 사용하는 WHERE 절에서 유용
• 인덱스 타기 위해서는 SQL문에서도 동일한 함수 적용 필요

6) 비트맵 인덱스 (Bitmap Index)

• 카디널리티가 낮은 컬럼 (ex. 성별, 상태값 등)에 적합
• 0/1 비트맵으로 구성되어, AND/OR 연산 성능이 뛰어남
• **OLTP 환경(자주 변경)**에는 부적합 → DML 시 Lock 경합 심화
• OLAP 환경에 적합

7) 해시 인덱스 (Oracle X)

• 오라클은 디폴트로 지원하지 않음, MySQL 등에서 주로 사용
• 해시 함수 기반으로 탐색
• O(1) 성능이지만 범위 검색 불가, 재해싱 부담 있음
• 등가 비교 (=)에만 적합

---

4. 인덱스 힌트 사용

오라클 힌트 구문

SELECT /*+ INDEX(tbl idx_name) */ * FROM tbl WHERE ...

목적

• 옵티마이저가 원하는 인덱스 경로로 강제 유도
• 비효율적인 계획 회피 (ex. Full Scan 강제 회피)

---

5. 인덱스의 단점 및 주의사항

항목	설명
쓰기 성능 저하	INSERT/UPDATE/DELETE 시마다 인덱스도 갱신됨
저장공간 증가	인덱스도 별도 세그먼트 공간을 차지
잘못된 인덱스 설계	불필요한 인덱스는 옵티마이저 혼란 유발 및 성능 저하 유발

 

→ DML이 잦은 컬럼에는 인덱스를 신중히 적용

---

6. 인덱스를 사용하지 않는 경우 (fullscan이 더 나을때?)

• WHERE 조건에 함수, 연산자 포함: WHERE salary + 1000 > 2000
• 컬럼 순서가 맞지 않는 복합 인덱스 사용
• 데이터가 소량일 때 (수십 ~ 수백 건): Full Table Scan이 더 빠름
• 조건이 전체 행의 대다수를 포함할 경우: 인덱스 I/O 후 테이블 접근보다 Full Scan이 유리

---

7. 성능 분석 도구

1) EXPLAIN PLAN

• 쿼리 실행 계획 확인 (인덱스 사용 여부 포함)

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2) AUTOTRACE

• SQL Plus에서 실행 계획 + 통계 함께 출력

SET AUTOTRACE ON
SELECT * FROM ...

---

8. 인덱스 설계 시 고려사항

• 선택도(Selectivity): 값의 다양성이 높은 컬럼일수록 유리
• DML 빈도: 자주 바뀌는 컬럼에는 가급적 인덱스 X
• 쿼리 패턴 분석: 실제 사용하는 WHERE 조건, JOIN 대상 고려
• 인덱스 재구성 (Rebuild): 파편화된 인덱스를 수동으로 최적화 가능

ALTER INDEX idx_name REBUILD;

---

9. 기타 실무 팁

• 이미 수백만 건 이상 데이터가 있는 테이블에 인덱스 생성 시:
  → ONLINE 옵션 또는 OFF HOURS 작업 권장
• 다중 인덱스보다 Covering Index 또는 Prefix Index가 효율적일 수 있음
• 인덱스가 너무 많으면 옵티마이저가 비효율 경로 선택할 가능성 있음

---

마무리 정리 포인트

개념	요약
B-Tree 인덱스	등가 + 범위 조건 모두 가능
복합 인덱스	왼쪽부터 순서 중요 (Prefix Rule)
커버링 인덱스	테이블 접근 없이 인덱스만으로 결과 반환 가능
비트맵 인덱스	변경 적은 OLAP 환경에 적합
Function-based Index	함수 포함 조건에도 인덱스 탐색 가능
주의사항	쓰기 성능 저하, 저장 공간 증가, 조건에 따라 인덱스 미사용

 

 

사진 출처 - 유튜브 쉬운코드