인덱스란 무엇인가
인덱스는 데이터를 빨리 찾기 위해, 미리 정렬해서 따로 저장해둔 자료구조다.
도서관에 비유하면 이렇다.
- 테이블 = 책장 전체. 1만 권의 책이 꽂혀 있다.
- 인덱스 = 검색용 컴퓨터. 책 자체는 없고, "책 제목 → 위치"만 정렬해서 보관한다.
책을 찾으려면 두 가지 방법이 있다.
- 1층부터 5층까지 모든 책장을 한 권씩 다 뒤진다 → 풀 테이블 스캔
- 검색 컴퓨터로 위치를 먼저 찾고, 그곳으로 간다 → 인덱스 조회
당연히 2번이 빠르다. 인덱스는 "검색 컴퓨터를 만들어두는 작업"이라고 생각하면 된다.
인덱스는 언제 쓰이는가
| 트리거 | 무슨 일을 하는가 |
|---|---|
| 1. 필터링 | WHERE / JOIN ON 으로 읽을 행을 좁힌다 |
| 2. 정렬 | ORDER BY 를 인덱스의 정렬 순서로 대체해서, 정렬 연산 자체를 생략한다 |
| 3. 커버링 | SELECT 에 필요한 컬럼이 전부 인덱스 안에 있어, 테이블 접근을 생략한다 |
아래에서 각 트리거를 예제와 함께 하나씩 본다. 예제는 모두 같은 인덱스를 가정한다.
-- 이 섹션 전체에서 가정하는 인덱스
CREATE INDEX idx_orders ON orders(user_id, created_at);트리거 1 — 필터링 (WHERE / JOIN ON)
가장 익숙한 경우다. 조건에 맞는 행만 골라내기 위해 인덱스로 점프한다.
-- O 선두 컬럼 user_id로 바로 좁힌다
SELECT * FROM orders WHERE user_id = 100;
-- O JOIN ON도 결국 필터링이다 (조인 키로 인덱스를 탄다)
SELECT *
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 100;여기까지가 "WHERE가 있어야 인덱스를 쓴다"는 직관에 들어맞는 영역이다. 문제는 인덱스가 이것만 하는 게 아니라는 점이다.
트리거 2 — 정렬 (ORDER BY)
ORDER BY 컬럼이 인덱스의 정렬 순서와 일치하면, DB는 인덱스를 순서대로 읽기만 하면 된다.
즉 정렬 연산(filesort) 자체가 사라진다.
-- O WHERE 없이 정렬 용도로만 인덱스 사용
-- 인덱스가 (user_id, created_at)로 이미 정렬돼 있으니
-- 그 순서 그대로 읽으면 정렬 끝
SELECT * FROM orders ORDER BY user_id, created_at LIMIT 10;
-- O 역방향도 가능 (인덱스를 거꾸로 읽으면 됨)
SELECT * FROM orders ORDER BY user_id DESC, created_at DESC LIMIT 10;
-- X 인덱스 정렬 순서와 어긋나면 정렬 연산이 다시 필요
-- (created_at만으로는 선두 컬럼 user_id 순서가 보장되지 않음)
SELECT * FROM orders ORDER BY created_at LIMIT 10;트리거 3 — 커버링 (SELECT 컬럼)
SELECT 에 필요한 컬럼이 전부 인덱스 안에 들어 있으면, 테이블 본체를 읽으러 갈 필요가 없다.
이걸 커버링(Index Only Scan)이라 한다.
-- O 필요한 컬럼(user_id, created_at)이 인덱스에 다 있다 → 테이블 룩업 생략
SELECT user_id, created_at FROM orders ORDER BY user_id, created_at;
-- X amount는 인덱스에 없다 → 결국 테이블로 가야 함 (커버링 깨짐)
SELECT user_id, created_at, amount FROM orders WHERE user_id = 100;확인하는 법: InnoDB는 EXPLAIN의 Extra에 Using index가 뜨면 커버링이다. PostgreSQL은 Index Only Scan으로 표기된다.
InnoDB 보너스: 세컨더리 인덱스 리프에 PK가 자동으로 붙으므로,
(user_id, created_at)인덱스는 사실상(user_id, created_at, id)처럼 동작한다. 그래서SELECT id나SELECT user_id, created_at, id도 커버링이 된다.
그리고 — 트리거는 겹쳐서 발동한다
실전에서는 한 쿼리가 여러 트리거를 동시에 건다. 이게 인덱스 설계의 묘미다.
-- 필터링(WHERE user_id) + 정렬(ORDER BY created_at)을 한 인덱스로 동시에 처리
-- (user_id, created_at) 인덱스 하나로 둘 다 공짜
SELECT * FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 10;user_id = 100 으로 구간을 좁힌 뒤, 그 구간 안은 이미 created_at 순으로 정렬돼 있으니 정렬도 따라온다.
데이터를 읽는 4가지 방식
DB가 SELECT 쿼리를 처리할 때 데이터를 읽어오는 방식은 크게 4가지다. 빠른 순서로 정리하면 이렇다.
1. 풀 테이블 스캔 (Seq Scan)
테이블의 모든 행을 처음부터 끝까지 읽는 방식이다.
도서관 비유: 모든 책장의 모든 책을 한 권씩 다 꺼내본다. 1만 권을 다 보는 것.
인덱스가 없거나, 있어도 활용할 수 없을 때 이 방식이 쓰인다. 또는 옵티마이저가 "어차피 거의 모든 행을 가져와야 하니 그냥 다 읽는 게 빠르다"고 판단할 때도 발생한다.
용어 메모:
Seq Scan은 PostgreSQL의 실행계획 표기다. MySQL/InnoDB 계열에서는EXPLAIN의type이ALL로 나오는 것이 이에 해당한다.
2. 인덱스 풀 스캔 (Index Scan)
인덱스의 처음부터 끝까지 다 훑는 방식이다.
도서관 비유: 검색 컴퓨터에 키워드를 입력하지 않고, 전체 도서 목록을 처음부터 끝까지 스크롤해서 본다.
테이블 자체보다 인덱스가 작기 때문에, 풀 테이블 스캔보다는 빠르다. 하지만 정상적인 인덱스 활용에 비하면 여전히 비효율적이다.
3. 인덱스 액세스 + 테이블 룩업
가장 일반적인 인덱스 활용 방식이다. 두 단계로 진행된다.
- 인덱스에서 조건에 맞는 행의 위치를 찾는다
- 그 위치를 들고 실제 테이블로 가서 행 데이터를 읽어온다
도서관 비유: 검색 컴퓨터로 "해리포터 → 3층 F구역"을 알아낸 뒤, 실제로 3층까지 가서 책을 꺼낸다.
여기서 2단계, 즉 "테이블로 다시 가는 행위"를 테이블 룩업 또는 북마크 룩업이라고 부른다. 룩업이 많아지면 디스크 I/O 비용이 커져서 느려진다.
용어 메모: PostgreSQL은 테이블 본체를 힙(heap) 이라 부르고, 인덱스는 힙의 위치를 가리키는 포인터만 갖는다. 그래서 "힙 방문(heap fetch)"이라는 표현을 쓴다. 반면 InnoDB(MariaDB 기본 엔진)에는 힙 개념이 없다. InnoDB는 테이블 자체가 PK 기준으로 정렬된 클러스터드 인덱스라서, 세컨더리 인덱스 → 클러스터드 인덱스 순으로 타고 들어간다. 같은 "룩업"이라도 엔진별 내부 구조가 다르다.
4. 커버링 인덱스 (Index Only Scan)
쿼리에 필요한 모든 컬럼이 이미 인덱스 안에 들어있는 경우, 테이블 룩업 자체를 생략할 수 있다.
도서관 비유: 검색 컴퓨터에 "위치, 작가, 출판년도"가 다 표시돼 있어서 굳이 책장까지 안 가도 답이 나온다.
이런 인덱스를 커버링 인덱스라고 한다. 4가지 방식 중 가장 빠르다.
-- 인덱스: (user_id, name, email)
SELECT name, email FROM users WHERE user_id = 100;
-- → 인덱스에 name, email이 다 있으니 테이블 안 봐도 됨엔진 차이 주의: InnoDB는 세컨더리 인덱스의 리프에 PK가 자동으로 따라붙는다.
그래서 인덱스를(created_at)으로만 만들어도 사실상(created_at, id)처럼 동작해서SELECT id가 저절로 커버된다.
PostgreSQL은 그렇지 않다. 진짜 Index Only Scan을 원하면(created_at, id)또는created_at INCLUDE (id)처럼 명시해야 한다.
복합 인덱스
복합 인덱스는 여러 컬럼을 묶어서 만든 인덱스다.
예를 들어, 아래와 같은 복합 인덱스를 생성한다고 하면
CREATE INDEX idx_user_created ON orders(user_id, created_at);1순위로
user_id기준 정렬, 같은user_id안에서 2순위로created_at기준 정렬하여 인덱싱한다는 의미다.
비유하자면 전화번호부와 같다. 성으로 먼저 정렬되고, 같은 성을 가진 사람들끼리 다시 이름순으로 정렬된다.
전화번호부:
김민수
김지영
김철수
박서준
박혜수이 구조에서 어떤 검색이 가능할까?
- "김씨 찾기" → 가능. 정렬돼 있으니 한 번에 점프
- "김씨 중 가나다순 첫 사람" → 가능. 이미 정렬돼 있음
- "전체에서 '철수' 찾기" → 어렵다. 이름으로 정렬된 게 아니라서
이걸 SQL로 옮기면 이렇다.
-- 인덱스: (user_id, created_at)
-- O 인덱스 잘 활용 (필터링 용도)
SELECT * FROM orders WHERE user_id = 100;
-- O 정렬도 공짜 (같은 user_id 안에서 이미 시간순)
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- O WHERE가 아예 없어도 인덱스가 켜진다 (정렬 + 커버링 용도)
-- ORDER BY가 인덱스 정렬 순서와 일치 → 정렬 연산 생략
-- SELECT 컬럼이 인덱스로 전부 덮임 → 테이블 룩업 생략
SELECT user_id, created_at FROM orders ORDER BY user_id, created_at LIMIT 10;
-- X 인덱스 활용 못 함 (선두 컬럼 user_id가 조건/정렬에 없음)
SELECT * FROM orders WHERE created_at > '2025-01-01';세 번째 쿼리가 핵심이다. WHERE 없이도, ORDER BY와 SELECT 컬럼만으로 인덱스가 쓰인다.
Leftmost Prefix Rule
위 패턴에서 발견할 수 있는 규칙이 "왼쪽 컬럼부터 순서대로 써야 인덱스가 효율적으로 동작한다"는 원칙이다. 흔히 Leftmost Prefix Rule이라고 부른다.
이 규칙 때문에 복합 인덱스를 설계할 때는 첫 번째 컬럼이 항상 조건(또는 정렬)으로 들어온다는 전제로 만들어야 한다. 첫 번째 컬럼 없이 두 번째 컬럼만으로 조회하면, 앞서 설명한 인덱스 풀 스캔으로 떨어지거나 풀 테이블 스캔으로 떨어진다.
인덱스 설계 시 고려할 점
카디널리티
카디널리티(Cardinality)는 컬럼 값의 다양성을 의미한다.
gender컬럼: M / F 두 가지 → 카디널리티 낮음email컬럼: 사용자마다 거의 다름 → 카디널리티 높음
일반적으로 카디널리티가 높은 컬럼을 첫 번째에 두는 것이 유리하다. 검색 결과를 더 좁게 좁힐 수 있기 때문이다.
-- (board_id, user_id) vs (user_id, board_id)
-- 게시판이 10개, 유저가 10만 명일 때
-- → user_id를 앞에 두는 게 일반적으로 유리다만 절대적인 규칙은 아니다. 실제 쿼리 패턴에 따라 달라진다. "항상 board_id로만 조회한다면" board_id를 앞에 둬야 한다.
인덱스의 비용
인덱스는 공짜가 아니다.
- 저장 공간을 추가로 차지한다
- INSERT, UPDATE, DELETE 시 인덱스도 갱신해야 하므로 쓰기 성능이 느려진다
- 인덱스가 많을수록 옵티마이저가 어떤 인덱스를 쓸지 고민하는 시간이 늘어난다
그래서 인덱스는 실제로 자주 사용되는 쿼리 패턴을 분석한 뒤에 만들어야 한다. "혹시 모르니 일단 만들자"는 좋지 않은 접근이다.
