ORDER BY RAND() 為什麼慢?業務常見需求:從大表中隨機抽取 N 條記錄。最直觀的寫法:
SELECT * FROM `table` ORDER BY RAND() LIMIT 5;
但官方文檔明確警告:RAND() 在 ORDER BY 子句中會被**多次執行**,導致全表掃描並生成臨時表排序。實測 15 萬行表查 5 條耗時 **8 秒+**,隨數據量線性惡化。
CREATE TABLE randnumber (number INT);
INSERT INTO randnumber VALUES (-5),(-4),(-3),(-2),(-1),(0),(1),(2),(3),(4),(5);
SELECT number FROM randnumber ORDER BY RAND() LIMIT 1;
優點:可指定離散值集合;缺點:範圍大時建表維護成本高,本質仍用 ORDER BY RAND()。
ROUND((0.5-RAND())*2*N) 生成範圍隨機整數SELECT ROUND((0.5-RAND())*2*5); -- 產生 -5 到 5 的隨機整數
數學原理清晰,但同樣無法解決大表隨機抽取的核心問題。
MAX(id) * RAND() + 子查詢(網上流傳最廣)SELECT * FROM `table` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5;
缺陷:會返回**連續 5 條**記錄,而非真正隨機分布。若需真隨機,需執行 5 次單條查詢。
WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) ...)SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;
測試耗時 **0.5 秒**,尚可但非最優。
為解決「ID 不連續導致前段數據被過度命中」及「子查詢執行計劃不穩定」問題,最終優化為:
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;
測試:單次 **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
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
測試:單次 **0.0015 秒**(10 次總耗時 0.015 秒),**比 WHERE 版快近 10 倍**。
| 方案 | 單次耗時 | 10次總耗時 | 隨機度 | 適用場景 |
|---|---|---|---|---|
ORDER BY RAND() | ~8000 ms | ~80000 ms | 真隨機 | 極小表(<1萬行) |
| MAX(id)*RAND() + JOIN | ~10 ms | ~100 ms | 連續塊 | 需連續樣本 |
| 子查詢 WHERE id>= | ~500 ms | ~5000 ms | 近似隨機 | ID 較連續表 |
| 雙邊界 JOIN(推薦) | ~1.5 ms | ~15 ms | 均勻隨機 | 大表、ID 稀疏、高頻隨機抽樣 |
WHERE id >= ... ORDER BY id LIMIT 1),僅掃描極少數頁。JOIN ... ON 的執行計劃優化成熟,而相關子查詢(WHERE id >= (SELECT ...))易產生依賴子查詢,導致重複執行或無法使用索引。(MAX - MIN) * RAND() + MIN 確保隨機點落在實際 ID 分布範圍內,避免「前段數據命中率異常高」的偏態問題。TABLESAMPLE(MySQL 8.0.25+ 支持 TABLESAMPLE SYSTEM(10) 伯努利採樣)。WHERE id >= (SELECT FLOOR(RAND() * MAX(id))) 簡化版,省去 MIN 子查詢。SELECT MAX(id), MIN(id) 緩存,再拼接 SQL,減少兩次子查詢開銷。ORDER BY RAND() 是典型的「寫時爽,跑時痛」反模式。掌握 「索引範圍查找 + 雙邊界隨機點 + JOIN 解關聯」 這一模式,能將百萬級表隨機抽樣從秒級降至毫秒級,是每位 MySQL 開發者必備的性能優化技能包。