MySQL 隨機查詢優化：ORDER BY RAND() 替代方案實測

ORDER BY RAND() 會導致全表掃描並多次計算隨機值，8000 行表查 5 行耗時 0.08s，大表不可用。以下兩種優化方案實測對比。

方案 1：JOIN 子查詢（需 MySQL 4.1+）

SELECT * 
FROM `table` AS t1 
JOIN (
    SELECT ROUND(RAND() * (
        SELECT MAX(id) FROM `table`
    )) AS id
) AS t2 ON t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

耗時 **0.02s**，但返回連續 5 行，需執行 5 次取 1 行再合併。

方案 2：WHERE 子查詢 + MIN/MAX 邊界（推薦）

-- 單行隨機
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.01s**，單次查詢即得真隨機行。

JOIN 版進階寫法（批量高效）

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 次循環實測對比

**結論**：JOIN 寫法效率碾壓 WHERE 子查詢，生產環境大表隨機抽樣首選 JOIN 方案。

前置條件

• 表需有自增主鍵 id 且無大量空洞（刪除導致 ID 不連續會降低命中率）。
• 極大空洞表可改用 SELECT id FROM table ORDER BY RAND() LIMIT 1 取 ID 再回表。