MySQL 隨機查詢優化：從 8 秒降至 0.01 秒實戰

大表隨機抽樣效能優化全記錄

15 萬條資料 ORDER BY RAND() LIMIT 5 耗時 8 秒+，經優化降至 0.01 秒。核心思路：**避免全表掃描與多次 RAND() 計算，改用主鍵範圍隨機定位**。

演進路線

❌ 方案 0：ORDER BY RAND()

SELECT * FROM tablename ORDER BY RAND() LIMIT 1;

官方文檔明確不建議：ORDER BY 子句會多次評估 RAND()，導致多次全表掃描。

❌ 方案 1：MAX(id) * RAND()

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 條；若 ID 不從 1 起，概率偏向前段資料。

✅ 方案 2：JOIN + MIN/MAX 邊界（推薦）

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;

• 修正 ID 非連續、非從 1 開始的偏移問題
• JOIN 寫法比 WHERE 子查詢快 10 倍（實測 0.015s vs 0.147s）
• 需多筆隨機時：單次 LIMIT 1，應用層循環呼叫 N 次

✅ 方案 3：WHERE 子查詢版（較慢但簡單）

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;

關鍵優化點總結

1. 利用主鍵索引定位，不掃描全表
2. 用 MIN(id)、MAX(id) 界定隨機範圍
3. 優先用 JOIN 而非 WHERE 子查詢
4. 多筆需求 → 應用層循環單筆查詢，避免連續資料

適用條件

• 表有自增/連續數字主鍵
• 可接受「非絕對均勻」隨機（ID 稀疏區概率較低）
• 極高均勻需求可建立專門隨機排序欄位