MySQL 隨機查詢優化：從 ORDER BY RAND() 到毫秒級方案

痛點：ORDER BY RAND() 的效率災難

對於 15 萬行的表，SELECT * FROM \`table\` ORDER BY RAND() LIMIT 5 耗時 8 秒以上。官方文檔明確指出：RAND() 在 ORDER BY 中會被多次評估，導致全表掃描並生成臨時表排序，性能極差。

演進之路：基於主鍵範圍的隨機採樣

核心思路：利用主鍵 id 的連續性（或近似連續），先隨機生成一個主鍵值，再向後查找 N 條記錄。

方案一：子查詢 + WHERE 篩選（0.5 秒）

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

缺點：未考慮 MIN(id)，導致一半概率查到表頭數據；子查詢每行可能重複執行。

方案二：優化子查詢，引入 MIN/MAX 範圍（0.01 秒）

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;

修正了範圍偏移，性能大幅提升。

方案三：JOIN 衍生表寫法（極致性能 0.0015 秒/次）

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;

原理：衍生表 t2 僅產生一個隨機起始 ID，MySQL 優化器能將其物化，避免重複計算；JOIN 配合索引定位極快。實測 10 次查詢總耗時 0.015 秒，較方案二快 10 倍。

避坑指南與最佳實踐

• 主鍵不連續怎麼辦？ 方案二/三基於 id >= 隨機值，即使有空洞也能正確返回「大於等於該值的第一條記錄」，不影響隨機性，但分布可能略微不均。極端稀疏時可考慮 TABLESAMPLE (MySQL 8.0+) 或預先生成隨機 ID 列表。
• 需要多條隨機記錄？ 將 LIMIT 1 改為 LIMIT N 即可獲取連續 N 條；若要求不連續，可在應用層循環調用或使用 UNION ALL 多個隨機起點。
• 非整數主鍵/無主鍵？ 建議添加自增代理鍵，或使用 ROW_NUMBER() OVER () (8.0+) 生成臨時序號。

總結

拒絕 ORDER BY RAND()。採用 JOIN + 衍生表 + 主鍵範圍隨機 模式，可將百萬級表隨機查詢從秒級降至毫秒級，是高併發場景下的標準解法。