MySQL中的隨機抽取如何實現

1. 引言

現在有一個需求是從一個單字表每次隨機選取三個單字。

這個表的建表語句和如下所示：

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

然後我們向其中插入10000行資料。接下來我們來看看如何從中隨機選出3個單字。

2. 記憶體臨時表

首先，我們通常會想到用order by rand()來實作這個邏輯：

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

雖然這句話很簡單，但執行流程則比較複雜。我們使用explain來看看語句的執行情況：

Extra欄位中Using temporary表示需要使用臨時表，Using filesort表示需要進行排序。也就是需要進行排序操作。

對於InnoDB表來說，執行全字段排序能夠減少對於磁碟的訪問，所以會被優先選擇。

而對於記憶體表來說，回表過程只是簡單地根據資料行的位置，直接存取記憶體得到數據，根本不會導致多存取磁碟。所以這時MySQL會優選選擇rowid排序。

我們接下來再來梳理下這條語句的執行流程：

• 建立一個臨時表，這個表使用memory引擎，表裡有兩個字段，第一個字段是double類型，記為R，第二個字段是varchar(64)類型，記為W。而這個表沒有索引。

• 從words表中，按主鍵順序取出所有的word。對於每個word，呼叫rand()函數隨機產生一個大於0小於1的隨機小數，並將這個隨機小數和word分別存入臨時表的R和W欄位。

• 接下來就是依照欄位R進行排序

• 初始化sort_buffer。 sort_buffer包括一個double類型和一個整數欄位。

• 從記憶體臨時表中一行行取出R值和位置信息，分別存入sort_buffer的兩個欄位裡。

• sort_buffer依照R值進行排序

• #排序完成後，取出前三個結果的位置信息，到記憶體臨時表中取出相應的word，回傳給客戶端。

流程示意圖如下所示：

#上面講的位置信息，其實就是行所在的位置，也就是我們之前說的rowid。

#對於InnoDB引擎來說，對於有沒有主鍵表來說有兩種處理方式：

• 對於有主鍵的InnoDB表來說，這個rowid就是主鍵id

• 對於沒有主鍵的InnoDB表來說，這個rowid是由系統產生的，用來識別不同行。

因此，order by randn()使用了記憶體臨時表，記憶體臨時表的排序方法用的是rowid排序方法。

3. 磁碟臨時表

不是所有的臨時表都是記憶體臨時表。 tmp_table_size這個配置限制了記憶體臨時表的大小，如果超過了這個大小，就會使用磁碟臨時表。 InnoDB引擎就是預設使用磁碟暫存表。

4. 優先隊列排序演算法

在MySQL5.6之後，引入了優先隊列排序演算法，這種演算法是不需要使用臨時檔案的。而原本的歸併排序演算法則是需要使用臨時檔案。

因為當你使用歸併演算法的時候，其實你只需要得到前3，但是你是用完歸併排序，那已經整體有序了，造成了資源的浪費。

而優先佇列排序演算法則可以只取到前三，執行流程如下：

• 對於這10000個準備排序的(R,rowid)，先取前三行，建構成一個堆，並且將最大的值放在堆頂；

• 取下一行（R’,rowid’），跟當前堆裡面最大的R比較，如果R’小於R，則把(R,rowid)從堆中去掉，換成（R’,rowid’）。

• 不斷重複上面的過程。

流程如下圖所示：

#但是當limit的數比較大時，維護堆比較困難，所以又會使用歸併排序演算法。

以上是MySQL中的隨機抽取如何實現的詳細內容。更多資訊請關注PHP中文網其他相關文章！