MySQL 隨機抽取資料高效能寫法：從 ORDER BY RAND() 到索引優化方案

朴素寫法：ORDER BY RAND() — 簡單但極慢

SELECT * FROM `table` ORDER BY RAND() LIMIT 1;

**缺點**：MySQL 需為全表產生隨機數、排序、再取前 N 筆，複雜度 O(N log N)，百萬筆資料查詢可達秒級，生產環境**嚴禁使用**。

進階寫法：MAX(ID) * RAND() — 連續區塊問題

SELECT * FROM `table` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
  ON t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

**缺點**：取得的是 **連續 5 筆** 而非真正隨機 5 筆；ID 有空洞（刪除導致）時分布不均；需重複執行 5 次才能得到 5 筆不連續資料，效率仍低。

高效寫法：ID 範圍隨機 + 索引覆蓋

SELECT * FROM `user`
WHERE userId >= (
    (SELECT MAX(userId) FROM `user`) - (SELECT MIN(userId) FROM `user`)
) * RAND() + (SELECT MIN(userId) FROM `user`)
LIMIT 5;

**原理**：

1. 計算 ID 範圍 [MIN, MAX]。
2. 在範圍內產生隨機起始點。
3. 利用 **聚簇索引（主鍵）範圍掃描** 直接定位，複雜度 O(log N + Limit)。

**適用條件**：

• 主鍵為 **自增整數（INT/BIGINT）**，分布相對均勻。
• 允許極小概率返回少於 LIMIT 筆（邊界情況可用 UNION ALL 補足）。

通用最佳實踐：兩階段隨機抽樣

**適用任意主鍵型別、任意分布、需精確 N 筆不重複**：

-- 階段 1：取得符合條件的主鍵總數
SELECT COUNT(*) INTO @total FROM `table` WHERE ...;

-- 階段 2：應用層產生 N 個不重複隨機數 (0 ~ @total-1)
-- 假設應用層產生 3, 17, 42

-- 階段 3：資料庫端使用 LIMIT offset, 1 逐筆抓取
SELECT * FROM `table` WHERE ... LIMIT 3, 1;
SELECT * FROM `table` WHERE ... LIMIT 17, 1;
SELECT * FROM `table` WHERE ... LIMIT 42, 1;

**優化**：將 N 筆 OFFSET 合併為單一查詢（MySQL 8.0+ 支援 LIMIT ... OFFSET ... 陣列語法，或使用 UNION ALL）。

大表專用：TABLESAMPLE / Bernoulli 抽樣（MySQL 8.0.23+）

SELECT * FROM `table` TABLESAMPLE SYSTEM (10) LIMIT 100;

• SYSTEM：頁面層級抽樣，極快但不精確。
• BERNOULLI：列層級獨立抽樣，統計特性較好。
• 適合 **統計分析、近似查詢**，不適合需精確業務邏輯。

效能對比總結

實務建議

• **高頻隨機抽取**（如廣告輪播、推薦候選）：維護獨立「候選池表」，定期離線打亂排序，線上直接 ORDER BY rand_sort LIMIT N。
• **分頁隨機**：使用 WHERE id > last_seen_id ORDER BY id LIMIT N 替代 OFFSET，避免深分頁效能崩塌。
• **監控**：慢查詢日誌記錄 Rows_examined，確認掃描行數符合預期。