인덱스의 종류

클러스터드 인덱스 = 사전
  데이터가 인덱스 키 순서대로 저장
  A → Apple → 바로 뜻(데이터)이 옆에 있음
  B → Banana → 바로 뜻(데이터)이 옆에 있음
  └→ 사전에서 단어를 찾으면 추가 이동 없이 바로 뜻을 읽음

비클러스터드 인덱스 = 교과서 뒷편 색인
  인덱스 → 페이지 번호(ROWID) → 해당 페이지로 이동
  트랜잭션 → p.245 → 245페이지로 이동해서 읽음
  └→ 색인에서 페이지 번호를 찾고, 그 페이지로 이동해야 함

B+Tree 내부 노드
               [30]
              /    \
         [10, 20] [30, 40, 50]

리프 노드 (전체 행 데이터 저장)
  [10, "Alice", "IT", 5000] → [20, "Bob", "HR", 4000] → [30, ...]
  ← 리프 노드에 행 데이터 전체가 정렬되어 저장됨 →

특징
  * 리프 노드 = 데이터 페이지 (별도 테이블 힙이 없음)
  * PK 순서로 데이터가 물리적으로 정렬
  * PK 범위 검색 → 연속된 페이지 읽기 → 매우 빠름

세컨더리 인덱스 B+Tree
               [Kim]
              /      \
         [Choi, Hong] [Kim, Lee, Park]

리프 노드 (PK 값을 저장)
  [Choi, PK=20] → [Hong, PK=40] → [Kim, PK=10] → [Lee, PK=30]

검색 과정 (WHERE name = 'Kim')
  1단계: 세컨더리 인덱스에서 'Kim' 찾기 → PK=10 발견
  2단계: PK=10으로 클러스터드 인덱스 재탐색 → 전체 행 반환
  → 이 2단계 과정을 "북마크 룩업" 또는 "테이블 액세스" 라고 함

PK가 INT (4바이트)인 경우
  세컨더리 인덱스 리프: [인덱스키, 4바이트 PK]

PK가 UUID (36바이트)인 경우
  세컨더리 인덱스 리프: [인덱스키, 36바이트 PK]
  → 인덱스 크기가 훨씬 커짐!
  → 페이지당 저장 가능한 엔트리 수 감소
  → 트리 높이 증가 → 성능 저하

권장: InnoDB에서는 AUTO_INCREMENT INT/BIGINT를 PK로 사용
  * 순차 삽입 → 페이지 분할 최소화
  * PK 크기 작음 → 세컨더리 인덱스 효율적
  * UUID PK → 랜덤 삽입 → 페이지 분할 빈번 → 성능 저하

유니크 인덱스 검색
  WHERE email = 'user@example.com'
  → B+Tree에서 해당 키를 찾으면 즉시 반환 (유일하므로)
  → 인덱스 유니크 스캔 (Index Unique Scan)

일반 인덱스 검색
  WHERE email = 'user@example.com'
  → 해당 키를 찾아도 다음 키까지 확인 (중복 가능하므로)
  → 인덱스 범위 스캔 (Index Range Scan)

차이: 유니크 인덱스는 한 건 찾으면 바로 종료
  → 미미하지만 일반 인덱스보다 약간 빠름

CREATE INDEX idx_orders ON orders(user_id, order_date, status);

인덱스 정렬 순서
  user_id | order_date  | status
  --------+-------------+----------
  1001    | 2024-01-01  | PENDING
  1001    | 2024-01-15  | DELIVERED
  1001    | 2024-02-01  | SHIPPED
  1002    | 2024-01-01  | PENDING
  1002    | 2024-03-01  | CANCELLED
  1003    | 2024-02-15  | DELIVERED

→ 1차 정렬: user_id
→ 2차 정렬: 같은 user_id 내에서 order_date
→ 3차 정렬: 같은 user_id, order_date 내에서 status

인덱스: (user_id, order_date, status)

사용 가능한 쿼리
  WHERE user_id = 1001                              ✓ (A)
  WHERE user_id = 1001 AND order_date = '2024-01-01' ✓ (A, B)
  WHERE user_id = 1001 AND order_date > '2024-01-01' ✓ (A, B)
  WHERE user_id = 1001 AND order_date = '2024-01-01' 
        AND status = 'PENDING'                       ✓ (A, B, C)

