MySQL 多表隨機查詢優化方案：從千萬級資料中高效抽樣

隨機查詢在推薦系統、廣告輪播、測試抽樣等場景極常見，但 ORDER BY RAND() 在大表上效能極差。本文提供單表與多表多種優化方案。

方案對照表

單表優化實戰

1. 應用層緩存 ID 列表 (推薦中小表)

-- 定期重建緩存 (如每小時)
SELECT id FROM articles WHERE status=1 ORDER BY id;
-- 應用層: random.choice(id_list) 或 random.sample(id_list, k)
-- 再: SELECT * FROM articles WHERE id IN (?, ?, ?)

2. MAX(id) * RAND() 近似隨機

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

⚠️ ID 有刪除空洞時，分布不均；需配合 WHERE status=1 等條件。

3. 雙查詢真隨機 (推薦大表)

-- 步驟 1: 取總數
SELECT COUNT(*) FROM articles WHERE status=1;  -- 假設 1000000

-- 步驟 2: 應用層產生隨機 offset
offset = random.randint(0, total-1)

-- 步驟 3: LIMIT offset, 1
SELECT * FROM articles WHERE status=1 ORDER BY id LIMIT 1000000, 1;

優化：建立覆蓋索引 (status, id)，避免回表。

多表 Join 隨機查詢優化

Join 類型快速回顧

• CROSS JOIN：笛卡爾積，極慢，慎用
• INNER JOIN：等值連接，僅回傳匹配行
• LEFT JOIN：左表全保留，右表無匹配填 NULL
• RIGHT JOIN：右表全保留

優化原則

1. 先過濾再 Join：子查詢/衍生表先套用 WHERE 條件縮小結果集
2. 小表驅動大表：STRAIGHT_JOIN 強制 Join 順序
3. 隨機抽樣下推：在驅動表完成隨機抽樣，再 Join 從表
4. 覆蓋索引：Join 條件欄位 + 選取欄位建立聯合索引

實戰範例：多表隨機推薦

-- 需求: 從有庫存的商品中，隨機推薦 5 個含分類名稱的商品

-- 方案: 衍生表先隨機抽樣商品 ID，再 Join 分類表
SELECT p.id, p.name, p.price, c.name AS category_name
FROM (
    SELECT id, name, price, category_id
    FROM products
    WHERE stock > 0
    ORDER BY RAND()  -- 僅在縮小後的結果集排序
    LIMIT 5
) p
LEFT JOIN categories c ON p.category_id = c.id;

大數據量多表隨機 (百萬級+)

-- 1. 應用層取得符合條件的主表 ID 列表 (可緩存、定期更新)
-- 2. 應用層隨機抽樣 k 個 ID
-- 3. 批次查詢詳情
SELECT p.*, c.name AS cat_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.id IN (?, ?, ?, ?, ?);

Join 優化器提示

-- 強制 Join 順序
SELECT STRAIGHT_JOIN p.*, c.name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.stock > 0
ORDER BY RAND() LIMIT 5;

-- 指定/忽略索引
SELECT * FROM products USE INDEX (idx_stock_id) WHERE stock > 0 ORDER BY RAND() LIMIT 5;
SELECT * FROM products IGNORE INDEX (idx_name) WHERE stock > 0 ORDER BY RAND() LIMIT 5;

總結建議

• 資料量 < 10萬：應用層緩存 ID 列表最簡高效
• 單表 10萬~千萬：雙查詢真隨機 + 覆蓋索引
• 多表 Join：衍生表先隨機抽樣驅動表，再 Join 從表
• 極大資料量：應用層抽樣 ID + 批次查詢詳情，避免 SQL 排序
• 嚴禁在大表直接 ORDER BY RAND() LIMIT N