MySQL RAND() 隨機查詢效能陷阱與優化方案：從 8 秒到 0.01 秒

現狀痛點

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

官方文檔明確警告：ORDER BY RAND() 會導致資料多次掃描。實測 15 萬行表查 5 條耗時 **8 秒+**。

優化演進路線

方案 1：輔助隨機數表 (適用離散範圍)

CREATE TABLE randnum (num INT);
INSERT INTO randnum VALUES (-5),(-4),...,(-1),(0),(1),...,(5);
SELECT num FROM randnum ORDER BY RAND() LIMIT 1;

缺點：範圍大時建表維護成本高。

方案 2：ROUND + RAND 數學生成 (單語句)

SELECT ROUND((0.5 - RAND()) * 2 * 5);  -- 產生 -5 ~ 5 整數

仍需配合業務主表查詢，未根本解決 ORDER BY RAND() 問題。

方案 3：MAX(id) * RAND() + JOIN (突破口)

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 次，單次 **0.01 秒**。

方案 4：WHERE 子查詢優化 (0.5 秒 → 0.01 秒)

-- 初版
SELECT * FROM `table` 
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`) 
ORDER BY id LIMIT 1;

-- 加入 MIN(id) 修正分佈傾斜 (關鍵！)
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;

方案 5：JOIN 寫法最終優化 (0.015 秒 / 10 次)

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;

測試 10 次平均：JOIN 寫法 **0.015 秒**，WHERE 子查詢 **0.147 秒**。JOIN 讓優化器更好利用索引範圍掃描。

核心原理解析

• 避免全表掃描：利用主鍵索引範圍查詢 (id >= random_point) 替代 ORDER BY RAND() 的 filesort。
• 修正 ID 不連續/不從 1 開始：引入 MIN(id) 偏移量，確保隨機點落在實際 ID 分佈區間內。
• JOIN 優於 WHERE 子查詢：MySQL 優化器對 JOIN ... ON t1.id >= t2.id 能產生更優執行計畫 (範圍存取 vs 全表過濾)。

適用條件與限制

• 表需有 **自增主鍵 (或單調遞增唯一鍵)**。
• 資料量極大且 ID 稀疏時，隨機點可能落在大空洞導致 LIMIT 1 掃描較多行；可改用 OFFSET 方案 (需預先 COUNT(*))。
• 嚴格均勻隨機需求：此法為「近似隨機」，極端分佈下有微小偏差，工程上可接受。

結語

拒絕 ORDER BY RAND()！採用 MAX/MIN(id) 計算隨機點 + JOIN 範圍掃描，將百萬級表隨機查詢從秒級壓至 **毫秒級**。代碼可封裝為存儲函數 RANDOM_ROW(tbl_name) 供業務複用。