인덱스는 많을수록 좋다

조회가 느리면 인덱스를 하나 더 걸면 된다는 말이 있습니다. 인덱스가 읽기를 빠르게 하는 물건인 것은 맞습니다. 다만 공짜로 얹히지는 않습니다. 표가 바뀔 때마다 데이터베이스가 인덱스도 같이 고쳐 둡니다.

쉽고 빠른 이해

인덱스를 하나 더 걸면 조회가 빨라진다는 경험에서 나온 생각입니다. id 컬럼에 인덱스를 걸어 두면 표를 처음부터 끝까지 훑지 않고 인덱스를 몇 단계만 따라가 그 행을 찾습니다.

왜 이렇게 하나. 인덱스가 없으면 데이터베이스는 표를 한 행씩 다 훑어야 합니다. 표가 클수록 그 비용이 커지니, 인덱스를 더 걸수록 좋을 거라는 생각이 자연스럽게 따라옵니다.

어떻게 도나.

1. id 같은 컬럼에 인덱스를 걸어 두면 표 전체 대신 몇 단계만 내려가 행을 찾습니다.
2. 표가 바뀔 때마다 시스템이 인덱스를 알아서 갱신합니다.
3. 인덱스 쪽이 더 효율적이라고 판단될 때만 조회에서 씁니다.

대가. 인덱스가 늘수록 표에 행을 넣거나 지우거나 고칠 때마다 그만큼 인덱스도 함께 갱신해야 합니다. 안 쓰이는 인덱스는 갱신 비용과 저장 공간만 남깁니다. 표가 대부분 읽기만 되고 거의 안 바뀐다면 이 대가를 치를 값어치가 있지만, 표가 자주 바뀐다면 인덱스를 적게 두는 쪽이 낫습니다.

상세

인덱스가 없으면 데이터베이스는 표를 처음부터 끝까지 훑습니다. PostgreSQL 문서는 아무 사전 준비가 없으면 시스템이 일치하는 항목을 전부 찾으려고 표 전체를 한 행씩 훑어야 한다고 적습니다. 표에 행이 많고 그런 질의가 돌려줄 행은 몇 개뿐이라면 이것은 분명히 비효율적인 방법이라고 적습니다. id 컬럼에 인덱스를 유지하라고 시스템에 일러 두었다면 일치하는 행을 찾는 더 효율적인 방법을 쓸 수 있습니다. 탐색 트리를 몇 레이어만 내려가면 되는 정도일 수도 있습니다.

MySQL 문서도 같은 자리를 적습니다. 인덱스는 특정 컬럼 값을 가진 행을 빠르게 찾는 데 쓰입니다. 인덱스가 없으면 MySQL 은 첫 행에서 시작해 관련 행을 찾을 때까지 표 전체를 읽어야 합니다. 표가 클수록 이 비용이 커집니다.

만들고 나면 손이 갈 일은 거의 없습니다. PostgreSQL 문서는 인덱스가 한 번 만들어지고 나면 더 개입할 필요가 없다고 적습니다. 표가 수정되면 시스템이 인덱스를 갱신하고, 시퀀셜 스캔 — 인덱스를 거치지 않고 표를 처음부터 순서대로 읽는 방법입니다 — 보다 인덱스를 쓰는 편이 더 효율적이라고 판단하면 질의에서 인덱스를 씁니다. 이렇게 어느 쪽이 더 효율적인지 판단하는 것이 쿼리 플래너입니다. 다만 쿼리 플래너가 제대로 된 판단을 하도록 통계를 갱신하려면 ANALYZE 명령을 주기적으로 돌려야 할 수도 있다고 덧붙입니다.

손이 안 간다는 말과 값을 안 치른다는 말은 다릅니다. 같은 문서가 이어서 적습니다. 인덱스가 만들어진 뒤 시스템은 그것을 표와 동기화된 상태로 유지해야 합니다. 이것이 데이터 조작 연산에 부담을 더합니다.

그래서 어느 인덱스를 둘지는 사람의 몫으로 남습니다. PostgreSQL 문서는 독자가 찾아볼 만한 항목을 미리 헤아리는 것이 저자의 일이듯, 어떤 인덱스가 쓸모 있을지 내다보는 것이 데이터베이스 프로그래머의 일이라고 적습니다. 표제어의 문장은 이 예측을 개수로 대신할 수 있다는 주장입니다.

통념

MySQL 8.4 레퍼런스 매뉴얼의 「Optimization and Indexes」 절은 이 문장으로 시작합니다.

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query.

SELECT 연산의 성능을 개선하는 최선의 방법은 질의에서 검사되는 컬럼 하나 이상에 인덱스를 만드는 것이라는 말입니다. 이 문장을 끝까지 밀면 「질의에 쓰이는 컬럼마다 인덱스를 걸면 된다」가 됩니다. 그렇게 하고 싶어진다는 것을 같은 문서가 적어 둡니다.

