동블

그래프 뷰

05. 실무 인덱스 설계 기준 상세

12 min read

이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.

  • 신규 API를 만들 때 인덱스를 어떤 순서로 검토해야 하는가?
  • 인덱스 추가 요청을 리뷰할 때 무엇을 확인해야 하는가?
  • 개인 프로젝트와 기업 운영 환경에서 인덱스 관리 수준은 어떻게 달라지는가?

개요

실무 인덱스 설계는 “느리니까 인덱스 추가”가 아니다. API 요구사항, SQL 패턴, 데이터 분포, 쓰기 부하, 배포 위험, 운영 지표를 함께 보는 의사결정이다.

인덱스는 코드와 달리 DB 전체에 영향을 준다. 잘못 추가한 인덱스는 즉시 티가 나지 않다가 쓰기 지연, 디스크 증가, 배치 시간 증가, DDL 장애로 돌아올 수 있다.

원리

실무 인덱스 설계의 원리는 “조회 하나를 빠르게 만드는 것”이 아니라 읽기 경로, 쓰기 경로, 배포 경로, 제거 경로를 함께 관리하는 것이다. 인덱스는 한 번 추가되면 모든 insert/update/delete와 backup, replication, disk usage에 영향을 주며, 나중에 제거하려면 실제 사용 여부를 증명해야 한다.

백엔드 개발자는 인덱스를 DDL 요청이 아니라 운영 변경 요청으로 봐야 한다. 어떤 API의 어떤 SQL을 지원하는지, 현재 증상이 무엇인지, 후보 인덱스가 어떤 정렬과 limit을 해결하는지, 쓰기 부하와 생성 중 lock은 어떤지까지 설명할 수 있어야 한다.

설계 순서

신규 API를 만들 때는 다음 순서로 본다.

  1. API가 실제로 필요한 데이터 모양을 정한다.
  2. 생성될 SQL을 확인한다.
  3. WHERE, JOIN, ORDER BY, LIMIT을 분리해서 본다.
  4. 이미 있는 인덱스와 겹치는지 확인한다.
  5. 예상 데이터량과 호출 빈도를 본다.
  6. 후보 인덱스로 실행 계획을 확인한다.
  7. 쓰기 부하와 배포 방식을 검토한다.

예를 들어 관리자 주문 검색 API가 있다.

SELECT id, member_id, status, created_at, total_price
FROM orders
WHERE status = 'PAID'
  AND created_at >= timestamp '2026-06-01'
  AND created_at < timestamp '2026-07-01'
ORDER BY created_at DESC, id DESC
LIMIT 50;

후보 인덱스는 다음과 같다.

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at DESC, id DESC);

하지만 이 API가 거의 호출되지 않는 관리자 기능이고 orders 쓰기가 매우 많은 서비스라면, 인덱스 추가가 항상 정답은 아니다. 호출 빈도, 데이터량, SLA, 운영 대안까지 본다.

인덱스 리뷰 체크리스트

인덱스 추가 PR이나 DDL 요청을 리뷰할 때는 다음 질문을 던진다.

  • 이 인덱스가 지원하려는 SQL은 무엇인가?
  • 그 SQL은 얼마나 자주 호출되고 얼마나 느린가?
  • 실행 계획상 현재 병목은 무엇인가?
  • 기존 인덱스와 중복되지는 않는가?
  • 복합 인덱스 컬럼 순서는 실제 조건과 정렬에 맞는가?
  • 쓰기 작업에 추가 비용을 얼마나 만들 것인가?
  • 대형 테이블이면 온라인 생성이 가능한가?
  • 실패하거나 오래 걸릴 때 중단할 수 있는가?

좋은 인덱스 요청은 보통 다음 정보를 포함한다.

대상 API: GET /api/members/{id}/orders
문제 SQL: member_id 조건 + created_at desc 정렬 + limit 20
현재 증상: p95 1.8s, orders 3천만 건
현재 계획: rows 약 120만, filesort 발생
후보 인덱스: (member_id, created_at desc, id desc)
예상 효과: member_id 구간에서 최신순 20건 탐색
배포 주의: 운영 대형 테이블이므로 온라인 인덱스 생성 필요

중복 인덱스 관리

중복 인덱스는 조용히 비용을 만든다.

CREATE INDEX idx_orders_member ON orders (member_id);
CREATE INDEX idx_orders_member_created ON orders (member_id, created_at);

두 번째 인덱스가 첫 번째 인덱스의 역할을 일부 대체할 수 있다. 하지만 항상 첫 번째를 삭제해도 된다는 뜻은 아니다. 인덱스 크기, 커버링 여부, 실행 계획, DB 엔진의 선택을 확인해야 한다.

인덱스 관리는 추가보다 제거가 어렵다. 제거 전에는 해당 인덱스를 사용하는 쿼리와 빈도를 확인하고, 가능하면 모니터링 기간을 둔다.

