MySQL RAND() 性能優化實戰：從全表掃描到 JOIN 子查詢的百倍提速

前言

ORDER BY RAND() LIMIT N 是 MySQL 著名的性能殺手：全表掃描 + 臨時表 + filesort。本文通過 7 組對比實驗（含 EXPLAIN、耗時），系統展示如何將隨機抽樣從 0.41 秒優化至 0.00 秒，核心技巧是將 RAND() 下推至 JOIN 衍生表，轉為常量等值/範圍查詢。

實驗環境

• 表 t_innodb_random：39 萬行，id INT UNSIGNED、user VARCHAR(64)、KEY idx_id(id)，無顯式主鍵（隱式聚簇索引 GEN_CLUST_INDEX）。

七輪對比實驗

核心原理圖解

-- 慢：全表賦值隨機數 -> 排序 -> 取前 N
SELECT id FROM t ORDER BY RAND() LIMIT 1000;

-- 快：衍生表產生 1 個隨機常量 -> 索引範圍/等值查找 -> 直接取 N 行
SELECT t1.id
FROM t_innodb_random t1
JOIN (SELECT RAND() * (SELECT MAX(id) FROM t_innodb_random) AS nid) t2
  ON t1.id > t2.nid
LIMIT 1000;

• RAND() 在衍生表中僅執行 1 次，結果作為常量參與 JOIN ON 條件。
• 優化器識別出 t1.id > const 可走 range 訪問，直接定位葉子頁，無臨時表、無 filesort。

三種生產可用模板

1. 取 1 行（等值，可能查不到）

SELECT t1.*
FROM t t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t)) AS nid) t2
  ON t1.id = t2.nid;

2. 取 1 行（範圍，必得）

SELECT t1.*
FROM t t1
JOIN (SELECT RAND() * (SELECT MAX(id) FROM t) AS nid) t2
  ON t1.id > t2.nid
LIMIT 1;

3. 取 N 行（批量，最高效）

SELECT t1.*
FROM t t1
JOIN (SELECT RAND() * (SELECT MAX(id) FROM t) AS nid) t2
  ON t1.id > t2.nid
LIMIT N;

4. 追求「完全隨機分佈」（代價略高）

SELECT t1.id
FROM t t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t)) AS nid FROM t LIMIT N) t2
  ON t1.id = t2.nid;

避坑指南

• ID 不連續/有洞：範圍法（>）仍正確，等值法（=）可能返回空集。
• 表極小（<1000 行）：ORDER BY RAND() 反而更簡單，優化收益微乎其微。
• 需多列隨機：只抽 id 回表，或直接 SELECT * 視業務而定。
• 並發高：衍生表每次執行重新計算 MAX(id)，可考慮應用層緩存最大 ID 或定時任務維護。

結語：MySQL 隨機抽樣的黃金法則——「把 RAND() 鎖在衍生表裡，讓優化器看到常量」。將 ORDER BY RAND() LIMIT N 改寫為 JOIN (SELECT RAND()*MAX(id)...) ON id > nid LIMIT N，即可在保證業務語義的前提下，獲得百倍性能提升。