志在巔峰的攀登者，不會陶醉在沿途的某個腳印之中

1 前言

如下我這里有一張抽題記錄表

我需要隨機從中選4條數據，于是我會這樣寫（隨機排序，取前4個）

執行時間 6.73秒，這個時間是絕對無法容忍的，你知道這個過程發生了什么嗎？？？

2 MySql 的 rand 查詢過程 內存臨時表

select * from question_extracting order by rand() limit 4

上述這一句話先隨機排序，再取前4條，它的執行過程如下：

• 第一步 創建一個臨時表，有兩個字段，一個是double 類型使用A表示，另一個是 varchar(64) 類型 使用B表示，記為 字段 W，需要注意的是這個表沒有建索引。
• 第二步就是 從 上述 question_extracting 表中，按主鍵順序取出所有的行（因為這里需要的是每行的所有數據），對于每一行數據，調用 rand() 函數生成一個大于 0 小于 1 的隨機小數，并把這個隨機小數和這一行數據 分別存入臨時表的 A 和 B 字段中，需要掃描全表，如這里的 4974098 行。

3. 第三步就是在臨時表中按照字段 A 排序，初始化 sort_buffer，sort_buffer 中會放兩個字段，一個是 double 類型，用來放臨時表中的 A 字段，另一個是整型，用來放臨時表中對應的數據的行號。
4. 第四步就是在 sort_buffer 中根據 A 的值進行排序，排序完成后，取出前 4 個結果的位置信息，然后回到依次到內存臨時表中取出 對就的行信息 值，返回給 客戶端。

在上述過程中，在第二步掃描了 question_extracting 全表 4974098 行 ，在第三步中也掃描了臨時表 4974098 行 ，然后在最后一步取數據又掃描了4行數據，所以這一次查詢總共掃描了 4974098 +4974098 + 4 次。

在上述這個過程中，order by rand() 使用了內存臨時表，內存臨時表排序的時候 使用了 rowid 排序方法，這個臨時表，沒有主鍵ID，由臨時生成的長度為 6 字節的 rowid 來作為主鍵。

3 MySql 的 rand 查詢過程 磁盤臨時表

在 MySql 中，參數 tmp_table_size 限制了內存臨時表的大小，默認值是 16M，如果臨時表大小超過了 tmp_table_size，那么內存臨時表就會轉成磁盤臨時表。

使用磁盤臨時表的時候，就是對一個沒有顯式索引的 InnoDB 表進行排序。

在這個過程中使用的是 MySQL 5.6 版本引入的優先隊列排序算法，如我們上述的隨機取值，按照 優先隊列排序算法 只需要取出 臨時表中最小的 4 個 A 值對應的數據就可以，這個過程可簡單描述如下：

• 第一步 對于臨時表中這 4974098 個準備排序的 (A,rowid)，先取前四行，構造成一個堆（可以理解為一個組）
• 第二步 取下一個行 (R’,rowid’)，跟當前堆里面最大的 R 比較，如果 R’小于 R，把這個 (R,rowid) 從堆中去掉，換成 (R’,rowid’)
• 重復第 2 步，直到第 4974098 個 (R’,rowid’) 完成比較
• 最后一步就是 拿到這最小的4個值后，回到臨時表中取出對應的數據。

3 MySql 隨機排序的正確姿勢

隨機取一條數據，按照最開始的寫法如下：

隨機算法的正確姿勢

你并沒有看錯，這就是正確的寫法

select max(id),min(id) into @A,@B from question_extracting ;

set @C= floor((@A-@B+1)*rand() + @B);

select * from question_extracting where id >= @C limit 1;

可描述如下：

• 第一步 取得這個表的主鍵 id 的最大值 M 和最小值 N，這個過程不需要掃描表
• 第二步 用隨機函數生成一個最大值到最小值之間的數 C = (A-B)*rand() + B;
• 第三步 取不小于 C 的第一個 ID 的行

完畢

不局限于思維，不局限于語言限制，才是編程的最高境界。

推薦閱讀：MySql 你真的會使用字符串索引嗎？