MySQL RAND() 高效隨機抽樣：從 ORDER BY RAND() 到 JOIN 優化完整演進

常見誤區：ORDER BY RAND() 效能災難

SELECT * FROM `table` ORDER BY RAND() LIMIT 5;

官方文件明確指出：RAND() 在 ORDER BY 中會被執行多次，大表效能極差（15 萬筆資料查 5 筆需 8 秒以上）。

優化演進路線

方案 1：MAX(id) * RAND() 子查詢（仍有缺陷）

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;

測試約 0.5 秒，但易產生連續 ID，且未處理 MIN(id) 導致前段資料機率過高。

方案 2：JOIN 衍生表 + 完整範圍公式（推薦）

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 ON t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

• 包含 MIN(id) 修正分布偏移。
• JOIN 衍生表僅掃描一次隨機值。
• 測試 10 次總耗 0.015 秒，極佳。

方案 3：WHERE 子查詢版（次優）

SELECT * FROM `table`
WHERE id >= (
    SELECT FLOOR(
        RAND() * ((SELECT MAX(id) FROM `table`) - (SELECT MIN(id) FROM `table`))
        + (SELECT MIN(id) FROM `table`)
    )
)
ORDER BY id LIMIT 1;

測試 10 次總耗 0.147 秒，慢於 JOIN 版約 10 倍。

批次更新隨機值

UPDATE cdb_posts SET views = RAND();

單陳述式即可為全表賦隨機值，無需迴圈。

總結建議

1. 大表隨機抽樣：優先用 JOIN 衍生表 + MIN/MAX 修正公式。
2. 避免 ORDER BY RAND()。
3. 確保 id 有索引且分布相對連續；若有大量刪除導致空洞，考慮建立連續序列輔助表。