TIL

9.2(2) ORDER BY 처리

9.2.3 ORDER BY 처리 (Using filesort)

• 정렬 처리는 인덱스를 이용하는 방법과 Filesort라는 별도 처리를 이용하는 방법으로 나눌 수 있다.
• 인덱스 이용
  • 장점 - INSERT, UPDATE, DELETE 쿼리 실행 시 이미 인덱스가 정렬돼 있어서 순서대로 읽으면 돼서 매우 빠르다.
  • 단점 - INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업 때문에 느리고, 디스크 공간과 InnoDB 버퍼 풀 메모리가 더 필요하다.
• Filesort 이용
  • 장점 - 인덱스를 생성하지 않아도 되기에 인덱스 이용의 단점이 장점이 된다. 정렬할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.
  • 단점 - 정렬 작업이 쿼리 실행 시 처리되므로 레코드 수가 많아질수록 응답 속도가 느려진다.
• 인덱스를 이용한 정렬은 빠르지만 다음의 이유로 모든 정렬을 인덱스를 이용하게 할 수는 없다.
  • 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능
  • GROUP BY 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우
• MySQL에서 인덱스를 이용하지 않고 별도 정렬을 했다면 실행 계획의 Extra 칼럼에 ‘Using filesort’ 메시지가 표시된다.

소트 버퍼

• MySQL은 정렬을 하기 위해 별도 메모리를 할당 받는데 이를 소트 버퍼라고 한다.
  • 버퍼 크기는 레코드 크기에 따라 가변적이지만 sort_buffer_size 시스템 변수로 최대값을 설정할 수 있다.
  • 쿼리 실행이 완료되면 메모리 공간은 즉시 반납된다.
  • 레코드 건수가 적다면 소트 버퍼로 빠르게 정렬할 수 있다.
• 정렬할 레코드 건수가 소트 버퍼 공간보다 큰 경우
  • MySQL은 정렬할 레코드를 여러 조각을 나눠 처리한다.
  • 이 과정에서 임시 저장을 위해 디스크를 사용한다.
  • 각 조각들을 정렬해 디스크에 기록하고 최종적으로 레코드 조각들을 병합
  • 위 모든 작업이 디스크 IO를 유발한다.
• sort_buffer_size를 무조건 크게 설정하면 안 되는 이유
  • 소트 버퍼 최대값이 커지면 정렬이 모두 메모리에서 처리되니 빨라질 수도 있지만 실제로는 그렇지 않다.
  • 실제로 어떤 실험에서는 16KB ~ 64MB 소트 버퍼 크기 중 256KB~8MB 사이에서 최적의 성능을 보였다.
  • MySQL의 글로벌 메모리 영역과 세션(로컬) 메모리 영역 중 소트 버퍼는 세션 메모리 영역이기 때문에 정렬 작업이 많아질수록 소트 버퍼 메모리 공간이 커져 운영체제에서 메모리 부족 현상이 일어날 수도 있다.

정렬 알고리즘

• 공식적인 명칭은 아니지만 정렬 모드를 싱글 패스와 투 패스 정렬 모드로 나눌 수 있다.
• 정확히는 MySQL 정렬 방식은 다음의 3가지가 있다.
  • <sort_key, rowid>: 정렬 키와 레코드의 row id만 가져와서 정렬 (투 패스)
  • <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와 정렬하는데 레코드 칼럼들은 고정 사이즈로 메모리 저장 (싱글 패스)
  • <sort_key, packed_additional_fileds>: 정렬 키와 레코드 전체를 가져와 정렬하는데 레코드 칼럼들은 가변 사이즈로 메모리 저장 (싱글 패스)
• 싱글 패스 정렬 방식
  • 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상 칼럼 전부를 담아 수행하는 정렬 방식
  • 최신 버전에서는 일반적으로 싱글 패스 방식을 사용한다.
  • 더 많은 소트 버퍼 공간이 필요하다.
• 투 패스 정렬 방식
  • 정렬 대상 칼럼과 프라이머리 키만 소트 버퍼에 담아 정렬을 수행 후, 정렬 순서대로 프라이머리 키로 테이블을 읽어 결과를 반환하는 방식
  • 싱글 패스 도입 이전에 사용하던 방식이지만 8.0 버전에서도 특정 조건에서는 투 패스 정렬 방식을 사용한다.
    • 레코드 크기가 max_length_for_sort_data 시스템 변수의 값보다 클 때
    • BLOB이나 TEXT 타입 칼럼이 SELECT 대상에 포함될 때

정렬 처리 방법

ORDER BY가 사용되면 다음 3가지 처리 방법 중 하나로 정렬이 처리된다.

