인덱스 쿼리 패턴

인덱스 파워링크

바로가기	볼 내용
개요	인덱스 전체 지도, 상황별 바로가기
구조와 종류	B+Tree, 클러스터/보조 인덱스, 물리 동작
B-Tree VS B+Tree	B-Tree와 B+Tree 구조 차이, DB가 B+Tree를 쓰는 이유
설계와 사용법	커버링 인덱스, 카디널리티, CREATE/ALTER/DROP
쿼리 패턴	연산자별 동작, 복합 인덱스, ESR 규칙
운영과 튜닝	고급 기법, 힌트, 통계, 모니터링, 베스트 프랙티스

연산자별 인덱스 동작

각 연산자가 인덱스를 어떻게 활용하는지 정리한다. EXPLAIN의 type 컬럼과 연결해서 이해하면 좋다.

`=` (등호)

가장 효율적인 인덱스 탐색. B-Tree를 루트부터 내려가 정확히 하나의 리프 노드에 도달한다.

-- UNIQUE 또는 PK → type: const (상수로 취급, 가장 빠름)
SELECT * FROM member WHERE id = 1;

-- 논유니크 인덱스 → type: ref (여러 건 매칭 가능)
SELECT * FROM orders WHERE member_id = 42;

복합 인덱스에서 Equality 조건은 인덱스 탐색 범위를 가장 좁힌다. ESR 원칙에서 맨 앞에 배치하는 이유다.

`IN`

각 값에 대한 등호 조건의 집합으로 처리된다. 복합 인덱스에서 Equality처럼 동작하므로, IN 이후 컬럼도 인덱스를 계속 사용할 수 있다.

-- type: range (각 IN 값마다 포인트 탐색)
SELECT * FROM orders
WHERE status IN ('ACTIVE', 'PENDING')
  AND created_at > '2024-01-01';  -- ✅ status 다음 컬럼도 인덱스 사용

주의: IN 값의 수가 매우 많아지면 (수백 개 이상) 옵티마이저가 Full Scan을 선택할 수 있다. 이 경우 임시 테이블과 JOIN으로 재작성하는 것이 효율적이다.

`<`, `>`, `<=`, `>=`, `BETWEEN` (범위 조건)

인덱스로 시작점을 찾은 뒤 조건을 만족하는 동안 리프 노드를 순차 스캔한다. EXPLAIN type: range.

BETWEEN a AND b는 >= a AND <= b와 동일하게 처리된다.

-- type: range
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- BETWEEN — 위와 동일한 range 스캔
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

복합 인덱스에서 범위 조건 이후 컬럼은 인덱스 탐색이 중단된다.

-- INDEX(member_id, created_at, amount)
WHERE member_id = 1             -- ✅ Equality: 탐색 범위 좁힘
  AND created_at > '2024-01-01' -- ✅ Range: range 스캔
  AND amount > 10000            -- ❌ range 이후 → 인덱스 미사용, 행 단위 필터링

`LIKE`

LIKE는 패턴의 앞부분이 고정되어 있어야 인덱스를 사용할 수 있다. B-Tree는 왼쪽부터 정렬되어 있으므로 앞쪽이 고정된 패턴만 탐색 범위를 좁힐 수 있다.

-- ✅ 뒤쪽만 와일드카드 → 인덱스 사용 (type: range)
WHERE name LIKE '김%'
-- '김'으로 시작하는 범위를 B-Tree에서 range 스캔

-- ❌ 앞쪽 와일드카드 → 인덱스 무력화 (type: ALL)
WHERE name LIKE '%철수'
WHERE name LIKE '%철%'
-- B-Tree 정렬 순서를 활용할 수 없어 Full Scan

팁: 앞쪽 와일드카드 검색이 필요하다면 Full-Text Index 또는 Elasticsearch를 고려한다. 또는 역방향 인덱스를 만들어 LIKE '%값'을 LIKE '값%'로 변환하는 방법도 있다.

-- 역방향 저장 컬럼 추가 후 뒤쪽 검색을 앞쪽 검색으로 변환
ALTER TABLE member ADD COLUMN name_rev VARCHAR(100) AS (REVERSE(name)) STORED;
CREATE INDEX idx_member_name_rev ON member (name_rev);
SELECT * FROM member WHERE name_rev LIKE CONCAT(REVERSE('철수'), '%');

`!=`, `NOT IN`, `NOT LIKE` (부정 조건)

부정 조건은 "해당하지 않는 값 전체"를 조회하므로 대부분 Full Scan이 발생한다. 옵티마이저가 인덱스를 선택하더라도 대부분의 행을 읽어야 해서 효율이 낮다.

-- ❌ 대부분 Full Scan
WHERE status != 'DELETED'
WHERE status NOT IN ('DELETED', 'EXPIRED')
WHERE name NOT LIKE '김%'

-- ✅ 긍정 조건으로 전환
WHERE status IN ('ACTIVE', 'PENDING', 'COMPLETED')

`IS NULL` / `IS NOT NULL`

-- ✅ IS NULL — 인덱스 사용 가능 (EXPLAIN type: ref_or_null)
SELECT * FROM member WHERE email IS NULL;

-- ⚠️ IS NOT NULL — 대부분 행을 읽어야 하므로 옵티마이저가 Full Scan 선택 가능
SELECT * FROM member WHERE email IS NOT NULL;

