데이터베이스 인덱스는 왜 조회를 빠르게 만들고, 언제 오히려 느려질까?
빠른 답
- 인덱스는 컬럼 값을 정렬해 둔 탐색 구조라서 테이블 전체를 읽지 않고 검색 범위를 줄일 수 있다.
- MySQL InnoDB의 세컨더리 인덱스 리프 노드에는 실제 레코드 주소가 아니라 PK 값이 저장된다.
- 조건에 맞는 데이터가 너무 많으면 인덱스 탐색과 PK 재조회가 반복되어 풀 테이블 스캔보다 느릴 수 있다.
- 인덱스 설계는 쿼리 모양과
EXPLAIN의type,key,rows,Extra를 함께 보며 검증해야 한다.
목차
시간 흐름으로 이해하기
하나의 조회 쿼리는 보통 조건을 해석하는 단계에서 시작한다. 옵티마이저는 사용할 수 있는 인덱스 후보를 보고, 인덱스를 쓰는 편이 나은지 테이블을 직접 읽는 편이 나은지 비용을 추정한다. 인덱스를 선택했다면 B+Tree의 루트 노드에서 시작해 리프 노드까지 내려가고, 리프 노드에서 필요한 범위를 읽는다.
InnoDB의 세컨더리 인덱스를 사용한 조회라면 여기서 끝나지 않을 수 있다. 리프 노드에 저장된 PK 값을 이용해 클러스터형 인덱스를 다시 조회해야 할 수 있기 때문이다. 이 재조회가 많아지면 인덱스를 사용했는데도 기대보다 느린 쿼리가 된다.
흐름으로 보기
인덱스는 “자주 검색하는 컬럼” 하나만 보고 만들기보다 쿼리가 데이터를 줄이는 순서를 보고 설계하는 편이 낫다. 동등 조건으로 먼저 범위를 좁히는지, 그다음 정렬이나 범위 조건이 이어지는지, 조회 컬럼 때문에 실제 레코드를 다시 읽어야 하는지를 함께 봐야 한다.
후보 인덱스를 만든 뒤에는 EXPLAIN으로 실제 실행 계획을 확인한다. 기대한 인덱스가 key에 잡혔는지, rows가 충분히 줄었는지, Extra에 별도 정렬이나 임시 테이블 사용이 보이는지 확인하면 인덱스가 실제로 도움이 되는지 판단하기 쉬워진다.
인덱스가 필요한 이유
인덱스가 없는 테이블에서 특정 사용자의 주문을 찾는다고 생각해보자. 데이터베이스는 조건을 만족하는 행을 찾기 위해 테이블을 처음부터 끝까지 읽어야 할 수 있다. 이를 풀 테이블 스캔이라고 부른다. 데이터가 적을 때는 문제가 작지만, 수십만 건이나 수백만 건으로 늘어나면 매 요청마다 전체를 훑는 비용이 커진다.
인덱스는 이 비용을 줄이기 위해 컬럼 값을 정렬된 구조로 따로 관리한다. 책의 색인에서 단어를 찾은 뒤 해당 페이지로 이동하는 방식과 비슷하지만, 데이터베이스 인덱스는 단순한 목록이 아니라 탐색과 범위 조회에 유리한 트리 구조로 관리된다.
예를 들어 다음처럼 주문 테이블이 있고, 특정 회원의 최근 주문을 자주 조회한다고 해보자.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
ordered_at DATETIME NOT NULL,
total_amount DECIMAL(12, 2) NOT NULL
);
CREATE INDEX idx_orders_user_ordered_at
ON orders (user_id, ordered_at);
이 인덱스는 user_id로 먼저 주문 범위를 좁힌 뒤, 같은 사용자 안에서 ordered_at 순서로 데이터를 읽는 데 도움을 준다. 단일 컬럼 인덱스보다 복합 인덱스가 더 잘 맞는 경우가 많은 이유도 여기에 있다.
다만 인덱스는 조회만 돕는 구조가 아니다. INSERT, UPDATE, DELETE가 발생하면 테이블 데이터뿐 아니라 관련 인덱스도 함께 갱신해야 한다. 그래서 인덱스를 많이 만들수록 모든 성능이 좋아지는 것이 아니라, 읽기 패턴과 쓰기 비용 사이에서 균형을 잡아야 한다.
B+Tree 인덱스의 조회 흐름
MySQL InnoDB에서 일반적으로 사용하는 인덱스는 B+Tree 계열 구조다. B+Tree는 루트 노드에서 시작해 중간 노드를 거쳐 리프 노드까지 내려가며 값을 찾는다. 트리의 높이가 낮게 유지되므로 데이터가 많아져도 비교 횟수가 급격히 늘어나지 않는다.
동등 조건은 특정 위치를 빠르게 찾는 데 유리하다.
SELECT id, status, ordered_at, total_amount
FROM orders
WHERE user_id = 1001
ORDER BY ordered_at DESC
LIMIT 20;
idx_orders_user_ordered_at 인덱스가 있다면 데이터베이스는 user_id = 1001인 구간을 찾고, 그 범위 안에서 ordered_at 순서를 이용해 최근 주문을 읽을 수 있다. LIMIT 20이 함께 있으면 필요한 만큼만 읽고 멈출 가능성도 커진다.
반면 다음 쿼리는 같은 인덱스를 온전히 활용하기 어렵다.
SELECT id, user_id, status
FROM orders
WHERE ordered_at >= '2026-04-01 00:00:00';
인덱스가 (user_id, ordered_at) 순서로 정렬되어 있기 때문이다. 첫 번째 컬럼인 user_id 조건 없이 ordered_at만으로 필요한 구간을 바로 찾기 어렵다. 복합 인덱스에서 컬럼 순서가 중요하다는 말은 이런 실행 구조에서 나온다.
InnoDB 세컨더리 인덱스와 PK 재조회
InnoDB에서는 PK 기준으로 실제 데이터가 정렬되어 저장된다. 이를 클러스터형 인덱스라고 부른다. PK가 아닌 인덱스, 즉 세컨더리 인덱스의 리프 노드에는 실제 레코드 주소가 아니라 해당 레코드의 PK 값이 저장된다.
예를 들어 status 인덱스가 있다고 하자.
CREATE INDEX idx_orders_status
ON orders (status);
SELECT id, ordered_at, total_amount
FROM orders
WHERE status = 'PAID';
이 쿼리는 status 인덱스에서 PAID에 해당하는 PK들을 찾은 뒤, ordered_at과 total_amount를 얻기 위해 실제 레코드를 다시 읽어야 할 수 있다. PAID 주문이 전체 주문의 작은 비율이라면 이 방식이 효과적일 수 있다. 하지만 전체의 70~90%가 PAID라면 많은 PK를 찾고 많은 레코드를 다시 읽게 된다.
조회에 필요한 컬럼이 모두 인덱스 안에 있으면 실제 레코드를 다시 읽지 않아도 된다. 이를 커버링 인덱스라고 부른다. 예를 들어 (status, ordered_at, total_amount) 인덱스가 있고 조회 컬럼도 그 안에서 해결된다면 PK 재조회 비용을 줄일 수 있다. 다만 커버링 인덱스를 위해 컬럼을 계속 추가하면 저장 공간과 쓰기 비용이 늘어난다.
실행 계획으로 확인하기
인덱스 튜닝에서 추측만으로 판단하기는 어렵다. 같은 쿼리라도 데이터 분포, 통계, 테이블 크기, 조건 선택도에 따라 옵티마이저의 선택이 달라질 수 있다. 그래서 EXPLAIN으로 실행 계획을 확인하는 과정이 필요하다.
아래는 특정 사용자의 최근 주문을 조회하는 쿼리의 실행 계획 예시다.
EXPLAIN
SELECT id, status, ordered_at, total_amount
FROM orders
WHERE user_id = 1001
ORDER BY ordered_at DESC
LIMIT 20;
예상 출력은 다음과 비슷할 수 있다.
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: idx_orders_user_ordered_at
key: idx_orders_user_ordered_at
key_len: 8
ref: const
rows: 42
Extra: Using where; Backward index scan
여기서 key는 실제 선택된 인덱스다. possible_keys에 후보가 있더라도 key가 NULL이면 해당 쿼리에서는 인덱스를 사용하지 않은 것이다. type은 접근 방식을 보여주며, ref는 인덱스 값으로 행을 찾는 접근에 가깝다. rows는 읽을 것으로 예상되는 행 수다. 정확한 결과 건수라기보다 옵티마이저의 추정치로 보는 편이 좋다.
위 결과에서는 idx_orders_user_ordered_at 인덱스를 사용했고 예상 읽기 행 수가 42개다. 특정 사용자의 주문 범위만 읽고 LIMIT 20으로 멈출 수 있는 형태라면 비교적 좋은 계획으로 볼 수 있다.
반대로 다음과 같은 결과는 확인할 부분이 많다.
id: 1
select_type: SIMPLE
table: orders
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1850000
Extra: Using where; Using filesort
type: ALL은 테이블 전체를 읽는 접근을 의미한다. key: NULL은 선택된 인덱스가 없다는 뜻이고, rows: 1850000은 약 185만 행을 읽을 수 있다고 추정한 값이다. Using filesort는 인덱스 순서만으로 정렬을 해결하지 못해 별도 정렬 작업이 필요하다는 신호다.
인덱스 스캔 방식과 해석 포인트
인덱스 레인지 스캔은 특정 시작 지점을 찾은 뒤 조건에 맞는 범위만 읽는 방식이다. 기간 조회나 특정 사용자 조회처럼 범위가 잘 좁혀지는 쿼리에서 효과가 크다. 예를 들어 created_at 인덱스가 있다면 특정 기간의 게시글을 찾는 쿼리는 시작 날짜 위치를 찾고 필요한 날짜 범위만 읽을 수 있다.
인덱스 풀 스캔은 인덱스를 사용하지만 인덱스 전체를 읽는 방식이다. 테이블 전체보다 인덱스가 작거나, 조회 컬럼이 인덱스 안에서 모두 해결되는 경우에는 풀 테이블 스캔보다 나을 수 있다. 하지만 인덱스를 사용했다는 사실만으로 빠른 실행이라고 보기는 어렵다. 결국 읽은 양이 많으면 비용도 커진다.
루스 인덱스 스캔은 필요한 인덱스 키만 건너뛰며 읽는 최적화 방식이다. GROUP BY, MIN(), MAX() 같은 쿼리에서 인덱스 구성이 맞으면 모든 값을 촘촘히 읽지 않고 그룹별 일부 키만 읽을 가능성이 있다.
이런 스캔 방식은 실행 계획에서 항상 같은 이름으로 선명하게 드러나지는 않는다. 그래서 type, key, rows, Extra를 함께 보고, 가능하면 실제 실행 시간과 읽은 페이지 수까지 같이 확인하는 편이 좋다.
인덱스가 오히려 느려지는 경우
인덱스가 기대보다 느려지는 대표적인 상황은 조건에 맞는 데이터가 너무 많을 때다. 예를 들어 status = 'ACTIVE'인 사용자가 전체의 90%라면 status 단일 인덱스는 읽기량을 크게 줄이지 못한다. 인덱스에서 많은 PK를 찾고 다시 테이블을 읽는 비용이 커지기 때문이다.
다음 실행 계획은 인덱스를 사용했지만 예상 읽기 행 수가 지나치게 많은 상황을 보여준다.
id: 1
select_type: SIMPLE
table: users
type: ref
possible_keys: idx_users_status
key: idx_users_status
key_len: 82
ref: const
rows: 920000
Extra: Using index condition
key가 잡혀 있으므로 겉으로는 인덱스를 사용한 쿼리처럼 보인다. 하지만 rows가 92만 행이라면 실제 성능은 좋지 않을 수 있다. 특히 조회 컬럼이 인덱스에 없어서 PK 재조회가 계속 발생한다면 인덱스를 사용한 이점이 줄어든다.
컬럼을 함수로 감싸는 조건도 자주 문제가 된다.
SELECT id, title
FROM posts
WHERE DATE(created_at) = '2026-04-01';
SELECT id, title
FROM posts
WHERE created_at >= '2026-04-01 00:00:00'
AND created_at < '2026-04-02 00:00:00';
첫 번째 쿼리는 created_at 값에 DATE() 함수를 적용한다. 일반적인 B+Tree 인덱스는 컬럼의 원래 정렬값을 기준으로 탐색하므로, 이런 조건에서는 인덱스의 시작 지점을 잡기 어려울 수 있다. 두 번째처럼 범위 조건으로 표현하면 인덱스가 가진 정렬 순서를 활용하기 쉬워진다.
앞쪽 와일드카드가 붙은 LIKE도 비슷하다. LIKE '%example.com'은 문자열 앞부분을 알 수 없어 일반적인 B+Tree 인덱스에서 시작 위치를 찾기 어렵다. 반면 LIKE 'minji%'처럼 앞부분이 고정된 검색은 인덱스 활용 가능성이 높다.
인덱스 설계에서 자주 생기는 오해
카디널리티가 낮은 컬럼에 단독 인덱스를 만들면 효과가 제한적일 수 있다. 카디널리티는 값의 다양성을 뜻한다. 성별, 상태값처럼 값 종류가 적은 컬럼은 조건에 맞는 행의 비율이 높아지기 쉽다. 다만 다른 컬럼과 결합한 복합 인덱스에서는 의미가 생길 수 있다.
복합 인덱스가 있으면 구성 컬럼을 어떤 순서로 검색해도 된다고 생각하기도 쉽다. 하지만 (user_id, ordered_at) 인덱스는 user_id 조건이 있을 때 강하다. ordered_at만으로 검색하는 쿼리에는 기대만큼 도움이 되지 않을 수 있다. 인덱스가 어떤 순서로 정렬되어 있는지를 쿼리 조건과 맞춰 봐야 한다.
EXPLAIN에서 인덱스가 보이면 튜닝이 끝났다고 보기에도 이르다. key가 잡혔더라도 rows가 너무 많거나, Extra에 Using filesort, Using temporary가 보이면 추가 확인이 필요하다. 실제 성능은 인덱스 선택, 읽은 행 수, PK 재조회, 정렬 비용, 반환 컬럼 범위가 함께 만든다.
인덱스를 검토할 때는 다음 질문을 함께 보면 좋다.
- 이 쿼리는 어떤 조건으로 읽기 범위를 줄이는가?
- 조건에 맞는 행이 전체 중 어느 정도 비율인가?
- 복합 인덱스의 왼쪽 컬럼부터 사용되고 있는가?
- 조회 컬럼 때문에 PK 재조회가 많이 발생하는가?
- 정렬이나 그룹화를 인덱스 순서로 해결할 수 있는가?
- 쓰기 작업이 많은 테이블에 불필요한 인덱스를 늘리고 있지는 않은가?
원문 참고
https://www.maeil-mail.kr/question/60
댓글
댓글 쓰기