MySQL 隨機查詢優化：從 ORDER BY RAND() 到 JOIN 方案的性能實測對比

問題背景

表 table 約 15 萬行，需隨機抽取 5 條記錄。最直觀的寫法：

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

實測耗時：8 秒+ —— 完全不可接受。

官方文檔解釋：RAND() 在 ORDER BY 中會被多次計算，導致全表掃描 + 臨時表排序，複雜度 O(N log N)。

方案演進與實測對比

方案一：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 條連續記錄，隨機性差
• 變通：每次 LIMIT 1 查 5 次，單次 0.01s

方案二：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.5s
• 問題：ID 不連續時，前段數據被查中的概率極高（需引入 MIN(ID) 修正）

方案三：優化版 WHERE 子查詢（引入 MIN/MAX 範圍）

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.01s ✅
• 原理：在 [MIN, MAX] 範圍內均勻隨機，避免 ID 稀疏導致的分布偏態

方案四：JOIN 版（終極性能王者）

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.0015s 🚀
• PHP 循環 10 次總耗時：0.015s（vs 方案三 0.147s）

性能總結表

核心原理解析

1. 避免全表排序：利用主鍵 id 有序特性，將隨機轉化為「隨機起點 + 範圍掃描」
2. JOIN 優於 WHERE 子查詢：MySQL 對 JOIN 的優化器支持更好，衍生表物化後可直接參與索引查找，而 WHERE 子查詢可能重複執行
3. MIN/MAX 修正偏態：自增 ID 刪除後不連續，直接 MAX*RAND 會導致前段數據概率加倍，引入 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;

-- PHP 批量獲取 N 條不重複隨機記錄
$ids = [];
$results = [];
for ($i = 0; $i < $N; $i++) {
    $sql = "上述模板";
    $row = $db->query($sql)->fetch();
    if ($row && !in_array($row['id'], $ids)) {
        $ids[] = $row['id'];
        $results[] = $row;
    }
}
// 15萬表，N=20 總耗時約 0.03s

避坑提醒

• ID 極度稀疏/非數值型主鍵：需改用 OFFSET FLOOR(RAND() * COUNT(*)) 方案（需覆蓋索引支持）
• 極高併發：考慮預生成隨機 ID 池（Redis 存儲），應用層直接取 ID 再查詳情
• MySQL 8.0+：可使用 TABLESAMPLE（伯努利/系統採樣），但僅支持 InnoDB 且為近似隨機

結語

MySQL 隨機查詢的本質是「如何用索引替代全表排序」。JOIN + 衍生表 + 主鍵範圍查找 是經過實測驗證的最高性能方案，將 15 萬表隨機查詢從 8 秒降至 1.5 毫秒，性能提升 5000 倍。記住：ORDER BY RAND() 只屬於教科書和極小表。