?

Author：flymorn Source：飄易
Categories：PHP編程?PostTime：2011-1-14 15:35:07 正 文：
????最近由于需要研究了一下MYSQL的隨機(jī)抽取實現(xiàn)方法。舉個例子，要從tablename表中隨機(jī)提取一條記錄，大家一般的寫法就是： SELECT?*?FROM?content?ORDER?BY?RAND()?LIMIT?1 。
【飄易注：3萬條記錄查詢花費(fèi)?0.3745?秒（下同）；從mysql?slow?query?log看出“ORDER?BY?RAND()?”全表掃描了2次！】

?

后來我查了一下MYSQL的官方手冊，里面針對RAND()的提示大概意思就是，在ORDER?BY從句里面不能使用RAND()函數(shù)，因為這樣會導(dǎo)致數(shù)據(jù)列被多次掃描。但是在MYSQL?3.23版本中，仍然可以通過ORDER?BY?RAND()來實現(xiàn)隨機(jī)。

?

但是真正測試一下才發(fā)現(xiàn)這樣效率非常低。一個15萬余條的庫，查詢5條數(shù)據(jù)，居然要8秒以上。查看官方手冊，也說rand()放在ORDER?BY?子句中會被執(zhí)行多次，自然效率及很低。

?

搜索Google，采用JOIN，查詢max(id)?*?rand()來隨機(jī)獲取數(shù)據(jù)。
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;?
【查詢花費(fèi)?0.0008?秒，飄易認(rèn)為可以推薦使用這個語句！！】

?

但是這樣會產(chǎn)生連續(xù)的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;??
上面這種方式保證了一定范圍內(nèi)的隨機(jī),查詢花費(fèi)?0.4265?秒，也不推薦。

?

下面的語句，mysql的論壇上有人使用
SELECT?*?
FROM?`content`?
WHERE?id?>=?(SELECT?FLOOR(?MAX(id)?*?RAND())?FROM?`content`?)?
ORDER?BY?id?LIMIT?1; ??
【查詢花費(fèi)?1.2254?秒，飄易強(qiáng)烈不推薦！因為實測后，3萬行的表，這個語句竟然會掃描500萬行！！】

?

跟上面的語句還是有很大差距。總覺有什么地方不正常。于是我把語句改寫了一下。
SELECT?*?FROM?`content`?
WHERE?id?>=?(SELECT?floor(RAND()?*?(SELECT?MAX(id)?FROM?`content`)))??
ORDER?BY?id?LIMIT?1;? ?
【查詢花費(fèi)?0.0012?秒】

?

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

?

最后，再把語句完善一下，加上MIN(id)的判斷。我在最開始測試的時候，就是因為沒有加上MIN(id)的判斷，結(jié)果有一半的時間總是查詢到表中的前面幾行。
完整查詢語句是：
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;? ?
【查詢花費(fèi)?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; ???
【查詢花費(fèi)?0.0008?秒】

?

最后在php中對這兩個語句進(jìn)行分別查詢10次，
前者花費(fèi)時間?0.147433?秒
后者花費(fèi)時間?0.015130?秒
看來采用JOIN的語法比直接在WHERE中使用函數(shù)效率還要高很多。（via）

?

======================================
【好了，最后飄易來總結(jié)下】：
第一種方案，即原始的?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萬條數(shù)據(jù)查12條隨機(jī)記錄，需要0.125秒，隨著數(shù)據(jù)量的增大，效率越來越低。

?

第二種方案，改進(jìn)后的?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萬條數(shù)據(jù)查12條隨機(jī)記錄，需要0.004秒，效率大幅提升，比第一種方案提升了約30倍。缺點(diǎn)：多次select查詢，IO開銷大。

?

第三種方案，SQL語句先隨機(jī)好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萬條數(shù)據(jù)查12條隨機(jī)記錄，需要0.001秒，效率比第二種方法又提升了4倍左右，比第一種方法提升120倍。注，這里使用了?order?by?field($idlist2)?是為了不排序，否則?IN?是自動會排序的。缺點(diǎn)：有可能遇到ID被刪除的情況，所以需要多選幾個ID。

?

測試方法：
$t?=?microtime(true);?
//執(zhí)行語句
echo?microtime(true)?-?$t; ?

轉(zhuǎn)載于:https://www.cnblogs.com/zengkefu/p/5725918.html

總結(jié)

以上是生活随笔為你收集整理的改进MySQL Order By Rand()的低效率的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。