이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.
ORDER BY는 왜 인덱스 설계와 함께 봐야 하는가?- 정렬이 실행 계획에서 병목이 되는 신호는 무엇인가?
- 최신순, 인기순, 관련도순은 왜 서로 다른 데이터 구조를 요구하는가?
개요
목록 API의 성능은 필터보다 정렬에서 무너지는 경우가 많다. WHERE로 행을 찾은 뒤 많은 행을 정렬하고 LIMIT으로 일부만 반환하면, DB는 이미 많은 일을 한 뒤에야 결과를 줄인다.
좋은 인덱스는 필터와 정렬을 함께 지원한다.
원리
다음 쿼리를 보자.
SELECT id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC, id DESC
LIMIT 20;다음 인덱스가 있으면 DB는 board 구간에서 최신순으로 앞부분을 읽을 수 있다.
CREATE INDEX idx_posts_board_created_id
ON posts (board_id, created_at DESC, id DESC);인덱스가 없으면 많은 게시글을 찾고 정렬한 뒤 20개만 반환할 수 있다. 데이터가 작을 때는 차이가 안 나지만 운영에서는 p95/p99 지연으로 드러난다.
실행 계획에서는 최종 반환 row 수보다 정렬 전에 읽은 row 수를 본다. LIMIT 20이 있어도 DB가 50만 row를 읽고 정렬한 뒤 20개만 반환한다면, 사용자는 20개만 받지만 DB는 이미 큰 비용을 쓴 것이다.
정렬 방향
DB는 인덱스를 정방향 또는 역방향으로 읽을 수 있다. 그러나 복합 정렬에서 방향 조합은 DB별 세부 동작을 확인해야 한다.
ORDER BY created_at DESC, id DESC정렬 방향과 인덱스 정의가 맞으면 계획이 단순해질 수 있다.
CREATE INDEX idx_posts_created_id_desc
ON posts (created_at DESC, id DESC);실무에서는 정확한 동작을 실행 계획으로 확인한다.
정렬 옵션이 많아질수록 인덱스 설계는 어려워진다.
최신순: created_at desc
댓글순: comment_count desc
조회순: view_count desc
가격낮은순: price asc모든 조합에 복합 인덱스를 만들면 쓰기 비용과 저장 공간이 폭증한다. 호출 빈도, SLA, 데이터량을 기준으로 핵심 정렬만 최적화하고, 나머지는 관리자 기능으로 제한하거나 별도 ranking/read model을 검토한다.
정렬 병목 신호
PostgreSQL에서는 실행 계획에 Sort가 크게 보일 수 있다. MySQL에서는 Using filesort, Using temporary가 단서가 될 수 있다.
EXPLAIN
SELECT id, title
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY view_count DESC
LIMIT 50;view_count 인기순 정렬은 최신순과 다른 요구사항이다. 인기순이 자주 호출되면 (status, view_count desc, id desc) 인덱스, 별도 랭킹 테이블, 캐시를 검토한다.
PostgreSQL에서는 Sort Method, Disk, temp file 로그가 정렬 spill의 단서가 될 수 있다. MySQL에서는 Using filesort, Using temporary, sort merge passes 같은 지표를 본다. 정렬이 디스크로 밀리면 API 지연뿐 아니라 같은 DB를 쓰는 다른 요청의 I/O까지 흔들 수 있다.
정렬 요구사항 구분
정렬 기준마다 비용과 구조가 다르다.
- 최신순:
created_at,id인덱스로 처리하기 쉽다. - 인기순: 카운터 업데이트와 정렬 인덱스가 함께 필요하다.
- 관련도순: full-text search나 검색 엔진의 ranking이 필요할 수 있다.
- 랜덤순: 대용량에서 매우 비쌀 수 있다.
- 사용자별 맞춤순: 추천 시스템이나 precomputed feed가 필요할 수 있다.
제품 요구사항에서 “정렬만 바꾸면 되죠?”라고 말해도 DB 입장에서는 완전히 다른 문제일 수 있다.
실전 팁
- 목록 API 인덱스는
WHERE + ORDER BY + LIMIT을 한 세트로 본다. - 정렬 안정성을 위해
id를 tie-breaker로 둔다. - count 쿼리와 본문 쿼리의 정렬 필요성을 분리한다.
- 인기순은 hot row 카운터와 정렬 인덱스를 함께 고려한다.
- 관련도순은 DB 기본 정렬보다 검색 도구 요구사항으로 본다.
- 정렬 옵션은 제품 요구와 호출 빈도 기준으로 제한한다. 모든 옵션을 같은 수준으로 빠르게 만들 필요는 없다.
- 실행 계획에서
LIMIT이전에 읽고 정렬한 row 수를 본다. - sort/temp 지표가 늘면 단순 쿼리 문제가 아니라 DB memory/I/O 경쟁 문제로 본다.
위험 신호!
- 실행 계획에 큰 Sort가 있는데 무시한다.
- 사용자가 정렬 옵션을 여러 개 선택할 수 있는데 모든 조합을 같은 쿼리로 처리한다.
- 인기순 정렬을 실시간 count update에만 의존한다.
- 랜덤 추천을
ORDER BY random()으로 대형 테이블에서 수행한다. - cursor pagination의 정렬 컬럼이 변경 가능하다.
- 정렬 옵션을 계속 추가하면서 인덱스와 쓰기 부하 예산을 검토하지 않는다.
LIMIT 20만 보고 정렬 전 처리 row 수를 확인하지 않는다.- sort가 디스크로 spill되는 신호를 API 로그만 보고 놓친다.
확인 질문
ORDER BY가 인덱스와 함께 설계되어야 하는 이유는 무엇인가?- 인덱스가 원하는 순서로 데이터를 제공하면 DB가 많은 행을 별도로 정렬하지 않고 limit만큼 읽을 수 있기 때문이다.
- 최신순과 인기순 정렬이 다른 설계를 요구하는 이유는 무엇인가?
- 최신순은 생성 시각 기준 정렬이지만 인기순은 카운터 갱신, hot row, 랭킹 계산과 연결되기 때문이다.
- 정렬 병목을 실행 계획에서 어떻게 의심할 수 있는가?
- PostgreSQL의 큰 Sort, MySQL의
Using filesort나Using temporary, 많은 rows와 높은 실행 시간이 단서다.
- PostgreSQL의 큰 Sort, MySQL의
- 정렬 옵션을 여러 개 제공할 때 인덱스를 무작정 늘리면 안 되는 이유는 무엇인가?
- 각 정렬 조합에 맞춘 인덱스는 쓰기 비용, 저장 공간, DDL 위험을 늘리므로 호출 빈도와 SLA가 높은 정렬만 우선 최적화해야 하기 때문이다.