mysql 정렬 차이

MySQL 정렬 모드에 대해 더 얕은 것부터 더 깊은 것까지, 이것이 MySQL의 다양한 정렬 모드 선택에 어떤 영향을 미치는지, 그리고 정렬을 최적화하는 방법에 대해 이야기해 보겠습니다.

추천 과정: MySQL 튜토리얼.

정렬은 데이터베이스의 기본 기능이며 MySQL도 예외는 아닙니다.

사용자는 Order by 문을 통해 지정된 결과 집합을 정렬할 수 있습니다. 실제로 Order by 문뿐만 아니라 Group by 문 및 Distinct 문에서도 암묵적으로 정렬을 사용합니다. 이 기사에서는 먼저 SQL이 인덱스를 사용하여 정렬 비용을 방지하는 방법을 간략하게 소개한 다음 정렬을 구현하기 위한 MySQL의 내부 원칙을 소개합니다.

모든 사람의 다음 질문을 해결하십시오.

MySQL은 정렬을 사용하는 위치와 MySQL이 정렬을 사용하는지 판단하는 방법,

MySQL에는 여러 정렬 모드가 있으며, MySQL이 다른 정렬 모드를 선택하도록 하는 방법과 관계는 무엇입니까? read_rnd_buffer_size와 어떤 상황에서 read_rnd_buffer_size를 증가시켜 정렬을 최적화할 수 있는지

MySQL이 정렬을 위해 디스크를 사용하는지 판단하는 방법과 디스크 정렬을 피하거나 최적화하는 방법

정렬 중에 가변 길이 필드(varchar) 데이터가 메모리에 저장되는 방법, 5.7에는 개선 사항이 있습니다.

이 경우 정렬 모드에는 어떤 개선 사항이 있나요?

sort_merge_pass는 무엇인가요? 상태 값이 너무 크면 어떤 방법을 사용할 수 있나요? MySQL이 정렬을 사용하는 경우 정렬 속도를 높이기 위해 어떤 방법을 분석하고 최적화할 수 있습니까?

2. Sorting

Explain을 통해 MySQL 실행 계획을 보면 Extra 열에 Using filesort가 표시되는 경우가 많습니다. 정렬을 피하기 위해 인덱스를 사용할 수 없는 SQL의 경우, 사용자 요구에 맞게 데이터베이스가 자체적으로 정렬 기능을 구현해야 합니다. 이때 SQL 실행 계획에 "파일 정렬 사용"이 표시됩니다. 파일 정렬을 의미하는 것은 아닙니다. 실제로는 주로 sort_buffer_size 매개변수와 결과 세트 크기에 의해 결정되는 메모리 정렬일 수도 있습니다.

실제로 이 상황은 MySQL이 정렬을 사용하고 있음을 보여줍니다. 파일 정렬을 사용하면 순서대로, 그룹화, 구분, 결합 등의 순서로 나타나는 경우가 많습니다.

MySQL에서 내부적으로 정렬을 구현하는 방법에는 일반 정렬, 최적화 정렬, 우선순위 대기열 정렬 등 세 가지 주요 방법이 있습니다.

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

다음 세 가지 종류의 차이점을 확인하세요.

a. 기존 정렬

(1) 테이블 t1에서 WHERE 조건을 충족하는 레코드를 가져옵니다(2). 레코드의 기본 키 + 정렬 키(id, col2)를 꺼내서 정렬 버퍼(3)에 넣습니다. 정렬 버퍼가 조건을 충족하는 모든 쌍(id, col2)을 저장할 수 있으면 정렬합니다. ; 그렇지 않으면 정렬 버퍼가 가득 차면 정렬을 진행하여 임시 파일로 굳힙니다. (정렬 알고리즘은 퀵 정렬 알고리즘을 사용합니다.)

(4) 정렬 중에 임시 파일이 생성되면 임시 파일의 레코드가 올바른지 확인하기 위해 병합 정렬 알고리즘을 사용해야 합니다

