인덱스의 개념과 필요성

Clustered Index (클러스터형)
  * 테이블당 1개만 가능
  * 데이터 행이 클러스터 키 순서의 구조 안에 저장됨
  * InnoDB는 기본 키를 클러스터 키로 사용
  * SQL Server는 PK를 clustered/nonclustered 중 선택 가능
  * 범위 검색에 매우 유리 (연속된 페이지 읽기)

Non-Clustered Index (비클러스터형)
  * 테이블당 여러 개 가능
  * 인덱스와 데이터가 별도로 저장됨
  * 인덱스에서 ROWID, 기본 키, 포인터 등으로 데이터 행을 참조
  * 랜덤 I/O 발생 (다수 행 접근 시 느릴 수 있음)

Clustered = 사전 (단어가 알파벳 순으로 정렬되어 있음)
Non-Clustered = 책의 색인 (색인은 정렬, 본문은 별도)

-- 기본 인덱스 생성
CREATE INDEX idx_users_email ON users (email);

-- 유니크 인덱스
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- 복합 인덱스 (컬럼 순서 중요!)
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);

-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

-- SQL Server는 filtered index 문법으로 같은 의도를 표현
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';

-- 표현식/함수 기반 인덱스 (PostgreSQL, Oracle 등)
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- 인덱스 삭제 (PostgreSQL)
DROP INDEX idx_users_email;

-- 인덱스 삭제 (MySQL)
DROP INDEX idx_users_email ON users;

-- 인덱스 삭제 (SQL Server)
DROP INDEX idx_users_email ON users;

-- 인덱스 목록 조회 (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- 인덱스 목록 조회 (MySQL)
SHOW INDEX FROM users;

-- 인덱스 재구성 (PostgreSQL)
REINDEX INDEX idx_users_email;

-- 인덱스 재구성 (SQL Server)
ALTER INDEX idx_users_email ON users REBUILD;

CREATE INDEX idx_orders ON orders (user_id, order_date);

-- 인덱스: (user_id, order_date, status)

-- ✅ 사용 가능
WHERE user_id = 1                              -- a만
WHERE user_id = 1 AND order_date = '2024-01-01' -- a, b
WHERE user_id = 1 AND order_date = '2024-01-01' AND status = 'completed' -- a, b, c

-- ⚠️ 부분 사용 (user_id만 인덱스 사용)
WHERE user_id = 1 AND status = 'completed'     -- a, c (b 건너뜀!)

-- ❌ 보통 사용 어려움
WHERE order_date = '2024-01-01'                -- b만 (a 없음!)
WHERE status = 'completed'                      -- c만
WHERE order_date = '2024-01-01' AND status = 'completed' -- b, c (a 없음!)

-- 1. 인덱스 컬럼에 함수를 적용하면 일반 인덱스 사용이 어려움
SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';
-- 해결: 함수 기반 인덱스 또는 컬럼 원본 값으로 비교

-- 2. 날짜 컬럼에 함수를 적용하면 범위 탐색이 어려움
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 해결: 범위 조건으로 변환
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 3. 암시적 타입 변환
SELECT * FROM users WHERE phone = 01012345678;  -- phone이 VARCHAR
-- 해결: 올바른 타입으로 비교
SELECT * FROM users WHERE phone = '01012345678';

-- 4. LIKE 앞부분 와일드카드 (B-tree 기준 일반적으로)
SELECT * FROM users WHERE name LIKE '%철수';  -- 인덱스 사용 불가
SELECT * FROM users WHERE name LIKE '김%';    -- 인덱스 사용 가능

-- 5. OR 조건 (경우에 따라)
SELECT * FROM users WHERE email = 'a@t.co' OR name = '김철수';
-- DBMS가 index merge/bitmap scan을 선택할 수도 있지만 항상 유리하지는 않음

-- 6. NOT, != 조건
SELECT * FROM users WHERE status != 'deleted';
-- 대부분의 행을 선택하면 Full Scan이 더 효율적

-- 검색은 user_id로, 결과 컬럼도 인덱스에서 바로 반환
CREATE INDEX idx_orders_covering
ON orders (user_id) INCLUDE (order_date, total_amount);

-- 인덱스별 스캔 횟수 확인
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- idx_scan = 0인 인덱스는 삭제 후보
-- 단, 통계 reset 여부, 드문 운영 쿼리, unique/constraint 보조 용도인지 먼저 확인

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;