MySQL5.6 LIMIT 쿼리의 최적화

8.2.1.19 LIMIT 쿼리의 최적화

결과 집합에서 지정한 수의 행만 필요한 경우 전체 결과 집합을 가져 오는 불필요한 데이터를 삭제하는 것이 아니라 쿼리에서 LIMIT 절을 사용합니다.

MySQL은 LIMIT row_count 구가 HAVING 절이없는 쿼리를 최적화 할 수 있습니다.

LIMIT 에서 약간 행만을 선택하면 MySQL에서는 보통 전체 테이블을 스캔하는보다 바람직한 특정 경우에 인덱스가 사용됩니다.
ORDER BY 와 함께 LIMIT row_count 를 사용하면 MySQL은 결과 전체를 정렬하는 것이 아니라, 정렬 된 결과의 첫 번째 row_count 행이 발견되면 즉시 정렬을 종료합니다. 인덱스를 사용하여 정렬이 이루어지고 있다면, 이것은 매우 빨라집니다. filesort를 수행 할 필요가있는 경우 첫 번째 row_count 를 찾기 전에 LIMIT 절을 사용하지 않는 쿼리와 일치하는 모든 행이 선택되고, 그들의 대부분 또는 전부가 정렬됩니다. 초기의 행이 발견되면 MySQL은 결과 집합의 나머지를 모두 정렬하지 않습니다.

이 동작을 명확하게 보여주고있다 현상의 하나는이 절에서 후술하는 바와 같이, LIMIT 을 넣거나 넣지 하나에서 ORDER BY 쿼리는 다른 순서로 행을 반환 할 수있는 것입니다.
LIMIT row_count 를 DISTINCT 과 함께 사용하면 MySQL은 row_count 고유 행이 발견되면 즉시 중지합니다.
경우에 따라 GROUP BY 는 키를 순서대로 읽기 (또는 키의 정렬을 실행) 다음 키 값이 바뀔 때까지 요약을 계산하여 해결할 수 있습니다. 이 경우, LIMIT row_count 는 불필요한 GROUP BY 값을 계산하지 않습니다.
MySQL은 필요한만큼의 행을 클라이언트로 전송하면 즉시, SQL_CALC_FOUND_ROWS 를 사용하지 않는 한, 쿼리를 중지합니다.
LIMIT 0 은 빠르게 하늘 세트를 돌려줍니다. 이것은 쿼리의 유효성 확인에 도움이 될 수 있습니다. 하나의 MySQL API를 사용하는 경우, 그것은 결과 컬럼의 형태를 가져 오는 데 사용할 수 있습니다. 이 기법은 mysql 클라이언트 프로그램에서는 작동하지 않고 그러한 경우에는 단순히 Empty set 를 표시합니다. 대신이 목적으로는 SHOW COLUMNS 또는 DESCRIBE 를 사용합니다.
서버는 쿼리를 해결하기 위해 임시 테이블을 사용하는 경우, LIMIT row_count 절을 사용하여 필요한 공간의 양을 계산합니다.

여러 줄의 ORDER BY 컬럼에 동일한 값이있는 경우, 서버는 자유롭게 그 행을 임의의 순서로 돌려줍니다 만, 그 실행은 실행 계획 전체에 따라 다를 수 있습니다. 즉, 그 행의 정렬 순서는 정렬되지 않은 컬럼에 대해 결정하지 않습니다.

실행 계획에 영향을주는 하나의 요소는 LIMIT 이기 때문에 LIMIT 를 넣거나 넣지 하나에서 ORDER BY 쿼리는 다른 순서로 행을 반환 할 수 있습니다. category 컬럼에 의해 정렬되지만, id 및 rating 컬럼에 대해 비 결정적 다음 쿼리를 고려합니다.

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

LIMIT 를 포함하면 각 category 값의 행의 순서에 영향을 줄 수 있습니다. 예를 들어, 이것은 유효한 쿼리 결과입니다.

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

각 케이스에서 행은 ORDER BY 컬럼에 의해 정렬됩니다하지만 SQL 표준에서 요구되는 것은 이것뿐입니다.

LIMIT 를 사용하여도없이 같은 줄 순서를 확보하는 것이 중요한 경우 ORDER BY 절에 순서를 결정하는 추가 컬럼을 포함합니다. 예를 들어, id 값이 고유 한 경우 지정된 category 값의 행을 id 순으로 표시하도록 정렬 할 수 있습니다.

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

MySQL 5.6.2 시점에서 최적화는 다음과 같은 형식의 쿼리 (및 서브 쿼리)를보다 효율적으로 처리합니다.

 SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [ M ,] N ;

이런 종류의 쿼리는 큰 결과 세트의 몇 줄 만 표시하는 Web 어플리케이션에서 일반적인 것입니다. 예 :

 SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
 SELECT col1, ... FROM t1 ... ORDER BY RAND () LIMIT 15;

정렬 버퍼는 sort_buffer_size 의 크기가 포함되어 있습니다. N 행 ( M 가 지정된 경우 M + N 행)의 정렬 요소가 정렬 버퍼에 들어갈 정도로 작은 경우, 서버는 병합 파일의 사용을 방지하고 정렬 버퍼를 우선 순위 대기열로 처리함으로써 메모리 에서 완전하게 정렬 할 수 있습니다.

테이블을 스캔하고 대기열 순서로 정렬 선택된 행에서 선택 목록 컬럼을 삽입합니다. 큐가 가득 찼을 경우 정렬 순서에서 마지막 행을 밀어냅니다.
큐에서 첫 N 행을 반환합니다. ( M 가 지정되어있는 경우 첫 번째 M 행을 건너 뛰고 다음의 N 행을 반환합니다.)

이전 서버는 정렬 병합 파일을 사용하여이 작업을 수행하고있었습니다.

테이블을 스캔하고 테이블의 마지막까지 다음 단계를 반복합니다.
- 정렬 버퍼가 가득 찰 때까지 행을 선택합니다.
- 버퍼 내의 최초의 N 행 ( M 가 지정된 경우 M + N 행)을 병합 파일에 기록합니다.
병합 파일을 정렬하여 첫 N 행을 반환합니다. ( M 가 지정되어있는 경우 첫 번째 M 행을 건너 뛰고 다음의 N 행을 반환합니다.)

테이블 스캔의 비용은 큐 방법으로도 병합 파일 방법도 동일하기 때문에 최적화는 기타 비용에 따라 방법을 선택합니다.

큐 방법은 대기열에 줄을 차례로 삽입하기 위해 많은 CPU를 필요로합니다
병합 파일 방법은 파일을 읽고 쓰는 I / O 비용과 그것을 정렬하는 CPU 비용이 있습니다

최적화는 N 의 특정 값과 행 크기의 이러한 요소의 균형을 고려합니다.