MySQL Order By 인덱스 최적화 방법

ORDER BY가 인덱스 순서와 정확히 일치하지 않더라도 사용되지 않은 인덱스 부분과 모든 추가 ORDER BY 필드가 WHERE 절에 포함되어 있는 한 인덱스를 계속 사용할 수 있습니다.

인덱스를 사용하여 MySQL 주문
다음 쿼리는 인덱스를 사용하여 ORDER BY 또는 GROUP BY 섹션:

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; 
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; 
SELECT * FROM t1 WHERE key_part1=constant GROUP 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;

Order By 인덱스가 없는 MySQL
다른 경우에는 MySQL이 ORDER를 충족하기 위해 인덱스를 사용할 수 없습니다. BY는 인덱스를 사용하여 WHERE 절과 일치하는 레코드를 찾습니다. 이러한 상황은 다음과 같습니다.
* 다른 인덱스 키에 대해 ORDER BY를 수행합니다.
SELECT * FROM t1 ORDER BY key1, key2
* 연속되지 않은 인덱스 키 부분에 대해 ORDER BY 수행:
SELECT * FROM t1 WHERE key2=상수 ORDER BY key_part2;
* ASC와 DESC 모두 사용:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 레코드 검색에 사용된 인덱스 키가 ORDER BY에 사용된 것과 동일하지 않습니다.
SELECT * FROM t1 WHERE key2=상수 ORDER BY key1; 함께 조인되는 테이블이 많고 읽기 레코드의 ORDER BY 필드가 모두 첫 번째 비상수 테이블(즉, EXPLAIN의 분석 결과 첫 번째 테이블의 조인 타입이 const가 아니다).
* 다른 ORDER BY 및 GROUP BY 표현식을 사용하세요.
* 테이블 인덱스의 레코드는 순차적으로 저장되지 않습니다. 예를 들어 HASH 및 HEAP 테이블의 경우입니다.

EXPLAIN SELECT ... ORDER를 실행하여 BY, MySQL이 쿼리에서 인덱스를 사용하는지 여부를 알 수 있습니다. Extra 필드의 값이 Using filesort인 경우 MySQL은 인덱스를 사용할 수 없습니다. 자세한 내용은 "7.2.1 EXPLAIN 구문(SELECT에 대한 정보 가져오기)". 결과를 정렬해야 할 때 MySQL 4.1 이전에는 다음을 사용했습니다. 파일 정렬 알고리즘:

1. 根据索引键读取记录，或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。 
2. 在缓冲中每条记录都用一个‘对'存储了2个值（索引键及记录指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。 
3. 当缓冲慢了时，就运行 qsort（快速排序）并将结果存储在临时文件中。将存储的块指针保存起来（如果所有的‘对'值都能保存在缓冲中，就无需创建临时文件了）。 
4. 执行上面的操作，直到所有的记录都读取出来了。 
5. 做一次多重合并，将多达 MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。 
6. 重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。 
7. 在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去。 
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。
缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。

이 근사 알고리즘의 문제점은 데이터베이스가 레코드를 두 번 읽는다는 것입니다. 한 번은 WHERE를 추정하기 위한 것입니다. 문장을 나눌 때 두 번째는 정렬할 때입니다. 처음에는 레코드를 성공적으로 읽었지만(예: 전체 테이블 스캔 수행) 두 번째에는 무작위로 읽혔습니다(인덱스 키는 정렬되었지만 레코드는 정렬되지 않음). MySQL 4.1에서 최신 버전에서는 파일 정렬 최적화 알고리즘을 사용하여 인덱스 키 값과 레코드 위치뿐 아니라 쿼리에 필요한 필드도 기록합니다. 이렇게 하면 레코드를 두 번 읽을 필요가 없습니다. 향상된 파일 정렬 알고리즘은 대략 다음과 같습니다.

1. 이전과 마찬가지로 WHERE 절과 일치하는 레코드를 읽습니다.
2. 각 레코드에 대해 인덱스 키 값, 레코드 위치 및 쿼리에 필요한 모든 필드를 포함하여 해당 정보 '튜플'이 기록됩니다.
3. 인덱스 키에 따라 '튜플' 정보를 정렬합니다.
4. 레코드를 순서대로 읽는 것은 데이터 테이블에서 다시 읽는 것이 아니라 정렬된 '튜플' 목록에서 레코드를 읽는 것입니다.

향상된 파일 정렬 사용 원래 알고리즘과 비교할 때 '튜플'은 '쌍'보다 더 긴 공간을 차지하며 정렬 버퍼에 정확히 맞는 경우가 거의 없습니다(버퍼의 크기는 sort_buffer_size에 의해 결정됨). 값)에 따라 결정됩니다. 따라서 더 많은 I/O 작업이 필요할 수 있으며 이로 인해 향상된 알고리즘이 느려질 수 있습니다. 속도가 느려지는 것을 방지하기 위해 이 최적화는 추가 필드의 크기 합계가 시스템 변수를 초과하는 경우 '튜플' 정렬에만 사용됩니다. max_length_for_sort_data의 상황(이 변수의 값을 너무 높게 설정하면 디스크 부하가 높고 CPU 부하가 낮아집니다). ORDER BY를 개선하고 싶습니다. 속도는 먼저 MySQL이 추가 정렬 프로세스 대신 인덱스를 사용할 수 있는지 여부에 따라 달라집니다. 인덱스를 사용할 수 없는 경우 다음 전략을 시도해 볼 수 있습니다.
* sort_buffer_size 값을 늘리세요.
* read_rnd_buffer_size 값을 늘립니다.
* 여유 공간이 많은 전용 파일 시스템을 가리키도록 tmpdir을 수정하세요.
MySQL 4.1 이상을 사용하는 경우 이 옵션을 사용하면 루프 형식의 다중 경로가 허용됩니다. Unix에서는 각 경로가 콜론(':')으로 구분됩니다. 윈도우, 넷웨어, OS/2 세미콜론(';')을 사용하세요. 이 기능을 사용하면 여러 디렉터리에 로드를 균등하게 분산할 수 있습니다. 참고: 이러한 경로는 동일한 물리적 디스크의 다른 디렉터리가 아니라 다른 물리적 디스크에 배포된 디렉터리여야 합니다.