MySQL ORDER BY RAND() 效能陷阱與優化方案

問題背景

常見隨機抽取寫法：

SELECT * FROM t ORDER BY RAND() LIMIT 1;

官方文檔警告：ORDER BY 子句中使用 RAND() 會導致該列被多次掃描評估，效率極低。實測 15 萬行表查 5 行耗時 8 秒+。

優化思路：利用主鍵範圍隨機

核心思路：先在主鍵 ID 範圍內隨機生成一個值，再查找大於等於該值的第一行。

方案一：JOIN 子查詢（最高效）

SELECT t1.*
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 ASC LIMIT 1;

• 子查詢僅執行一次，生成隨機 ID。
• 主查詢利用索引快速定位。
• 實測 10 次查詢僅 0.015 秒。

方案二：WHERE 子查詢（次之）

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;

• 實測 10 次查詢約 0.147 秒，比 JOIN 慢近 10 倍。
• MySQL 優化器對 WHERE 子查詢處理較弱。

關鍵細節：必須包含 MIN(id)

若表主鍵不從 1 開始或有刪除空洞，直接用 MAX(id) * RAND() 會導致前段數據被查中概率極高。加上 MIN(id) 偏移量可保證均勻分布。

適用條件與局限

• 表必須有連續或近似連續的數值型主鍵（自增 ID 最佳）。
• 大量刪除導致 ID 稀疏時，隨機分布會偏向稠密區段。
• 需隨機抽取多行時，建議循環調用單行查詢，或生成多個隨機 ID 批量 IN 查詢。

替代方案對比