15 萬行表,SELECT * FROM t ORDER BY RAND() LIMIT 5 耗時 **8 秒+**。官方文檔警告:ORDER BY 中 RAND() 會被多次計算,導致全表掃描 + 臨時表 + filesort。
SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;
子查詢每行執行一次,仍有開銷。
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;
將 MAX/MIN 提取為獨立子查詢,避免重複計算。
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;
| 方案 | 總耗時 | 平均/次 |
|---|---|---|
| WHERE 子查詢 | 0.147 秒 | 0.015 秒 |
| JOIN 子查詢 | 0.015 秒 | 0.0015 秒 |
結論:JOIN 寫法穩定 10 倍+ 於 WHERE 子查詢,生產環境首選。
SELECT id FROM t ORDER BY RAND() LIMIT 1 取隨機 ID 再回表IN 查詢TABLESAMPLE SYSTEM (10) 伯努利採樣(近似隨機)