假設(shè)有一個(gè)場景，一個(gè)英語學(xué)習(xí)APP首頁有一個(gè)隨機(jī)顯示單詞的功能，用戶每次訪問首頁的時(shí)候，都會(huì)隨機(jī)滾動(dòng)顯示三個(gè)單詞。
已知表里有10000條記錄，來看看隨機(jī)選擇3個(gè)單詞有什么方法，又存在什么問題。
建表語句：
mysql> CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
內(nèi)存臨時(shí)表
首先，可以使用order by rand()來實(shí)現(xiàn)：
select word from words order by rand() limit 3;
該語句的執(zhí)行情況：
Extra字段顯示Using temporary，表示需要使用臨時(shí)表；Using filesort，表示需要執(zhí)行排序操作。
為了更好地分析，這里引用上一篇文章中全字段排序和rowid排序的流程圖：
那么對(duì)于臨時(shí)內(nèi)存表的排序來說，它會(huì)選擇哪一種算法呢？
對(duì)于內(nèi)存表，回表過程只是簡單根據(jù)數(shù)據(jù)行的位置，直接訪問內(nèi)存得到數(shù)據(jù)，不會(huì)導(dǎo)致多訪問磁盤。這種情況下，優(yōu)化器會(huì)考慮用于排序的行的大小，所以MySQL會(huì)選擇rowid排序方法。
因此該語句的執(zhí)行流程為：
創(chuàng)建一個(gè)臨時(shí)表，臨時(shí)表用的是MEMORY引擎，表里有兩個(gè)字段，第一個(gè)是double類型，記為字段R，第二個(gè)是varchar(64)類型，記為字段W，臨時(shí)表沒有建索引；
從words表按主鍵順序取出所有的word值，對(duì)于每一個(gè)word，調(diào)用rand()生成一個(gè)大于0小于1的隨機(jī)數(shù)，并把這個(gè)隨機(jī)數(shù)和word分別存入臨時(shí)表的R和W字段中，該步驟掃描行數(shù)10000行；
初始化sort_buffer，里面有兩個(gè)字段，一個(gè)是double類型，另一個(gè)是整型；
從內(nèi)存臨時(shí)表中逐行取出R值和“位置信息”（后面解釋），分別存入sort_buffer中的兩個(gè)字段里，這個(gè)過程要對(duì)內(nèi)存臨時(shí)表做全表掃描，該步驟掃描行數(shù)10000行；
在sort_buffer中根據(jù)R值進(jìn)行排序；
排序完成后，取出前三個(gè)結(jié)果的位置信息，依次到內(nèi)存臨時(shí)表取出word值，返回給客戶端。該步驟訪問三行，因此總掃描行數(shù)變?yōu)?0003。
完整的排序執(zhí)行流程圖：
位置信息本質(zhì)是數(shù)據(jù)庫引擎用來快速定位“一行數(shù)據(jù)”的唯一標(biāo)識(shí)，一般稱為rowid，在不同引擎中其具體形式不同：
對(duì)于有主鍵的InnoDB表，rowid就是主鍵ID；
對(duì)于沒有主鍵的InnoDB表，rowid是由系統(tǒng)生成的6字節(jié)的主鍵；
對(duì)于MEMORY引擎，由于其不是索引組織表，可以認(rèn)為是一個(gè)數(shù)組，因此rowid其實(shí)是數(shù)組的下標(biāo)。
因此，可以總結(jié)：order by rand()使用了內(nèi)存臨時(shí)表，內(nèi)存臨時(shí)表排序時(shí)候使用了rowid排序方法。
磁盤臨時(shí)表
并不是所有的臨時(shí)表都是內(nèi)存表，參數(shù)tmp_table_size配置限制了內(nèi)存臨時(shí)表的大小，默認(rèn)是16M，如果臨時(shí)表大小超過了配置值，內(nèi)存臨時(shí)表會(huì)轉(zhuǎn)成磁盤臨時(shí)表。
磁盤臨時(shí)表使用的引擎默認(rèn)是InnoDB，是由參數(shù)internal_tmp_disk_storage_engine控制。
當(dāng)使用磁盤臨時(shí)表，對(duì)應(yīng)是一個(gè)沒有顯式索引的InnoDB表的排序過程。這里把tmp_table_size設(shè)為1024，sort_buffer_size設(shè)為32768，max_length_for_sort_data設(shè)為16，查看OPTIMIZER_TRACE，得到部分結(jié)果如下：
對(duì)于結(jié)果：
sort_mode里是rowid，這個(gè)符合預(yù)期，因?yàn)閙ax_length_for_sort_data設(shè)為16，小于word字段的長度定義，因此使用rowid算法，參與排序是隨機(jī)值R和6字節(jié)的主鍵；
number_of_tmp_files=0，沒有用到臨時(shí)文件是因?yàn)檫@個(gè)語句的排序用的是MySQL 5.6版本引入的優(yōu)先隊(duì)列排序算法。
對(duì)于取R值最小的3個(gè)rowid的目標(biāo)，如果使用歸并排序，在算法結(jié)束后已經(jīng)將10000行數(shù)據(jù)都排好序了，其實(shí)浪費(fèi)了比較多的計(jì)算量，而使用優(yōu)先隊(duì)列算法就可以精確只得到三個(gè)最小值，其執(zhí)行流程為：
對(duì)于10000個(gè)準(zhǔn)備排序的(R,rowid)，取前三行構(gòu)造大根堆；
取下一行(R',rowid')，與堆頂R比較，如果R'小于R，將堆頂彈出，放入(R',rowid')；
重復(fù)上一步，直到第10000個(gè)數(shù)據(jù)完成比較；
取出堆中的rowid，去臨時(shí)表里拿到word字段。
在OPTIMIZER_TRACE結(jié)果中，filesort_priority_queue_optimization里的chosen=true，就表示使用了優(yōu)先隊(duì)列排序算法。
那為什么上篇文章的查詢語句（見下）沒有使用優(yōu)先隊(duì)列呢？
select city,name,age from t where city='杭州' order by name limit 1000 ;
是因?yàn)槿绻褂脙?yōu)先隊(duì)列，需要維護(hù)的堆的大小是1000行的(name,rowid)，超過了設(shè)置的sort_buffer_size大小，所以只能使用歸并排序算法。
不過，不論是使用內(nèi)存臨時(shí)表還是磁盤臨時(shí)表，order by rand()這種方法都會(huì)讓計(jì)算過程非常復(fù)雜，需要大量的掃描行數(shù)。
隨機(jī)排序方法
把問題簡化一下，如果只隨機(jī)選擇一個(gè)word值，那么思路為：
我們把這個(gè)算法稱為算法1，其執(zhí)行語句為：
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的效率很高，因?yàn)槿∽钪刀疾恍枰獟呙杷饕谌降牟樵円材芾盟饕焖俣ㄎ?，可以認(rèn)為總共就掃描了3行。但這個(gè)算法并不嚴(yán)格滿足要求，因?yàn)镮D中間可能有空洞，那么選擇不同行的概率不一樣，并不是真正的隨機(jī)。
比如4個(gè)id，分別是1、2、4、5，如果按照上面的方法，那么取到id=4的概率是取得其他行概率的兩倍。
為了做到嚴(yán)格隨機(jī)，可以用下面流程：
取得整個(gè)表的行數(shù)C；
取得\(Y=floor(C*rand())\)；
用limit Y,1取一行。
我們把這個(gè)算法稱為算法2，其執(zhí)行語句為：
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時(shí)會(huì)按順序一個(gè)一個(gè)讀出來，丟掉前Y個(gè)后將下一個(gè)記錄返回，因此需要掃描Y+1行，算上計(jì)算行數(shù)掃描的C行，總共掃描C+Y+1行，執(zhí)行代價(jià)高于算法1，但小于order by rand()。
那么按照算法2的思路，隨機(jī)取3個(gè)word值的做法為：
其執(zhí)行語句為：
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； //在應(yīng)用代碼里面取Y1、Y2、Y3值，拼出SQL后執(zhí)行
select * from t limit @Y2，1；
select * from t limit @Y3，1；
最后總結(jié)：對(duì)于隨機(jī)排序，盡量避免使用order by rand()。
?轉(zhuǎn)自https://www.cnblogs.com/san-mu/p/18990102
該文章在 2025/10/13 11:46:21 編輯過
400 186 1886
