Sort 처리에 대하여

쿼리 결과를 정렬하기 위해서는 ORDER BY 구문을 사용한다.

mysql> SELECT * FROM article ORDER BY created_at DESC LIMIT 10;

created_at 컬럼이 인덱스로 존재하는 경우는 추가적인 처리가 필요하지 않다.
인덱스는 정렬된 데이터가 격납되어 있기 때문에 선두부터 차례대로 읽기만 하면 되기 때문이다.

MySQL에서는 인덱스가 아닌 컬럼의 정렬을 몇가지 최적화 하고 있다.
어떠한 최적화를 하고 있는 지 알아보기로 하자.

Sort 처리 동작

MySQL은 ORDER BY 구문의 조건이 인덱스로 해결될 수 있는 지 확인한다.

인덱스로 해결 되었는 지 확인하는 방법은 EXPLAIN 으로 가능하다.

Extra열에 Using filesort가 기술되어 있지 않으면 인덱스로 해결된 것이고, 기술 되어 있는 경우는 후술 정렬 처리가 실행되고 있는 것이다.

해당 정렬 처리는 2종류가 있다. 

• 종래의 방식: 정렬하는 컬럼과 row ID를 이용해 quick sort처리를 한 후, 정렬된 순서를 유지하면서 row ID로 부터 해당 row를 읽어 들인다.
• 새로운 방식: MySQL 5.6에서 추가되었다. row ID가 아니라 SELECT로 지정한 컬럼들을 소트버퍼에 채우면서 sort처리를 한다. 정렬이 완료되면 순서대로 읽기만 하면 된다. 필요한 컬럼을 row ID를 이용해서 습득하지 않아도 소트버퍼에 해당 컬럼들이 존재하기 때문에 직접 읽어들일 수 있기 때문이다.

선택방법

어떤 방식을 옵티마이저가 선택할지는 max_length_for_sort_data 시스템 변수에 따라 바뀐다.
소트버퍼에 채우는 소트 키 및 SELECT로 지정된 컬럼의 합계 사이즈가 이 변수보다 작으면 새로운 방식을 채용한다.

* MySQL 8.0.20 에서 해당 변수가 deprecated 되었고, 항상 새로운 방식을 사용하고 있는 거 같다.

확인방법

어떤 방식이 선택 되었는 지 확인하기 위해서는 optimizer_trace의 file_summary에서 알 수 있다.

- 종래의 방식: sort_mode키의 값이 <sort_key, rowid>

"filesort_summary": {

    "rows": 16777217,
    "examined_rows": 16777217,
    "number_of_tmp_files": 11268,
    "sort_buffer_size": 32760,
    "sort_mode": "<sort_key, rowid>"

}

- 새로운 방식: sort_mode키의 값이 <sort_key, additional_fields>

"filesort_summary": {
    "rows": 16777217,
    "examined_rows": 16777217,
    "number_of_tmp_files": 16385,
    "sort_buffer_size": 32768,
    "sort_mode": "<sort_key, additional_fields>"
}

# Refer

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_length_for_sort_data