MySQL 隨機查詢效能優化：從 ORDER BY RAND() 到 JOIN 方案的實測對比

問題背景

經典寫法 SELECT * FROM tbl ORDER BY RAND() LIMIT 5 在 15 萬筆資料表中耗時 8 秒以上。官方文檔明確警告：RAND() 在 ORDER BY 中會被多次評估，導致全表掃描與臨時表排序，效能極差。

方案演進與實測

方案一：MAX(id) * RAND() 子查詢（0.5 秒）

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

缺點：ID 不連續時易查到前段資料，需補上 MIN(id) 修正。

方案二：JOIN 衍生表（0.01 秒）⭐ 推薦

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;

優勢：利用索引快速定位，避免全表排序。

方案三：多次單查取得 N 筆不重複資料

若需 5 筆不連續資料，執行 5 次方案二（或用應用層去重），總耗時 ~0.05 秒，遠優於原生 RAND()。

PHP 壓測結果（10 次迴圈）

核心原理總結

1. 避免 ORDER BY RAND()： 強制全表掃描 + filesort。
2. 利用主鍵索引： 隨機產生 ID 範圍，走 range 或 ref 存取。
3. JOIN 優於 WHERE 子查詢： MySQL 對衍生表優化較好，執行計劃更穩定。
4. 處理 ID 空洞： 必須包含 MIN(id) 修正偏移量。

適用場景建議

• 單表、主鍵為自增整數、資料量 > 1 萬筆 → 使用 JOIN 方案。
• 非整數主鍵或複合主鍵 → 考慮應用層隨機抽樣（先 COUNT(*) 再 LIMIT offset, 1）。
• 極高併發隨機讀 → 引入 Redis 集合 SRANDMEMBER 卸載 DB 壓力。