ORDER BY RAND() 是隨機查詢最直觀但性能最差的寫法。本文記錄 15 萬數據量下從 10 秒優化到 0.0008 秒的完整過程,提供生產可用的高性能隨機查詢方案。
| 方案 | SQL 核心邏輯 | 15萬行耗時 | 原理 |
|---|---|---|---|
| 原始 | ORDER BY RAND() LIMIT 10 | 10.11 秒 | 全表掃描 + 臨時表 + 文件排序 |
| 改進一 | WHERE id >= (MAX-MIN)*RAND() + MIN | 0.022 秒 | 主鍵範圍隨機定位 |
| 改進二 (JOIN) | JOIN (SELECT ROUND(RAND()*MAX(id)) AS id) t2 | 0.0025 秒 | 衍生表隨機點 + 索引範圍掃描 |
| 最終優化 | + 列裁剪 + 業務過濾 | 0.0008 秒 | 減少 I/O + 索引下推 |
SELECT * FROM articles ORDER BY RAND() LIMIT 10;
-- EXPLAIN: type=ALL, Using temporary; Using filesort
SELECT * FROM articles
WHERE id >= (
(SELECT MAX(id) FROM articles) - (SELECT MIN(id) FROM articles)
) * RAND() + (SELECT MIN(id) FROM articles)
LIMIT 10;
缺陷: 3 次子查詢、ID 不連續時分布不均、無法加業務過濾條件。
SELECT a.*
FROM articles AS a
JOIN (
SELECT ROUND(RAND() * (SELECT MAX(id) FROM articles)) AS rand_id
) AS b ON a.id >= b.rand_id
ORDER BY a.id ASC
LIMIT 10;
優勢: 衍生表僅掃描 1 行,主查詢走主鍵範圍掃描,利用聚簇索引順序讀取。
SELECT a.id, a.title, a.url -- ① 列裁剪,避免 SELECT *
FROM articles AS a
JOIN (
SELECT ROUND(RAND() * (SELECT MAX(id) FROM articles)) AS rand_id
) AS b ON a.id >= b.rand_id
WHERE a.category_id = 5 -- ② 業務過濾下推 (需有索引)
AND a.status = 1
ORDER BY a.id ASC
LIMIT 10;
SELECT * FROM (
SELECT * FROM articles WHERE id >= (SELECT ROUND(RAND()*MAX(id)) FROM articles) ORDER BY id LIMIT 5
UNION ALL
SELECT * FROM articles WHERE id >= (SELECT ROUND(RAND()*MAX(id)) FROM articles) ORDER BY id LIMIT 5
) t ORDER BY RAND() LIMIT 10;
-- 1. 添加隨機列並建索引
ALTER TABLE articles ADD COLUMN rand_val DOUBLE NOT NULL DEFAULT 0;
UPDATE articles SET rand_val = RAND();
CREATE INDEX idx_rand_val ON articles (rand_val);
-- 2. 查詢
SELECT * FROM articles
WHERE rand_val >= RAND()
ORDER BY rand_val LIMIT 10;
-- 3. 定期更新隨機值 (事件調度器)
CREATE EVENT update_rand_val ON SCHEDULE EVERY 1 HOUR
DO UPDATE articles SET rand_val = RAND();
-- 1. 獲取 ID 範圍 (緩存 5 分鐘)
SELECT MIN(id), MAX(id) FROM articles WHERE category_id = 5 AND status = 1;
-- 2. 應用層生成 10 個隨機 ID
-- 3. 批量查詢
SELECT id, title, url FROM articles WHERE id IN (?, ?, ?, ...);
a.id >= b.rand_id 確保向後查找總有結果RAND() 在主從複製中不安全 (STATEMENT 格式),建議 Row 格式或應用層生成LIMIT 100000, 10 同樣慢,需用延遲關聯或覆蓋索引| 場景 | 推薦方案 | QPS 參考 |
|---|---|---|
| 低頻後台管理、報表 | ORDER BY RAND() 可接受 | < 10 QPS |
| 高頻推薦流、廣告輪播 | JOIN 衍生表 + 列裁剪 | 100-1000 QPS |
| 極高頻、毫秒級 SLA | 應用層隨機 ID + 批量查詢 | > 5000 QPS |
| 需嚴格均勻分布 | 預計算 rand_val 列 + 索引 | 視更新頻率 |