mysql 隨機獲取記錄order by rand 優化

如果要隨機獲取記錄數，在mysql裡最簡單的方法肯定是order by rand()了，但是這種方法只能在表記錄極少的情況下才能使用。主要是因為order by rand()導致了using filesort.這個時候查詢類型會變成all，索引會失效。只需簡單的變通下，完成可以做到同樣的效果。

下面我就以users（userId，userName，password……）表（有一百多萬條記錄）為例，對比講解下幾個方法效率問題：

 代碼如下 復制代碼

1.select * from users order by rand() LIMIT 1

執 行該sql語句，老半天沒有反應，最後被迫手動停止執行，怎個傷人了得啊！後來我查了一下MYSQL手冊，裡面針對RAND()的提示大概意思就是，在 ORDER BY從句裡面不能使用RAND()函數，因為這樣會導致數據列被多次掃描，導致效率相當相當的低！效率不行，切忌使用！

 代碼如下 復制代碼

2.SELECT * FROM users  AS t1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(userId) FROM `users`)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1

執行該sql語句，用時0.031s，效率沒說的，相當的給力！心裡那個爽啊，緊接著，我把”LIMIT 1“改為了”LIMIT 100“ 隨機取一百條記錄，用時0.048，給力吧。可是就在此時問題出現了，發現結果好像不是隨機的？為了驗證結果，又執行了N次，真不是隨機的， 問題出現在”ORDER BY t1.userId“這裡，按userId排序了。隨機取一條記錄還是不錯的選擇，多條就不行了啊！

 代碼如下 復制代碼

3.SELECT * FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) * RAND() + (SELECT MIN(userId) FROM users)  LIMIT 1

執行該sql語句，用時0.039s，效率太給力了！接著我就把”LIMIT 1“改為了”LIMIT 10000“，用時0.063s。經過多次驗證，哥對燈發誓，結果肯定是隨機的！
結論：隨機取一條或多條記錄，方法都不錯！

4.通過sql獲得最大值和最小值，然後通過php的rand生成一個隨機數randnum，再通過

 代碼如下 復制代碼 SELECT * FROM users WHERE userId >= randnum LIMIT 1

，獲得一條記錄效率應該還可以，多條應該就不行了。
 

根據記錄的類型，分類連續和非連續兩種。
連續指記錄是連續存放的，並且有字段可以證明記錄是連續的，例如自增id。
非連續是指記錄是隨機存放的，例如有條件的查詢，結果肯定不是連續的。

一、連續記錄優化
先得到表的最大id和最小id。select max(id),min(id) from table

1.在程序裡隨機一個在最大id和最小id的中間數，查詢的時候大於這個隨機數的就是隨機記錄了。

Sql代碼 

 代碼如下 復制代碼

1.select * from table where id > 中間數 limit length; 
select * from table where id > 中間數 limit length;缺點：如果中間數很大的話，獲取不了需要的記錄數，隨機性不強

 代碼如下 復制代碼

2.在程序裡隨機n個最大id和最小id的中間數，查詢的時候用in獲得這幾個中間數的記錄
Sql代碼 
1.select * from table where id in (中間數1, 中間數2,中間數3) 
select * from table where id in (中間數1, 中間數2,中間數3)需要注意的是，如果你要獲取5條記錄，那建議隨機10個數。
缺點：性能不如第1種方法，但是隨機性更強

二、非連續記錄優化

其實非連續記錄的方法一樣可以應用在連續記錄中。
首先獲得記錄的總數，例如：select count(*) from table where groupid = 1;
然後在程序裡隨機n個小於記錄總數的中間數，之後通過循環

Sql代碼 

 代碼如下 復制代碼 1.select * from table where groupid = 1 limit 中間數,1 
select * from table where groupid = 1 limit 中間數,1來獲得記錄。

關於優化循環sql可以采用prepare或者union all來優化循環執行

結論：方法1效率不行，切忌使用；隨機獲得一條記錄，方法2是相當不錯的選擇，采用JOIN的語法比直接在WHERE中使用函數效率還是要高一些的，不過方法3也不錯；隨機獲得多條記錄