場景
從百萬級表 reportcard_patient_temp 隨機抽取 10 條記錄，業務要求響應 < 100ms。
方案對比實測 (40 萬行表)
| 方案 | SQL | 耗時 | EXPLAIN 關鍵信息 | 評價 |
|---|
| 1. ORDER BY RAND() | SELECT * FROM t ORDER BY RAND() LIMIT 10; | 1.2 s | Using temporary; Using filesort | 全表掃描 + 內存臨時表排序，極慢 |
| 2. 主鍵 >= RAND()*MAX (需自增連續) | SELECT * FROM t WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) FROM t) ORDER BY id LIMIT 10; | ~50 ms | 子查詢 MAX(id) + 主鍵範圍 | 要求 ID 連續無大洞，否則分布不均 |
| 3. COUNT(*) * RAND() 偏移映射 (作者自創) | SELECT * FROM (SELECT zyid, ROUND(COUNT(*)*RAND()*100) AS rn FROM t GROUP BY zyid) x ORDER BY rn LIMIT 10; | 0.2 s | 聚合 + 排序 | 比方案 1 快 6 倍，但仍有臨時表排序 |
| 4. 游標分頁 / Keyset (最優) | SELECT * FROM t WHERE id > (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t))) ORDER BY id LIMIT 10; | < 5 ms | 主鍵範圍掃描 | 無臨時表、無 Filesort、利用索引順序 |
| 5. 預計算隨機權重列 (寫入時賦值) | ALTER TABLE t ADD rand_weight DOUBLE; UPDATE t SET rand_weight=RAND(); CREATE INDEX idx_rw (rand_weight);
SELECT * FROM t ORDER BY rand_weight LIMIT 10; | < 1 ms | ref 索引掃描 | 寫入成本換讀取極速，適合高頻隨機抽樣 |
方案 4 詳解：基於主鍵範圍的隨機游標
-- 1. 添加列並建索引 (一次性)
ALTER TABLE `table` ADD COLUMN `rand_weight` DOUBLE NOT NULL DEFAULT 0;
UPDATE `table` SET `rand_weight` = RAND();
CREATE INDEX `idx_rand_weight` ON `table` (`rand_weight`);
-- 2. 寫入時同步賦值 (應用層或觸發器)
INSERT INTO `table` (..., `rand_weight`) VALUES (..., RAND());
-- 3. 隨機查詢 (毫秒級)
SELECT * FROM `table` ORDER BY `rand_weight` LIMIT 10;
- 索引
idx_rand_weight 已按隨機值排序，**ORDER BY 直接走索引順序**，無 Filesort、無臨時表。
- 每次查詢只掃描索引前 10 個條目，**極致性能**。
- 維護成本：寫入多一列、索引多一枝、批量回填歷史數據。
避坑指南
- ❌
ORDER BY RAND() **生產環境禁用**，百萬級表秒級起步，併發下直接拖垮實例。
- ❌ 方案 2 用
MAX(id) 而非 COUNT(*)，前者不受刪除空洞影響，後者會導致偏移計算錯誤。
- ⚠️ 方案 4/5 在主鍵非單調 (如 UUID v4) 時失效，需改用
rand_weight 列。
- ✅ 所有方案 **EXPLAIN 驗收**：確認
type=ref/range、rows 極小、無 Using temporary/filesort。
總結選型
| 業務特徵 | 推薦方案 | 改造成本 |
|---|
| 低頻隨機、表 < 10 萬、可接受秒級 | ORDER BY RAND() (僅開發/測試) | 0 |
| 中頻、主鍵自增、不想加列 | 方案 4: 主鍵範圍隨機游標 | 低 (只改 SQL) |
| 高頻隨機抽樣、寫入量可接受額外列 | 方案 5: 預計算 rand_weight 列 + 索引 | 中 (加列、回填、索引) |
| 極高併發、分布式、需嚴格均勻 | 應用層算法 (一致性哈希/儲水池抽樣) + Redis 預計算 | 高 (架構級) |
