MySQL ORDER BY RAND() 效能優化：從 8 秒降至 0.01 秒的實戰方案

痛點：ORDER BY RAND() 為何慢？

• MySQL 必須為每行產生隨機數、排序、再取 LIMIT，時間複雜度 O(N log N)。
• 15 萬行測試：SELECT * FROM t ORDER BY RAND() LIMIT 5 耗時 8 秒以上。
• 官方文檔明確警告：ORDER BY RAND() 會掃描全表並多次評估隨機值。

優化思路：利用主鍵 ID 連續性 + 隨機偏移

假設表有自增主鍵 id，且刪除較少（ID 較密集）。

方案 A：子查詢 + WHERE 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;

• 執行時間：0.01 秒。
• 原理：隨機挑一個 ID 區間起點，向後取第一筆。
• 缺點：ID 有洞（大量刪除）時，分布不均，易連續抽到同幾筆。

方案 B：JOIN 衍生表（官方推薦模式，效能最佳）

SELECT t1.*
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;

• 執行時間：0.0015 秒（10 次平均 0.015 秒）。
• 衍生表只產生 1 行隨機 ID，JOIN 走主鍵索引 range，極快。

方案 C：需要多筆不重複隨機行 → 多次單查或應用層去重

上述方案單次只能取 1 行（或連續 N 行）。若需 5 筆不重複：

1. 應用層循環呼叫 5 次方案 B（總耗時 ~0.05 秒，可接受）。
2. 或一次取較大範圍（如 LIMIT 20），應用層 shuffle 取 5 筆。

進階：ID 不連續/分布不均的改良版

-- 先取總行數
SELECT COUNT(*) INTO @total FROM `table`;

-- 隨機 OFFSET（需 MySQL 8.0+ 支援 CTE/變數）
SET @offset = FLOOR(RAND() * @total);

PREPARE stmt FROM 'SELECT * FROM `table` LIMIT ?, 1';
EXECUTE stmt USING @offset;
DEALLOCATE PREPARE stmt;

缺點：LIMIT offset, 1 深分頁同樣慢（需掃描 offset 行）。適合中小表（< 10 萬）。

對比總結表

MySQL 8.0.23+ 新功能：TABLESAMPLE

SELECT * FROM `table` TABLESAMPLE SYSTEM (10) LIMIT 5;
-- SYSTEM: 頁面層級採樣，適合大表近似隨機
SELECT * FROM `table` TABLESAMPLE BERNOULLI (10) LIMIT 5;
-- BERNOULLI: 行層級獨立採樣，較均勻但較慢

適用於分析型查詢，不適合業務精確隨機抽獎。

總結

生產環境嚴禁 ORDER BY RAND()。
主鍵 ID 較密集時：JOIN 衍生表 + 隨機 ID 起點 是效能與簡潔度的最佳平衡（毫秒級）。
需嚴格均勻分布：應用層 COUNT(*) + 多次 LIMIT offset, 1 或 TABLESAMPLE BERNOULLI。
永遠記得：EXPLAIN 驗證 type=ref/range，而非 ALL。