옵티마이저 — Cost-Based Optimizer가 쿼리를 분석하는 방식

사용자 쿼리 → 파서 → 옵티마이저 → 실행 엔진 → 결과
                        ↑
                  "어떤 인덱스를 쓸까?"
                  "어떤 순서로 조인할까?"
                  "서브쿼리를 풀어야 할까?"

총 비용 = I/O 비용 + CPU 비용

-- 비용 상수 확인
SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;

-- 테이블 통계 확인
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'orders';

-- 인덱스 통계 확인
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'orders';

-- SHOW INDEX로도 확인 가능
SHOW INDEX FROM orders;

카디널리티가 높은 컬럼: email (거의 모든 값이 다름) → 인덱스 효과 좋음
카디널리티가 낮은 컬럼: gender ('M', 'F' 두 값) → 인덱스 효과 낮음

-- 수동 갱신
ANALYZE TABLE orders;

-- 통계 수집 방식 설정
SHOW VARIABLES LIKE 'innodb_stats_persistent';       -- ON: 영구 통계 (기본)
SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';  -- 샘플 페이지 수 (기본 20)

-- status 컬럼에 인덱스가 없다고 가정
-- 'active': 95%, 'inactive': 4%, 'banned': 1%

SELECT * FROM users WHERE status = 'banned';
-- 옵티마이저는 33%로 추정 (3개 값이 균등하다고 가정)
-- 실제로는 1%만 해당

-- 히스토그램 생성
ANALYZE TABLE users UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 히스토그램 확인
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE table_name = 'users' AND column_name = 'status'\G

-- 히스토그램 삭제
ANALYZE TABLE users DROP HISTOGRAM ON status;

SELECT * FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id
WHERE A.status = 'active';

-- 탐색 깊이 설정 (기본 62, 0이면 자동)
SHOW VARIABLES LIKE 'optimizer_search_depth';

-- 인덱스 사용 강제
SELECT /*+ INDEX(orders idx_customer) */ *
FROM orders WHERE customer_id = 42;

-- 인덱스 사용 금지
SELECT /*+ NO_INDEX(orders idx_customer) */ *
FROM orders WHERE customer_id = 42;

-- 조인 순서 강제
SELECT /*+ JOIN_ORDER(customers, orders) */ *
FROM customers c JOIN orders o ON c.id = o.customer_id;

-- 조인 방식 강제
SELECT /*+ HASH_JOIN(orders) */ *
FROM customers c JOIN orders o ON c.id = o.customer_id;

-- 서브쿼리 전략 지정
SELECT /*+ SEMIJOIN(@subq MATERIALIZATION) */ *
FROM orders WHERE customer_id IN (
    SELECT /*+ QB_NAME(subq) */ id FROM customers WHERE city = '서울'
);

-- FORCE INDEX
SELECT * FROM orders FORCE INDEX (idx_customer)
WHERE customer_id = 42;

-- IGNORE INDEX
SELECT * FROM orders IGNORE INDEX (idx_customer)
WHERE customer_id = 42;

-- STRAIGHT_JOIN (FROM절 순서대로 조인)
SELECT STRAIGHT_JOIN * FROM A JOIN B ON ...;

-- optimizer_trace 활성화
SET optimizer_trace = "enabled=on";

-- 분석할 쿼리 실행
SELECT * FROM orders WHERE customer_id = 42 AND status = 'completed';

-- 트레이스 결과 확인
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 비활성화
SET optimizer_trace = "enabled=off";

{
  "rows_estimation": [
    {
      "table": "orders",
      "range_analysis": {
        "table_scan": { "rows": 1000000, "cost": 102345.6 },
        "potential_range_indexes": [
          { "index": "idx_customer", "usable": true },
          { "index": "idx_status", "usable": true }
        ],
        "chosen_range_access_summary": {
          "type": "range_scan",
          "index": "idx_customer",
          "rows": 100,
          "cost": 31.02
        }
      }
    }
  ]
}

-- 대량 데이터 변경 후 통계가 낡은 경우
ANALYZE TABLE orders;  -- 통계 갱신으로 해결

-- 99%가 status='active'인데 옵티마이저가 균등 분포로 가정
-- Histogram으로 해결
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;

-- city='서울'이고 district='강남'인 비율을 독립적으로 추정
-- 실제로는 강남은 서울에만 있으므로 과소 추정
WHERE city = '서울' AND district = '강남'