MySQL 隨機查詢優化：ORDER BY RAND() 替代方案與 JOIN 寫法性能實測

ORDER BY RAND() LIMIT N 在大表上極慢（全表掃描+文件排序+臨時表）。本文實測對比 4 種隨機查詢方案：RAND()、MAX(id)*RAND() 子查詢、JOIN 關聯、JOIN + MIN/MAX 範圍，給出百萬級大表最優寫法與分批隨機抽樣策略。

方案對比實測（15 萬行表，抽 1 行）

最優寫法（方案 4 完整版）

SELECT t.*
FROM `table` t
JOIN (
  SELECT ROUND(RAND() * (
    (SELECT MAX(id) FROM `table`) - (SELECT MIN(id) FROM `table`)
  ) + (SELECT MIN(id) FROM `table`)) AS rand_id
) r ON t.id >= r.rand_id
ORDER BY t.id
LIMIT 1;

原理解析

1. 子查詢 r 僅掃描聚合索引（MAX(id)/MIN(id)），極快
2. 計算隨機 ID 落在 [MIN, MAX] 範圍內
3. JOIN ... ON t.id >= r.rand_id 利用主鍵索引範圍查找，定位到第一個 ≥ 隨機值的行
4. ORDER BY t.id LIMIT 1 取該行，無需排序全表

ID 不連續/有洞的分布偏差修正

上述方案仍有偏差：ID 稀疏區間的行被選中概率 ∝ 區間長度。極致均勻需：

-- 方案 A：預計算行號映射表（維護成本高）
-- 方案 B：應用層兩次查詢
-- 1. SELECT COUNT(*) FROM t; -- total
-- 2. offset = FLOOR(RAND() * total)
-- 3. SELECT * FROM t LIMIT offset, 1;  -- 仍需掃描 offset 行

-- 方案 C：TABLESAMPLE (8.0.3+ 不支持)
-- 方案 D：工程妥協 —— 方案 4 已足夠優，偏差業務可接受

抽取 N 行不重複（分批單抽）

-- 應用層循環 N 次調用方案 4，收集 ID 去重
-- 或單次 SQL（8.0+ CTE）
WITH RECURSIVE rnd AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n+1 FROM rnd WHERE n < 5
)
SELECT t.* FROM `table` t
JOIN (
  SELECT ROUND(RAND() * ((SELECT MAX(id)) - (SELECT MIN(id))) + (SELECT MIN(id))) AS rand_id
  FROM rnd
) r ON t.id >= r.rand_id
GROUP BY t.id  -- 去重
LIMIT 5;

大表分區/分表場景

• 分區表：在各分區獨立隨機，再 UNION
• 分表：應用層隨機選表 + 表內隨機
• 百萬級以上：建議維護 random_id 列（隨機浮點數）+ 索引，ORDER BY random_id LIMIT N 穩定毫秒級

總結

禁用 ORDER BY RAND()。百萬級以內：**JOIN + MIN/MAX 範圍隨機 ID** 穩定 0.01 秒。百萬級以上：**預計算 random_id 列 + 索引** 或 **應用層 COUNT + OFFSET**（接受掃描成本）。核心原則：**利用主鍵索引範圍查找替代全表排序**。