ORDER BY (정렬)

1. 정렬 서론

크게 두가지로 나뉜다.

1. 인덱스를 사용하는 방법

2. Filesort를 별도로 사용

 

인덱스 장점 - 인덱스 이미 정렬되어있어서 엄청 빠름

인덱스 단점 - 인덱스의 단점을 모두 공유

파일소트 장점 - 인덱스를 별도로 안만드는것, 정렬해야할 레코드가 많지 않으면 메모리에서 처리되므로 충분히 빠름

파일소트 단점 - 대상 레코드가 많아질 수록 느리다

 

 

모든 정렬이 인덱스를 이용하도록 하는건 사실상 어렵다.

• 정렬 기준이 너무 많아 요건별로 인덱스를 생성하기 어려움
• 그룹바이나 디스딩트의 결과를 처리
• UNION같은 임시 테이블의 결과를 정렬
• 랜덤하게 결과 레코드를 가져올때

 

2.  소트 버퍼

정렬을 수행하기위한 별도의 메모리 공간이다

세션 메모리 영역에 해당함 공유 불가의 공간

정렬이 필요한 공간때 할당되어 쿼리 실행 완료시 반납 됨

시스템 변수로 공간 크기 조정 가능

 

문제는 정렬해야할 레코드가 소트버퍼의 크기를 초과할 때

MySQL은 정렬해야할 레코드를 여러 조각으로 나눠서 처리 이 때 임시 저장을 위해 디스크 사용

 

멀티 머지 : 메모리 소트 버퍼에서 정렬 수행 -> 결과 임시에 디스크 기록 -> 다음 레코드 가져오기 무한 반복 -> 정렬된 레코드 병합하면서 다시 정렬

 

소트 버퍼는 공유불가능하기 때문에 크기를 너무 크게잡으면 예를들어 10MB로 잡으면 요청 1000개가 들어오면 메모리는 10GB증가

 

 

3. 파일소트 정렬 방식

1. 싱글 패스 : 정렬키, 레코드 전체, SELECT할 컬럼에 포함되어있으면 정렬에 불 필요해도 전부 들고와서 정렬한다. (최신 방식)

2. 투 패스 : 정렬키, 로우 ID사용, 정렬에 필요한 컬럼만 가져와 정렬하고 정렬끝난뒤에 다른 컬럼을 가져와서 붙인다. (예전 방식)

 

투패스는 두번의 I/O 발생

싱글패스는 더 많은 소트버퍼 공간 필요

 

일반적으로 현재 싱글패스를 선택하는데 투패스가 선택되는 경우

1. 레코드크기가 소트버퍼최대보다 클때

2. BLOB이나 TEXT컬럼이 SELECT에 포함될때

 

참고로 SELECT(*)가 비 효율적인 이유 : 소트버퍼를 비효율적으로 이용해서 꼭 필요한 것만 가져오도록 하자 임시 테이블에도 영향을 미친다.

 

 

4. 오더바이 정렬 방식

1. 인덱스를 이용한 정렬 - 스트리밍 방식

2. 조인에서 드라이빙 테이블만 정렬 - 실행계획에 Using filesort 표시 - 버퍼링 방식

3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 - 실행계획에 Using temporary; Using filesort 표시 - 버퍼링 방식

 

인덱스를 사용할 수 없는 경우 옵티마이저는 WHERE 조건에 일치하는 레코드를 정렬 버퍼에 저장하며 정렬을 처리함

이 때 두가지 방법

 

1. 조인의 드라이빙 테이블만 정렬한 뒤 다음 조인 수행

2. 조인 끝나고 일치하는 모든 레코드를 가져온 후 정렬 수행

 

조인이 수행되면 레코드가 불어나서 당연히 1번이 좋음

 

5. 인덱스를 이용한 정렬

조건

1. ORDER BY에 명시된 컬럼이 드라이빙 테이블에 속해야함

2. ORDER BY의 순서대로 생성된 인덱스가 있어야 함

3. B트리가 아닌 해시 인덱스는 사용불가

 

 

6. 조인의 드라이빙 테이블의 정렬 (파일소트)

조인이 되면 레코드의 건수가 불어나고 레코드의 크기도 늘어난다. 그래서 조인하기 전에 정렬하는게 좋음

 

조건

1. 드라이빙테이블의 칼럼만으로 ORDER BY를 작성해야 함

 

 

7. 임시 테이블을 통한 정렬

조인을 사용하지 않으면 발생하지 않는다.

파일소트의 조건을 벗어나게 되면 조인의 결과를 임시테이블에 저장하고 그 결과를 다시 정렬하게 됨

예를들면 ORDEY BY의 조건이 드리븐 테이블에 있는 경우다

실행계획에서 Using temporary; Using filesort가 출력됨

 

 

8. 정렬 처리 방식

LIMIT는 테이블이나 처리 결과의 일부만 가져와서 서버의 작업량을 줄여줌

그러나 ORDER BY나 GROUP BY는 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼 가져와서는 처리 불가능

조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그룹핑을 실행해야 LIMIT가 실행가능

두가지가 있다.

1. 스트리밍 처리

2. 버퍼링 처리

 

 

9. 스트리밍 방식

조건에 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해줌

쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간 보장

이런 쿼리에서는 LIMIT는 전체 실행시간을 줄여줌

 

참고로 JDBC는 MySQL이 스트리밍 방식으로 반환해도 내부 버퍼에 모아뒀다가 마지막 결과를 받아야 애플리케이션에 전달함

 

 

10. 버퍼링 방식

ORDER BY나 GROUP BY는 스트리밍 처리가 불가능 하다.

WHERE 조건에 일치하는 모든 레코드를 가져온 후 정렬하거나 그룹핑해야하고 차례대로 보내야하기 때문

MySQL에서는 모든 레코드를 검색하고 정렬 작업을 하는동안 클라이언트가 기다려야한다.

버퍼링방식은 일괄가공이기 때문에 LIMIT가 성능 향상에 도움이 안됨

네트워크로 전송되는 양은 줄이지만 MySQL 서버가 하는 작업량은 그대로