Although it can be tempting to create an indexes for every possible column used in a query, ...

질의에 쓰일 만한 모든 컬럼에 인덱스를 만들고 싶어질 수 있다는 말입니다. 원문의 an indexes 는 매뉴얼에 그대로 있는 오타입니다.

Markus Winand 의 Use The Index, Luke! 도 같은 유혹을 적습니다.

If the concept of function-based indexing is new to you, you might be tempted to just index everything, but this is in fact the very last thing you should do.

함수 기반 인덱싱은 컬럼 값 그대로가 아니라 그 값에 함수를 적용한 결과에 인덱스를 거는 방식입니다. 이 개념이 처음이라면 그냥 전부 인덱싱하고 싶어질 수 있다는 말입니다.

두 문서 모두 「인덱스는 많을수록 좋다」를 단언하지는 않습니다. 그렇게 하고 싶어지는 자리가 있다고 적을 뿐입니다. 이 유혹이 그럴듯한 이유는 앞 절에 있습니다. 인덱스가 없으면 표를 처음부터 끝까지 읽어야 하고, 표가 클수록 그 비용이 커집니다. 인덱스를 하나 걸어서 오래 걸리던 조회가 빨라지는 경험은 실제로 자주 일어납니다. 통념은 그 경험을 개수의 규칙으로 일반화한 것입니다.

반증

뒤집는 문장은 인덱스를 만드는 쪽 제품 문서에 이미 적혀 있습니다.

PostgreSQL 문서는 인덱스가 만들어진 뒤 시스템이 그것을 표와 동기화된 상태로 유지해야 한다고 적습니다. 이것이 데이터 조작 연산에 부담을 더합니다. 그래서 질의에서 거의 또는 전혀 쓰이지 않는 인덱스는 제거되어야 한다고 적습니다.

Oracle 문서는 개수를 정면으로 다룹니다.

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified.

표는 인덱스를 얼마든지 가질 수 있습니다. 다만 인덱스가 많을수록 표가 수정될 때 드는 부담이 커집니다. 같은 문서가 이어서 적습니다. 행이 삽입되거나 삭제되면 표의 모든 인덱스도 함께 갱신되어야 합니다. 컬럼이 갱신되면 그 컬럼을 담은 모든 인덱스가 갱신되어야 합니다. 그래서 표에서 데이터를 꺼내는 속도와 표를 갱신하는 속도 사이에 트레이드오프가 있다고 적습니다.

MariaDB 안내서는 과잉 인덱싱을 피하라는 항목을 따로 둡니다. 여분의 인덱스는 저장 공간을 소모하고 INSERT·UPDATE·DELETE 연산을 느리게 만들 수 있다고 적습니다. MySQL 문서도 질의 성능을 개선하는 데 필요한 인덱스만 만들라고 적습니다. 인덱스는 조회에는 쓸모가 있지만 삽입과 갱신 연산을 느리게 만든다고 적습니다.

통념이 어긋나는 지점은 읽기가 아니라 쓰기입니다. 인덱스를 하나 더 두면 그 표를 읽을 때 쓸 수 있는 경로가 하나 늘어납니다. 동시에 그 표에 대한 모든 삽입·갱신·삭제가 건드려야 할 자료구조도 하나 늘어납니다. 「많을수록」이 성립하려면 뒤쪽 비용이 0 이어야 합니다. 네 제품 문서 모두 그 비용이 0 이 아니라고 적습니다.

경계

읽기만 하는 표에 인덱스를 하나 더 두는 것도 과잉 인덱스인가. 아닙니다. Oracle 문서가 그 선을 직접 긋습니다. 표가 주로 읽기 전용이면 인덱스를 더 두는 것이 쓸모 있을 수 있습니다. 표가 많이 갱신된다면 인덱스를 적게 두는 편이 나을 수 있습니다. 통념이 맞는 자리는 여기입니다. 갱신이 드문 표에서는 하나를 더 두는 쪽이 실제로 이득일 수 있습니다.

다만 갱신이 드물다는 조건만으로는 부족합니다. 그 인덱스를 질의가 실제로 쓸 것인가가 남습니다. 문서들이 그 선을 재는 잣대를 두 가지 적어 둡니다.

선택도

선택도는 질의가 표에서 꺼내는 행이 전체의 몇 퍼센트인가를 뜻합니다. Oracle 문서는 큰 표에서 행의 15% 미만을 자주 꺼내고 싶다면 인덱스를 만들라고 적습니다. 이어서 그 퍼센트는 표 스캔의 상대적인 속도와 행 데이터가 인덱스 키에 대해 어떻게 분포하는지에 따라 크게 달라진다고 적습니다. 표 스캔이 빠를수록 퍼센트는 낮아지고, 행 데이터가 더 뭉쳐 있을수록 퍼센트는 높아집니다.

