假設有一個場景，一個英語學習APP首頁有一個隨機顯示單詞的功能，用戶每次訪問首頁的時候，都會隨機滾動顯示三個單詞。

已知表里有10000條記錄，來看看隨機選擇3個單詞有什么方法，又存在什么問題。

建表語句：

mysql> CREATE TABLE `words` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`word` varchar(64) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

?

粉絲福利！

需要全套2025最新Java面試筆記的【點擊此處即可】即可免費獲取！

內存臨時表

首先，可以使用order by rand()來實現：

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

該語句的執行情況：

Extra字段顯示Using temporary，表示需要使用臨時表；Using filesort，表示需要執行排序操作。

為了更好地分析，這里引用上一篇文章中全字段排序和rowid排序的流程圖：

那么對于臨時內存表的排序來說，它會選擇哪一種算法呢？

對于內存表，回表過程只是簡單根據數據行的位置，直接訪問內存得到數據，不會導致多訪問磁盤。這種情況下，優化器會考慮用于排序的行的大小，所以MySQL會選擇rowid排序方法。

因此該語句的執行流程為：

• 創建一個臨時表，臨時表用的是MEMORY引擎，表里有兩個字段，第一個是double類型，記為字段R，第二個是varchar(64)類型，記為字段W，臨時表沒有建索引；

• 從words表按主鍵順序取出所有的word值，對于每一個word，調用rand()生成一個大于0小于1的隨機數，并把這個隨機數和word分別存入臨時表的R和W字段中，該步驟掃描行數10000行；

• 初始化sort_buffer，里面有兩個字段，一個是double類型，另一個是整型；

• 從內存臨時表中逐行取出R值和“位置信息”（后面解釋），分別存入sort_buffer中的兩個字段里，這個過程要對內存臨時表做全表掃描，該步驟掃描行數10000行；

• 在sort_buffer中根據R值進行排序；

• 排序完成后，取出前三個結果的位置信息，依次到內存臨時表取出word值，返回給客戶端。該步驟訪問三行，因此總掃描行數變為20003。

完整的排序執行流程圖：

位置信息本質是數據庫引擎用來快速定位“一行數據”的唯一標識，一般稱為rowid，在不同引擎中其具體形式不同：

• 對于有主鍵的InnoDB表，rowid就是主鍵ID；

• 對于沒有主鍵的InnoDB表，rowid是由系統生成的6字節的主鍵；

• 對于MEMORY引擎，由于其不是索引組織表，可以認為是一個數組，因此rowid其實是數組的下標。

因此，可以總結：order by rand()使用了內存臨時表，內存臨時表排序時候使用了rowid排序方法。

磁盤臨時表

并不是所有的臨時表都是內存表，參數tmp_table_size配置限制了內存臨時表的大小，默認是16M，如果臨時表大小超過了配置值，內存臨時表會轉成磁盤臨時表。

磁盤臨時表使用的引擎默認是InnoDB，是由參數internal_tmp_disk_storage_engine控制。

當使用磁盤臨時表，對應是一個沒有顯式索引的InnoDB表的排序過程。這里把tmp_table_size設為1024，sort_buffer_size設為32768，max_length_for_sort_data設為16，查看OPTIMIZER_TRACE，得到部分結果如下：

對于結果：

• sort_mode里是rowid，這個符合預期，因為max_length_for_sort_data設為16，小于word字段的長度定義，因此使用rowid算法，參與排序是隨機值R和6字節的主鍵；

• number_of_tmp_files=0，沒有用到臨時文件是因為這個語句的排序用的是MySQL 5.6版本引入的優先隊列排序算法。

對于取R值最小的3個rowid的目標，如果使用歸并排序，在算法結束后已經將10000行數據都排好序了，其實浪費了比較多的計算量，而使用優先隊列算法就可以精確只得到三個最小值，其執行流程為：

• 對于10000個準備排序的(R,rowid)，取前三行構造大根堆；

• 取下一行(R',rowid')，與堆頂R比較，如果R'小于R，將堆頂彈出，放入(R',rowid')；

• 重復上一步，直到第10000個數據完成比較；

• 取出堆中的rowid，去臨時表里拿到word字段。

在OPTIMIZER_TRACE結果中，filesort_priority_queue_optimization里的chosen=true，就表示使用了優先隊列排序算法。

那為什么上篇文章的查詢語句（見下）沒有使用優先隊列呢？

select city,name,age from t where city='杭州' order by name limit 1000 ;

是因為如果使用優先隊列，需要維護的堆的大小是1000行的(name,rowid)，超過了設置的sort_buffer_size大小，所以只能使用歸并排序算法。

不過，不論是使用內存臨時表還是磁盤臨時表，order by rand()這種方法都會讓計算過程非常復雜，需要大量的掃描行數。

隨機排序方法

把問題簡化一下，如果只隨機選擇一個word值，那么思路為：

• 取表的主鍵id的最大值M和最小值N；

• 用隨機函數生成一個最大值與最小值之間的數；

• 取不小于X的第一個ID的行。

我們把這個算法稱為算法1，其執行語句為：

mysql> select max(id),min(id) into @M,@N from t ;

set @X= floor((@M-@N+1)*rand() + @N);

select * from t where id >= @X limit 1;

算法1的效率很高，因為取最值都不需要掃描索引，而第三步的查詢也能利用索引快速定位，可以認為總共就掃描了3行。但這個算法并不嚴格滿足要求，因為ID中間可能有空洞，那么選擇不同行的概率不一樣，并不是真正的隨機。

比如4個id，分別是1、2、4、5，如果按照上面的方法，那么取到id=4的概率是取得其他行概率的兩倍。

為了做到嚴格隨機，可以用下面流程：

• 取得整個表的行數C；

• 取得；

• 用limit Y,1取一行。

我們把這個算法稱為算法2，其執行語句為：

mysql> select count(*) into @C from t;

set @Y = floor(@C * rand());

set @sql = concat("select * from t limit ", @Y, ",1");

prepare stmt from @sql;

execute stmt;

DEALLOCATE prepare stmt;

MySQL處理limit Y,1時會按順序一個一個讀出來，丟掉前Y個后將下一個記錄返回，因此需要掃描Y+1行，算上計算行數掃描的C行，總共掃描C+Y+1行，執行代價高于算法1，但小于order by rand()。

那么按照算法2的思路，隨機取3個word值的做法為：

• 取得整個表的行數C；

• 根據相同的隨機方法取得Y1，Y2，Y3；

• 執行三個limit Y,1取得三行數據。

其執行語句為：

mysql> select count(*) into @C from t;

set @Y1 = floor(@C * rand());

set @Y2 = floor(@C * rand());

set @Y3 = floor(@C * rand());

select * from t limit @Y1，1； //在應用代碼里面取Y1、Y2、Y3值，拼出SQL后執行

select * from t limit @Y2，1；

select * from t limit @Y3，1；

最后總結：對于隨機排序，盡量避免使用order by rand()。