인덱스가 안 타는 경우 — 옵티마이저가 인덱스를 무시하는 이유

-- 인덱스: created_at

-- 인덱스 사용 X: 컬럼에 함수 적용
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-19';
SELECT * FROM users WHERE UPPER(name) = 'KIM';
SELECT * FROM orders WHERE amount + 100 > 1000;

-- 인덱스 사용 O: 범위 조건으로 변환
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

SELECT * FROM orders
WHERE created_at >= '2026-03-19' AND created_at < '2026-03-20';

-- 인덱스 사용 O: 값 쪽에 연산 적용
SELECT * FROM orders WHERE amount > 1000 - 100;

-- 함수 결과를 인덱스로 생성
CREATE INDEX idx_year ON orders ((YEAR(created_at)));
CREATE INDEX idx_lower_email ON users ((LOWER(email)));

-- 이제 함수를 써도 인덱스 사용 가능
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = 'test@test.com';

-- phone은 VARCHAR(20) 타입, 인덱스 있음

-- 인덱스 사용 X: 문자열 컬럼에 숫자 비교 → 모든 행을 숫자로 변환
SELECT * FROM users WHERE phone = 01012345678;

-- 인덱스 사용 O: 올바른 타입으로 비교
SELECT * FROM users WHERE phone = '01012345678';

-- 자주 발생하는 형변환 실수들

-- 인덱스 사용 X: INT 컬럼에 문자열 비교 (이 경우는 보통 인덱스 사용 가능)
-- 하지만 혼란을 피하기 위해 올바른 타입을 사용하는 것이 좋습니다
SELECT * FROM orders WHERE id = '42';  -- 대부분 OK, 하지만 습관적으로 피해야 함

-- 인덱스 사용 X: 문자셋 불일치
-- utf8_general_ci 컬럼과 utf8mb4_general_ci 값 비교 시 변환 발생 가능

-- 인덱스: name

-- 인덱스 사용 X: 앞에 % (어디서 시작하는지 알 수 없음)
SELECT * FROM users WHERE name LIKE '%철수';
SELECT * FROM users WHERE name LIKE '%철%';

-- 인덱스 사용 O: 앞부분 고정 (접두사 매칭)
SELECT * FROM users WHERE name LIKE '김%';
SELECT * FROM users WHERE name LIKE '김철%';

-- 인덱스: idx_name(name), idx_email(email)

-- 인덱스 사용 가능: 양쪽 모두 인덱스 있음 (Index Merge 가능)
SELECT * FROM users WHERE name = '김철수' OR email = 'kim@test.com';
-- EXPLAIN: type=index_merge, Extra: Using union(idx_name, idx_email)

-- 인덱스 사용 X: 한쪽에 인덱스 없음
SELECT * FROM users WHERE name = '김철수' OR phone = '01012345678';
-- phone에 인덱스가 없으면 전체 풀 스캔

-- 방법 1: UNION으로 분리 (각각 인덱스 활용)
SELECT * FROM users WHERE name = '김철수'
UNION
SELECT * FROM users WHERE phone = '01012345678';

-- 방법 2: 누락된 인덱스 추가
CREATE INDEX idx_phone ON users(phone);

-- 인덱스: status

-- 인덱스 사용 제한: 부정 조건
SELECT * FROM orders WHERE status != 'cancelled';
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

-- 대부분의 행이 결과에 포함되면 옵티마이저가 풀 스캔 선택
-- 소수의 행만 결과에 포함되면 인덱스 사용할 수도 있음

-- 인덱스: deleted_at

-- InnoDB에서는 NULL도 인덱스에 포함됩니다
-- 인덱스 사용 가능: IS NULL
SELECT * FROM users WHERE deleted_at IS NULL;

-- 인덱스 사용 여부는 NULL 비율에 따라 다름
-- NULL이 소수면 인덱스 사용, 대부분이 NULL이면 풀 스캔
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- gender 컬럼: 'M', 'F' 두 가지 값만 존재
CREATE INDEX idx_gender ON users(gender);

-- 옵티마이저: 인덱스로 50%를 읽는 것보다 풀 스캔이 더 빠르다고 판단
SELECT * FROM users WHERE gender = 'M';
-- type: ALL (풀 스캔)

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

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

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

-- 행이 수십 개밖에 없는 테이블
-- 인덱스 탐색 비용 > 풀 스캔 비용
-- 옵티마이저가 풀 스캔을 선택하는 것이 정상

-- 테이블 통계 갱신
ANALYZE TABLE users;

-- 인덱스 통계 확인
SHOW INDEX FROM users;
-- Cardinality 컬럼 확인

EXPLAIN SELECT * FROM users WHERE name = '김철수';

-- possible_keys vs key
EXPLAIN SELECT * FROM users WHERE age > 20;
-- possible_keys: idx_age (사용 가능한 인덱스)
-- key: NULL (실제로 선택된 인덱스 — NULL이면 미사용)

-- 인덱스 사용 강제 (디버깅/비교용)
SELECT * FROM users FORCE INDEX (idx_age) WHERE age > 20;

-- 인덱스 무시 (다른 인덱스 사용 유도)
SELECT * FROM users IGNORE INDEX (idx_age) WHERE age > 20;

-- MySQL 8.0 옵티마이저 힌트
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 20;

문제: 함수 사용 → 범위 조건으로 변환 또는 함수 기반 인덱스
문제: 형변환 → 올바른 타입 사용
문제: OR 조건 → UNION 또는 인덱스 추가
문제: 카디널리티 낮음 → 복합 인덱스로 조합
문제: 통계 오래됨 → ANALYZE TABLE