PostgreSQL에서는 다음처럼 인덱스 사용 통계를 확인할 수 있다.

SELECT schemaname,
       relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;

idx_scan이 낮다고 바로 삭제하지는 않는다. 드물지만 중요한 운영 쿼리, 제약을 위한 unique index, 장애 대응용 조회가 있을 수 있다. MySQL에서는 Performance Schema, sys schema, slow query digest, 실행 계획 이력을 함께 활용한다.

개인 프로젝트 기준

개인 프로젝트에서도 다음 수준은 챙기는 편이 좋다.

  • 모든 PK와 FK 후보를 명확히 둔다.
  • 로그인, 상세 조회, 목록 조회의 기본 인덱스를 설계한다.
  • Unique 제약으로 중복 가입, 중복 좋아요 같은 규칙을 막는다.
  • 주요 목록 API는 offset과 cursor 중 선택 이유를 문서화한다.
  • README나 기술 블로그에 “이 인덱스가 왜 필요한지”를 남긴다.

포트폴리오에서 단순 CRUD보다 인상적인 부분은 인덱스 이름 자체가 아니라 판단 과정이다. “회원별 주문 목록은 (member_id, created_at desc, id desc) 인덱스로 최신순 cursor pagination을 지원했다”처럼 설명할 수 있으면 좋다.

기업 운영 기준

기업 운영 환경에서는 인덱스가 배포 절차의 일부다.

  • DDL이 어떤 락을 잡는지 DB 버전별로 확인한다.
  • 트래픽 낮은 시간대 또는 온라인 DDL 도구를 검토한다.
  • replica lag와 백업 window에 미치는 영향을 본다.
  • 인덱스 생성 중 실패 시 중단과 재시도 절차를 둔다.
  • 추가 후 slow query, write latency, disk usage를 관찰한다.

인프라/DBA 입장에서는 개발자가 “인덱스 하나만 추가해주세요”라고 말하는 순간에도 운영 리스크를 계산해야 한다. 대형 테이블에서 인덱스 하나는 CPU, I/O, WAL/binlog, replica, backup, lock을 모두 흔들 수 있다.

실전 팁

  • 인덱스 이름은 테이블과 주요 컬럼을 드러내게 짓는다.
  • Unique 제약은 비즈니스 불변식 이름으로 설명한다.
  • 날짜 범위 목록 API는 created_at 단독보다 필터 컬럼과 함께 검토한다.
  • 삭제 예정 인덱스는 실제 사용량을 확인하고 단계적으로 제거한다.
  • 장애 대응 중 인덱스를 추가할 때는 임시 완화와 근본 해결을 분리한다.
  • 인덱스 삭제는 추가보다 더 보수적으로 한다. 사용량 통계, slow query, 배포 이력, DBA 의견을 함께 본다.
  • PostgreSQL partial index, expression index 같은 기능은 강력하지만 DBMS 이식성과 ORM migration 도구 지원을 확인한다.
  • 인덱스 설계 문서에는 “지원 SQL”, “만든 이유”, “제거 조건”을 남긴다.

위험 신호!

  • 인덱스 추가 근거가 “아마 빨라질 것 같아서”뿐이다.
  • 실행 계획 없이 운영 DDL을 요청한다.
  • 같은 테이블에 비슷한 인덱스가 10개 이상 쌓인다.
  • 쓰기 부하가 큰 테이블에 조회 편의를 위해 인덱스를 계속 붙인다.
  • 인덱스 생성 후 모니터링 계획이 없다.
  • 사용량이 낮다는 이유만으로 Unique 제약을 뒷받침하는 인덱스를 삭제하려 한다.
  • 장애 중 급히 만든 인덱스가 회고 없이 영구 구조로 남는다.
  • 인덱스 생성 성공만 보고 write latency, replica lag, disk 증가를 보지 않는다.

확인 질문

  • 인덱스 추가 요청에 포함되어야 할 최소 근거는 무엇인가?
    • 대상 SQL, 현재 증상, 실행 계획, 후보 인덱스, 예상 효과, 쓰기 비용과 배포 위험이다.
  • 개인 프로젝트에서 인덱스 설계 역량을 보여주려면 무엇을 설명해야 하는가?
    • 어떤 API의 어떤 조회 패턴을 위해 어떤 복합 인덱스를 선택했고, offset/cursor와 정렬 안정성을 어떻게 고려했는지 설명해야 한다.
  • 인프라/DBA가 인덱스 추가를 신중하게 보는 이유는 무엇인가?
    • 인덱스 생성과 유지가 DB CPU, I/O, 로그, 복제, 락, 저장 공간에 영향을 주기 때문이다.
  • 인덱스를 삭제하기 전에 확인해야 할 것은 무엇인가?
    • 실제 사용량 통계, 해당 인덱스가 지원하는 SQL과 제약, 대체 인덱스의 실행 계획, 장애 대응 경로, 삭제 후 모니터링 계획이다.

참고 문서

관련 문서