MySQL ‐ ORDER BY - woojin-playground/Backend-PlayGround GitHub Wiki

MySQL - ORDER BY

ORDER BY와 인덱스의 관계

• 단순히 ORDER BY를 쓰는 것은 쉽지만, 데이터가 수백만 건일 때 아무렇게나 정렬을 요청하면 DB 서버가 뻗을 수 있다.
• 최악의 상황 : Filesort(파일소트)가 발생한다.
  • 인덱스를 활용하지 못하면, DB는 조건에 맞는 데이터를 일단 다 메모리(또는 디스크)로 끌고 온 뒤, 자체적인 정렬 알고리즘(Quick Sort 등)을 돌려서 정렬한다.

-- (인덱스: stock_code, trade_date)
-- 종목은 찾았지만, 인덱스에 없는 거래량(trade_volume)으로 정렬하라고 요청한 경우
SELECT * FROM stock_trade_history 
WHERE stock_code = '005930' 
ORDER BY trade_volume DESC;

• 최고의 상황 : Index Sort (인덱스 정렬 타기)
  • 복합 인덱스는 종목코드로 모아두고 그 안에서 거래일자 순으로 이미 정렬을 해둔 상태이다.
  • 이미 정렬된 상태를 그대로 읽어오기만 하면 정렬 작업(Filesort)을 아예 생략할 수 있다.

ORDER BY와 LIMIT의 시너지(페이징/Top-N 쿼리)

• ORDER BY는 보통 가져올 개수를 제한하는 LIMIT와 함께 쓰인다.
• 이 떄, 인덱스 정렬을 타는지 여부에 따라 성능 차이가 생긴다.

A. 인덱스를 타지 못하는 Filesort + LIMIT의 경우

SELECT * FROM stock_trade_history 
WHERE stock_code = '005930' 
ORDER BY trade_volume DESC 
LIMIT 10;

• 삼성전자 데이터 2,500건을 모두 가져와서 정렬을 마친 후에야 상위 10개를 떼어서 주기 때문에 매우 비효율적이다.

B. 인덱스를 타는 Index Sort + LIMIT의 경우

SELECT * FROM stock_trade_history 
WHERE stock_code = '005930' 
ORDER BY trade_date DESC 
LIMIT 10;

• 데이터가 이미 날짜 순으로 정렬되어 있고 DB는 2,500건을 모두 다 보지 않는다.
• 인덱스 트리 끝에서부터 딱 10개의 레코드만 순서대로 읽고 바로 응답을 내어주기 때문에 엄청나게 빠르다.

트레이드오프

• ORDER BY는 데이터를 원하는 기준으로 정렬해준다.
• 하지만 실무에서는 무작정 ORDER BY를 걸기보다는 내가 정렬하려는 컬럼이 인덱스 순서와 일치하여 Using filesort를 피할 수 있는지를 꼭 확인해야한다.