MySQL 隨機查詢優化：ORDER BY RAND() 陷阱與高效替代方案實測

ORDER BY RAND() 為何慢？

MySQL 手冊明確警告：ORDER BY RAND() 會導致該欄位被多次掃描，且無法使用索引。15 萬筆資料取 5 筆耗時 8 秒以上，隨資料量線性惡化。

常見錯誤嘗試：MAX(id) * RAND()

-- 產生連續 5 筆，非真隨機
SELECT * FROM t WHERE id >= (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t))) ORDER BY id LIMIT 5;

需執行 5 次單筆查詢才能得到 5 筆不連續資料，雖單次 0.01s 但總耗時仍可觀。

優化演進：JOIN + 子查詢定位

版本 1：WHERE 子查詢 (0.5s)

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

版本 2：優化子查詢範圍 (0.01s)

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

加入 MIN(id) 修正 ID 不從 1 開始的偏移問題，大幅提速。

版本 3：JOIN 寫法 (0.0015s，最快)

SELECT * 
FROM t AS t1 
JOIN (
    SELECT ROUND(RAND() * ((SELECT MAX(id) FROM t) - (SELECT MIN(id) FROM t)) + (SELECT MIN(id) FROM t)) AS id
) AS t2 ON t1.id >= t2.id 
ORDER BY t1.id LIMIT 1;

PHP 實測 10 次平均耗時

JOIN 寫法快 10 倍，因優化器能更好處理衍生表物化與索引下推。

完整通用模板（支援 ID 不連續、非從 1 開始）

SELECT * 
FROM `table` AS t1 
JOIN (
    SELECT FLOOR(RAND() * (
        (SELECT MAX(id) FROM `table`) - (SELECT MIN(id) FROM `table`)
    ) + (SELECT MIN(id) FROM `table`)) AS rand_id
) AS t2 ON t1.id >= t2.rand_id 
ORDER BY t1.id LIMIT 1;

多筆隨機取樣策略

1. 小樣本 (≤ 10)：迴圈呼叫上述單筆查詢 N 次，應用層去重。
2. 大樣本：SELECT * FROM t WHERE RAND() < 0.01 LIMIT 1000 (伯努利取樣)，近似隨機且極快，適合統計分析。
3. 精確隨機 N 筆：MySQL 8.0 使用 TABLESAMPLE SYSTEM (10) REPEATABLE (42) (需特定引擎支援) 或應用層抽取 ID 列表再 IN 查詢。

總結

拒絕 ORDER BY RAND()。採用「JOIN + 子查詢隨機主鍵定位 + 範圍掃描」模式，配合 MIN/MAX(id) 修正邊界，單筆隨機查詢可達毫秒級。大量取樣請改用伯努利取樣或應用層抽樣策略。