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;

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

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

對于InnoDB表來說，執(zhí)行全字段排序能夠減少對于磁盤的訪問，所以會被優(yōu)先選擇。

而對于內存表來說，回表過程只是簡單地根據數據行的位置，直接訪問內存得到數據，根本不會導致多訪問磁盤。所以這時MySQL會優(yōu)選選擇rowid排序。

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

• 創(chuàng)建一個臨時表，這個表使用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是由系統(tǒng)生成的，用來標識不同行。

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

3. 磁盤臨時表

不是所有的臨時表都是內存臨時表。tmp_table_size這個配置限制了內存臨時表的大小，如果超過了這個大小，就會使用磁盤臨時表。InnoDB引擎就是默認使用磁盤臨時表。

4. 優(yōu)先隊列排序算法

在MySQL5.6之后，引入了優(yōu)先隊列排序算法，這種算法是不需要使用臨時文件的。而原本的歸并排序算法則是需要使用臨時文件。

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

而優(yōu)先隊列排序算法則可以只取到前三，執(zhí)行流程如下：

• 對于這10000個準備排序的(R,rowid)，先取前三行，構造成一個堆，并且將最大的值放在堆頂；
• 取下一行（R’,rowid’），跟當前堆里面最大的R比較，如果R’小于R，則把(R,rowid)從堆中去掉，換成（R’,rowid’）。
• 不斷重復上面的過程。

流程如下圖所示：

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

來源：自己整理的MySQL實戰(zhàn)45講筆記

到此這篇關于MySQL中的隨機抽取的實現的文章就介紹到這了,更多相關MySQL 隨機抽取內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論