Decide whether to add an index

1. 기존 인덱스 커버리지

idx_orders_user_id는 새 복합 인덱스가 생기면 선두 컬럼 prefix로 대체할 수 있습니다. idx_orders_created_at은 현재 제시된 쿼리만 보면 대체 사용처가 없습니다.

2. 추가할 인덱스

1) 주문 목록

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

• user_id, status: 동등 조건이므로 앞에 배치
• created_at DESC: ORDER BY ... DESC LIMIT 20을 인덱스 순회로 처리
• user_id가 보통 고객 식별자로 충분히 선택적이므로 status보다 앞이 적절합니다.
• status는 결제 상태 변경 때마다 갱신되므로 이 인덱스는 잦은 업데이트 비용이 있습니다.

SELECT *이므로 일반적인 covering index로 만들기는 어렵습니다. 모든 주문 컬럼을 INCLUDE하는 것은 인덱스가 지나치게 커질 가능성이 높아 권하지 않습니다.

2) 스토어 매출 집계

CREATE INDEX idx_orders_store_created
ON orders (store_id, created_at);

가능한 DB라면 total_price를 포함하는 covering 형태가 더 좋습니다.

-- PostgreSQL 예시
CREATE INDEX idx_orders_store_created
ON orders (store_id, created_at)
INCLUDE (total_price);

store_id로 먼저 좁힌 뒤 created_at 범위를 검색해야 하므로 이 순서가 필요합니다. total_price를 포함하면 테이블 본문 접근 없이 SUM을 계산할 수 있지만, 인덱스 크기와 쓰기 비용은 증가합니다.

3) 상품 리뷰 조회

is_hidden = false가 항상 고정된 조건이라면 partial index가 적합합니다.

CREATE INDEX idx_reviews_visible_product_created
ON reviews (product_id, created_at DESC)
WHERE is_hidden = false;

partial index를 지원하지 않거나 쿼리 패턴이 바뀔 수 있다면 다음을 사용합니다.

CREATE INDEX idx_reviews_product_hidden_created
ON reviews (product_id, is_hidden, created_at DESC);

is_hidden의 true 비율이 높을수록 partial index의 이점이 큽니다. SELECT *이므로 리뷰 전체 행을 포함하는 covering index는 대체로 비경제적입니다.

4) 고객센터 전화번호 검색

CREATE INDEX idx_orders_phone_number
ON orders (phone_number);

전화번호가 사실상 고객별로 유일하고 데이터 품질상 보장된다면 UNIQUE 인덱스도 검토할 수 있습니다. 반대로 동일 전화번호가 여러 주문에 반복되고 조회 선택도가 낮다면 실행 계획에서 인덱스를 사용하지 않을 수 있습니다. 현재 정보만으로는 전화번호의 선택도를 알 수 없습니다.

3. 쓰기 비용

orders의 평균 일일 변경량은 다음과 같습니다.

• INSERT: 약 8,000건
• 결제 상태 UPDATE: 약 24,000건
• 합계: 약 32,000건의 인덱스 유지 이벤트/일

orders에 제안하는 세 인덱스는 각각 다음 비용이 있습니다.

• (user_id, status, created_at): INSERT 8,000회 + 상태 변경 UPDATE 약 24,000회에서 유지 비용 발생
• (store_id, created_at): INSERT 시 유지 비용 발생. 두 컬럼이 변경되지 않는다면 일반 UPDATE에는 보통 영향이 작음
• (phone_number): INSERT 시 유지 비용 발생. 전화번호 변경 시에도 비용 발생

특히 status가 복합 인덱스에 들어가므로 결제 상태 변경 때 인덱스 엔트리를 삭제하고 다시 삽입해야 합니다. 이는 in-place row update를 방해할 수 있고, 피크 시간대인 1113시와 1820시에 random I/O, WAL/redo, 페이지 분할, 잠금 경합을 키울 수 있습니다.

리뷰 partial index는 리뷰 INSERT와 is_hidden 변경 때만 유지됩니다. 리뷰의 숨김 처리도 빈번하다면 partial 여부와 실제 변경량을 함께 측정해야 합니다.

4. 삭제 후보

삭제 권장

DROP INDEX idx_orders_user_id;

idx_orders_user_id는 (user_id, status, created_at)의 선두 prefix로 대체됩니다. 별도 쿼리가 user_id만 사용하는 경우에도 복합 인덱스가 선두 컬럼을 제공하므로 보통 중복입니다.

DROP INDEX idx_orders_created_at;

제시된 쿼리만 기준으로는 단독 created_at 인덱스를 사용하는 쿼리가 없습니다. 다른 기간별 주문 조회나 최근 주문 조회가 없다면 삭제 대상입니다.

삭제 전에는 실제 사용 여부를 확인해야 합니다. 운영 환경에서 pg_stat_user_indexes, MySQL의 performance_schema, 또는 쿼리 로그/실행 통계를 확인하십시오.

5. 크기와 메모리

정확한 크기는 현재 행 수, 평균 키 길이, DB 엔진, 페이지 크기, 중복도 정보가 없어 계산할 수 없습니다.

대략적인 산정은 다음과 같습니다.

• 일반 B-tree 인덱스: 행당 약 수십 바이트
• (user_id, status, created_at): orders 행당 대략 40~80바이트 수준
• (store_id, created_at) + total_price 포함: 대략 50~100바이트 이상
• phone_number: 전화번호 길이에 따라 대략 30~70바이트
• 리뷰 partial index: 전체 리뷰가 아니라 is_hidden = false인 행에만 크기 발생

orders에 백만 행이 있다면 새 인덱스 3개만 대략 수백 MB가 될 수 있습니다. 실제 값은 pg_relation_size, SHOW TABLE STATUS, 또는 DB별 인덱스 크기 도구로 확인해야 합니다.

인덱스 전체가 메모리에 남는다고 가정하면 안 됩니다. 자주 실행되는 상위 인덱스와 최근 데이터가 버퍼 풀에 남을 가능성이 높고, 매출 집계용 범위 스캔은 기간이 넓으면 디스크 접근을 발생시킬 수 있습니다. 다음을 측정해야 합니다.

• EXPLAIN (ANALYZE, BUFFERS) 또는 DB별 실행 계획
• 인덱스 크기와 버퍼 풀/캐시 적중률
• 피크 시간대의 디스크 I/O, WAL/redo량, CPU
• 상태 UPDATE의 평균 지연과 lock wait

최종 권고 순서

1. 추가

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

CREATE INDEX idx_orders_store_created
ON orders (store_id, created_at);

CREATE INDEX idx_reviews_visible_product_created
ON reviews (product_id, created_at DESC)
WHERE is_hidden = false;

CREATE INDEX idx_orders_phone_number
ON orders (phone_number);

단, 전화번호의 선택도가 낮으면 추가하지 않고 실행 계획으로 확인합니다.

2. 검증 후 삭제

DROP INDEX idx_orders_user_id;
DROP INDEX idx_orders_created_at;

각 인덱스가 실제로 사용되지 않는다는 인덱스 사용 통계가 확인된 뒤 삭제합니다.

3. 그대로 유지

• orders의 PK(id)
• 새로 추가하는 복합 인덱스
• 전화번호 인덱스는 선택도가 충분할 때만 유지

user_id, store_id, phone_number, 리뷰의 product_id별 행 수와 실제 쿼리의 EXPLAIN ANALYZE 결과를 보면 최종 인덱스 순서와 partial/covering 여부를 확정할 수 있습니다.