[DB] 인덱스란?

SQLP 대비

📌 인덱스란?

인덱스는 데이터베이스에서 데이터를 빠르게 검색하기 위한 자료구조입니다. 책의 색인처럼 원하는 데이터를 빠르게 찾을 수 있도록 도와줍니다.

인덱스의 기본 구조

인덱스는 일반적으로 B-Tree(Balanced Tree) 구조를 가지고 있으며, 다음과 같은 특징이 있습니다:

• 인덱스 키값으로 정렬되어 있음
• 같은 키값이면 ROWID 순으로 정렬
• 소량의 데이터를 빠르게 찾고 ROWID를 얻기 위한 목적

인덱스 탐색의 두 단계

인덱스 검색은 크게 두 단계로 이루어집니다:

1. 수직적 탐색 (Vertical Scan)

   • 인덱스 스캔 시작 지점을 찾는 과정
   • 루트 노드부터 리프 노드까지 수직으로 탐색

2. 수평적 탐색 (Horizontal Scan)

   • 리프 노드에서 실제 데이터를 찾는 과정
   • 조건을 만족하는 범위를 순차적으로 스캔

---

🎯 인덱스 튜닝의 핵심 요소

1. 인덱스 스캔 효율화 튜닝

• 인덱스를 스캔하는 범위를 최소화
• 불필요한 인덱스 블록 읽기 방지

2. 랜덤 액세스 최소화 튜닝

• 테이블 랜덤 액세스 횟수 줄이기
• ROWID를 통한 테이블 접근을 최소화

---

💾 왜 DB 성능이 느릴까?

데이터베이스 성능 저하의 주범은 디스크 I/O입니다.

• 메모리 접근: 나노초(ns) 단위
• 디스크 접근: 밀리초(ms) 단위 → 수백만 배 차이

인덱스는 이러한 디스크 I/O를 최소화하기 위해:

• 범위 스캔(Range Scan)을 가능하게 함
• 필요한 데이터만 선택적으로 읽을 수 있게 함

---

🔑 결합 인덱스(Composite Index) 생성 원칙

선택도(Selectivity)를 고려한 컬럼 순서

-- 잘못된 예: 선택도가 높은(중복값 많은) 컬럼을 앞에
CREATE INDEX idx_wrong ON employee(gender, employee_id);
-- gender는 M/F 두 가지 값만 존재 (선택도 낮음)

-- 올바른 예: 선택도가 높은(중복값 적은) 컬럼을 앞에
CREATE INDEX idx_correct ON employee(employee_id, gender);
-- employee_id는 고유값 (선택도 높음)

원칙: 선택도가 낮은 컬럼(중복값이 적은)을 앞에 배치

인덱스 생성 시 추가 고려사항

• 자주 조회되는 컬럼 조합을 우선 고려
• WHERE 절에 자주 등장하는 순서를 반영
• 등치(=) 조건이 범위 조건보다 앞에 오도록 배치

---

⚠️ 인덱스 사용 불가 케이스

1. 인덱스 컬럼 가공

-- 인덱스 사용 불가 ❌
SELECT * FROM employee 
WHERE UPPER(name) = 'HONG';

SELECT * FROM employee 
WHERE salary * 12 > 50000000;

SELECT * FROM employee 
WHERE TO_CHAR(hire_date, 'YYYY') = '2024';

-- 인덱스 사용 가능 ✅
SELECT * FROM employee 
WHERE name = 'HONG';

SELECT * FROM employee 
WHERE salary > 50000000 / 12;

SELECT * FROM employee 
WHERE hire_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
  AND hire_date < TO_DATE('2025-01-01', 'YYYY-MM-DD');

핵심: 인덱스 컬럼은 가공하지 않은 상태로 조건에 사용해야 함

2. 묵시적 형변환

-- employee_id가 VARCHAR2 타입일 때
SELECT * FROM employee WHERE employee_id = 1000;  -- 숫자로 조회 ❌

-- 올바른 방법
SELECT * FROM employee WHERE employee_id = '1000';  -- 문자열로 조회 ✅

---

🔍 인덱스 Range Scan 조건

인덱스 Range Scan이 가능하려면:

1. 인덱스 선두 컬럼이 조건절에 있어야 함
2. 컬럼이 가공되지 않은 상태여야 함

-- 인덱스: (dept_id, hire_date, salary)

