本文主要介紹了MySQL中的隨機(jī)抽取的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

1. 引言

現(xiàn)在有一個(gè)需求是從一個(gè)單詞表中每次隨機(jī)選取三個(gè)單詞。

這個(gè)表的建表語(yǔ)句和如下所示：

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

然后我們向其中插入10000行數(shù)據(jù)。接下來(lái)我們看看如何從中隨機(jī)選擇3個(gè)單詞。

2. 內(nèi)存臨時(shí)表

首先，我們通常會(huì)想到用order by rand()來(lái)實(shí)現(xiàn)這個(gè)邏輯：

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

雖然這句話很簡(jiǎn)單，但是執(zhí)行流程則比較復(fù)雜。我們使用explain來(lái)看看語(yǔ)句的執(zhí)行情況：

Extra字段中Using temporary表示需要使用臨時(shí)表，Using filesort表示需要進(jìn)行排序。也就是需要進(jìn)行排序操作。

對(duì)于InnoDB表來(lái)說(shuō)，執(zhí)行全字段排序能夠減少對(duì)于磁盤的訪問(wèn)，所以會(huì)被優(yōu)先選擇。

而對(duì)于內(nèi)存表來(lái)說(shuō)，回表過(guò)程只是簡(jiǎn)單地根據(jù)數(shù)據(jù)行的位置，直接訪問(wèn)內(nèi)存得到數(shù)據(jù)，根本不會(huì)導(dǎo)致多訪問(wèn)磁盤。所以這時(shí)MySQL會(huì)優(yōu)選選擇rowid排序。

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

創(chuàng)建一個(gè)臨時(shí)表，這個(gè)表使用memory引擎，表里有兩個(gè)字段，第一個(gè)字段是double類型，記為R，第二個(gè)字段是varchar(64)類型，記為W。并且這個(gè)表沒(méi)有索引。
從words表中，按主鍵順序取出所有的word。對(duì)于每個(gè)word，調(diào)用rand()函數(shù)隨機(jī)生成一個(gè)大于0小于1的隨機(jī)小數(shù)，并把這個(gè)隨機(jī)小數(shù)和word分別存入臨時(shí)表的R和W字段中。
接下來(lái)就是按照字段R進(jìn)行排序
初始化sort_buffer。sort_buffer有兩個(gè)字段，一個(gè)是double類型，另一個(gè)是整型。
從內(nèi)存臨時(shí)表中一行行取出R值和位置信息，分別存入sort_buffer的兩個(gè)字段里。
sort_buffer按照R值進(jìn)行排序
排序完成后，取出前三個(gè)結(jié)果的位置信息，到內(nèi)存臨時(shí)表中取出相應(yīng)的word，返回給客戶端。

流程示意圖如下所示：

上面講的位置信息，其實(shí)就是行所在的位置，也就是我們之前說(shuō)的rowid。

對(duì)于InnoDB引擎來(lái)說(shuō)，對(duì)于有沒(méi)有主鍵表來(lái)說(shuō)有兩種處理方式：

對(duì)于有主鍵的InnoDB表來(lái)說(shuō)，這個(gè)rowid就是主鍵id
對(duì)于沒(méi)有主鍵的InnoDB表來(lái)說(shuō)，這個(gè)rowid是由系統(tǒng)生成的，用來(lái)標(biāo)識(shí)不同行。

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

3. 磁盤臨時(shí)表

不是所有的臨時(shí)表都是內(nèi)存臨時(shí)表。tmp_table_size這個(gè)配置限制了內(nèi)存臨時(shí)表的大小，如果超過(guò)了這個(gè)大小，就會(huì)使用磁盤臨時(shí)表。InnoDB引擎就是默認(rèn)使用磁盤臨時(shí)表。