인덱스는 어떤 조회를 줄이고 어떤 쓰기를 늘릴까¶

“조회가 느리면 인덱스”라는 규칙 대신, 후보 페이지를 얼마나 줄이고 그 대가로 어떤 복사본을 유지하는지 계산합니다.

예상 읽기 시간 8분 · 핵심 질문: 이 인덱스가 실제로 건너뛰게 만드는 데이터는 무엇인가

인덱스는 원하는 행의 위치를 빠르게 좁히기 위해 원본 표와 별도로 유지하는 자료구조다. 전화번호부처럼 순서를 이용하지만, 중요한 점은 비유보다 비용이다. 인덱스가 없다면 조건에 맞는 행을 찾기 위해 표의 많은 페이지를 훑어야 한다. 인덱스가 있으면 일부 키 페이지에서 후보 위치를 찾고 필요한 원본 페이지만 방문할 수 있다.

인덱스는 원본을 없애지 않는다. 삽입·업데이트·삭제가 생길 때마다 관련 인덱스도 바뀌고, 메모리와 디스크 공간을 차지한다. 후보가 표의 절반이라면 인덱스를 따라 흩어진 원본 페이지를 여러 번 방문하는 것보다 표를 순서대로 훑는 편이 더 쌀 수 있다.

따라서 시작 질문은 “인덱스가 있는가”가 아니다. 어떤 조건으로 후보를 몇 개까지 줄이는지, 그 후보가 어느 페이지에 흩어져 있는지, 이 절약이 모든 쓰기의 유지 비용보다 큰지를 본다.

flowchart TB
  Q["SQL의 조건·정렬·반환 열"] --> O["최적화기가 통계로 비용 예상"]
  O --> C{"어느 경로가 더 싼가?"}
  C -->|"인덱스"| I["키 구조에서 후보 위치 축소"]
  I --> T["필요한 원본 페이지만 방문"]
  C -->|"순차 조회"| S["표 페이지를 연속해서 훑음"]
  W["삽입·업데이트·삭제"] --> D["원본 변경"]
  W --> M["관련 인덱스도 함께 변경"]

B-Tree는 정렬된 범위의 시작점을 찾는다¶

B-Tree(Balanced Tree, 균형 트리)는 키를 정렬한 채 페이지 여러 개에 나누어 둔다. 루트 페이지에서 비교를 시작해 중간 페이지를 거쳐 리프 페이지까지 내려간다. 균형 트리라는 말은 모든 리프가 루트에서 비슷한 단계에 있다는 뜻이다.

메모리의 이진 탐색 트리처럼 자식이 두 개뿐인 구조는 아니다. 데이터베이스 페이지 하나에는 많은 키와 자식 페이지 주소가 들어간다. 한 단계에서 넓은 범위를 제외할 수 있으므로 데이터가 커져도 트리 높이는 비교적 낮게 유지된다.

같음 조건은 키가 있는 리프 위치를 찾고, 범위 조건은 시작 리프를 찾은 뒤 옆 리프를 순서대로 따라간다. 키의 정렬 순서가 ORDER BY와 같으면 별도 정렬도 줄일 수 있다. B-Tree가 =, 범위와 정렬에 두루 사용되는 이유다.

삽입할 리프 페이지가 가득 차면 일부 항목을 새 페이지로 옮기는 페이지 분할이 발생한다. 무작위 위치로 계속 들어오는 키와 인덱스 수가 많을수록 페이지 변경, 복구 로그와 캐시 부담이 커질 수 있다. O(log N)이라는 계산 복잡도만으로는 이 비용을 설명할 수 없다.

복합 인덱스는 열을 사전식으로 묶는다¶

복합 인덱스 (tenant_id, status, created_at)은 먼저 tenant_id로, 값이 같으면 status, 둘도 같으면 created_at으로 정렬한다. 다음 질의는 세 열이 이어진 좁은 범위를 찾기 좋다.

SELECT id, title, created_at
FROM posts
WHERE tenant_id = 42
  AND status = 'PUBLISHED'
  AND created_at >= :from
ORDER BY created_at DESC
LIMIT 20;

반대로 status만 조건에 있다면 서로 다른 모든 tenant_id 묶음 안에서 상태를 찾아야 한다. 선두 열이 빠진 질의가 항상 인덱스를 못 쓰는 것은 아니지만 한 번의 좁은 연속 범위가 되기 어렵다.