(5) . 조건을 충족하는 모든 레코드가 정렬에 포함될 때까지 위 프로세스를 실행합니다.

(6). 정렬된 (id, col2) 쌍을 스캔하고 id를 사용하여 열(col1, col2, col3)을 가져옵니다. ) SELECT

(7)에서 반환해야 하는 결과 집합을 사용자에게 반환합니다.

위 프로세스에서 파일 정렬을 사용할지 여부는 주로 정렬 버퍼가 정렬해야 하는 (id, col2) 쌍을 수용할 수 있는지 여부에 따라 달라집니다. 이 버퍼의 크기는 sort_buffer_size 매개변수에 의해 제어됩니다. 또한 정렬에는 두 개의 IO가 필요합니다. 하나는 (id, col2)를 검색하는 것이고, 두 번째는 (col1, col2, col3)을 검색하는 것입니다. 반환된 결과 집합이 col2를 기준으로 정렬되므로 ID가 잘못되었습니다. 해당 ID로 (col1, col2, col3)을 낚시하면 대량의 Random IO가 발생합니다. 두 번째 MySQL 자체에 대한 최적화는 ID를 검색하기 전에 정렬하고 이를 버퍼에 넣는 것입니다. 이 버퍼의 크기는 read_rnd_buffer_size 매개변수에 의해 제어된 다음 순서대로 레코드를 검색하여 무작위 IO를 순차 IO로 변환합니다.

b. 최적화된 정렬

기존 정렬 방법에는 정렬 자체 외에 두 개의 추가 IO가 필요합니다. 기존 정렬과 비교하여 최적화된 정렬 방법은 두 번째 IO를 줄입니다. 가장 큰 차이점은 정렬 버퍼가 (id, col2)가 아니라 (col1, col2, col3)이라는 것입니다. 정렬 버퍼에는 쿼리에 필요한 모든 필드가 포함되어 있으므로 데이터를 다시 검색할 필요 없이 정렬이 완료된 후 바로 반환될 수 있습니다. 이 방법의 비용은 동일한 크기의 정렬 버퍼에 저장할 수 있는 (col1, col2, col3)의 수가 (id, col2)보다 작다는 것입니다. 정렬 버퍼가 충분히 크지 않으면 임시 파일이 생성될 수 있습니다. 작성해야 하므로 추가 IO가 발생합니다. 물론 MySQL은 max_length_for_sort_data라는 매개변수를 제공하는데, 정렬된 튜플이 max_length_for_sort_data보다 작은 경우에만 최적화된 정렬 방법을 사용할 수 있으며, 그렇지 않은 경우에는 기존 정렬 방법만 사용할 수 있습니다.

c. 우선순위 대기열 정렬

최종 정렬 결과를 얻으려면 어떤 경우에도 조건에 맞는 모든 레코드를 정렬한 후 반환해야 합니다. 그렇다면 정렬 방법을 최적화하는 것과 비교하면 여전히 최적화의 여지가 있습니까? 버전 5.6은 공간 수준에서 Order by Limit M, N 문을 최적화하고 힙 정렬을 사용하여 구현되는 새로운 정렬 방법인 우선 순위 큐를 추가했습니다. 힙 정렬 알고리즘의 특성은 제한 M, N의 정렬 문제를 해결할 수 있습니다. 모든 요소가 여전히 정렬에 참여해야 하지만 M과 N이 있는 시나리오의 경우 M+N 튜플의 정렬 버퍼 공간만 필요합니다. 작습니다. 기본적으로 정렬 버퍼가 부족하여 병합 정렬을 위한 임시 파일이 필요한 문제는 없습니다. 오름차순의 경우 큰 상단 힙이 사용되며 최종 힙의 요소는 가장 작은 N 요소를 형성합니다. 내림차순의 경우 작은 상단 힙이 사용되며 최종 힙의 요소는 가장 큰 N 요소를 형성합니다.

위 내용은 mysql 정렬 차이의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!