MySQL5.6 ORDER BY 최적화

8.2.1.15 ORDER BY 최적화

경우에 따라 MySQL은 인덱스를 사용하여 특별한 정렬을하지 않고 ORDER BY 절을 만날 수 있습니다.

인덱스의 모든 사용되지 않는 부분과 모든 특별한 ORDER BY 컬럼이 WHERE 절에 상수 인 한, ORDER BY 가 인덱스에 완전하게 일치하지 않는 경우에도 인덱스를 사용할 수 있습니다. 다음 쿼리는 인덱스를 사용하여 ORDER BY 부분을 해결합니다.

 SELECT * FROM t1
   ORDER BY key_part1 , key_part2 , ...;

 SELECT * FROM t1
   WHERE key_part1 = constant
   ORDER BY key_part2 ;

 SELECT * FROM t1
   ORDER BY key_part1 DESC, key_part2 DESC;

 SELECT * FROM t1
   WHERE key_part1 = 1
   ORDER BY key_part1 DESC, key_part2 DESC;

 SELECT * FROM t1
   WHERE key_part1 > constant
   ORDER BY key_part1 ASC;

 SELECT * FROM t1
   WHERE key_part1 < constant
   ORDER BY key_part1 DESC;

 SELECT * FROM t1
   WHERE key_part1 = constant1 AND key_part2 > constant2
   ORDER BY key_part2 ;

경우에 따라 MySQL은 WHERE 절에 일치하는 행을 찾기 위해 인덱스를 사용하여도 ORDER BY 를 해결하기 위해 인덱스를 사용하지 못할 수 있습니다. 이러한 경우에는 다음과 같은 것이 포함됩니다.

다양한 키에 대해서 ORDER BY 를 사용합니다.
```
 SELECT * FROM T1 ORDER BY key1 , key2 ;
```

키 인접하지 않은 부분에 대해 ORDER BY 를 사용합니다.

 SELECT * FROM T1 WHERE key2 = constant ORDER BY key_part2 ;

ASC 와 DESC 를 혼합합니다.

 SELECT * FROM T1 ORDER BY key_part1 DESC, key_part2 ASC;

행을 인출하는 데 사용되는 키가 ORDER BY 에 사용되는 키와 같고 없습니다.
```
 SELECT * FROM T1 WHERE key2 = constant ORDER BY key1 ;
```
키 컬럼 이름 이외의 항목을 포함하는 식으로 ORDER BY 를 사용합니다.
```
 SELECT * FROM t1 ORDER BY ABS ( key );
 SELECT * FROM t1 ORDER BY - key ;
```
다수의 테이블을 조인하려고하고 있으며, ORDER BY 의 열이 모든 행의 취득에 사용되는 최초의 비 상수 테이블에서의 것과는 아닙니다. (이것은 EXPLAIN 출력에서 const 결합 형을 가지지 않는 첫 번째 테이블입니다.)
ORDER BY 식 및 GROUP BY 표현식이 다릅니다.
ORDER BY 절에 지정된 컬럼의 프리픽스에만 인덱스를 설정합니다. 이 경우 인덱스를 사용하여 정렬 순서를 완전히 해결할 수 없습니다. 예를 들어, CHAR(20) 컬럼이 그 첫 번째 10 바이트 만 인덱스를 설정하는 경우 인덱스에서 10 번째 바이트를 넘는 값을 구별 할 수 없기 때문에 filesort 가 필요합니다.
사용 된 테이블 인덱스의 종류가 행을 순차적으로 저장하지 않습니다. 예를 들어, 이것은 MEMORY 테이블의 HASH 인덱스에 적용됩니다.

인덱스 정렬에 사용할 수 있는지 여부는 컬럼 별칭의 사용에 의해 영향을받을 수 있습니다. 컬럼 t1.a 에 인덱스가 설정되어 있다고합니다. 다음 문은 선택 목록에서 컬럼 이름은 a 입니다. 이것은 t1.a 를 가리키는 때문에 ORDER BY 에서 a 에 대한 참조는 인덱스를 사용할 수 있습니다.

 SELECT a FROM t1 ORDER BY a;