PostgreSQL 문서는 반대쪽에서 같은 선을 긋습니다. 흔한 값을 찾는 질의는 어차피 인덱스를 쓰지 않습니다. 여기서 흔한 값이란 표 전체 행의 몇 퍼센트를 넘게 차지하는 값입니다. 부분 인덱스는 표의 모든 행이 아니라 조건에 맞는 행만 골라 만든 인덱스입니다. 그래서 부분 인덱스를 쓰는 주된 이유 하나가 흔한 값을 인덱스에 아예 넣지 않는 것입니다. 인덱스 크기가 줄어 그 인덱스를 실제로 쓰는 질의가 빨라집니다. 인덱스를 모든 경우에 갱신할 필요가 없어져 많은 표 갱신 연산도 빨라집니다.

표 크기

MySQL 문서는 작은 표에 대한 질의나 대부분의 행을 처리하는 리포트 질의에서는 인덱스가 덜 중요하다고 적습니다. 질의가 대부분의 행에 접근해야 할 때는 인덱스를 거치는 것보다 순차적으로 읽는 편이 빠릅니다. 순차 읽기는 필요 없는 행까지 읽더라도 디스크 시크를 최소화합니다. 이 순차 읽기가 앞서 나온 시퀀셜 스캔·표 스캔과 같은 것입니다. PostgreSQL 문서는 아주 작은 테스트 데이터셋을 쓰는 것이 특히 치명적이라고 적습니다. 100000 행에서 1000 행을 고르는 것은 인덱스 후보가 될 수 있지만, 100 행에서 1 행을 고르는 것은 거의 후보가 되지 못합니다. 그 100 행이 디스크 페이지 하나에 들어갈 것이고, 디스크 페이지 하나를 순차적으로 가져오는 것을 이길 계획이 없기 때문입니다. MariaDB 안내서도 인덱스는 버퍼(자주 쓰는 데이터를 담아 두는 메모리 영역) 크기보다 큰 표에서 아주 작은 표보다 더 큰 속도 향상을 준다고 적습니다.

인덱스만으로 끝나는 조회

PostgreSQL 은 인덱스 온리 스캔을 지원합니다. 표의 실제 데이터가 저장되는 곳인 힙에 전혀 접근하지 않고 인덱스만으로 질의에 답하는 방식입니다. 기본 발상은 연관된 힙 항목을 보는 대신 각 인덱스 항목에서 값을 곧바로 돌려주는 것입니다.

flowchart TD
    subgraph 일반["일반 인덱스 스캔"]
        Q1["질의"] --> I1["인덱스"] --> H1["힙"]
    end
    subgraph 온리["인덱스 온리 스캔"]
        Q2["질의"] --> I2["인덱스"]
    end

일반 인덱스 스캔은 인덱스에서 행 위치를 찾은 뒤 힙까지 들러야 합니다. 인덱스 온리 스캔은 인덱스 항목만 보고 끝나, 이 연산이 건드리는 범위에서 힙이 빠집니다.

같은 문서가 조건을 답니다. 인덱스 온리 스캔이 가능하더라도, 힙 페이지 중 상당한 비율에 all-visible 맵 비트가 설정되어 있어야만 이득이 됩니다. all-visible 맵 비트는 그 페이지의 모든 행을 모든 트랜잭션이 볼 수 있다고 표시해 두는 비트입니다. 행의 큰 비율이 바뀌지 않는 표는 흔한 편이어서, 이 스캔 방식이 실무에서 매우 쓸모 있다고 적습니다. 검색 조건에 쓰이는 컬럼뿐 아니라 질의가 돌려줄 값의 컬럼까지 얹어서, 그 인덱스만 보고도 질의에 필요한 값을 전부 낼 수 있게 만든 인덱스를 커버링 인덱스라 부릅니다. 이 자리를 노리고 만드는 인덱스입니다.

행 대부분이 안 바뀌는 표라면, 인덱스를 더 두어 이 방식을 노릴 값어치가 커집니다. 통념이 개수만 보고 놓치는 조건 하나가 여기 있습니다 — 같은 인덱스라도 표가 얼마나 안 바뀌는가에 따라 이득의 크기가 갈립니다.

실패

통념대로 인덱스를 계속 더했을 때 깨지는 자리는 문서마다 다른 이름으로 적혀 있습니다. 아래 표의 non-key 컬럼은 검색 조건에는 쓰이지 않고 결과 값만 실어 나르는, 인덱스에 딸린 페이로드 컬럼을 가리킵니다.

인덱스 하나를 더할 때 늘어나는 것

