SELECT * FROM t ORDER BY RAND() LIMIT 10;
ORDER BY 子句中不能使用 RAND()
SELECT * FROM t
WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM t)
ORDER BY id LIMIT 5;
耗時 0.5 秒,但返回連續 5 條,隨機性極差。
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;
耗時 0.01 秒,真隨機單條。
-- 方案 A:應用層循環調用 10 次單條查詢(推薦,總耗時 ~0.1s)
-- 方案 B:單條 SQL 多 JOIN(複雜度高,不推薦)
SELECT t1.*
FROM `table` AS t1
JOIN (
SELECT ROUND(
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 10;
| 方案 | 耗時 | 隨機性 | 備註 |
|---|---|---|---|
| ORDER BY RAND() | 8.2 秒 | 完美 | 生產禁用 |
| WHERE id >= RAND()*MAX | 0.5 秒 | 連續塊 | 不均勻 |
| JOIN + 邊界修正 (單條循環 10 次) | 0.15 秒 | 完美 | 推薦 |
| JOIN 直接 LIMIT 10 | 0.015 秒 | 連續塊 | 極快但非真隨機 |
ORDER BY RAND() LIMIT N 可接受單條 JOIN 隨機 N 次,或預先生成隨機 ID 列表再 WHERE id IN (...)TABLESAMPLE(8.0.2+)、ClickHouse/ES 等 OLAP 引擎WHERE id >= rand_id 配合 ORDER BY id LIMIT 1 正確處理稀疏 IDMAX(id)/MIN(id) 若無索引會全表掃描,確保主鍵索引存在OFFSET FLOOR(RAND()*COUNT(*))(仍慢)或維护映射表