MySQL 高效隨機取單筆資料：從 ORDER BY RAND() 到毫秒級優化

為什麼 ORDER BY RAND() 很慢？

EXPLAIN SELECT * FROM money_logs ORDER BY RAND() LIMIT 1;
-- type=ALL, Using temporary; Using filesort
-- 15萬筆資料需 0.3 秒，資料量增長呈 N log N 惡化

無法使用索引，需建立臨時表並檔案排序，大表災難性。

優化演進四階段

階段 1：應用層產生隨機 ID (假設 ID 連續無空洞)

-- 1. 取得最大 ID (可快取)
SELECT MAX(id) FROM tbl;
-- 2. 應用層產生隨機數 $rand = mt_rand(1, $max_id);
-- 3. 直接點查
SELECT * FROM tbl WHERE id = $rand;

兩條極簡查詢，極快。但 ID 有空洞 (刪除導致) 會查無資料。

階段 2：資料庫層產生隨機 ID + JOIN 避免重複計算

SELECT t.* FROM tbl t
JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM tbl)) AS id) r
USING (id);

• 子查詢 SELECT MAX(id) 被優化器常數化
• 衍生表 r 只產生一行，JOIN 走 const 類型
• 無排序、無臨時表、無應用層介入

階段 3：處理 ID 空洞 (刪除導致不連續)

SELECT t.* FROM tbl t
JOIN (SELECT (RAND() * (SELECT MAX(id) FROM tbl)) AS id) r
WHERE t.id >= r.id
ORDER BY t.id ASC
LIMIT 1;

• 取 >= 隨機值 的第一筆，自動跳過空洞
• 可省略 CEIL()，直接用浮點數比較
• EXPLAIN 顯示 range 掃描極少行數

階段 4：非均勻分布修正 — 映射表 + 觸發器維護

當 ID 分布極不均勻 (如 1,2,4,8,16)，上述方法會讓大間距 ID 被選中機率飆升。

1. 建立映射表 map_tbl(row_id PK, real_id)，row_id 連續無空洞
2. 隨機查 row_id，再 JOIN 回主表
3. 用 AFTER INSERT/DELETE/UPDATE 觸發器同步維護映射表

-- 觸發器範例 (刪除時壓縮 row_id)
CREATE TRIGGER tad_tbl AFTER DELETE ON tbl
FOR EACH ROW BEGIN
  DELETE FROM map_tbl WHERE real_id = OLD.id;
  UPDATE map_tbl SET row_id = row_id - 1 WHERE real_id > OLD.id;
END;

批次取多筆隨機資料

用儲存程序迴圈寫入暫存表，再一次性讀取：

CREATE PROCEDURE get_rands(IN cnt INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS rands;
  CREATE TEMPORARY TABLE rands (rand_id INT);
  loop_me: LOOP
    IF cnt < 1 THEN LEAVE loop_me; END IF;
    INSERT INTO rands
    SELECT t.id FROM tbl t
    JOIN (SELECT (RAND() * (SELECT MAX(id) FROM tbl)) AS id) r
    WHERE t.id >= r.id ORDER BY t.id ASC LIMIT 1;
    SET cnt = cnt - 1;
  END LOOP;
END;

效能對比 (1000 次查詢平均)

優化版趨近常數時間，ORDER BY RAND() 百萬級近 3 分鐘，生產環境不可用。