萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 隨機查詢四大方案效能大比拼:ORDER BY RAND() 並非唯一選擇

四大方案原理與代碼

方案一:ORDER BY RAND() (最簡單、最慢)

SELECT * FROM `table` ORDER BY RAND() LIMIT 1;

機制:建立臨時表 → 全表掃描賦隨機值 → 排序 → 取第一行。複雜度 O(N log N),N=表行數。

方案二:隨機 ID 範圍 + >= 查詢 (需連續/稠密 ID)

# PHP 範例
$range = $pdo->query("SELECT MAX(id) max_id, MIN(id) min_id FROM `table`")->fetch();
$random = mt_rand($range['min_id'], $range['max_id']);
$stmt = $pdo->prepare("SELECT * FROM `table` WHERE id >= ? LIMIT 1");
$stmt->execute([$random]);

缺陷:ID 有空洞時,分布不均;邊界 ID 機率較高。

方案三:RAND() * COUNT(*) 計算偏移量 (均勻分布)

$offset = $pdo->query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM `table`")->fetchColumn();
$stmt = $pdo->prepare("SELECT * FROM `table` LIMIT ?, 1");
$stmt->execute([$offset]);

優勢:真正均勻隨機,無需連續 ID。劣勢:LIMIT offset, 1 大偏移仍需掃描 offset 行。

方案四:子查詢最大 ID * RAND() (單條 SQL,需連續 ID)

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

效能實測對比 (相對方案一耗時 100%)

方案相對耗時均勻度適用條件備註
1. ORDER BY RAND()100% (基準)完美任意表大表不可用
2. 隨機 ID >= 查詢~79%邊界偏高ID 稠密PHP 兩次往返
3. RAND()*COUNT 偏移量~13% ⭐完美任意表大偏移仍慢
4. 子查詢 MAX*RAND~16%邊界偏高ID 稠密單條 SQL

進階優化:大表真正均勻隨機

方案 A:預計算隨機值欄位 (寫入時賦值)

ALTER TABLE `table` ADD COLUMN `rand_val` DOUBLE NOT NULL DEFAULT (RAND());
CREATE INDEX idx_rand ON `table` (rand_val);

-- 查詢
SELECT * FROM `table` WHERE rand_val >= RAND() ORDER BY rand_val LIMIT 1
UNION ALL
SELECT * FROM `table` WHERE rand_val < RAND() ORDER BY rand_val LIMIT 1
LIMIT 1;

方案 B:主鍵 ID 陣列抽樣 (應用層)

# 1. 取得 ID 範圍
min_id, max_id = SELECT MIN(id), MAX(id) FROM t;

# 2. 應用層生成 K 個隨機 ID (K=100)
random_ids = random.sample(range(min_id, max_id+1), k=100)

# 3. 批次查詢
SELECT * FROM t WHERE id IN (?, ?, ...) ORDER BY RAND() LIMIT 1
-- 或用 VALUES ROW 建構臨時表 JOIN

方案 C:TABLESAMPLE (8.0.25+ 系統版本)

SELECT * FROM `table` TABLESAMPLE SYSTEM (1) LIMIT 1;
-- 系統採樣:按頁面採樣,非行級均勻,但極快

選型決策

表行數 < 1 萬?
  ├─ 是 → 方案一 ORDER BY RAND() 簡單可用
  └─ 否
       ├─ ID 連續/稠密 → 方案四 (單 SQL) 或 方案二
       ├─ 需嚴格均勻 → 方案三 (中小表) / 方案 A 預計算欄位 (大表)
       └─ 極大表 (億級) → 方案 B 應用層抽樣 / 方案 C TABLESAMPLE
copyright © 萬盛學電腦網 all rights reserved