인덱스 실전

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스 (다중 컬럼)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

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

-- 인덱스 삭제
DROP INDEX idx_users_email;

-- 인덱스 리빌드 (단편화 해소)
ALTER INDEX idx_orders_user_date REBUILD;

-- 인덱스 리빌드 (온라인 옵션, 지원 에디션/상황 확인 필요)
ALTER INDEX idx_orders_user_date REBUILD ONLINE;

-- 인덱스 상태 확인 (테스트/점검용, 운영 영향 확인 필요)
ANALYZE INDEX idx_orders_user_date VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows,
       ROUND(del_lf_rows / DECODE(lf_rows, 0, 1, lf_rows) * 100) AS frag_pct
FROM index_stats;

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

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

-- 인덱스 재생성 예시 (온라인 가능 여부는 버전/엔진/작업에 따라 다름)
ALTER TABLE orders DROP INDEX idx_orders_user_date,
                   ADD INDEX idx_orders_user_date(user_id, order_date),
                   ALGORITHM=INPLACE, LOCK=NONE;

-- 인덱스 통계 갱신
ANALYZE TABLE users;

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

-- 서비스 중단 없는 인덱스 생성 (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_orders_date
ON orders(order_date);

-- 부분 인덱스 (조건부 인덱스)
CREATE INDEX idx_orders_active
ON orders(user_id, order_date)
WHERE status = 'ACTIVE';

-- 인덱스 재구성 (일반 REINDEX는 잠금 영향이 있을 수 있음)
REINDEX INDEX idx_users_email;

-- PostgreSQL 12+: 잠금 영향을 줄이는 재구성
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 실행 계획 생성
EXPLAIN PLAN FOR
SELECT * FROM users WHERE email = 'test@example.com';

-- 실행 계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 실제 실행 통계 포함 (Oracle 10g+)
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM users WHERE email = 'test@example.com';

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

-- 기본 실행 계획
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 상세 실행 계획 (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- FORMAT=JSON으로 비용 정보 확인
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

-- 활성 주문만 인덱싱 (전체의 10%가 ACTIVE라면)
CREATE INDEX idx_orders_active_user
ON orders(user_id, order_date)
WHERE status = 'ACTIVE';

-- 효과: 조건 밖 행은 인덱스에 없어 크기와 갱신 비용 감소
-- 조건: 쿼리 조건이 부분 인덱스 조건을 만족함을 옵티마이저가 알아야 함

-- 1. 주문 조회: 특정 사용자의 최근 주문
--    WHERE user_id = ? ORDER BY order_date DESC
CREATE INDEX idx_orders_user_date
ON orders(user_id, order_date DESC);

-- 2. 상품 검색: 카테고리별 가격순
--    WHERE category_id = ? ORDER BY price
CREATE INDEX idx_products_cat_price
ON products(category_id, price);

-- 3. 주문 상세: 주문별 상품 목록
--    WHERE order_id = ?
CREATE INDEX idx_oitems_order ON order_items(order_id);

-- 4. 리뷰: 상품별 최신 리뷰
--    WHERE product_id = ? ORDER BY created_at DESC
CREATE INDEX idx_reviews_prod_date
ON reviews(product_id, created_at DESC);

-- 5. 사용자 로그인: 이메일로 조회
--    WHERE email = ?
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 6. 주문 상태 조회: 특정 상태의 주문 목록
--    WHERE status = ? AND order_date >= ?
CREATE INDEX idx_orders_status_date
ON orders(status, order_date);

-- 7. 커버링 인덱스: 주문 목록 API
--    SELECT order_id, order_date, total, status
--    WHERE user_id = ? ORDER BY order_date DESC LIMIT 20
--    InnoDB는 보조 인덱스에 PK가 자동 포함될 수 있지만,
--    PostgreSQL/SQL Server는 INCLUDE 절도 후보가 됨
CREATE INDEX idx_orders_cover
ON orders(user_id, order_date DESC, order_id, total, status);