사용 불가능한 쿼리
  WHERE order_date = '2024-01-01'                    ✗ (B만, A 누락!)
  WHERE status = 'PENDING'                           ✗ (C만, A,B 누락!)
  WHERE order_date = '2024-01-01' AND status = 'X'   ✗ (B,C만, A 누락!)

부분적으로 사용
  WHERE user_id = 1001 AND status = 'PENDING'        △ (A만 사용, B건너뜀)
  → user_id=1001인 범위에서 status 필터링 (인덱스 스킵)

1순위: 등호(=) 조건으로 자주 사용되는 컬럼
2순위: 범위 조건(>, <, BETWEEN)으로 사용되는 컬럼
3순위: ORDER BY에 사용되는 컬럼
4순위: SELECT에만 사용되는 컬럼 (커버링 목적)

이유: 등호 조건이 앞에 있어야 범위를 최대한 좁힌 상태에서
      다음 컬럼의 범위 조건을 활용할 수 있음

예시
  WHERE user_id = ? AND order_date BETWEEN ? AND ? ORDER BY status
  → 인덱스: (user_id, order_date, status)  ← 최적!
  → 인덱스: (order_date, user_id, status)  ← user_id 등호 활용 못함

CREATE INDEX idx_covering ON orders(user_id, order_date, total);

쿼리: SELECT order_date, total FROM orders WHERE user_id = 1001;

일반 인덱스 (user_id만)
  1. 인덱스에서 user_id=1001인 ROWID 목록 획득
  2. 각 ROWID로 테이블 접근 → order_date, total 읽기
  → 테이블 랜덤 I/O 발생!

커버링 인덱스 (user_id, order_date, total)
  1. 인덱스에서 user_id=1001인 엔트리 → order_date, total 포함
  2. 테이블 접근 불필요!
  → 인덱스만으로 응답 → 매우 빠름!

MySQL EXPLAIN에서 Extra에 "Using index" 표시 = 커버링 인덱스 사용

장점
  * 테이블 액세스 제거 → I/O 대폭 감소
  * 특히 대량 데이터에서 극적 성능 향상

단점
  * 인덱스 크기 증가 → 디스크 사용량 증가
  * DML 시 인덱스 갱신 비용 증가
  * 모든 쿼리를 커버링하려면 인덱스가 너무 많아짐

실무 원칙
  * 빈번하고 성능이 중요한 쿼리에만 적용
  * SELECT * 대신 필요한 컬럼만 선택하는 습관이 커버링 활용에 유리

-- Oracle: 대소문자 무시 검색
CREATE INDEX idx_upper_name 
    ON employees(UPPER(last_name));

SELECT * FROM employees WHERE UPPER(last_name) = 'KIM';
-- → 일반 인덱스는 UPPER() 때문에 사용 불가
-- → 함수 기반 인덱스는 사용 가능!

-- MySQL 8.0+: 표현식 인덱스
CREATE INDEX idx_year ON orders((YEAR(order_date)));

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- PostgreSQL: 함수 인덱스
CREATE INDEX idx_lower_email ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'user@test.com';

WHERE UPPER(name) = 'KIM'      → UPPER()에 인덱스
WHERE YEAR(created_at) = 2024  → YEAR()에 인덱스
WHERE price * 1.1 > 10000      → (price * 1.1)에 인덱스
WHERE JSON_VALUE(data, '$.age') > 20  → JSON 추출에 인덱스

주의: 함수 기반 인덱스를 만들더라도
  WHERE 절의 표현식이 인덱스 정의와 정확히 일치해야 사용됨

-- 활성 주문에만 인덱스 (전체의 10%라면 인덱스 크기 90% 절약)
CREATE INDEX idx_active_orders 
    ON orders(order_date) 
    WHERE status = 'ACTIVE';

SELECT * FROM orders 
WHERE status = 'ACTIVE' AND order_date > '2024-01-01';
-- → 부분 인덱스 사용!

SELECT * FROM orders 
WHERE status = 'CANCELLED' AND order_date > '2024-01-01';
-- → 부분 인덱스 사용 불가! (CANCELLED는 인덱스에 없음)

문서 1: "데이터베이스 설계는 중요하다"
문서 2: "데이터베이스 인덱스 최적화"
문서 3: "인덱스 설계 가이드"