정렬 처리 방법	실행 계획의 EXTRA 칼럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인의 드라이빙 테이블만 정렬	Using filesort
조인 결과를 임시 테이블로 저장 후 정렬	Using temporary; Using filesort

정렬 처리 방법1 - 인덱스를 이용한 정렬

• 인덱스를 이용한 정렬을 위해서 필요한 조건
  • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속해야 한다.
  • ORDER BY 순서대로 생성된 인덱스가 있어야 한다.
  • WHERE 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
  • B-Tree가 아닌 인덱스에서는 인덱스 정렬을 할 수 없다.
  • 조인을 사용한다면 네스티드-루프 방식 조인에서만 이 방식을 사용할 수 있다.
• 인덱스 정렬대로 그대로 읽어오기 때문에 ORDER BY가 있든 없든 결과는 똑같다.

정렬 처리 방법2 - 조인의 드라이빙 테이블만 정렬

• 일반적으로 조인이 수행되면 레코드 건수가 몇 배로 불어나고 레코드 하나하나의 크기도 늘어나기에 조인 전 드라이빙 테이블만 먼저 정렬하는 것이 좋다.
• 드라이빙 테이블만 먼저 정렬하기 위한 조건
  • 드라이빙 테이블 칼럼만으로 ORDER BY 절을 작성해야 한다.
  • 정렬 조건 칼럼으로 인덱스가 만들어져 있었다면 인덱스를 이용한 정렬을 했겠지만 그 경우가 아닌 경우

정렬 처리 방법3 - 임시 테이블을 이용한 정렬

• 2개 이상의 테이블을 조인한 결과를 정렬해야 한다면 임시 테이블이 필요
• 정렬할 레코드 수가 가장 많기에 가장 느린 정렬 방법이다.
• 인덱스 정렬도 이용할 수 없고 ORDER BY 정렬 조건이 드라이빙 테이블 칼럼이 아니면 임시 테이블을 사용하게 된다.

정렬 처리 방법의 성능 비교

쿼리가 처리되는 방법을 다음의 2가지 방식으로 구분할 수 있다.

• 스트리밍 방식
  • 서버에서 처리할 데이터가 얼마인지에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트에 전송해주는 방식
  • 원했던 첫 번째 레코드를 빠르게 전달받을 수 있기에 응답 시간이 빨라진다.
  • LIMIT 조건을 추가하면 레코드 건수가 줄어들기에 마지막 레코드를 가져오기까지의 시간까지 상당히 줄일 수 있다.
• 버퍼링 방식
  • ORDER BY나 GROUP BY는 스트리밍 방식을 사용할 수 없다.
    • WHERE 조건에 일치하는 모든 레코드를 가져와 정렬하거나 그루핑해야 하기 때문
  • 모든 레코드를 검색하고 정렬 등을 하는 동안 클라이언트는 기다려야 하기에 응답 속도가 느리고 이 방식을 버퍼링 방식이라고 한다.
  • 버퍼링 방식으로 처리하면 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진에서 가져올 때까지 기다려아 한다.
  • LIMIT가 있어도 MySQL 서버의 작업량에는 그다지 변화가 없다.
    • 네트워크로 전송되는 레코드 건수는 줄일 수 있어도
• 정렬 방법 중 인덱스를 사용한 정렬 방식만 스트리밍으로 처리되며 나머지는 버퍼링된 후 정렬된다.

JDBC 라이브러리를 이용하는 경우 MySQL 서버로부터 스트리밍 방식으로 결과를 전달 받아도 받은 레코드를 내부 버퍼에 담아두었다가 클라이언트 애플리케이션에게 모든 결과를 한 번에 반환한다. 즉 JDBC 자체적으로 버퍼링을 하는 것이다. JDBC가 자체 버퍼링을 하는 이유는 이 방식이 전체 처리(Throughput) 시간이 짧고 MySQL과의 서버 통신 횟수가 적어 자원 소모가 줄어들기 때문이다. JDBC 버퍼링은 기본 작동 방식이며, 아주 대량의 데이터를 가져와야 할 때는 스트리밍 방식으로 변경할 수도 있다.

정렬 관련 상태 변수

• MySQL 서버는 처리하는 주요 작업에 대해 해당 작업의 실행 횟수를 상태 변수로 저장한다.

FLUSH STATUS;
SHOW STATUS LIKE ‘Sort%’;

• Sort_merge_passes: 멀티 머지 처리 횟수
  • 소트 버퍼 크기가 모자랐을 때 조각낸 레코드들을 병합 정렬한 횟수
• Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 횟수
• Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 횟수
• Sort_rows: 지금까지 정렬한 전체 레코드 건수