mysql 隨機擷取記錄order by rand 最佳化

下面我就以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也不錯；隨機獲得多條記錄