무작위 필드에 보조 인덱스를 얹는 것은 조용히 데이터베이스를 죽이고 있다.

Source: Dev.to

Overview

왜 우리는 SQL 필드에 인덱스를 추가할까요? 검색을 더 빠르게 하기 위해서죠, 맞습니다.

하지만 이것이 큰 단점을 가지고 있다는 사실을 알고 계셨나요? 쓰기 작업이 느려져서 개발자들이 어떤 필드에 보조 인덱스를 붙일지 전략적으로 선택해야 합니다.

Example query

SELECT * FROM book WHERE author = 'C.S. Lewis';

book 테이블에 (id, title, author, pub_date, isbn) 필드와 100만 행이 있다고 가정해 봅시다. 그 중 C.S. Lewis가 쓴 책은 단 20개뿐입니다. 인덱스가 없으면 데이터베이스는 전체 힙(행이 물리적으로 저장된 영역)을 스캔하게 되며, 이는 전체 테이블 스캔이 되어 O(n) 복잡도로 매우 느립니다.

The Fix

author 컬럼에 인덱스를 추가하면:

CREATE INDEX idx_author ON book(author);

데이터베이스는 별도의 B‑Tree 구조를 만들어 저자 이름을 알파벳 순으로 정렬하고, 힙에 있는 해당 행을 가리키는 포인터를 저장합니다. 이제 쿼리는 B‑Tree를 O(log n) 시간에 탐색하여 일치하는 20개의 포인터를 찾고, 그 포인터를 통해 행을 직접 가져옵니다. 이것이 보조 인덱스가 읽기 속도를 높이는 이유입니다.

Why Writes Get Slower

이제 행을 삽입하거나 업데이트할 때는 두 단계가 필요합니다:

1. 행 데이터를 힙에 기록한다.
2. 영향을 받는 모든 보조 B‑Tree를 업데이트한다.

테이블에 보조 인덱스가 다섯 개 있다면, 각 INSERT 또는 UPDATE는 힙 뿐만 아니라 디스크에 있는 다섯 개의 B‑Tree도 수정해야 합니다. 이 숨겨진 비용이 쓰기 성능을 크게 저하시킬 수 있습니다.

Tips for Secondary Indexes

• 자주 업데이트되는 컬럼(예: 페이지 방문 수, 조회수)에는 인덱스를 만들지 않도록 하여 쓰기 오버헤드를 줄이세요.
• WHERE, JOIN, ORDER BY 절에 사용되는 컬럼에만 인덱스를 생성하고, 인덱스로 얻는 성능 이점이 쓰기 비용을 초과할 때만 적용하세요.