MySQL에서 무작위 추출을 구현하는 방법

1. 소개

이제 단어 목록에서 한 번에 세 단어를 무작위로 선택해야 하는 요구 사항이 있습니다.

이 테이블의 테이블 생성문은 다음과 같습니다.

mysql> Create table 'words'(
    'id' int(11) not null auto_increment;
    'word' varchar(64) default null;
    primary key ('id')
) ENGINE=InnoDB;

그런 다음 10,000행의 데이터를 삽입합니다. 다음으로 무작위로 3개의 단어를 선택하는 방법을 살펴보겠습니다.

2. 메모리 임시 테이블

우선, 우리는 일반적으로 이 논리를 구현하기 위해 rand()로 order를 사용하는 것을 생각합니다.

mysql> select word from words order by rand() limit 3;

이 문장은 매우 간단하지만 실행 과정은 더 복잡합니다. explain을 사용하여 명령문의 실행을 확인합니다. Extra 필드에서 temporary를 사용하면 임시 테이블을 사용해야 함을 나타내고, filesort를 사용하면 정렬이 필요함을 나타냅니다. 즉, 정렬 작업이 필요합니다.

InnoDB 테이블

의 경우 전체 필드 정렬을 수행하면 디스크 액세스가 줄어들 수 있으므로 선호됩니다.

메모리 테이블의 경우 테이블 반환 프로세스는 단순히 메모리에 직접 액세스하여 데이터 행의 위치를 ​​기반으로 데이터를 가져오므로 다중 디스크 액세스가 전혀 발생하지 않습니다

. 따라서 현재 MySQL은 rowid 정렬에 우선순위를 부여합니다.

이 명령문의 실행 과정을 정리해 보겠습니다.

임시 테이블을 만듭니다. 이 테이블은 테이블에 두 개의 필드가 표시되어 있습니다. R과 마찬가지로 두 번째 필드는 W로 표시된 varchar(64) 유형입니다. 그리고 이 테이블에는 인덱스가 없습니다.

• 단어 테이블에서 기본 키 순서대로 모든 단어를 추출합니다. 각 단어에 대해 rand() 함수를 호출하여 0보다 크고 1보다 작은 임의의 십진수를 무작위로 생성하고 임시 테이블의 R 및 W 필드에 각각 임의의 십진수와 단어를 저장합니다.

다음 단계는 R
sort_buffer를 초기화하는 것입니다. sort_buffer에는 double 유형과 정수 필드가 포함됩니다.
임시 메모리 테이블에서 R 값과 위치 정보를 한 줄씩 꺼내서 sort_buffer의 두 필드에 각각 저장합니다.
sort_buffer는 R 값에 따라 정렬됩니다.
정렬이 완료되면 처음 3개 결과의 위치 정보를 꺼내고, 메모리 임시 테이블에서 해당 단어를 꺼내어 다시 테이블로 반환합니다. 고객.
프로세스 다이어그램은 다음과 같습니다.

위에서 언급한 위치 정보는 실제로 앞서 언급한 rowid인 row의 위치입니다.

InnoDB 엔진의 경우 기본 키가 있거나 없는 테이블에 대해 두 가지 처리 방법이 있습니다.

기본 키가 있는

InnoDB 테이블

의 경우 이 rowid는 기본 키 ID입니다

• 기본 키가 없는 경우 InnoDB 테이블의 경우 이 rowid는 시스템에 의해 생성되며 다른 행을 식별하는 데 사용됩니다.

• 따라서

  order by randn()은 메모리 임시 테이블을 사용하고, 메모리 임시 테이블의 정렬 방법은 rowid 정렬 방법을 사용합니다.

  3. 디스크 임시 테이블

모든 임시 테이블이 메모리 임시 테이블은 아닙니다. tmp_table_size 구성은 메모리 임시 테이블의 크기를 제한합니다. 이 크기를 초과하면 디스크 임시 테이블이 사용됩니다. InnoDB 엔진은 기본적으로 디스크 임시 테이블을 사용합니다

.

4. 우선순위 대기열 정렬 알고리즘

MySQL 5.6 이후에는 우선순위 대기열 정렬 알고리즘이 도입되었습니다. 이 알고리즘은 임시 파일을 사용할 필요가 없습니다. 원래 병합 정렬 알고리즘에서는 임시 파일을 사용해야 합니다.

병합 알고리즘을 사용하면 실제로는 상위 3개만 구하면 되는데, 병합 정렬이 부족하면 이미 모든 것이 정돈되어 있어 자원 낭비가 발생하기 때문입니다.

우선순위 큐 정렬 알고리즘은 상위 3개만 가져올 수 있으며 실행 과정은 다음과 같습니다.

이 10,000개(R, rowid)를 정렬하려면 먼저 처음 3개 행을 가져와서 힙을 구성하고, 가장 큰 값을 힙의 맨 위에 배치합니다.

다음 행(R’, rowid’)을 꺼내서 현재 힙에서 가장 큰 R과 비교합니다. 힙에서 (R, rowid)를 제거하고 (R’,rowid’)로 바꿉니다.

• 위의 과정을 반복하세요.

• 과정은 아래 그림과 같습니다.
하지만 제한 개수가 상대적으로 클 경우 힙을 유지하기가 더 어려우므로 병합 정렬 알고리즘을 사용하게 됩니다.

위 내용은 MySQL에서 무작위 추출을 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!