問題場景：需要從大表快速取得隨機樣本
常見需求：推薦系統、廣告輪播、測試資料抽樣、問卷隨機抽題。直覺寫法：
SELECT * FROM table_name ORDER BY RAND() LIMIT 5;
方案一：ORDER BY RAND() —— 簡單但不適合大表
機制
- 為每行產生隨機數 → 臨時表/檔案排序 → 取前 N 行
- MySQL 3.23+ 支援，語法標準相容
實測效能（8,000 筆資料）
Query_time: 0.08 sec -- 全表掃描 + 檔案排序
資料量增長呈 **O(N log N)** 惡化，百萬級表可能耗秒甚至分鐘。
方案二：索引利用的隨機跳躍法（需 MySQL 4.1+ 子查詢支援）
SELECT *
FROM table_name AS r1
JOIN (
SELECT ROUND(RAND() * (SELECT MAX(id) FROM table_name)) AS id
) AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 5;
原理解析
- 取得主鍵最大值 `MAX(id)`
- 產生隨機起始點 `RAND() * MAX(id)`
- 利用索引 `WHERE id >= random_point` 直接定位
- 順序掃描取滿 LIMIT 行
實測效能（同 8,000 筆）
Query_time: 0.02 sec -- 索引範圍掃描，快 4 倍
適用條件與限制
- ✅ 表有**連續/近似連續**自增主鍵（或唯一索引）
- ✅ 資料分佈相對均勻
- ❌ 大量刪除導致主鍵稀疏 → 隨機點落入空洞，結果偏向後段資料
- ❌ 非自增主鍵（UUID、字串鍵）無法直接套用
- ❌ MySQL 4.0 及以下不支援子查詢
方案三：應用層兩階段查詢（通用、無版本限制）
-- 步驟 1：取得總行數（可快取）
SELECT COUNT(*) FROM table_name; -- 假設 1,000,000
-- 步驟 2：應用層產生隨機偏移
offset = random(0, 999995) -- 留 5 行緩衝
-- 步驟 3：利用 LIMIT offset, 5
SELECT * FROM table_name LIMIT 12345, 5;
優缺點
- ✅ 所有版本、所有表結構通用
- ✅ 應用層可實作複雜抽樣邏輯（分層、加權）
- ⚠️ `LIMIT offset, N` 大 offset 仍需掃描前 offset 行（InnoDB 二級索引回表）
- ⚠️ 需兩次往返，網路延遲敏感場景不利
方案四：維護隨機排序欄位（寫入換讀取，極致讀效能）
- 表新增 `rand_sort FLOAT DEFAULT RAND()`
- 建立索引 `INDEX idx_rand (rand_sort)`
- 定期（每日/每小時）批次更新：`UPDATE table SET rand_sort = RAND()`
- 查詢：`SELECT * FROM table ORDER BY rand_sort LIMIT 5` → **索引順序掃描，極快**
權衡
- 寫入成本增加（批次更新鎖表/複制延遲）
- 儲存空間 +4 bytes/行
- 適合「讀多寫少、極度敏感隨機查詢延遲」場景
效能對比總結表
| 方案 | 適用版本 | 資料量擴展性 | 資料分佈公平性 | 實作複雜度 | 典型延遲(百萬行) |
|---|
| ORDER BY RAND() | 3.23+ | 極差 O(N log N) | 完美 | 極低 | >10 秒 |
| 索引跳躍法 | 4.1+ | 優 O(log N) | 主鍵稀疏時偏差 | 中 | ~5 ms |
| 應用層 OFFSET | 所有 | 中 O(offset) | 完美 | 低 | ~50 ms (offset 小) |
| 隨機排序欄位 | 所有 | 極優 O(1) | 完美 | 高(維護機制) | ~1 ms |
最佳實務建議
- **小表 (<1萬行)**：直接 `ORDER BY RAND()`，簡單可維護
- **中大表、有自增主鍵、MySQL 4.1+**：索引跳躍法首選
- **極高頻隨機讀取**：投資隨機排序欄位 + 定時重排任務
- **複雜抽樣邏輯（分層/加權/去重）**：應用層實作，資料庫只負責 `LIMIT offset, N`
- **生產環境監控**：慢查詢日誌記錄隨機查詢，設定 `long_query_time=1`
延伸：分頁隨機化（避免固定首頁內容）
-- 固定種子確保同一用戶分頁一致性
SELECT * FROM articles
WHERE category='tech'
ORDER BY RAND(USER_SEED)
LIMIT 20 OFFSET 40;
應用層為每用戶 Session 產生固定 `USER_SEED`，實現「個人化隨機但分頁穩定」。