MySQL 문서는 불필요한 인덱스가 공간을 낭비하고 MySQL 이 어느 인덱스를 쓸지 정하는 데 드는 시간을 낭비한다고 적습니다. 인덱스는 삽입·갱신·삭제의 비용도 더합니다. 각 인덱스가 갱신되어야 하기 때문입니다. 그래서 최적의 인덱스 집합으로 질의를 빠르게 하려면 옳은 균형을 찾아야 한다고 적습니다.

Use The Index, Luke! 는 같은 것을 지속적인 유지라고 부릅니다. 모든 인덱스가 지속적인 유지를 유발합니다. 함수 기반 인덱스는 중복 인덱스를 만들기 아주 쉽게 만든다는 점에서 특히 성가시다고 적습니다. 같은 컬럼의 함수 적용 결과에 인덱스를 하나 더 만들 수는 있지만, 그러면 데이터베이스가 모든 삽입·갱신·삭제 문장마다 인덱스 두 개를 유지해야 한다고 적습니다.

인덱스를 넓히다 삽입이 실패하는 자리

PostgreSQL 문서는 인덱스에 non-key 페이로드 컬럼을 더하는 것에 보수적인 편이 현명하다고 적습니다. 특히 넓은 컬럼이 그렇습니다. 인덱스 튜플(인덱스 항목 하나를 실제로 담는 저장 단위)이 그 인덱스 타입에 허용된 최대 크기를 넘으면 데이터 삽입이 실패합니다. 어느 경우든 non-key 컬럼은 인덱스의 표에 있는 데이터를 중복시키고 인덱스 크기를 부풀려 검색을 잠재적으로 느리게 만듭니다. 그리고 표가 충분히 느리게 바뀌어 인덱스 온리 스캔이 힙에 접근하지 않아도 될 만한 경우가 아니라면 페이로드 컬럼을 인덱스에 포함시킬 이유가 거의 없다고 적습니다.

만드는 동안 막히는 쓰기

큰 표에 인덱스를 만드는 일은 오래 걸릴 수 있습니다. PostgreSQL 은 기본적으로 인덱스 생성과 병렬로 표에 대한 읽기가 일어나도록 허용합니다. 쓰기는 인덱스 빌드가 끝날 때까지 막힙니다. 문서는 프로덕션 환경에서 이것이 종종 받아들일 수 없는 일이라고 적습니다. 인덱스를 하나 더 두기로 한 결정의 값은 만들어진 뒤가 아니라 만드는 동안에도 나갑니다.

몇 개가 맞는지 세는 절차

개수로 답을 내려는 시도 자체가 여기서 막힙니다. PostgreSQL 문서는 어떤 인덱스를 만들지 정하는 일반적인 절차를 세우기는 어렵다고 적습니다. 앞 절들의 예제에서 보인 전형적인 경우가 여럿 있을 뿐이고, 상당한 실험이 흔히 필요하다고 적습니다.

세는 대신 확인합니다. 같은 문서는 PostgreSQL 의 인덱스가 유지나 튜닝을 필요로 하지는 않지만 실제 질의 워크로드가 어느 인덱스를 실제로 쓰는지 확인하는 것은 여전히 중요하다고 적습니다. 개별 질의에 대해서는 EXPLAIN 명령으로 봅니다. 돌아가는 서버에서 인덱스 사용에 대한 전체 통계를 모으는 것도 가능합니다.

확인의 결과는 대개 제거입니다. PostgreSQL 문서는 질의에서 거의 또는 전혀 쓰이지 않는 인덱스는 제거되어야 한다고 적습니다. Oracle 문서는 더 이상 필요하지 않은 인덱스를 드롭하는 것이 모범 사례라고 적습니다. 질의를 빠르게 만들지 못하는 인덱스, 애플리케이션의 질의가 쓰지 않는 인덱스가 드롭을 고려할 대상입니다.

관련 항목

인덱스를 둘러싼 계층

데이터베이스 · 테이블 · 컬럼 · 인덱스

인덱스 사용 여부를 가르는 판단

질의 · 쿼리 플래너 · 워크로드

인덱스 대신 쓰는 접근 경로

시퀀셜 스캔 · 디스크 시크

인덱스 효과를 재는 지표

성능 · 선택도

인덱스만으로 조회가 끝나는 조건

인덱스 온리 스캔 · 커버링 인덱스 · 힙 · 트랜잭션

인덱스의 하위 종류

부분 인덱스 · 함수 기반 인덱스 · 중복 인덱스

인덱스를 갱신시키거나 살피는 명령

ANALYZE · EXPLAIN · SELECT · INSERT · UPDATE · DELETE

인덱스가 오가는 저장 단위

버퍼 · 인덱스 튜플 · 페이로드 컬럼

이 통념을 다루는 제품 문서

PostgreSQL · MySQL · Oracle · MariaDB