인덱스 실전

인덱스 사용 시
───────────────────────────────────────────────
| Id | Operation                   | Name            | Rows |
───────────────────────────────────────────────
|  0 | SELECT STATEMENT            |                 |    1 |
|  1 |  TABLE ACCESS BY INDEX ROWID| USERS           |    1 |
|* 2 |   INDEX RANGE SCAN          | IDX_USERS_EMAIL |    1 |
───────────────────────────────────────────────
→ INDEX RANGE SCAN: 인덱스를 사용하여 범위 검색
→ TABLE ACCESS BY INDEX ROWID: 인덱스에서 찾은 ROWID로 테이블 접근

인덱스 미사용 시
───────────────────────────────────────────────
| Id | Operation         | Name  | Rows  |
───────────────────────────────────────────────
|  0 | SELECT STATEMENT  |       | 10000 |
|* 1 |  TABLE ACCESS FULL| USERS | 10000 |
───────────────────────────────────────────────
→ TABLE ACCESS FULL: 전체 테이블 스캔 (Full Scan)

주요 인덱스 접근 방식
┌──────────────────────────┬────────────────────────────────┐
│ 접근 방식                │             설명               │
├──────────────────────────┼────────────────────────────────┤
│ INDEX UNIQUE SCAN        │ PK/유니크 인덱스로 1건 조회    │
│ INDEX RANGE SCAN         │ 범위 조건으로 여러 건 조회     │
│ INDEX FULL SCAN          │ 인덱스 전체를 순서대로 읽기    │
│ INDEX FAST FULL SCAN     │ 인덱스 전체를 빠르게 읽기      │
│ INDEX SKIP SCAN          │ 복합 인덱스 선두 컬럼 생략     │
│ TABLE ACCESS BY INDEX    │ 인덱스 ROWID로 테이블 접근     │
│ TABLE ACCESS FULL        │전체 테이블 스캔 (인덱스 미사용)│
└──────────────────────────┴────────────────────────────────┘

+----+------+---------------+-----------+------+------+-----------+
| id | type | possible_keys | key       | rows | filt | Extra     |
+----+------+---------------+-----------+------+------+-----------+
|  1 | ref  | idx_email     | idx_email |   1  | 100  | Using idx |
+----+------+---------------+-----------+------+------+-----------+

type 컬럼 해석 (성능 순)
┌──────────┬──────────────────────────────────────────┐
│   type   │                   설명                   │
├──────────┼──────────────────────────────────────────┤
│ system   │ 테이블에 1건만 존재 (최상)               │
│ const    │ PK/UNIQUE 인덱스로 상수 조회 (최상)      │
│ eq_ref   │ 조인에서 PK/UNIQUE로 1건 조회 (우수)     │
│   ref    │ 비유니크 인덱스로 조회 (양호)            │
│ range    │ 인덱스 범위 검색 (양호)                  │
│ index    │ 인덱스 풀 스캔 (주의)                    │
│   ALL    │ Full Table Scan (최악)                   │
└──────────┴──────────────────────────────────────────┘

Extra 컬럼 주요 값
* Using index: 커버링 인덱스 → 테이블 접근 불필요 (최상)
* Using where: WHERE 조건 필터링 적용
* Using temporary: 임시 테이블 생성 (주의)
* Using filesort: 추가 정렬 수행 (주의)
* Using index condition: 인덱스 조건 푸시다운 (ICP)

인덱스 무시
  SELECT * FROM users WHERE UPPER(name) = 'KIM';
  → 인덱스는 name 컬럼 원본값으로 정렬되어 있음
  → UPPER(name)은 인덱스에 없는 변환값 → Full Scan

해결 A: 값 쪽을 변환
  SELECT * FROM users WHERE name = 'Kim';
  → 대소문자 구분 없는 검색이면 컬럼이 아닌 비교값을 맞춤

