인덱스 실전

-- 단일 컬럼 인덱스
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;

-- 인덱스 재구성 (MySQL 5.6+ ONLINE DDL)
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 INDEX 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';

인덱스 비효율
  SELECT * FROM users
  WHERE department_id = 10 OR location_id = 20;
  → 두 조건이 서로 다른 인덱스 → Full Scan 가능

해결: UNION ALL로 분리
  SELECT * FROM users WHERE department_id = 10
  UNION ALL
  SELECT * FROM users WHERE location_id = 20
    AND department_id <> 10;  -- 중복 방지
  → 각 쿼리가 각 인덱스를 사용 ✓

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

-- 효과: 인덱스 크기 90% 감소, 더 빠른 검색
-- 조건: 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
CREATE INDEX idx_orders_cover
ON orders(user_id, order_date DESC, total, status);