MySQL Order By Rand()效率分析

大家一般的寫法就是：SELECT * FROM content ORDER BY RAND() LIMIT 1。
【飄易註：3萬條記錄查詢花費 0.3745 秒（下同）；從mysql slow query log看出“ORDER BY RAND() ”全表掃描了2次！】

後來我查了一下MYSQL的官方手冊，裡面針對RAND()的提示大概意思就是，在ORDER BY從句裡面不能使用RAND()函數，因為這樣會導致資料列被多次掃描。但是在MYSQL 3.23版本中，仍然可以通過ORDER BY RAND()來實現隨機。

但是真正測試一下才發現這樣效率非常低。一個15萬餘條的庫，查詢5條資料，居然要8秒以上。查看官方手冊，也說rand()放在ORDER BY 子句中會被執行多次，自然效率及很低。

搜尋Google，採用JOIN，查詢max(id) * rand()來隨機擷取資料。

代碼如下	複製代碼
SELECT * FROM `content` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1;

【查詢花費 0.0008 秒，飄易認為可以推薦使用這個語句！！】

但是這樣會產生連續的5條記錄。解決辦法只能是每次查詢一條，查詢5次。即便如此也值得，因為15萬條的表，查詢只需要0.01秒不到。

有一個方法：

代碼如下	複製代碼
SELECT * FROM `content` AS a JOIN ( SELECT MAX( ID ) AS ID FROM `content` ) AS b ON ( a.ID >= FLOOR( b.ID * RAND( ) ) ) LIMIT 5;

上面這種方式保證了一定範圍內的隨機,查詢花費 0.4265 秒，也不推薦。

下面的語句，mysql的論壇上有人使用

代碼如下	複製代碼
SELECT * FROM `content` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `content` ) ORDER BY id LIMIT 1;

【查詢花費 1.2254 秒，飄易強烈不推薦！因為實測後，3萬行的表，這個語句竟然會掃描500萬行！！】

跟上面的語句還是有很大差距。總覺有什麼地方不正常。於是我把語句改寫了一下。

代碼如下	複製代碼
SELECT * FROM `content` WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `content`)))  ORDER BY id LIMIT 1;

【查詢花費 0.0012 秒】

這下，效率又提高了，查詢時間只有0.01秒

最後，再把語句完善一下，加上MIN(id)的判斷。我在最開始測試的時候，就是因為沒有加上MIN(id)的判斷，結果有一半的時間總是查詢到表中的前面幾行。
完整查詢語句是：

代碼如下	複製代碼
SELECT * FROM `content` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `content`)-(SELECT MIN(id) FROM `content`)) + (SELECT MIN(id) FROM `content`)))  ORDER BY id LIMIT 1;

【查詢花費 0.0012 秒】

代碼如下	複製代碼
SELECT * FROM `content` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `content`)-(SELECT MIN(id) FROM `content`))+(SELECT MIN(id) FROM `content`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;

【查詢花費 0.0008 秒】

最後在php中對這兩個語句進行分別查詢10次，
前者花費時間 0.147433 秒
後者花費時間 0.015130 秒
看來採用JOIN的文法比直接在WHERE中使用函數效率還要高很多。（via）

第一種方案，即原始的 Order By Rand() 方法：

代碼如下	複製代碼
$sql="SELECT * FROM content ORDER BY rand() LIMIT 12"; $result=mysql_query($sql,$conn); $n=1; $rnds=''; while($row=mysql_fetch_array($result)){ $rnds=$rnds.$n.". <a href='show".$row['id']."-".strtolower(trim($row['title']))."'>".$row['title']."</a><br />n"; $n++; }

3萬條資料查12條隨機記錄，需要0.125秒，隨著資料量的增大，效率越來越低。

第二種方案，改進後的 JOIN 方法：

代碼如下	複製代碼
for($n=1;$n<=12;$n++){ $sql="SELECT * FROM `content` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `content`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1"; $result=mysql_query($sql,$conn); $yi=mysql_fetch_array($result); $rnds = $rnds.$n.". <a href='show".$yi['id']."-".strtolower(trim($yi['title']))."'>".$yi['title']."</a><br />n"; }

3萬條資料查12條隨機記錄，需要0.004秒，效率大幅提升，比第一種方案提升了約30倍。缺點：多次select查詢，IO開銷大。

第三種方案，SQL語句先隨機好ID序列，用 IN 查詢（飄易推薦這個用法，IO開銷小，速度最快）：

代碼如下

複製代碼

$sql="SELECT MAX(id),MIN(id) FROM content"; $result=mysql_query($sql,$conn); $yi=mysql_fetch_array($result); $idmax=$yi[0]; $idmin=$yi[1]; $idlist='';    for($i=1;$i<=20;$i++){    if($i==1){ $idlist=mt_rand($idmin,$idmax); }    else{ $idlist=$idlist.','.mt_rand($idmin,$idmax); }    }  $idlist2="id,".$idlist; $sql="select * from content where id in ($idlist) order by field($idlist2) LIMIT 0,12"; $result=mysql_query($sql,$conn); $n=1; $rnds=''; while($row=mysql_fetch_array($result)){ $rnds=$rnds.$n.". <a href='show".$row['id']."-".strtolower(trim($row['title']))."'>".$row['title']."</a><br />n"; $n++; }

3萬條資料查12條隨機記錄，需要0.001秒，效率比第二種方法又提升了4倍左右，比第一種方法提升120倍。注，這裡使用了 order by field($idlist2) 是為了不排序，否則 IN 是自動會排序的。缺點：有可能遇到ID被刪除的情況，所以需要多選幾個ID。