해결 B: 함수 기반 인덱스 (Function-Based Index)
  CREATE INDEX idx_users_name_upper ON users(UPPER(name));
  SELECT * FROM users WHERE UPPER(name) = 'KIM';
  → 인덱스에 UPPER(name) 값이 저장 → 인덱스 사용 ✓

인덱스 무시
  -- phone 컬럼이 VARCHAR인데 숫자로 비교
  SELECT * FROM users WHERE phone = 01012345678;
  → DBMS가 내부적으로 TO_NUMBER(phone)으로 변환
  → 컬럼에 함수 적용과 동일한 효과 → Full Scan

해결: 타입 일치
  SELECT * FROM users WHERE phone = '01012345678';
  → 문자열 비교 → 인덱스 사용 ✓

주의: 반대 경우
  -- num_col이 NUMBER인데 문자로 비교
  SELECT * FROM orders WHERE order_id = '12345';
  → DBMS가 '12345'를 숫자로 변환 → 컬럼은 그대로 → 인덱스 사용 ✓
  → 숫자 컬럼에 문자를 넣으면 값쪽 변환 → 인덱스 영향 없음

복합 인덱스: (user_id, order_date, status)

인덱스 사용 ✓
  WHERE user_id = 1                           → 선두 컬럼 사용
  WHERE user_id = 1 AND order_date = '2024-01-01'  → 선두 2개
  WHERE user_id = 1 AND order_date > '2024-01-01'
        AND status = 'A'                      → 전체 사용

인덱스 미사용 ✗ (또는 비효율)
  WHERE order_date = '2024-01-01'             → 선두 컬럼 누락!
  WHERE status = 'A'                          → 선두 컬럼 누락!
  WHERE user_id = 1 AND status = 'A'          → 중간 컬럼 누락
                                                → user_id만 사용

복합 인덱스 원칙
  "왼쪽부터 연속된 컬럼만 인덱스 활용 가능"
  (a, b, c) 인덱스에서:
  - a ✓
  - a, b ✓
  - a, b, c ✓
  - b ✗ (선두 누락)
  - a, c △ (a만 사용, c는 필터)

LIKE '김%'    → 인덱스 사용 ✓ (접두사 고정)
LIKE '%김'    → 인덱스 미사용 ✗ (접두사 불확정)
LIKE '%김%'   → 인덱스 미사용 ✗ (양쪽 와일드카드)
LIKE '김_수'  → 인덱스 사용 ✓ (접두사 고정)

'%김%' 해결: 전문 검색 인덱스
  Oracle: CREATE INDEX idx_ft ON users(name)
          INDEXTYPE IS CTXSYS.CONTEXT;
  MySQL:  ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
  PostgreSQL: CREATE INDEX idx_gin ON users
              USING gin(to_tsvector('korean', name));

선택도(Selectivity) = 조건에 해당하는 행 수 / 전체 행 수

높은 선택도 (인덱스 효과적)
  WHERE email = 'test@example.com'
  → 100만 건 중 1건 → 선택도 0.0001% → 인덱스 효과 극대화

낮은 선택도 (인덱스 비효율)
  WHERE gender = 'M'
  → 100만 건 중 50만 건 → 선택도 50% → Full Scan이 더 빠름

인덱스 효과 판단 기준
  선택도 5~10% 이하  → 인덱스 효과적
  선택도 10~20%      → 상황에 따라 결정 (DBMS 옵티마이저 판단)
  선택도 20% 이상    → Full Scan이 유리할 수 있음

※ B-Tree 인덱스의 경우
  인덱스 → ROWID로 테이블 접근 = Random I/O
  Full Scan = Sequential I/O (연속 읽기)
  Random I/O가 전체의 10~15% 이상이면 Full Scan이 더 빠름

일반 인덱스 조회
  인덱스 검색 → ROWID 획득 → 테이블 접근 → 데이터 반환
  (2번의 I/O)

커버링 인덱스 조회
  인덱스 검색 → 인덱스에서 바로 데이터 반환
  (1번의 I/O)

