MySQL 隨機查詢優化終極指南：從 5 秒到 0.0003 秒的完整演進

問題現場

5 萬行表，ORDER BY RAND() LIMIT 10 耗時 5 秒+。業務需高頻隨機推薦，完全不可接受。

優化演進時間軸

第一代：ORDER BY RAND()（基準）

SELECT * FROM `table` ORDER BY RAND() LIMIT 10;
-- 5 秒+，全表掃描 + 臨時表 + filesort

第二代：MAX(id) * RAND() JOIN（連續資料）

SELECT * FROM `table` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 10;
-- 0.01 秒，但返回連續 10 行，非真隨機

第三代：WHERE 子查詢版（0.5 秒 → 0.01 秒）

-- 未優化
SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;
-- 0.5 秒

-- 優化：RAND() 移出聚合
SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;
-- 0.01 秒

第四代：MIN/MAX 修正非零起始 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;
-- 解決 ID 從 10000 起導致前段資料被過度查詢

第五代：JOIN 最終版（單次 0.0015 秒，10 次 0.015 秒）

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 次總耗時 0.015 秒，單次 0.0015 秒

第六代：批量隨機 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 10;
-- 單次查詢取 10 行（連續段），業務可接受「近似隨機」
-- 耗時 0.0003 秒，比原始 5 秒快 16000 倍

效能對比總表

生產部署檢查清單

1. 確認 id 為主鍵/唯一索引，且型別為整數（BIGINT/INT）。
2. ID 分布極不均（大量洞）時，建立 seq_id 影子欄位（觸發器維護 1..N 連續），隨機查 seq_id 再 JOIN 回主表。
3. 非整數主鍵（UUID/字串）：CRC32() 或 CONV(SUBSTRING(MD5(),1,16),16,10) 映射為數值，建立函數索引（MySQL 8.0+）或影子欄位。
4. 極高併發（QPS > 1000）隨機讀：考慮 Redis SRANDMEMBER / ZRANDMEMBER 緩存熱點 ID 集合，降級走 MySQL。
5. 監控慢查詢日誌，確認無 ORDER BY RAND() 殘留。

MySQL 8.0 新選項：TABLESAMPLE

-- Bernoulli 採樣（每行獨立概率）
SELECT * FROM `table` TABLESAMPLE BERNOULLI (0.1) LIMIT 10;

-- SYSTEM 採樣（頁面級，更快但較粗糙）
SELECT * FROM `table` TABLESAMPLE SYSTEM (10) LIMIT 10;

無需索引、無需主鍵整數、真隨機、極快。但 8.0.25+ 才支援，且為近似行數。