MySQL filesort 알고리즘

 

 

 

MySQL filesort 알고리즘

 

 

ORDER BY/GROUP BY 처리에 인덱스를 사용하지 못하는 경우, MySQL은 Filesort 알고리즘을 통해 데이터를 정렬한다.
Filesort 알고리즘을 사용하게 되면 쿼리 실행플랜 Extra 필드에 Using filesort 구문이 나오게 되며, 이런 경우 대체로 쿼리 처리 비용이 증가하게 된다.

Filesort 동작 방식과 Filesort와 관련된 Variable, Status 항목을 정리해 보면 아래와 같다.

 

 

 

 

 

(1) 데이터 Block에 대한 Scan 및 WHERE Filter 처리후 조건에 맞는 row를 Sort Buffer에 저장
     정렬 조건 컬럼 값과 데이터 포지션 정보를 Buffer에 저장하며, Clustered Index 구조를 사용하는 InnoDB의 경우 PK값이 Buffer에 함께 저장됨

     (sort_buffer_size 환경 변수로 지정)

(2) Sort Buffer가 가득차면, Quick Sort 처리하여 결과를 별도의 임시 파일에 저장하며, 위 과정을 조건에 부합하는 모든 row를 다 읽을때까지 반복

      (임시 파일은 실제 물리적인 파일이 아님)

(3) 생성된 임시 파일들을 Merge

      Merge 횟수는 sort_merge_pass 상태 변수에 카운트 돠며, sort_buffer_size가 작은 경우, sort_merge_pass 상태 변수 값이 커짐

(4) Merge 완료된 결과는, 정렬 조건 순서대로 데이터 포지션 정보를 가지고 있음
      결과 파일 순서대로 데이터 Block을 읽는 경우 많은 Random I/O를 유발되므로, 

      별도의 버퍼(read_rnd_buffer 환경 변수)에서 결과 파일의 위치 정보를 정렬하여 Random I/O를 Sequential I/O로 유도

이 Filesort 알고리즘은 데이터 Block에 대한 Access가 중복으로 발생한다는 단점이 있다.
이 문제를 해결하기 위해 MySQL 4.1 이후부터는 개선된 Filesort 알고리즘을 추가로 사용한다.
Sort Buffer에서 정렬 수행시 데이터 포지션 뿐만 아니라, SELECT 쿼리가 요청한 컬럼의 내용도 같이 처리를 하는 것이다.
Sort Merge가 완료되면 추가적인 데이터 Access 없이 Result Set을 생성할 수 있다.

일반적인 경우에는 개선된 Filesort 알고리즘이 더 빠르지만,
추가 처리되는 컬럼의 크기가 길면, Sort Buffer의 활용도가 떨어지고 Sort Merge의 부담이 발생하여 오히려 기존 Filesort 알고리즘에 비해 더 느려지게 된다.
그래서 아래 조건을 만족하는 경우에만 개선된 Filesort 알고리즘이 사용되고, 그렇지 않으면 원래 Filesort 알고리즘이 그대로 사용된다.
  - SELECT 쿼리가 요청한 컬럼이 TEXT / BLOB이 아닐 것
  - SELECT 쿼리가 요청한 컬럼의 사이즈 합이 max_length_for_sort_data 환경 변수 값 이하 (기본값 1KB)

[결론]
가급적 Filesort를 피하는 것이 좋으나, 불가피하게 Filesort를 해야 하는 경우 불필요하게 SELECT 하는 컬럼을 줄이거나 불필요한 BLOB/TEXT 사용을 자제하면 성능 향상을 얻을 수 있다.

[유의사항]

sort_buffer_size가 불필요하게 큰 경우에는 오히려 성능 저하 발생 (default 값을 사용해도 무방)

 

 

 

Posted by  김득은

 

자료 출처 : http://seuis398.blog.me/70039224614