MySQL ORDER BY RAND() 效能災難與高效隨機查詢最佳實踐

為什麼 ORDER BY RAND() 很慢？

• MySQL 必須為每行產生隨機數、建立臨時表、排序、再取前 N 行。
• 複雜度 O(N log N)，15 萬行查 5 行需 8 秒以上。
• 官方文檔明確警告：ORDER BY 子句中不可使用 RAND()。

高效替代方案核心思路

利用主鍵 id 連續性（或近似），在應用層產生隨機 ID，再回表查詢。

方案一：單行隨機（最推薦，效能穩定）

-- 適用於 AUTO_INCREMENT 主鍵、刪除較少、ID 分布均勻
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;

原理：子查詢計算隨機 ID 範圍，外層查詢走主鍵索引 range 掃描，極快（< 0.01 秒）。

方案二：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 LIMIT 1;

實測 10 次平均 0.015 秒，比 WHERE 子查詢版（0.147 秒）快 10 倍。

方案三：多行隨機（N 行不連續）

方法 A：應用層循環 N 次方案一（推薦，簡單高效）。

方法 B：單次查詢（複雜）：

SELECT * FROM `table` WHERE id IN (
    SELECT FLOOR(RAND() * (max_id - min_id) + min_id)
    FROM (SELECT MAX(id) max_id, MIN(id) min_id FROM `table`) t
    -- 重複 N 次 UNION ALL 或用變數生成 N 個隨機數
);

方案四：大表/ID 不連續/需嚴格均勻分布

1. 建立輔助表 random_picks (id PRIMARY KEY)，定期任務填入所有 ID。
2. 隨機抽樣：SELECT id FROM random_picks ORDER BY RAND() LIMIT N（輔助表較小）。
3. 或使用 TABLESAMPLE（MySQL 8.0.23+ 支援 BERNOULLI/SYSTEM 採樣）。

效能對比總結（15 萬行測試）

避坑指南

• 必須加 MIN(id) 判斷，否則 50% 機率落在表頭前幾行。
• 大量刪除導致 ID 稀疏時，方案一二會偏向密集區，改用方案四。
• 嚴格統計隨機需求（如抽獎），務必用加密級隨機源（PHP random_int()、Java SecureRandom）。

拒絕 ORDER BY RAND()，擁抱索引友好的隨機查詢模式。