5.2 ORDER BY 처리(USING FILESORT)

정렬 처리

INDEX 이용

• DML처리시 인텍스가 정렬돼 있으므로 빠르지만 부가적인 인덱스 추가 및 디스크 공간 필요.

Filesort

• 인덱스를 생성하지 않으므로 인덱스의 단점이 보완되지만 레코드가 많으면 느려진다.

인덱스를 이용하는 정렬이 힘든 경우.

1. 정렬기준 많음
2. Group BY, DISTINCT
3. 임시테이블 결과(UNION)
4. 랜덤한 레코드

5.2.1 소트 버퍼 (Sort buffer)

1. 정렬 처리시에 필요한 메모리 공간으로 정렬이 완료되면 시스템으로 반납됨.
2. sort_buffer_size, aria_sort_buffer_size 변수로 설정가능.
3. 멀티머지(Multi merge)횟수를 줄이기 위해 소트버퍼를 크게 해도 차이는 없음.(저자추천 256KB~512KB)
4. 소트버퍼는 세션메모리 영역으로 작업이 많거나 커넥션이 많으면 OOM-Killer에 의해 종료당 할 수 있음.

5.2.2 정렬 알고리즘

SQL 예시

SELECT emp_no, first_name, last_name
  FROM employees
 ORDER BY first_name;

1. 싱글패스(single pass)

• MySQL 5.0이후 최신 버전에 도입됨.
• emp_no, first_name, last_name 모두 읽어 정렬.
• 건수가 작을 때 유리.

2. 투패스(two pass)

• 정렬에 필요한 first_name 칼럼과 키인 emp_no만 읽어서 정렬을 수행하고 다시 last_name 읽음.
• 건수가 클 때 유리.
• 팁 : 따라서 컬럼을 항상 지정하는 습관이 좋다. *는 자제하도록 하자.

5.2.3 정렬의 처리 방식

5.2.3.1 인덱스를 사용한 정렬

• SQL 예시

SELECT * FROM employees e, salaries s
 WHERE 1=1
   AND s.emp_no=e.emp_no
   AND e.emp_no BETWEEN 100002 AND 100020
 ORDER BY e.emp_no；

계획 코멘트 없음

• 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고，ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
• 완벽한 조건을 요구하는 만큼 처리는 빠르며 유일한 스트리밍 방식이다.
• 팁 : 인덱스 사용한다고 어차피 정렬된다는 생각에 ORDER BY를 제외하면 안된다.
  경험상(본인) 기계는 매우 멍청하다. 정확한 지정이 필요하다.

5.2.3.2 드라이빙 테이블만 정렬

• SQL 예시

SELECT *
  FROM employees e, salaries s
 WHERE 1=1
   AND s.emp_no=e.emp_no
   AND e.emp_no BETWEEN 100002 AND 100010
 ORDER BY e.last_name；

Using filesort

1. emp_no BETWEEN 100002 AND 100010 키 활용으로 범위 검색
2. last_name로 정렬.(드라이빙 테이블)
3. 차례대로 조인하며 결과 도출.
4. 버퍼링 방식.

5.2.3.3 임시 테이블을 이용한 정렬

• SQL 예시

SELECT *
  FROM employees e, salaries s
 WHERE 1=1
   AND s.emp_no=e.emp_no
   AND e.emp_no BETWEEN 100002 AND 100010
 ORDER BY s.salary;

Using filesort, Using temporary

1. ORDER BY s.salary가 드리븐 테이블 컬럼이므로 일단 조인 후 임시 테이블에서 정렬.
2. 버퍼링 방식.

5.2.3.4 정렬 방식의 성능 비교

1. 인덱스 활용하도록 유도. 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도.
2. 업무설계시 애초에 테이블을 잘 만드는 것이 더 좋을 것으로 보임.

5.2.4 ORDER BY .. LIMIT n 최적화

• SQL 예시

SELECT emp_no, first_name, last_name
  FROM employees
 WHERE 1=1
   AND emp_no BETWEEN 10001 AND 10900
 ORDER BY last_name
 LIMIT 10;

1. 우선순위 큐(Priority Queue) 한다는 말이다.
2. 900개 정렬하는 것이 아니라 큐에 들어오는 10개만 정렬한다.

5.2.5 정렬 관련 상태 변수

1. Sort_merge_passes는 멀티 머지 처리 횟수
2. Sort_range는 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 ?수.
3. Sort_scan은 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수.
4. Sort_scan과 Sort_range는 둘 다 정렬작업 횟수를 누적하고 있는 상태 값.
5. Sort_rows는 지금까지 정렬한 전체 레코드 건수를 의미.