복합 인덱스 설계 — 최좌선 접두사와 카디널리티

CREATE INDEX idx_dept_age ON employees(department, age);

(department='Engineering', age=25)
(department='Engineering', age=28)
(department='Engineering', age=32)
(department='Marketing',  age=22)
(department='Marketing',  age=27)
(department='Sales',      age=24)
(department='Sales',      age=31)

-- 인덱스: (department, age, salary)

-- 인덱스 사용 O: 선두 컬럼부터 연속
SELECT * FROM employees WHERE department = 'Engineering' AND age = 28;

-- 인덱스 사용 X: 선두 컬럼 없음
SELECT * FROM employees WHERE age = 28 AND salary > 5000;

-- 부분 사용: department만 인덱스 사용, salary는 필터
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 5000;

인덱스 (department, age) 정렬:
Engineering, 25
Engineering, 28
Marketing,  22
Marketing,  27
Sales,      24

→ department 기준으로 정렬되어 있으므로, department 없이 age=22를 찾으려면
  전체를 스캔해야 합니다. age만으로는 정렬되어 있지 않기 때문입니다.

-- 인덱스: (a, b, c)

-- a, b 모두 인덱스 범위 스캔 사용
WHERE a = 1 AND b = 2 AND c = 3

-- a만 인덱스 범위 스캔, b는 필터
WHERE a > 1 AND b = 2

-- a, b 인덱스 범위 스캔, c는 필터
WHERE a = 1 AND b > 2 AND c = 3

-- 나쁜 순서: 범위 조건 컬럼이 앞에 있음
CREATE INDEX idx_bad ON orders(created_at, status, user_id);
-- WHERE created_at > '2026-01-01' AND status = 'paid' AND user_id = 1
-- created_at만 인덱스 범위 스캔, status와 user_id는 필터

-- 좋은 순서: 등가 조건 컬럼을 앞에 배치
CREATE INDEX idx_good ON orders(status, user_id, created_at);
-- WHERE status = 'paid' AND user_id = 1 AND created_at > '2026-01-01'
-- 세 컬럼 모두 인덱스 사용

-- 카디널리티 확인
SELECT
    COUNT(DISTINCT status) AS status_card,        -- 5 (낮음)
    COUNT(DISTINCT department) AS dept_card,       -- 50 (중간)
    COUNT(DISTINCT user_id) AS user_card           -- 100,000 (높음)
FROM orders;

-- 좋은 예: 카디널리티 높은 → 낮은
CREATE INDEX idx_user_status ON orders(user_id, status);
-- user_id로 100,000개 중 1개를 찾고, 그 안에서 status 필터

-- 나쁜 예: 카디널리티 낮은 → 높은
CREATE INDEX idx_status_user ON orders(status, user_id);
-- status로 5개 중 1개(전체의 20%)를 찾고, 그 안에서 user_id 필터

-- 이 쿼리가 가장 빈번하다면:
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;

-- 카디널리티와 무관하게 (status, created_at) 인덱스가 최적
-- status로 필터 + created_at 정렬을 인덱스가 모두 처리
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 인덱스: (gender, age)
-- 전통적으로는 gender 없이 age 검색 시 인덱스 사용 불가

SELECT * FROM users WHERE age = 28;

gender의 고유 값: 'M', 'F'

스킵 스캔은 내부적으로 다음과 같이 변환:
WHERE gender = 'M' AND age = 28
UNION
WHERE gender = 'F' AND age = 28

-- EXPLAIN에서 확인
EXPLAIN SELECT * FROM users WHERE age = 28;
-- Extra: Using index for skip scan

-- 인덱스: (department, created_at)

-- filesort 없음: 인덱스 정렬 그대로 사용
SELECT * FROM employees
WHERE department = 'Engineering'
ORDER BY created_at;

-- filesort 없음: DESC도 가능 (역방향 스캔)
SELECT * FROM employees
WHERE department = 'Engineering'
ORDER BY created_at DESC;

-- filesort 발생: 정렬 방향 혼합
SELECT * FROM employees
WHERE department = 'Engineering'
ORDER BY created_at ASC, name DESC;
-- MySQL 8.0에서는 DESC 인덱스로 해결 가능:
-- CREATE INDEX idx ON employees(department, created_at ASC, name DESC);

-- 인덱스: (department, age)

-- 인덱스 사용: GROUP BY가 인덱스 순서와 일치
SELECT department, AVG(age) FROM employees GROUP BY department;

-- 인덱스 사용 불가: GROUP BY 순서가 다름
SELECT age, COUNT(*) FROM employees GROUP BY age;
-- age가 인덱스 선두가 아니므로 임시 테이블 필요

-- 슬로우 쿼리 로그 분석
-- 또는 performance_schema에서 확인
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 20;

-- 쿼리 패턴:
-- WHERE status = ? AND user_id = ? AND created_at > ? ORDER BY created_at

-- 최적 인덱스:
CREATE INDEX idx_optimal ON orders(status, user_id, created_at);
-- 등가(status) → 등가(user_id) → 범위+정렬(created_at)

-- 이 두 인덱스 중 하나는 불필요
CREATE INDEX idx_a ON orders(status);
CREATE INDEX idx_ab ON orders(status, user_id);
-- idx_ab가 idx_a의 역할도 수행하므로 idx_a 삭제 가능

-- 인덱스 사용 현황 확인
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;