IS NOT NULL은 데이터 대부분이 NOT NULL인 경우 선택도가 매우 낮아 인덱스보다 Full Scan이 빠를 수 있다. NULL인 행이 많다면 IS NOT NULL도 인덱스를 활용한다.

연산자별 인덱스 동작 요약

연산자	EXPLAIN type	인덱스 활용	비고
`= 값`	const / ref	✅ 완전 활용	PK/UNIQUE는 const, 논유니크는 ref
`IN (값1, 값2, ...)`	range / ref	✅ Equality처럼 동작	값이 수백 개 이상이면 Full Scan 가능
`<`, `>`, `<=`, `>=`	range	✅ 범위 스캔	복합 인덱스에서 이후 컬럼 탐색 중단
`BETWEEN a AND b`	range	✅ 범위 스캔	`>= a AND <= b`와 동일
`LIKE '값%'`	range	✅ 앞부분 고정 시 사용	prefix 부분까지만 인덱스 탐색
`LIKE '%값'`	ALL	❌ 무력화	앞쪽 와일드카드는 B-Tree 탐색 불가
`!=`, `NOT IN`	ALL	❌ 보통 Full Scan	긍정 조건으로 재작성 권장
`IS NULL`	ref_or_null	✅ 사용 가능
`IS NOT NULL`	ALL 가능	⚠️ 선택도 낮으면 Full Scan	NULL 비율에 따라 달라짐
`FUNC(컬럼)`	ALL	❌ 무력화	범위 조건 재작성 또는 함수 기반 인덱스

복합 인덱스 설계

선두 컬럼 규칙

-- idx_order_member_date: (member_id, created_at) 순서로 생성
-- ✅ 사용됨
WHERE member_id = 1
WHERE member_id = 1 AND created_at > '2024-01-01'

-- ❌ 사용 안 됨 (선두 컬럼 없음)
WHERE created_at > '2024-01-01'

복합 인덱스는 왼쪽부터 순서대로 사용한다. 선두 컬럼(첫 번째 컬럼)이 WHERE에 없으면 인덱스를 타지 않는다.

ESR 규칙 (Equality → Sort → Range)

ESR 규칙: 복합 인덱스의 컬럼 순서를 정하는 실전 규칙. Equality 조건 컬럼 → Sort(ORDER BY) 컬럼 → Range 조건 컬럼 순서로 배치한다.

Equality(등호 =): 인덱스 노드를 정확히 좁힌다
Sort(ORDER BY): 정렬된 순서로 읽으면 별도 정렬이 필요 없다
Range(범위 >, <, BETWEEN): ==범위 조건 이후 컬럼은 인덱스 탐색이 중단==된다

주의: 인덱스는 정렬을 도와줄 뿐, 결과 순서를 보장하지 않는다

인덱스는 내부적으로 정렬된 자료구조지만, SQL 조회 결과의 행 순서는 ORDER BY가 있을 때만 보장된다. 옵티마이저가 어떤 인덱스를 탈지, 어떤 실행 계획을 선택할지는 상황에 따라 달라질 수 있으므로 ORDER BY 없는 결과 순서에 의존하면 안 된다.

-- 쿼리: 특정 회원의 주문을 최근 3개월 이내, 날짜 내림차순
SELECT * FROM orders
WHERE member_id = 1              -- Equality
  AND created_at > '2024-01-01'  -- Range
ORDER BY created_at DESC;        -- Sort

-- 최적 인덱스: (member_id, created_at)
-- member_id(E) → created_at이 Sort와 Range를 동시에 처리

정렬이 필요한 쿼리는 반드시 ORDER BY를 명시한다. 인덱스는 그 ORDER BY를 더 빠르게 처리하도록 도와주는 역할이다.

-- ❌ 정렬 보장 안 됨
SELECT *
FROM orders
WHERE member_id = 1;

-- ✅ 정렬 보장
SELECT *
FROM orders
WHERE member_id = 1
ORDER BY created_at DESC;

동일한 정렬 값이 여러 개라면 결과 순서가 흔들릴 수 있다. 안정적인 페이징이나 재현 가능한 결과가 필요하면 PK 같은 보조 정렬 기준을 함께 둔다.

SELECT *
FROM orders
WHERE member_id = 1
ORDER BY created_at DESC, id DESC;

Range 이후 인덱스 중단

B-Tree에서 범위 조건이 적용되면, 그 이후 컬럼은 정렬 순서가 보장되지 않아 인덱스 탐색이 불가능하다.

Bad — Range가 앞에 위치

-- INDEX(age, name, created_at)
SELECT * FROM member
WHERE age > 20 AND name = '김철수';

age에서 Range가 걸려 name은 인덱스를 사용하지 못한다.

Good — Equality가 앞에 위치

-- INDEX(name, age, created_at)
SELECT * FROM member
WHERE name = '김철수' AND age > 20;

name(Equality)이 선두이므로 정확히 좁힌 후 age(Range)를 처리한다.

주의: IN 연산자는 MySQL 옵티마이저가 등호 조건 목록으로 처리하므로 ==Range가 아닌 Equality처럼 동작==한다. BETWEEN, >, <와 다르다.

-- IN은 Equality처럼 동작 → 이후 컬럼도 인덱스 사용 가능
WHERE status IN ('ACTIVE', 'PENDING') AND created_at > '2024-01-01'

이전: 인덱스 설계와 사용법 · 다음: 인덱스 운영과 튜닝