인덱스와 풀 스캔

인덱스가 있는데 왜 안 탈까

조건에 맞는 행을 찾는 방법은 두 가지입니다. 테이블을 처음부터 끝까지 읽는 방법(풀 스캔)과, 인덱스에서 위치를 찾아 그 행만 읽는 방법입니다. 어느 쪽이 싼지는 조건에 걸리는 행의 비율에 달려 있고, 옵티마이저가 통계를 보고 고릅니다.

개념 모형 · 경험칙에 맞춘 비용 모형

이 실습에 나오는 개념

인덱스: 테이블에서 원하는 행을 빨리 찾기 위해 특정 컬럼의 값을 정렬된 형태로 따로 유지하는 자료구조다. 인덱스가 없으면 조건에 맞는 행을 찾기 위해 테이블 전체를 읽어야 한다. 조회는 빨라지지만 데이터를 추가, 수정, 삭제할 때마다 인덱스도 갱신해야 하므로 쓰기 성능과 저장 공간에 비용이 든다.
B-트리 인덱스: 대부분의 관계형 데이터베이스가 기본으로 쓰는 인덱스 구조다. 값이 정렬된 상태로 유지되는 균형 잡힌 트리이며, 한 노드에 많은 키를 담아 트리의 높이가 낮다. 루트에서 몇 단계만 내려가면 원하는 값을 찾을 수 있고, 값이 정렬되어 있어 같은 값 조회뿐 아니라 범위 조회와 정렬에도 사용된다.
풀 테이블 스캔: 조건에 맞는 행을 찾기 위해 테이블의 모든 행을 처음부터 끝까지 읽는 방식이다. 조건에 쓸 인덱스가 없거나, 인덱스가 있어도 조건에 해당하는 행이 너무 많아 전체를 읽는 편이 낫다고 옵티마이저가 판단할 때 선택된다. 큰 테이블에서 소수의 행을 찾는 데 이 방식이 쓰이면 성능 문제의 원인이 된다.
실행 계획(EXPLAIN): 데이터베이스가 쿼리를 어떤 방법으로 실행할지 정한 계획이다. 어떤 인덱스를 쓰는지, 테이블을 어떤 순서로 조인하는지, 몇 행을 읽을 것으로 예상하는지를 보여 준다. 쿼리 앞에 EXPLAIN을 붙여 확인하며, 쿼리가 느릴 때 원인을 찾는 출발점이다.
옵티마이저: SQL을 실행할 수 있는 여러 방법 가운데 비용이 가장 적을 것으로 예상되는 방법을 고르는 데이터베이스의 구성 요소다. 테이블의 행 수와 값의 분포 같은 통계 정보를 바탕으로 판단한다. 통계가 실제와 다르면 비효율적인 방법을 고를 수 있다.
카디널리티와 선택도: 카디널리티는 컬럼에 들어 있는 서로 다른 값의 개수다. 서로 다른 값이 많을수록(예: 주민번호, 이메일) 조건 하나로 걸러지는 행이 적어 인덱스의 효과가 크고, 값의 종류가 적을수록(예: 성별) 조건에 해당하는 행이 많아 인덱스의 효과가 작다. 선택도는 카디널리티를 테이블의 행 수로 나눈 값으로, 1에 가까울수록 조건 하나로 행이 잘 걸러진다.

이렇게 바꿔 보세요

처음 설정으로 — 10만 행 중 0.1%만 조건에 맞습니다. 인덱스로 100행만 읽습니다.
같은 쿼리, 인덱스 없음 — 100행을 찾으려고 10만 행을 다 읽습니다.
절반이 조건에 맞을 때 — 인덱스가 있어도 옵티마이저는 풀 스캔을 고릅니다.
그래도 인덱스를 강제하면 — 오히려 더 비쌉니다.
경계 근처: 20%와 30% — 20%에서 실행해 보고 30%로 바꿔 비교해 보세요.
1천만 행에서 인덱스 없이 — 행이 늘어난 만큼 그대로 느려집니다.

처음 설정으로 실행하면

쿼리가 도착했습니다. 옵티마이저가 실행 방법을 정하려고 통계 정보를 봅니다. 테이블은 약 100,000행이고, status = 'CANCELED' 조건에는 약 100행(0.1%)이 걸릴 것으로 예상합니다.
방법 1, 풀 스캔: 테이블을 처음부터 끝까지 순서대로 읽으며 조건을 확인합니다. 100,000행을 읽습니다. 순서대로 읽는 것은 한 건당 비용이 쌉니다. 예상 비용 100,000.
방법 2, 인덱스: 인덱스에서 조건에 맞는 100건의 위치를 찾고, 한 건씩 테이블로 가서 나머지 컬럼을 읽습니다. 읽는 행은 적지만 한 건마다 테이블의 여기저기를 찾아가야 해서 건당 비용이 비쌉니다(여기서는 4배로 가정). 예상 비용 402.
옵티마이저가 비용이 더 낮은 인덱스를 고릅니다.
인덱스(B-트리)의 루트에서 출발해 2단계를 내려가 조건에 맞는 첫 위치를 찾습니다. 각 단계에서는 정렬된 키 사이에서 범위를 좁혀 갑니다. 행이 100,000개여도 2단계면 도착합니다.
찾은 위치부터 인덱스를 옆으로 읽어 나가며 조건에 맞는 100건을 얻고, 건마다 테이블에서 해당 행을 읽어 옵니다. 조건에 맞지 않는 행은 읽지 않았습니다.

실행 결과

조건에 걸리는 비율이 낮을수록(선택도가 좋을수록) 인덱스의 효과가 큽니다. 풀 스캔이었다면 비용은 100,000으로 약 249배였습니다.

코드

CREATE INDEX idx_orders_status ON orders (status);

SELECT *
  FROM orders
 WHERE status = 'CANCELED';   -- 전체의 0.1%

이 모형의 전제

비용은 관계를 보이기 위한 단순한 모형입니다. 순서대로 읽는 한 건의 비용을 1, 인덱스를 거쳐 테이블에서 한 건을 읽는 비용을 4로 가정했습니다. 이 비율이면 조건에 걸리는 행이 약 25%를 넘을 때 풀 스캔이 유리해지는데, MySQL 관련 서적에서 흔히 말하는 "전체의 20~25%를 넘으면 풀 스캔이 낫다"는 경험칙에 맞춘 값입니다. 실제 옵티마이저는 페이지 수, 메모리에 올라와 있는 비율, 컬럼 통계 등을 더 세밀하게 계산하므로 경계는 상황마다 다릅니다. B-트리 단계 수는 한 노드에 키가 500개 들어간다고 보고 계산했습니다. SELECT *가 아니라 인덱스에 있는 컬럼만 조회하면 테이블에 가지 않아도 되어 계산이 달라지며, 이 내용은 "복합·커버링 인덱스" 실습에서 다룹니다.

앞 실습: 이진 탐색과 B-트리 · 다음 실습: 복합·커버링 인덱스

이 실습에 나오는 개념

이 실습에서 보는 것

설정

실행 전에 결과 예측하기

코드

그림

실행 비교