萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ORDER BY RAND() 效率優化:從 8 秒到 0.01 秒的 JOIN 索引掃描方案

問題現場

15 萬行表,SELECT * FROM t ORDER BY RAND() LIMIT 5 耗時 **8 秒+**。官方文檔警告:ORDER BY 中 RAND() 會被多次計算,導致全表掃描 + 臨時表 + filesort。

優化演進路線

方案 1:WHERE id >= (子查詢) — 0.5 秒

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;

子查詢每行執行一次,仍有開銷。

方案 2:優化子查詢 — 0.01 秒

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;

將 MAX/MIN 提取為獨立子查詢,避免重複計算。

方案 3:JOIN 寫法(最快)— 0.015 秒 / 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 1;

PHP 壓測對比(10 次循環)

方案總耗時平均/次
WHERE 子查詢0.147 秒0.015 秒
JOIN 子查詢0.015 秒0.0015 秒

結論:JOIN 寫法穩定 10 倍+ 於 WHERE 子查詢,生產環境首選。

避坑清單

  1. 必須包含 MIN(id):否則隨機值偏向表頭,前幾行命中率異常高
  2. 主鍵斷檔嚴重:改用 SELECT id FROM t ORDER BY RAND() LIMIT 1 取隨機 ID 再回表
  3. 需多行隨機:應用層循環調用單行隨機,或生成多個隨機 ID 用 IN 查詢
  4. MySQL 8.0+:可用 TABLESAMPLE SYSTEM (10) 伯努利採樣(近似隨機)
copyright © 萬盛學電腦網 all rights reserved