다음 명령문은 선택 목록에서 컬럼 이름은 a 하지만 이것은 별칭 이름입니다. 이것은 ABS(a) 를 가리키는 데, ORDER BY 에서 a 에 대한 참조는 인덱스를 사용할 수 없습니다.

 SELECT ABS (a) AS a FROM t1 ORDER BY a;

다음 문은 ORDER BY 는 선택 목록에서 열 이름이 아닌 이름을 참조합니다. 그러나 t1 에는 a 라는 컬럼이 있기 때문에 ORDER BY 는 그것을 사용하여 인덱스를 사용할 수 있습니다. (물론 결과의 정렬 순서는 ABS(a) 의 순서와는 전혀 다를 수 있습니다.)

 SELECT ABS (a) AS b FROM t1 ORDER BY a;

기본적으로 MySQL은 모든 GROUP BY col1 , col2 , ... 쿼리를 ORDER BY col1 , col2 , ... 와 쿼리에 지정된 것처럼 정렬합니다. 동일한 컬럼 목록을 포함하여 명시적인 ORDER BY 절이 포함 된 경우 정렬 처리는 계속되고 있지만, 속도 저하없이 MySQL 최적화에 의해 그것을 제거합니다. 쿼리에 GROUP BY 가 포함되어 있지만, 결과의 정렬의 오버 헤드를 피하려면 ORDER BY NULL 을 지정하여 소트를 억제 할 수 있습니다. 예 :

 INSERT INTO foo
 SELECT a, COUNT (*) FROM bar GROUP BY a ORDER BY NULL;

참고

MySQL 5.6에서 암시 적 GROUP BY 정렬에 의존는 비추천되어 있습니다. 그룹화 된 결과 특정 정렬 순서를 실현하려면 명시 적 ORDER BY 절을 사용하는 것이 좋습니다. GROUP BY 정렬 예를 들어, 최적화가 가장 효율적이라고 생각 어떠한 방법으로도 그룹화를 지시 할 수있게하거나 정렬 오버 헤드를 방지하는 데 등에 향후 릴리스에서 변경 될 수 성있는 MySQL 확장 기능입니다.

EXPLAIN SELECT ... ORDER BY 를 사용하면 MySQL이 인덱스를 사용하여 쿼리를 해결할 수 있는지 여부를 확인할 수 있습니다. Extra 컬럼에 Using filesort 로 표시된 경우 그것은 수 없습니다. ' 섹션 8.8.1 "EXPLAIN으로 쿼리 최적화" "를 참조하십시오. filesort는 MEMORY 스토리지 엔진에 사용되는 것과 유사한 고정 길이의 행 스토리지 포맷을 사용합니다. VARCHAR 같은 가변 길이 형은 고정 길이를 사용하여 저장됩니다.

MySQL은 결과를 정렬하고 검색하기 위해 두 가지 filesort 알고리즘이 있습니다. 원래 메소드는 ORDER BY 컬럼 만 사용합니다. 변경된 메소드는 ORDER BY 컬럼뿐만 아니라 쿼리에서 참조되는 모든 컬럼을 사용합니다.

어떤 filesort 알고리즘을 사용하거나 최적화가 선택합니다. 일반적으로 수정 된 알고리즘이 사용되지만, BLOB 컬럼과 TEXT 컬럼이 포함되는 경우를 제외합니다. 그 경우에는 원래의 알고리즘이 사용됩니다. 두 알고리즘에서 정렬 버퍼 크기는 sort_buffer_size 시스템 변수 값입니다.

원래 filesort 알고리즘은 다음과 같이 작동합니다.

