MySQL Order By Rand()效率分析

本文章轉載一篇關於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。