MySQL 高效隨機查詢：從 ORDER BY RAND() 到 JOIN 優化的完整演進

為什麼 ORDER BY RAND() 極其低效？

MySQL 會為每行生成隨機數、建立臨時表、排序、再取前 N 行。15 萬行查 5 行耗時 8 秒+，複雜度 O(N log N)，大表不可用。

優化演進路線圖

階段 1：基於 MAX(id) 的隨機偏移（連續資料問題）

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 LIMIT 5;

⚠️ 產生連續 5 行，非真正隨機。需執行 5 次 LIMIT 1 並去重。

階段 2：子查詢 WHERE 版（0.5 秒）

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

階段 3：優化子查詢執行計畫（0.01 秒）

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

將 RAND() * MAX(id) 移出聚合函數，避免重複掃描。

階段 4：引入 MIN(id) 修正非零起始 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 從 10000 起始導致前段資料被過度查詢的問題。

階段 5：JOIN 版最終形態（0.0015 秒/次，10 次僅 0.015 秒）

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;

JOIN 版比 WHERE 版快近 10 倍，因衍生表僅掃描一次索引，且優化器能更好利用索引範圍掃描。

多行隨機查詢策略

1. 小表（<1 萬行）：ORDER BY RAND() LIMIT N 可接受。
2. 大表需真正隨機 N 行：循環執行單行 JOIN 版 N 次，應用層去重（或用 UNION）。
3. 近似隨機：SELECT * FROM t WHERE id >= (隨機起點) ORDER BY id LIMIT N 取連續段，效能極高。
4. UUID/非整數主鍵：用 CRC32() 或 MD5() 轉數值，或維護對應自增影子欄位。

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

生產部署建議

• 確保 id 有索引（主鍵自動）。
• ID 分布極不均勻（大量洞）時，考慮維護「連續序號」影子欄位。
• 極高併發隨機讀場景，評估 Redis 集合 SRANDMEMBER 替代。