인덱스는 쿼리가 읽어야 할 데이터와 정렬 작업을 줄이는 접근 경로입니다. 효과는 데이터 분포·조회 범위·쓰기 빈도에 따라 달라집니다. 실행 계획에 풀스캔이 보인다는 이유만으로 잘못된 쿼리라고 판단하거나, 인덱스가 DB 성능의 일정 비율을 결정한다고 일반화할 수는 없습니다.
1. 풀스캔과 인덱스 스캔
테이블의 많은 행을 반환하거나 테이블 자체가 작으면 순차적으로 읽는 편이 유리할 수 있습니다. 반대로 큰 테이블에서 소수의 행만 찾는데 매번 전체를 읽으면 인덱스가 효과적인 개선 후보입니다.
비교할 대상은 인덱스 사용 여부보다 실제로 읽은 행·페이지, 정렬 비용, 응답 시간입니다. 인덱스를 통해 행 위치를 찾은 뒤 테이블을 반복해서 방문하는 비용도 포함해야 합니다.
2. 구조와 비용
| 종류 | 주로 지원하는 접근 | 확인할 조건 |
|---|---|---|
| B-tree 계열 | 동등·범위 검색, 순서가 맞는 정렬 | 선두 컬럼, 정렬 방향, 비교 연산·문자열 정렬 규칙 |
| Hash | 동등 검색 | DBMS·엔진의 지원 여부, 범위·정렬에는 부적합 |
| 전문 검색 인덱스 | 단어·토큰 기반 검색 | 분석기와 검색 연산자의 의미 |
| 공간 인덱스 | 공간 후보 축소 | 좌표계·공간 연산·정확한 조건 재검사 |
인덱스가 행을 가리키는 방식도 다릅니다. 예를 들어 InnoDB 보조 인덱스는 기본 키를 포함하고, PostgreSQL의 일반적인 인덱스는 힙의 튜플 위치를 참조합니다. 인덱스가 정렬되어 있어도 ORDER BY 없는 결과 순서는 보장되지 않습니다.
인덱스를 추가하면 저장 공간·버퍼 사용량과 변경 시 유지 비용이 늘어납니다. 매번 전체를 다시 정렬하는 것은 아니지만, 엔트리 추가·삭제·페이지 분할·로그 기록 등이 발생할 수 있습니다. 생성·재구성 중 잠금과 I/O 부하도 운영 계획에 포함합니다.
3. 컬럼 하나보다 쿼리 전체에서 시작하기
다음은 특정 회원의 최근 주문을 찾는 예시입니다.
CREATE INDEX idx_orders_user_created_id
ON orders (user_id, created_at DESC, id DESC);
SELECT id, created_at
FROM orders
WHERE user_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 20;user_id로 조회 범위를 좁히고 그 안에서 created_at, id 순서를 활용하는 설계입니다. 실제 채택 여부는 행 수·분포·기존 인덱스와 실행 계획으로 확인합니다.
복합 인덱스의 컬럼 순서와 SQL의 AND 조건 작성 순서는 다릅니다. (name, age) 인덱스를 사용할 때 WHERE age = 30 AND name = '홍길동'처럼 작성했다고 해서 선두 컬럼 조건이 사라지는 것은 아닙니다.
B-tree는 일반적으로 선두 컬럼의 동등 조건과 이어지는 범위 조건이 탐색 범위를 줄이는 데 중요합니다. 선두 조건이 없어도 DBMS·버전·분포에 따라 인덱스 스캔이나 skip scan을 사용할 수 있으므로 “절대 사용 불가”로 외우지 않습니다. PostgreSQL 복합 인덱스
4. 카디널리티와 선택도
카디널리티는 고유한 값의 수입니다. 실제 비용에는 특정 조건으로 얼마나 많은 행이 선택되는지가 더 직접적으로 작용합니다. 값이 두 종류뿐이어도 PENDING이 극히 적고 그 행만 반복 조회한다면 관련 인덱스가 유용할 수 있습니다.
“카디널리티가 높은 컬럼을 무조건 맨 앞에”라는 규칙보다 동등 조건·범위·정렬·함께 사용하는 쿼리를 봅니다. 지원하는 DB에서는 부분 인덱스도 후보지만, 쿼리 조건이 해당 인덱스의 조건과 맞아야 합니다.
함수 적용·암묵적 형변환·선행 % 패턴은 일반 B-tree의 효율적인 범위 탐색을 방해할 수 있습니다. 표현식 인덱스나 전문 검색 인덱스 등 대안이 있는지 확인합니다. OR, !=, NOT IN도 그 자체만으로 인덱스 사용 여부가 결정되지는 않습니다.
5. 제약 조건과 자동 인덱스
| 항목 | 주의할 차이 |
|---|---|
| PK·UNIQUE | 지원 인덱스가 만들어지는 경우가 일반적이지만 엔진별 동작 확인 |
| UNIQUE의 NULL | NULL 중복 처리 방식은 DBMS와 옵션에 따라 다름 |
| 외래 키의 참조받는 컬럼 | 참조 대상의 키·유일성 요건 확인 |
| 외래 키를 가진 자식 컬럼 | 자동 인덱스 생성 여부를 별도로 확인 |
PostgreSQL은 외래 키를 선언해도 자식 테이블의 참조 컬럼 인덱스를 자동 생성하지 않습니다. MySQL InnoDB는 외래 키 검사에 필요한 자식 인덱스가 없으면 생성합니다. “대부분의 DB가 FK 인덱스를 자동으로 만든다”는 가정 대신 실제 정의를 조회합니다. PostgreSQL 제약 조건, MySQL 외래 키
6. 커버링 인덱스의 조건
커버링은 해당 쿼리에 필요한 값이 인덱스에 포함된다는 의미입니다. 모든 쿼리를 커버하는 독립적인 인덱스 종류가 아닙니다. 넓은 컬럼을 무작정 추가하면 공간과 쓰기 비용이 커집니다.
PostgreSQL에서는 필요한 값이 모두 있어도 MVCC 가시성을 확인하려고 힙을 방문할 수 있습니다. Index Only Scan이라는 이름만 보지 말고 Heap Fetches와 버퍼 접근을 함께 확인합니다. 인덱스 전용 스캔
7. 적용과 관리 순서
- 느린 요청의 실제 SQL·파라미터·빈도·반환 행 수를 수집합니다.
- 실행 계획의 예상 행 수와 실제 행 수, 정렬·버퍼·테이블 접근을 비교합니다.
- 쿼리 조건과 기존 인덱스의 중복을 확인해 후보를 설계합니다.
- 대표 데이터에서 읽기 개선과 쓰기·저장 비용을 함께 측정합니다.
- 운영 적용 시 생성 방식·잠금·여유 공간·롤백 계획을 확인합니다.
사용량이 적은 인덱스를 삭제하기 전에는 집계 기간, 통계 초기화, 월말 배치와 제약 조건 의존성을 확인합니다. 정기 리빌드도 모든 DB에 일괄 적용하는 습관보다 실제 팽창·부하 근거가 우선입니다. 좋은 인덱스는 개수가 많은 인덱스가 아니라, 반복되는 요청의 비용을 충분히 줄여 유지 비용을 정당화하는 인덱스입니다.