예시
  CREATE INDEX idx_orders_cover
  ON orders(user_id, order_date, total_amount);

  SELECT order_date, total_amount
  FROM orders
  WHERE user_id = 100;
  → user_id, order_date, total_amount 모두 인덱스에 있음
  → 테이블 접근 불필요!
  → MySQL EXPLAIN: Extra = "Using index"

커버링 인덱스 설계 원칙
  1. WHERE 조건 컬럼 → 인덱스 앞쪽
  2. ORDER BY 컬럼 → 그 다음
  3. SELECT 컬럼 → 인덱스 끝쪽 (커버를 위해)
  → (WHERE, ORDER BY, SELECT) 순서로 설계

원칙 1: 등호(=) 조건 컬럼을 앞에
  WHERE user_id = 100 AND order_date >= '2024-01-01'
  → 인덱스: (user_id, order_date)  ← user_id가 앞
  → (order_date, user_id)보다 효율적

원칙 2: 카디널리티 높은 컬럼을 앞에 (같은 조건이면)
  WHERE department_id = 10 AND status = 'ACTIVE'
  → department_id 100개 vs status 5개
  → (department_id, status)가 더 효율적
  → 첫 번째 컬럼에서 대부분 필터링

원칙 3: 범위 조건은 마지막
  WHERE status = 'A' AND created_at BETWEEN ... AND ...
  → (status, created_at)?  ← 범위 조건 마지막
  → 범위 이후 컬럼은 인덱스 효과 감소

원칙 4: ORDER BY 컬럼 포함
  WHERE user_id = 100 ORDER BY created_at DESC
  → (user_id, created_at DESC)
  → 인덱스로 정렬까지 해결 → filesort 제거

쿼리:
  SELECT * FROM orders
  WHERE status = 'COMPLETED'
    AND user_id = 100
    AND order_date >= '2024-01-01';

후보 1: (status, user_id, order_date)
  status='COMPLETED' (등호) → user_id=100 (등호) → order_date>= (범위)
  → 3개 컬럼 모두 인덱스 활용 ✓

후보 2: (order_date, status, user_id)
  order_date>= (범위) → 범위 이후 status, user_id는 인덱스 활용 제한 ✗
  → 비효율

후보 3: (user_id, status, order_date)
  user_id=100 (등호, 카디널리티 높음) → status='COMPLETED' → order_date>=
  → 3개 컬럼 모두 활용 + 선택도 최상 ✓✓ ← 최적

결론: 등호 조건 중 카디널리티 높은 것 → 등호 → 범위 순

──── 사용되지 않는 인덱스 찾기 ────
-- 인덱스 모니터링 시작
ALTER INDEX idx_users_email MONITORING USAGE;

-- 일정 기간 후 사용 여부 확인
SELECT index_name, monitoring, used, start_monitoring
FROM v$object_usage;

-- USED = 'NO'인 인덱스 → 삭제 후보

──── 인덱스 단편화 확인 ────
SELECT index_name,
       blevel,          -- 트리 높이
       leaf_blocks,     -- 리프 블록 수
       distinct_keys,   -- 고유 키 수
       num_rows,        -- 인덱스 행 수
       clustering_factor -- 클러스터링 팩터
FROM user_indexes
WHERE table_name = 'ORDERS';

클러스터링 팩터 해석:
  * 테이블 블록 수에 가까움 → 잘 정렬됨 (최상)
  * 인덱스 행 수에 가까움 → 흩어져 있음 (비효율)

──── 사용되지 않는 인덱스 찾기 (MySQL 8.0+) ────
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

──── 인덱스 중복 확인 ────
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'mydb';

──── 인덱스 사용 통계 ────
SELECT index_name,
       rows_selected,   -- 인덱스로 조회한 행 수
       rows_inserted,
       rows_updated,
       rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND object_name = 'orders';

