MySQL 隨機抽樣查詢：從 ORDER BY RAND() 到高性能 JOIN 方案演進

需求：從千萬級表中高效隨機抽取 N 行。本文對比三種方案性能，揭示 ORDER BY RAND() 的全表掃描本質，推薦基於主鍵範圍的 JOIN 方案，並給出生產環境參數化模板。

方案對比測試（8,000 行測試表）

高性能方案：主鍵範圍 + JOIN

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

原理解析

1. 子查詢 SELECT MAX(id) 獲取主鍵上界（極快，走索引）。
2. RAND() * MAX(id) 生成隨機起始點。
3. JOIN 關聯主表，WHERE t1.id >= t2.id 定位到該點及其後的行。
4. ORDER BY t1.id LIMIT N 取連續 N 行（主鍵聚簇索引順序讀取，零開銷）。

優勢

• **只掃描索引**，不讀取數據頁（覆蓋索引）。
• **無臨時表、無 filesort**，執行計劃 ref / range。
• 毫秒級穩定，隨表增長性能幾乎不退化。

局限與改進

• 主鍵不連續/有洞：抽樣概率不均勻（大洞後的行被過度採樣）。
  → 改進：SELECT id FROM t ORDER BY id LIMIT 1 OFFSET FLOOR(RAND() * (SELECT COUNT(*) FROM t)) 獲取真隨機主鍵，再 JOIN 取數據（兩次索引查找，仍極快）。
• 需多行且不連續：循環調用上述單行查詢，或用 UNION ALL 合併多個隨機點。
• 非整數主鍵/聯合主鍵：需映射到連續整數序列（可用 ROW_NUMBER() 物化視圖）。

生產環境參數化模板（MySQL 8.0+ CTE 寫法）

WITH rand_start AS (
    SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM `big_table`)) AS offset_val
),
rand_id AS (
    SELECT id FROM `big_table` ORDER BY id LIMIT 1 OFFSET (SELECT offset_val FROM rand_start)
)
SELECT b.* FROM `big_table` b
JOIN rand_id r ON b.id = r.id;

總結建議

1. 開發/測試/小表：ORDER BY RAND() LIMIT N 簡單直觀。
2. 生產大表/高頻：**主鍵範圍 JOIN** 或 **CTE + OFFSET** 方案，性能提升 2~3 個數量級。
3. 嚴格均勻隨機需求：接受雙查詢開銷，用 OFFSET RAND()*COUNT(*) 取真隨機主鍵。
4. 監控 Handler_read_rnd_next 指標，驗證是否退化為全表掃描。

隨機抽樣看似簡單，實則考驗對索引、執行計劃、存儲引擎特性的綜合理解。選對方案，千萬行表也能毫秒級返回。