열 순서는 선택도 하나로 정하지 않는다. 같음 조건, 첫 범위 조건, 정렬, LIMIT, 조인과 실제로 함께 등장하는 질의 묶음을 본다. 자주 실행되는 질의 하나를 빠르게 하려고 거의 같은 복합 인덱스를 계속 추가하면 전체 쓰기 비용이 더 크게 늘 수 있다.

커버링 인덱스는 원본 행 방문을 줄인다¶

질의에 필요한 조건과 반환 열이 인덱스 안에 모두 있으면 원본 행을 다시 방문하지 않거나 방문 횟수를 줄일 수 있다. 이를 커버링 인덱스(Covering Index)라고 한다.

PostgreSQL의 INCLUDE는 검색 순서를 정하는 키와 결과에 실어 둘 열을 구분한다. 앞의 예에서 (tenant_id, status, created_at DESC) INCLUDE (id, title)를 만들면 id와 title은 탐색 순서에는 참여하지 않지만 리프에서 결과를 제공할 수 있다.

반환 열을 계속 넣으면 인덱스가 넓어져 한 페이지에 담기는 항목 수가 줄고, 캐시와 쓰기 비용이 커진다. PostgreSQL은 MVCC 가시성을 확인하기 위해 힙 페이지를 방문할 수도 있다. “필요한 열이 모두 있다”와 “원본 페이지 방문이 항상 0이다”는 같은 뜻이 아니다.

클러스터형 인덱스는 행의 저장 위치와 연결된다¶

InnoDB의 기본 키 인덱스는 리프 페이지에 행 전체를 둔다. 기본 키로 찾으면 곧바로 행에 도착하지만, 보조 인덱스는 기본 키를 저장하므로 필요한 열이 없으면 기본 키 트리를 다시 탐색한다. 기본 키가 길면 모든 보조 인덱스가 함께 넓어진다.

PostgreSQL의 일반 인덱스는 힙의 튜플 위치를 가리킨다. 두 제품 모두 B-Tree를 사용해도 원본 행까지 가는 경로가 다르므로 같은 인덱스 정의의 공간과 조회 비용도 같다고 가정하면 안 된다.

부분·표현식 인덱스는 저장할 대상을 줄인다¶

부분 인덱스(Partial Index)는 조건을 만족하는 행만 저장한다. 삭제되지 않은 행이 전체의 2%라면 WHERE deleted_at IS NULL인 행만 넣어 작고 집중된 인덱스를 만들 수 있다. 실제 질의 조건이 인덱스의 조건과 논리적으로 맞아야 최적화기가 사용할 수 있으므로 ORM이 만든 SQL까지 확인해야 한다.

표현식 인덱스(Expression Index)는 lower(email)처럼 계산한 결과를 저장한다. 조회 때 같은 계산을 반복하지 않고 대소문자를 통일한 검색 경로를 만들 수 있다. 대신 데이터가 바뀔 때마다 표현식을 계산하고, 애플리케이션의 조건식도 인덱스 표현과 일치시켜야 한다.

접두사 인덱스는 긴 문자열의 앞부분만 저장한다¶

MySQL의 접두사 인덱스는 긴 문자열의 앞 N글자 또는 바이트만 키로 저장해 인덱스 폭을 줄인다. 앞부분만으로도 후보가 충분히 줄어드는 데이터에 유용하다. 비슷한 접두사가 많으면 선택도가 낮고, 전체 문자열이 없으므로 커버링에 필요한 값을 제공하지 못할 수 있다. 실제 값 분포로 접두사 길이를 정한다.

해시와 비트맵은 B-Tree와 다른 정보를 보존한다¶

해시 인덱스는 해시 함수로 버킷을 찾아 같은 값 조건에 맞지만 키의 순서를 보존하지 않아 범위와 정렬에는 직접 사용할 수 없다. B-Tree도 같음 조건을 잘 처리하므로 해시는 O(1)이라는 문장만으로 더 빠르다고 결론 내리지 않는다. 페이지 접근, 동시성, 복구 로그와 구현의 성숙도를 함께 본다.