-- Range Scan 가능 ✅
SELECT * FROM employee WHERE dept_id = 10;
SELECT * FROM employee WHERE dept_id = 10 AND hire_date >= '2024-01-01';

-- Range Scan 불가 ❌ (선두 컬럼 없음)
SELECT * FROM employee WHERE hire_date >= '2024-01-01';
SELECT * FROM employee WHERE salary > 5000000;

인덱스 사용 시 정렬 생략

인덱스는 이미 정렬되어 있으므로:

• ORDER BY 절의 정렬 연산이 생략됨
• 단, ORDER BY 컬럼이 인덱스 컬럼 순서와 일치해야 함

-- 인덱스: (dept_id, hire_date)

-- Sort 연산 생략 ✅
SELECT * FROM employee 
WHERE dept_id = 10 
ORDER BY hire_date;

-- Sort 연산 필요 ❌
SELECT * FROM employee 
WHERE dept_id = 10 
ORDER BY salary;

---

📊 옵티마이저의 IN 절 처리: IN-List Iterator

옵티마이저는 IN 절을 만나면 IN-List Iterator 방식으로 처리합니다.

SELECT * FROM employee 
WHERE dept_id IN (10, 20, 30);

-- 내부적으로 다음과 같이 처리됨:
-- dept_id = 10 → Range Scan
-- dept_id = 20 → Range Scan
-- dept_id = 30 → Range Scan

특징:

• IN 절의 리스트 개수만큼 Range Scan을 반복 수행
• 각각의 값에 대해 독립적인 Range Scan 실행
• Union All과 유사한 실행 계획

---

📖 인덱스 스캔 유형

1. Index Range Scan (인덱스 범위 스캔)

가장 일반적이고 효율적인 스캔 방식입니다.

사용 조건:

• 인덱스 선두 컬럼이 조건절에 존재
• 범위 조건 (>, <, BETWEEN, LIKE 등)

-- 인덱스: (dept_id, hire_date)
SELECT * FROM employee 
WHERE dept_id = 10 
  AND hire_date >= '2024-01-01';

특징:

• B-Tree의 일부 범위만 스캔
• 양방향 스캔 가능 (ASC/DESC)

---

2. Index Full Scan (인덱스 전체 스캔)

인덱스 전체를 처음부터 끝까지 스캔합니다.

사용 시점:

• 인덱스 선두 컬럼이 조건절에 없지만
• 인덱스에 포함된 컬럼만으로 처리 가능한 경우
• 소량의 데이터만 테이블 액세스가 필요한 경우
• ORDER BY 절을 위해 정렬된 결과가 필요한 경우

-- 인덱스: (dept_id, salary, name)
-- dept_id는 조건에 없지만, 인덱스에 필요한 컬럼이 모두 있음
SELECT dept_id, salary, name 
FROM employee 
WHERE salary > 5000000 
ORDER BY dept_id, salary;

특징:

• 단방향 스캔 (ASC만 가능)
• Single Block I/O 방식
• 정렬 효과 활용 가능

---

3. Index Unique Scan (인덱스 유일 스캔)

유일 인덱스를 통해 단 하나의 값을 찾는 스캔입니다.

사용 조건:

• PK 또는 Unique 인덱스
• 등치(=) 조건으로 검색

-- PK 인덱스: (employee_id)
SELECT * FROM employee 
WHERE employee_id = 1000;

특징:

• 가장 효율적인 스캔 방식
• 수직적 탐색만 수행 (수평적 탐색 불필요)
• 최대 1건의 데이터만 반환

---

4. Index Skip Scan (인덱스 스킵 스캔)

인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용할 수 있는 방식입니다.

사용 조건:

• 인덱스 선두 컬럼의 Distinct Value가 적음
• 후행 컬럼의 Distinct Value가 많음
• 후행 컬럼에 대한 조건이 있음

-- 인덱스: (gender, employee_id)
-- gender는 'M', 'F' 두 가지 값만 존재

SELECT * FROM employee 
WHERE employee_id = 1000;
-- gender 조건이 없지만 Index Skip Scan 가능

-- 내부적으로 다음과 같이 처리:
-- WHERE gender = 'M' AND employee_id = 1000
-- WHERE gender = 'F' AND employee_id = 1000

특징:

• 선두 컬럼의 Distinct Value만큼 Range Scan 반복
• 선두 컬럼의 카디널리티가 높으면 비효율적
• Oracle 9i부터 지원

효과적인 케이스:

