커버링 인덱스와 인덱스 컨디션 푸시다운

1. 세컨더리 인덱스 탐색 → PK 값 획득
2. PK로 클러스터형 인덱스 탐색 → 행 데이터 반환 (테이블 룩업)

-- 이 쿼리는 인덱스를 타지만 테이블 룩업이 발생
CREATE INDEX idx_age ON users(age);
SELECT name, email FROM users WHERE age BETWEEN 20 AND 30;
-- 1. idx_age에서 age 조건으로 PK 목록 획득
-- 2. 각 PK로 클러스터형 인덱스 접근 → name, email 읽기
-- 결과 행이 많으면 수천 번의 랜덤 I/O 발생

-- 커버링 인덱스 생성
CREATE INDEX idx_age_name_email ON users(age, name, email);

-- 이 쿼리는 인덱스만으로 처리 가능 (테이블 룩업 없음)
SELECT name, email FROM users WHERE age BETWEEN 20 AND 30;

EXPLAIN SELECT name, email FROM users WHERE age BETWEEN 20 AND 30;

-- 인덱스: (department, salary)

-- 커버링 O: 모든 컬럼이 인덱스에 있음
SELECT department, salary FROM employees WHERE department = 'Engineering';

-- 커버링 X: name이 인덱스에 없음
SELECT department, salary, name FROM employees WHERE department = 'Engineering';

-- 커버링 O: COUNT(*)는 인덱스만으로 계산 가능
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- PK는 항상 세컨더리 인덱스에 포함 (InnoDB 특성)
-- 인덱스: (department) → 실제로 (department, id)
SELECT id FROM employees WHERE department = 'Engineering';  -- 커버링 O

-- 패턴 1: 자주 조회하는 컬럼을 인덱스에 포함
-- 목록 화면에서 name, email만 표시하는 경우
CREATE INDEX idx_covering ON users(status, name, email);
SELECT name, email FROM users WHERE status = 'active';

-- 패턴 2: COUNT 최적화
-- 조건별 건수를 자주 세는 경우
CREATE INDEX idx_status ON orders(status);
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- 패턴 3: EXISTS 서브쿼리 최적화
-- user_id만 확인하면 되는 경우
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- orders에서 user_id만 읽으면 되므로 커버링 인덱스

-- 나쁜 예: 모든 컬럼을 인덱스에 포함 (의미 없음)
CREATE INDEX idx_all ON users(name, email, phone, address, bio, created_at);

-- 좋은 예: 빈번한 쿼리의 핵심 컬럼만 포함
CREATE INDEX idx_list ON users(status, name, email);

-- 인덱스: (department, age)
SELECT * FROM employees WHERE department LIKE 'Eng%' AND age > 30;

1. 스토리지 엔진: idx에서 department LIKE 'Eng%'인 행의 PK를 모두 반환
2. MySQL 서버: 각 행을 테이블 룩업으로 읽어서 age > 30 필터링

1. 스토리지 엔진: idx에서 department LIKE 'Eng%' AND age > 30 모두 평가
   → age <= 30인 행은 테이블 룩업 없이 바로 스킵
2. MySQL 서버: 조건을 통과한 행만 처리

EXPLAIN SELECT * FROM employees
WHERE department LIKE 'Eng%' AND age > 30;

-- ICP 활성화/비활성화 (기본: ON)
SET optimizer_switch='index_condition_pushdown=on';

-- ICP 비활성화로 성능 차이 비교
SET optimizer_switch='index_condition_pushdown=off';

세컨더리 인덱스 → PK: 42, 7, 193, 15, 88
클러스터형 인덱스 접근: 42 → 7 → 193 → 15 → 88 (랜덤 I/O)

세컨더리 인덱스 → PK: 42, 7, 193, 15, 88
PK 정렬: 7, 15, 42, 88, 193
클러스터형 인덱스 접근: 7 → 15 → 42 → 88 → 193 (순차 I/O에 가까움)

-- MRR 관련 설정
SET optimizer_switch='mrr=on,mrr_cost_based=on';

-- MRR 버퍼 크기 (정렬에 사용)
SET read_rnd_buffer_size = 262144;  -- 256KB

EXPLAIN SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

-- 인덱스: (department, age)
SELECT * FROM employees
WHERE department IN ('Engineering', 'Marketing') AND age > 25;

-- 실행 흐름:
-- 1. ICP: 인덱스에서 department + age 조건 모두 평가
-- 2. MRR: 통과한 PK를 정렬
-- 3. PK 순서로 클러스터형 인덱스 접근 (순차 I/O)

-- BKA 활성화 (기본: OFF)
SET optimizer_switch='batched_key_access=on,mrr_cost_based=off';

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.age > 25;

-- 실행 흐름:
-- 1. employees에서 age > 25인 행의 department_id를 모음
-- 2. department_id를 정렬
-- 3. departments 테이블을 순차적으로 접근

-- 원본 쿼리 (느림)
SELECT u.name, u.email, o.amount, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 1단계: EXPLAIN 확인
EXPLAIN FORMAT=TREE ...;

-- 2단계: orders에 복합 인덱스 추가
CREATE INDEX idx_status_created ON orders(status, created_at, user_id, amount);
-- status(등가) → created_at(범위+정렬) → user_id, amount(커버링)

-- 3단계: 결과 확인
-- Extra: Using index (커버링), Backward index scan (DESC)
-- orders에서 테이블 룩업 없이 처리