ORDER BY RAND() LIMIT 1 在大表上極慢:產生臨時表、全表掃描、檔案排序,複雜度 O(N log N)。多表 JOIN 後更災難性。
SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;
利用索引範圍掃描,避免全表排序。缺點: ID 有空洞時分布不均,邊界值機率較高。
# 1. 取總數
SELECT COUNT(*) FROM `table` WHERE condition;
# 2. 應用層產生 offset = mt_rand(0, count-1)
# 3. 查詢
SELECT * FROM `table` WHERE condition LIMIT 1 OFFSET $offset;
優點: 完美均勻分布。**缺點: OFFSET 大時仍需掃描前 N 行(InnoDB 需遍歷聚簇索引)。
ALTER TABLE `table` ADD COLUMN rand_val DOUBLE NOT NULL DEFAULT 0;
UPDATE `table` SET rand_val = RAND();
CREATE INDEX idx_rand_val ON `table`(rand_val);
-- 查詢
SELECT * FROM `table` WHERE rand_val >= RAND() ORDER BY rand_val LIMIT 1;
-- 邊界處理
UNION ALL
SELECT * FROM `table` ORDER BY rand_val LIMIT 1;
寫入時維護 rand_val,查詢極快,適合高頻隨機抽樣。
-- 低效寫法
(SELECT * FROM table1 WHERE cond)
UNION ALL
(SELECT * FROM table2 WHERE cond)
ORDER BY RAND() LIMIT 10;
# PHP 範例
$results = [];
foreach (['table1', 'table2', 'table3'] as $t) {
$cnt = $pdo->query("SELECT COUNT(*) FROM $t WHERE cond")->fetchColumn();
if ($cnt) {
$offset = mt_rand(0, $cnt - 1);
$stmt = $pdo->prepare("SELECT * FROM $t WHERE cond LIMIT 1 OFFSET ?");
$stmt->execute([$offset]);
$results[] = $stmt->fetch();
}
}
shuffle($results);
return array_slice($results, 0, 10);
INSERT INTO sample_pool SELECT ... FROM ... UNION ALL ...,查詢時直接抽樣| Join 類型 | 語法 | 適用場景 |
|---|---|---|
| INNER JOIN | t1 JOIN t2 ON t1.id=t2.id | 只需匹配行,效能最好 |
| LEFT JOIN | t1 LEFT JOIN t2 ON ... | 保留左表所有行 |
| STRAIGHT_JOIN | t1 STRAIGHT_JOIN t2 | 強制連接順序,優化器不重排 |
WHERE right_col IS NULL 可用於找出左表無匹配行(反向查詢)-- 1. 先從主表隨機抽樣主鍵 ID
CREATE TEMPORARY TABLE tmp_ids AS
SELECT id FROM main_table WHERE status=1
ORDER BY RAND() LIMIT 1000;
-- 2. 再 JOIN 關聯表(小結果集 JOIN 大表,利用索引)
SELECT m.*, d.detail
FROM tmp_ids t
JOIN main_table m ON m.id = t.id
LEFT JOIN detail_table d ON d.main_id = m.id;
-- 強制使用索引
SELECT * FROM t USE INDEX (idx_status) WHERE status=1 ORDER BY RAND() LIMIT 1;
-- 忽略索引
SELECT * FROM t IGNORE INDEX (idx_name) WHERE name LIKE 'abc%';
-- 強制連接順序
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id=t2.t1_id WHERE t1.status=1;
| 場景 | 推薦方案 | 複雜度 | 均勻度 |
|---|---|---|---|
| 單表、低頻、小資料 | ORDER BY RAND() LIMIT 1 | 低 | 完美 |
| 單表、高頻、大資料 | 預計算 rand_val 欄位 | 中 | 完美 |
| 單表、ID 連續 | 主鍵範圍 + RAND() | 低 | 良好 |
| 多表 UNION | 應用層獨立抽樣 + 合併 | 中 | 完美 |
| 多表 JOIN | 主表先抽樣 ID,再 JOIN | 中 | 良好 |