-- 인덱스: (사용여부, 고객ID, 주문일자)
-- 사용여부는 'Y', 'N' 두 가지만 존재

SELECT * FROM orders 
WHERE 고객ID = 'C1000' 
  AND 주문일자 >= '2024-01-01';
-- 사용여부 조건 없이도 인덱스 활용 가능

---

5. Index Fast Full Scan (인덱스 고속 전체 스캔)

인덱스를 Multiblock I/O 방식으로 빠르게 읽는 스캔입니다.

사용 조건:

• 쿼리에 사용한 컬럼이 모두 인덱스에 포함되어야 함
• 인덱스만으로 쿼리 처리 가능 (테이블 액세스 불필요)

-- 인덱스: (dept_id, salary, hire_date)
SELECT dept_id, COUNT(*), AVG(salary)
FROM employee 
GROUP BY dept_id;
-- 테이블 액세스 없이 인덱스만으로 처리 가능

특징:

• Multiblock I/O 방식 사용 (여러 블록을 한 번에 읽음)
• Index Full Scan보다 빠름
• 정렬 순서 보장 안 됨 (병렬 처리 가능)
• 대량의 인덱스 블록을 읽을 때 효과적

Index Full Scan vs Index Fast Full Scan

구분	Index Full Scan	Index Fast Full Scan
I/O 방식	Single Block I/O	Multiblock I/O
정렬 순서	보장됨	보장 안 됨
병렬 처리	불가능	가능
속도	느림	빠름
사용 케이스	ORDER BY 필요 시	집계/통계 쿼리

---

6. Index Range Scan Descending (인덱스 범위 역순 스캔)

인덱스를 역순으로 스캔하는 방식입니다.

사용 시점:

• ORDER BY DESC 절이 있을 때
• 최댓값을 빠르게 찾을 때

-- 인덱스: (dept_id, hire_date)

-- 최근 입사자 조회
SELECT * FROM employee 
WHERE dept_id = 10 
ORDER BY hire_date DESC;

-- 가장 최근 입사자 1명만 조회
SELECT * FROM employee 
WHERE dept_id = 10 
ORDER BY hire_date DESC 
FETCH FIRST 1 ROW ONLY;

특징:

• 내림차순 정렬 시 Sort 연산 생략
• 인덱스를 뒤에서부터 읽음
• Range Scan과 동일한 효율

---

💡 인덱스 스캔 방식 선택 기준

1. 유일값 검색? 
   → Index Unique Scan

2. 선두 컬럼 조건 있음?
   → Index Range Scan (가장 일반적)

3. 선두 컬럼 조건 없지만 선두 컬럼 Distinct Value 적음?
   → Index Skip Scan

4. 인덱스에 필요한 컬럼 모두 있음 + 대량 데이터?
   → Index Fast Full Scan

5. 인덱스에 필요한 컬럼 모두 있음 + 정렬 필요?
   → Index Full Scan

6. 내림차순 정렬 필요?
   → Index Range Scan Descending

---

📝 정리 및 실무 팁

인덱스 설계 체크리스트

✅ 선택도가 높은 컬럼을 선두에 배치했는가?
✅ WHERE 절에 자주 사용되는 컬럼 조합인가?
✅ 등치 조건 컬럼을 범위 조건 컬럼보다 앞에 두었는가?
✅ ORDER BY 절과 인덱스 컬럼 순서가 일치하는가?

SQL 작성 시 주의사항

❌ 피해야 할 것:

• 인덱스 컬럼 가공 (함수, 연산)
• 묵시적 형변환
• 선두 컬럼 누락

✅ 권장사항:

• 인덱스 컬럼은 가공하지 않고 사용
• 명시적 형변환
• 선두 컬럼부터 순차적으로 조건 작성

성능 측정 습관

-- 실행계획 확인
EXPLAIN PLAN FOR
SELECT ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 실제 수행 통계
SELECT /*+ GATHER_PLAN_STATISTICS */ ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---

🎓 SQLP 시험 대비 핵심 포인트

1. 인덱스 구조: B-Tree, 키값 정렬, ROWID 순서
2. 튜닝 핵심: 스캔 효율화 + 랜덤 액세스 최소화
3. Range Scan 조건: 선두 컬럼, 가공 금지
4. 스캔 유형: 6가지 스캔 방식의 특징과 사용 조건
5. IN 절 처리: IN-List Iterator 방식
6. 인덱스 설계: 선택도, 컬럼 순서, 사용 패턴

---