MySQL 隨機抽樣查詢優化：ORDER BY RAND() 效能陷阱與 JOIN 子查詢高效替代方案

問題：ORDER BY RAND() 效能災難

SELECT * FROM big_table ORDER BY RAND() LIMIT 10;

• MySQL 必須為全表產生隨機數、排序、再取前 N 筆 → O(N log N)，全表掃描 + 臨時表 + filesort。
• 8,000 筆測試耗時 0.08s；百萬筆將達秒級甚至分鐘級。

優化方案：JOIN 子查詢 (需 MySQL 4.1+ 支援子查詢)

SELECT t.*
FROM big_table AS t
JOIN (
    SELECT ROUND(RAND() * (SELECT MAX(id) FROM big_table)) AS rand_id
) AS r ON t.id >= r.rand_id
ORDER BY t.id ASC
LIMIT 5;

• 原理：利用主鍵連續性，隨機取一個起始 ID，向後取 N 筆。
• 測試同表耗時 0.02s，**提升 4 倍**。
• 前提：id 為自增主鍵且無大量空洞 (刪除導致)。

進階方案：多點隨機 + UNION (分佈更均勻)

(SELECT * FROM big_table WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM big_table) ORDER BY id LIMIT 2)
UNION ALL
(SELECT * FROM big_table WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM big_table) ORDER BY id LIMIT 2)
UNION ALL
(SELECT * FROM big_table WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM big_table) ORDER BY id LIMIT 1)
LIMIT 5;

通用最佳實踐 (應用層)

1. 應用層隨機：先 SELECT COUNT(*) → 應用產生隨機 offset → LIMIT offset, N (需評估大 offset 效能)。
2. 系統表抽樣：MySQL 8.0.2+ TABLESAMPLE SYSTEM (10) (伯努利抽樣)。
3. 專用抽樣表：維護一張 sample_ids 定期重建 (含隨機排序的主鍵)，查詢時直接 JOIN。

效能對比總結