키에 따라 또는 테이블 스캔하여 모든 행을 읽습니다. WHERE 절에 일치하지 않는 행을 건너 뜁니다.
행마다 정렬 버퍼에 값 쌍 (정렬 키 값과 행 ID)를 포함합니다.
모든 쌍이 소트 버퍼에 들어가는 경우 임시 파일이 생성되지 않습니다. 그렇지 않은 경우, 정렬 버퍼가 가득 차면 메모리에서 그에 대한 qsort (quicksort)가 실행되고 그것이 임시 파일에 기록됩니다. 정렬 된 블록에 대한 포인터를 저장합니다.
모든 행을 읽을 때까지 이전 단계를 반복합니다.
다른 임시 파일에서 최대 MERGEBUFF (7) 영역의 하나의 블록에 멀티 머지를 수행합니다. 첫 번째 파일의 모든 블록이 두 번째 파일에 저장 될 때까지이 과정을 반복합니다.
나머지가 MERGEBUFF2 (15) 블록보다 작을 때까지 다음을 반복합니다.
마지막 멀티 병합에서 행 ID (값 쌍의 마지막 부분) 만 결과 파일에 기록됩니다.
결과 파일에서 행 ID를 사용하여 정렬 된 순서로 행을 읽습니다. 이를 최적화하려면 행 ID의 큰 블록을 읽은 다음 정렬하여 그들을 사용하여 정렬 된 순서로 행을 행 버퍼에 읽어들입니다. 행 버퍼 크기는 read_rnd_buffer_size 시스템 변수입니다. 이 단계의 코드는 sql/records.cc 소스 파일에 있습니다.

이 방법의 문제 중 하나는 WHERE 절을 평가할 때 1 회 - 값 쌍의 정렬 후에 다시 한 번과 2 번 행을 읽을 수 있습니다. 또한 첫 번째 행이 순차적으로 접근되어도 (테이블 스캔을 수행하는 경우 등), 두 번째는 그들이 무작위로 액세스됩니다. (정렬 키는 순서되지만 행의 위치는 정렬되지 않습니다.)

수정 된 filesort 알고리즘은 행을 두 번 읽을 것을 방지하는 최적화가 포함되어 있습니다. 그것은 정렬 키 값을 기록하고 있지만, 행 ID 대신 쿼리에서 참조되는 컬럼을 기록합니다. 수정 된 filesort 알고리즘은 다음과 같이 작동합니다.

WHERE 절에 일치하는 행을 읽습니다.
행마다 정렬 키 값과 쿼리에서 참조되는 컬럼에서 구성된 값 튜플을 기록합니다.
정렬 버퍼가 가득 차면 메모리에서 정렬 키 값에 의해 튜플을 정렬하고이를 임시 파일에 씁니다.
임시 파일의 병합 정렬 후 정렬 된 순서로 행을 얻을 수 있지만, 두 번째는 테이블에 액세스하는 것이 아니라, 정렬 된 튜플에서 직접 필요한 컬럼을 읽습니다.

수정 된 filesort 알고리즘을 사용하면 튜플이 원래 메소드에서 사용되는 쌍보다 길어지고 정렬 버퍼에 들어가 그 수가 적습니다. 그 결과 추가 I / O에 의해 수정 된 접근이 더 빨라지는 것이 아니라 느려질 수 있습니다. 속도의 저하를 방지하기 위해 최적화 소토타뿌루의 추가 컬럼의 크기가 max_length_for_sort_data 시스템 변수의 값을 초과하지 않는 경우에만 수정 된 알고리즘을 사용합니다. (이 변수의 값을 현저하게 높게 설정하면 높은 디스크 활동과 낮은 CPU 활동의 조합을 볼 수 있습니다.)

filesort 가 실행되면, EXPLAIN 출력에서 Extra 컬럼에 Using filesort 가 포함되어 있습니다. 또한 최적화 추적 출력에 filesort_summary 블록이 포함됩니다. 예 :

 "filesort_summary": {
   "rows": 100,
   "examined_rows": 100,
   "number_of_tmp_files": 0,
   "sort_buffer_size": 25192,
   "sort_mode": "<sort_key, additional_fields>"
 }