역인덱스
  "데이터베이스" → [문서1, 문서2]
  "설계"         → [문서1, 문서3]
  "인덱스"       → [문서2, 문서3]
  "중요"         → [문서1]
  "최적화"       → [문서2]
  "가이드"       → [문서3]

검색 "설계 AND 인덱스"
  "설계" → [문서1, 문서3]
  "인덱스" → [문서2, 문서3]
  AND → [문서3]

employees 테이블 (10행)
  ID: 1   2   3   4   5   6   7   8   9   10
  부서: IT  HR  IT  IT  HR  FIN IT  FIN HR  IT

비트맵 인덱스 (부서)
  IT:  1 0 1 1 0 0 1 0 0 1
  HR:  0 1 0 0 1 0 0 0 1 0
  FIN: 0 0 0 0 0 1 0 1 0 0

WHERE 부서='IT' AND 성별='F'
  IT:  1 0 1 1 0 0 1 0 0 1
  F:   0 0 1 0 1 0 1 1 0 1
  AND: 0 0 1 0 0 0 1 0 0 1  → 3, 7, 10번 행
  → 비트 AND 연산으로 초고속 필터링!

장점
  * 낮은 카디널리티에서 B+Tree보다 효율적
  * 복합 조건(AND, OR, NOT) → 비트 연산으로 초고속 처리
  * 인덱스 크기가 매우 작음
  * DW/OLAP 환경의 대량 분석 쿼리에 최적

단점
  * DML(INSERT/UPDATE/DELETE) 시 전체 비트맵 갱신 → 성능 저하
  * 행 잠금이 아닌 비트맵 세그먼트 잠금 → 동시성 매우 낮음
  * OLTP 환경에서는 절대 사용 금지

Oracle 전용 구문
  CREATE BITMAP INDEX idx_dept ON employees(department);

해시 함수: h(key) = key % 4

key=10 → h(10)=2 → 버킷 2에 저장
key=25 → h(25)=1 → 버킷 1에 저장
key=14 → h(14)=2 → 버킷 2에 저장 (충돌!)

검색: WHERE id = 25
  h(25) = 1 → 버킷 1 직접 접근 → O(1)

범위: WHERE id > 10 AND id < 30
  → 해시 값은 원래 순서를 보존하지 않음
  → 모든 버킷을 다 봐야 함 → 사용 불가!

-- 최신 주문부터 조회하는 쿼리가 빈번할 때
CREATE INDEX idx_order_desc ON orders(order_date DESC);

-- 복합 인덱스에서 방향 혼합
CREATE INDEX idx_mixed ON scores(class_id ASC, score DESC);
-- → class_id 오름차순 정렬 후, 같은 class 내에서 score 내림차순
-- → ORDER BY class_id ASC, score DESC 에 최적

-- Oracle 11g+
ALTER INDEX idx_orders_date INVISIBLE;
-- → 옵티마이저가 이 인덱스를 무시 (삭제하지 않고 테스트)

ALTER INDEX idx_orders_date VISIBLE;
-- → 다시 사용 가능하게 복원

-- MySQL 8.0+
ALTER TABLE orders ALTER INDEX idx_date INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_date VISIBLE;

-- 용도: 인덱스 삭제 전 영향도 테스트
-- DROP 하면 재생성 비용이 크므로, INVISIBLE로 먼저 테스트

┌──────────────────┬──────────────────────────────────────┐
│ 인덱스 종류      │ 적합한 상황                          │
├──────────────────┼──────────────────────────────────────┤
│ 클러스터드       │ PK 기반 범위 검색, 순차 접근         │
│ 비클러스터드     │ 다양한 검색 조건, 여러 인덱스 필요   │
│      유니크      │중복 허용 불가 컬럼 (이메일, 주민번호)│
│       복합       │ 다중 조건 검색, 정렬 최적화          │
│      커버링      │ 빈번한 조회 쿼리의 테이블 접근 제거  │
│ 함수 기반        │ 연산 결과로 검색 (UPPER, YEAR 등)    │
│       부분       │ 특정 조건에 해당하는 행만 인덱싱     │
│ 전문 검색        │ 텍스트 내용 검색                     │
│      비트맵      │ 낮은 카디널리티 + OLAP 환경          │
└──────────────────┴──────────────────────────────────────┘