mysql實(shí)現(xiàn)隨機(jī)查詢經(jīng)驗(yàn)談

更新時(shí)間：2013年10月19日 21:41:16 作者：

官方文檔中進(jìn)行說明：Order By和RAND()連用，會(huì)多次掃描表，導(dǎo)致速度變慢，下面看下一些測(cè)試詳解

一、隨機(jī)查詢一條數(shù)據(jù)

方法一：SELECT * FROM `table` ORDER BY RAND() limit 1

評(píng)價(jià)：不建議使用，效率非常低，官方文檔中進(jìn)行說明：Order By和RAND()連用，會(huì)多次掃描表，導(dǎo)致速度變慢。

方法二：SELECT * FROM `table`
WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;

解釋：SELECT MAX(id) FROM `table` 這句話查詢出最大的id值

SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)) 這句獲取一個(gè)小于MAX(id)的隨機(jī)數(shù)

WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) 這句話篩選出所有的大于生成隨機(jī)數(shù)的id的行

然后最后就把大于這個(gè)隨機(jī)id的行查詢出來，然后按照id排序，選擇第一個(gè)，就相當(dāng)與獲取了所有行中隨機(jī)的一行。

評(píng)價(jià)：有問題，如果id不是從0開始的話，比如從10000開始自增，那么 SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)) 得到的將是會(huì)喲很大概率得到小于10000的值，經(jīng)過where限定的查詢結(jié)果將會(huì)是所有的查詢結(jié)果的幾率變大，最后limit 1獲取的是第一行數(shù)據(jù)的幾率變高。

方法三：SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECTMIN(id) FROM `table`))) ORDER BY id LIMIT 1;

方法四：SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+

(SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

評(píng)價(jià)：解決了方法二中MAX(id)的問題，RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECTMIN(id) FROM `table`)可以獲取MAX(id)和MIN(id)中的隨機(jī)數(shù)。

方法四要比方法三稍快一點(diǎn)，http://www.dbjr.com.cn/article/42229.htm 這篇文章指出，15w條數(shù)據(jù)前者花費(fèi)時(shí)間 0.147433 秒，后者花費(fèi)時(shí)間 0.015130 秒。

以上解決方案都默認(rèn)有一個(gè)不重復(fù)的數(shù)字字段，其實(shí)現(xiàn)在很多表的設(shè)計(jì)都是以一個(gè)自增段作為主鍵，當(dāng)然還有一些是以u(píng)uid作為主鍵的，而沒有數(shù)字鍵，這樣的話，可以用mysql的函數(shù)將uuid的字符串轉(zhuǎn)換成數(shù)字。而且還有一個(gè)問題，如果id字段的數(shù)字分布不均勻的話（比如按照1,4,5,6,7,8,45這樣分布），也會(huì)造成隨機(jī)查詢的不合理，但是這里就不討論那么復(fù)雜的問題了。

二、隨機(jī)查詢多條數(shù)據(jù)

方法一：把隨機(jī)查詢一條數(shù)據(jù)的limit 1修改成limit 5

評(píng)價(jià)：這樣獲取的數(shù)據(jù)會(huì)是連續(xù)的。

方法二：

SELECT *

FROM `table` AS t1 JOIN (

SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id

from `table` limit 50) AS t2 on t1.id=t2.id

ORDER BY t1.id LIMIT 1;

解釋：

SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id

from `table` limit 50)這樣會(huì)獲取50個(gè)隨機(jī)數(shù)字，然后on t1.id=t2.id會(huì)挑選出不大于50行的隨機(jī)數(shù)據(jù)，然后取5條就好了。