sort_mode 값은 사용 된 알고리즘과 정렬 버퍼 내의 튜플 내용에 대한 정보를 제공합니다.

<sort_key, rowid> : 정렬 버퍼 튜플는 정렬 키 값과 원래의 테이블 행의 행 ID가 포함됩니다. 튜플는 정렬 키 값으로 정렬 된 행 ID는 테이블에서 행을 읽는 데 사용됩니다.
<sort_key, additional_fields> : 정렬 버퍼 튜플는 정렬 키 값과 쿼리에서 참조되는 컬럼이 포함됩니다. 튜플는 정렬 키 값으로 정렬 된 컬럼 값은 튜플에서 직접 읽습니다.

최적화 프로그램 추적 내용은 " MySQL Internals : Tracing the Optimizer "를 참조하십시오.

테이블 t1 에 4 개의 VARCHAR 컬럼 a , b , c 및 d 가 최적화 프로그램은이 쿼리에 filesort 를 사용합니다.

 SELECT * FROM t1 ORDER BY a, b;

쿼리는 a 와 b 로 정렬되지만 모든 열을 반환하는 쿼리에서 참조되는 컬럼은 a , b , c 및 d 입니다. 최적화가 어떤 filesort 알고리즘을 선택 하느냐에 따라 쿼리는 다음과 같이 실행됩니다.

원래 알고리즘의 경우, 소트 버퍼 튜플의 내용은 다음과 같습니다.

 (fixed size a value, fixed size b value,
 row ID into t1)

최적화는 고정 된 크기 값으로 정렬합니다. 정렬 후 최적화 차례로 튜플을 읽어 각 튜플의 행 ID를 사용하여 t1 에서 행을 읽고 선택 목록 컬럼 값을 가져옵니다.

수정 된 알고리즘의 경우, 소트 버퍼 튜플의 내용은 다음과 같습니다.

 (fixed size a value, fixed size b value,
 a value, b value, c value, d value)

최적화는 고정 된 크기 값으로 정렬합니다. 정렬 후 최적화 차례로 튜플을 읽고 a , b , c 및 d 의 값을 사용하여 t1 을 다시 읽을 수없이 선택 목록 컬럼 값을 가져옵니다.

filesort 가 사용되지 않는 느린 쿼리는 max_length_for_sort_data 을 filesort 가 트리거되는 적절한 값까지 줄여보십시오.

ORDER BY 속도를 향상하려면 MySQL에서 추가 정렬 단계가 아닌 인덱스를 사용 할 수 있는지 여부를 확인합니다. 이것이 불가능한 경우 다음 전략을 시도 할 수 있습니다.

sort_buffer_size 변수 값을 늘립니다.
read_rnd_buffer_size 변수 값을 늘립니다.
컬럼에 저장된 값을 유지하기 위해 필요한만큼의 크기로 컬럼을 선언함으로써 행 당에 사용할 RAM을 줄입니다. 예를 들어, 값이 16자를 초과하지 않으면 CHAR(16) 쪽이 CHAR(200) 보다 적합합니다.
tmpdir 시스템 변수를 변경하여 충분한 여유 공간이있는 전용 파일 시스템을 가리 키도록합니다. 변수 값은 라운드 로빈 방식으로 사용되는 여러 경로를 나열 할 수 있습니다. 이 기능을 사용하여 여러 디렉토리에 부하를 분산 할 수 있습니다. 경로는 UNIX에서는 콜론 문자 ( " : "), Windows에서는 세미콜론 (" ; ")으로 구분하도록하십시오. 경로는 동일한 디스크의 다른 파티션이 아닌 다른 디스크에있는 파일 시스템의 디렉토리를 지정하십시오.

ORDER BY 에 인덱스가 사용되지 않지만, LIMIT 절에도 존재하는 경우, 최적화 병합 파일의 사용을 피하고 메모리에서 행을 정렬 할 수 있습니다. 자세한 내용은 섹션 8.2.1.19 "LIMIT 쿼리 최적화" 를 참조하십시오.