MySQL ORDER BY RAND() 效率災難與優化方案實測

為什麼 ORDER BY RAND() 極慢？

• MySQL 為每行生成隨機數 → 臨時表 → 文件排序
• 15 萬行表查 5 行需 **8 秒+**
• 官方文檔明確不建議在 ORDER BY 中使用 RAND()

優化思路：利用主鍵連續性 + 隨機偏移

-- 核心思想：隨機一個 ID 範圍內的值，向後取第一條
SELECT * FROM `table` 
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;

實測對比（15 萬行表）

完整最優 SQL（含 MIN 修正 ID 不連續）

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;

多行隨機取樣（N 行不連續）

方案：循環執行 N 次單行查詢，或應用層收集 ID 後 WHERE id IN (...)。

-- PHP 示例
$ids = [];
for ($i=0; $i<5; $i++) {
    $id = $pdo->query("SELECT id FROM t ORDER BY RAND() LIMIT 1")->fetchColumn();
    $ids[] = $id;
}
$stmt = $pdo->prepare("SELECT * FROM t WHERE id IN (" . implode(',', array_fill(0,5,'?')) . ")");
$stmt->execute($ids);

適用條件與避坑

• ✅ 表有**自增/連續數值主鍵**
• ✅ 數據分佈相對均勻
• ❌ 主鍵非數值/大量空洞/極度傾斜 → 結果偏差大
• ❌ 需嚴格均勻隨機 → 應用層 Reservoir Sampling / 全表 ID 列表隨機抽取