PostgreSQL의 비트맵 스캔은 여러 인덱스가 찾은 후보를 비트 집합으로 합치고, 원본 페이지별로 묶어 방문한다. 영구적인 비트맵 인덱스 제품과는 다른 실행 방식이다. 후보가 적지는 않지만 여러 조건을 조합할 때 흩어진 무작위 방문을 줄일 수 있다.

GIN·GiST·SP-GiST·BRIN은 질문의 모양이 다르다¶

GIN(Generalized Inverted Index, 범용 역색인)은 배열 원소, JSON 키와 문서 단어처럼 한 행에서 여러 검색 키가 나올 때 각 키에서 행 집합으로 연결한다. 포함 검색은 빠르지만 한 행이 많은 인덱스 항목을 만들 수 있어 구축과 업데이트 비용이 크다.

GiST(Generalized Search Tree, 범용 검색 트리)는 공간의 겹침, 범위와 거리처럼 단순한 한 줄 정렬로 표현하기 어려운 값을 요약해 하위 영역을 제외한다. SP-GiST(Space-Partitioned GiST, 공간 분할 범용 검색 트리)는 트라이와 사분 트리처럼 공간을 재귀적으로 나누는 자료에 맞는다. 두 방식은 자료형 이름보다 사용할 연산자와 후보 재확인 방식을 확인해야 한다.

BRIN(Block Range Index, 블록 범위 인덱스)은 큰 물리 구간마다 최소·최대 같은 요약만 저장한다. 매우 작고 쓰기 비용도 낮지만 값의 순서와 물리 저장 순서가 비슷해야 많은 구간을 건너뛴다. 시간순으로 계속 추가되는 큰 표에는 유리할 수 있고 무작위 분포에서는 거의 모든 구간이 후보가 될 수 있다.

전문 검색은 문장을 단어 단위로 다시 저장한다¶

전문 검색 인덱스는 문서를 토큰과 어휘소로 분석해 단어에서 문서로 이어지는 역색인을 만든다. 언어별 형태 분석, 관련도 순위와 새 데이터가 검색에 보이기까지의 지연을 함께 다룬다. 문자열의 일부를 찾는 요구를 B-Tree 접두사 검색으로 억지로 해결하는 것과 비용 구조가 다르다.

벡터 인덱스는 속도와 재현율을 교환한다¶

HNSW와 IVFFlat 같은 ANN(Approximate Nearest Neighbor, 근사 최근접 이웃) 인덱스는 모든 벡터를 정확히 비교하지 않고 가까울 가능성이 큰 후보를 빠르게 찾는다. 속도를 얻는 대신 실제 상위 결과를 일부 놓칠 수 있다. 실행 시간뿐 아니라 정답 후보를 얼마나 되찾는지 나타내는 recall@k와 메타데이터 필터 뒤의 후보 수를 함께 측정해야 한다.

EXPLAIN ANALYZE에서는 추정과 실제를 비교한다¶

최적화기는 표와 열 통계로 순차 조회, 인덱스 조회와 조인 순서의 비용을 예상한다. 인덱스가 존재해도 많은 행을 반환하거나 원본 페이지가 흩어져 있으면 순차 조회를 선택할 수 있다.

EXPLAIN ANALYZE에서는 인덱스 이름만 보지 않는다. 예상 행 수와 실제 행 수, 반복 횟수, 조건에서 제거된 행, 원본 페이지 방문과 정렬 방식을 본다. 예상과 실제가 크게 다르면 통계가 오래됐는지, 값이 한쪽에 몰렸는지, 여러 열의 상관관계를 놓쳤는지 확인한다.

인덱스가 오히려 느려지는 신호¶

인덱스 수가 늘면서 업데이트 지연과 복구 로그가 커지거나, 넓은 커버링 인덱스가 표만큼 커지면 조회 하나의 절약보다 전체 쓰기 비용이 클 수 있다. 선택도가 낮은 단일 열 인덱스가 많은 원본 페이지 방문을 만들 때도 순차 조회보다 느릴 수 있다.

무작위 키 삽입으로 페이지 분할이 잦은지, 부분 인덱스 조건이 실제 SQL과 일치하는지, BRIN의 물리 상관관계와 벡터 인덱스의 재현율이 유지되는지도 본다. 새 인덱스를 만들기 전에는 대표 질의의 실행 계획과 지연뿐 아니라 삽입·업데이트 지연, 인덱스 크기와 캐시 변화를 함께 기록한다.