MySQL ORDER BY RAND() 效能陷阱與高效隨機查詢替代方案實測

問題背景

常見寫法：SELECT * FROM tablename ORDER BY RAND() LIMIT 1。官方手冊警告：ORDER BY 子句中不可用 RAND()，會導致資料列被多次掃描。MySQL 3.23 仍可用但效率極低。實測 15 萬行表查 5 條耗時 8 秒+。

常見替代方案：MAX(id) * RAND()

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 ASC LIMIT 5;

缺點：產生連續 5 條記錄。需查 5 次各取 1 條。但單次查詢 < 0.01 秒，值得。

WHERE 子查詢版本

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM `table`)
ORDER BY id LIMIT 1;
-- 測試 0.5 秒，較 JOIN 版慢

優化改寫：單層子查詢

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table`)))
ORDER BY id LIMIT 1;
-- 效率大增，僅 0.01 秒

完美版：含 MIN(id) 邊界保護

早期測試無 MIN(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;

-- 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
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

PHP 壓測對比 (各跑 10 次)

• WHERE 子查詢版：0.147433 秒
• JOIN 版：0.015130 秒

結論： JOIN 語法效率遠高於 WHERE 子查詢函數調用。