┌────────────────────┬──────────────────────────────────┐
│ 인덱스 유형        │               용도               │
├────────────────────┼──────────────────────────────────┤
│ 함수 기반 인덱스   │ UPPER(name), TO_CHAR(date)       │
│ (Function-Based)   │ → 함수 결과값으로 인덱스         │
├────────────────────┼──────────────────────────────────┤
│ 비트맵 인덱스      │ 카디널리티 낮은 컬럼             │
│ (Bitmap)           │ gender, status (Oracle DW)       │
├────────────────────┼──────────────────────────────────┤
│ 부분 인덱스        │ 특정 조건의 행만 인덱싱          │
│ (Partial)          │WHERE status='ACTIVE' (PostgreSQL)│
├────────────────────┼──────────────────────────────────┤
│ 전문 검색 인덱스   │ 텍스트 내용 검색                 │
│ (Full-Text)        │ LIKE '%keyword%' 대체            │
├────────────────────┼──────────────────────────────────┤
│ 역방향 키 인덱스   │ 순차 삽입 시 경합 방지           │
│ (Reverse Key)      │ Oracle RAC 환경                  │
├────────────────────┼──────────────────────────────────┤
│ GiST / GIN         │ 공간, 배열, JSON 검색            │
│ (PostgreSQL)       │ 다차원 데이터 인덱싱             │
└────────────────────┴──────────────────────────────────┘

-- 카디널리티가 낮은 컬럼에 적합
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- 비트맵 인덱스 장점: 여러 조건의 AND/OR 연산이 빠름
SELECT COUNT(*) FROM orders
WHERE status = 'COMPLETED' AND payment_type = 'CARD';
-- → 두 비트맵 인덱스의 비트 AND 연산 → 매우 빠름

-- 주의: DML이 빈번한 OLTP에는 부적합
-- → 행 잠금이 아닌 비트맵 세그먼트 잠금 → 동시성 저하

1. 과도한 인덱스 금지
   → INSERT/UPDATE/DELETE마다 인덱스도 갱신
   → 테이블당 인덱스 3~5개 권장 (OLTP 기준)
   → DML 성능과 SELECT 성능의 균형

2. 인덱스 중복 확인
   → (a, b) 인덱스가 있으면 (a) 인덱스는 불필요
   → (a, b)가 (a)의 역할도 수행

3. 사용되지 않는 인덱스 제거
   → 미사용 인덱스 = 쓰기 부하만 증가
   → 모니터링 후 확인하고 삭제

4. 통계 정보 갱신
   → 데이터 변경 후 통계가 오래되면 옵티마이저 판단 오류
   → ANALYZE TABLE (MySQL), DBMS_STATS (Oracle)

5. 온라인 인덱스 생성
   → 운영 중 인덱스 생성 시 ONLINE/CONCURRENTLY 사용
   → 테이블 잠금 방지

6. 인덱스 리빌드 정기 수행
   → DELETE가 많은 테이블 → 인덱스 단편화
   → 단편화 20% 이상 시 리빌드 고려

┌─────────────────────────────────────────────────────────┐
│              인덱스 실전 5대 원칙                       │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  1. 인덱스를 만들면 반드시 EXPLAIN으로 확인하라         │
│     → 인덱스가 있어도 안 타는 경우가 많음               │
│                                                         │
│  2. 복합 인덱스 컬럼 순서가 성능을 결정한다             │
│     → 등호 → 등호 → 범위 → ORDER BY → SELECT 순서       │
│                                                         │
│  3. 커버링 인덱스로 테이블 접근을 없애라                │
│     → 가장 효과적인 성능 개선 기법                      │
│                                                         │
│  4. 인덱스는 적을수록 좋다 (필요한 것만)                │
│     → 과도한 인덱스 = DML 성능 저하                     │
│                                                         │
│  5. 사용하지 않는 인덱스는 삭제하라                     │
│     → 정기 모니터링으로 미사용 인덱스 식별              │
│                                                         │
└─────────────────────────────────────────────────────────┘