Order By Rand() 替代方案

先說,我手邊現行的資料庫的資料量都不大,所以無法測出當資料量到達 5萬 或是 10萬筆的時候效率

好不好!

至少可以很肯定 ORDER BY RAND() 這樣的寫法在資料量大的時候,或是條件繁複的時候會有不好的效率,這點在 MySQL 的使用手冊中就有講到,詳情請見 MySQL 使用手冊 12.4.2 章節關於 RAND 函數的講解。
主要是因為【在ORDER BY語句中,不能使用一個帶有RAND()值的列,原因是 ORDER BY 會計算列的多重時間】與【在WHERE語句中,WHERE每執行一次, RAND()就會被再計算一次】
所以就要想出其他的替代方案來。

第一個想法,先下一個SQL

SELECT COUNT(id) AS id FROM `table` WHERE 1

使用 php 將 t_id = rand(0, id);

SELECT * FROM `table` WHERE 1 LIMIT t_id , 1

但是這樣程式就不需不斷的在 DB 與 WEB 中不斷的來回穿梭,接下來就是想要用純 SQL 的方式解決。
於是就在網路上找到了這篇文章【MySQL使用rand函數實現隨機數

他提出了這樣的 SQL 語法來解決問題

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;

不過當你取得多篇文章的時候,他還是連續的文章,而不是隨機的多篇文章。
後來我嘗試修改他的 SQL 語法成為

SELECT t1.*, rand()*t2.id AS o_id
FROM `table` AS t1
 JOIN (SELECT MAX(id) AS id FROM `table`) AS t2
WHERE 1
ORDER BY o_id LIMIT 1;

這樣就可以達到我想要的多篇不連續的隨機文章的目的了。
不過後來又繼續在 Google 上看了一下文章,看到這篇【MySQL 的 ORDER BY RAND() 的替代方案】的文章之後,我發覺我這樣的語法在資料庫的筆數很多的時候應該效率也不會太好,不過目前我的資料庫筆數不多,就先這樣使用。

分類: 程式設計真苦命,標籤: , , , 。這篇內容的永久連結

在〈Order By Rand() 替代方案〉中有 4 則留言

  1. 索尼斯表示:

    我是用rand()取得陣列
    shuffle()將陣列打亂後
    以mysql_fetch_assoc()抓特定資料列 ^^

    SQL太亂我容易瘋掉…ㄎㄎ

  2. 索尼斯表示:

    打字打太快
    我是用range()取得陣列 :P

  3. richer表示:

    我以 SQL 來解決問題的主要想法是,是想要盡量的減少 SQL 的查詢次數並且每次查詢的時候都盡量不搜尋出多餘的列數(LIMIT 到所需~)
    當然,問題的解決辦法很多,只是提出我最近喜好的解決辦法給你做參考啦~

  4. 索尼斯表示:

    的確想找個時間檢視一下SQL的讀寫是否過多
    系統越大, 程式的精簡越顯重要…..

    但說真的…不容易! :P

留言功能已關閉。