單表隨機查詢優化回顧

方案 A：小表全撈 + 應用層隨機

SELECT * FROM `table`;  -- 撈全表
$list = $stmt->fetchAll();
$random = $list[mt_rand(0, count($list)-1)];  // mt_rand 比 rand 快 4 倍

配合 Redis/Memcached 快取結果集，適合 < 1 萬行靜態/準靜態表。

方案 B：SQL 級隨機 (大表)

-- 經典寫法 (全表掃描 + filesort，極慢)
SELECT * FROM `table` ORDER BY RAND() LIMIT 1;

-- 優化：利用主鍵連續性 (假設 ID 無大量空洞)
SELECT * FROM `table` 
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM `table`))) 
ORDER BY id LIMIT 1;

**缺陷**：ID 不連續 (刪除/間隙) 導致分布不均、可能查無結果需重試。

多表隨機查詢：JOIN 類型與優化策略

JOIN 類型速查

類型	關鍵字	語意	結果集
交叉連接	CROSS JOIN / JOIN / ,	笛卡爾積	M × N 行
內連接	INNER JOIN	滿足 ON 條件的匹配行	交集
左外連接	LEFT [OUTER] JOIN	左表全保留 + 右表匹配	左表行數 ≥ 結果行數
右外連接	RIGHT [OUTER] JOIN	右表全保留 + 左表匹配	右表行數 ≥ 結果行數

LEFT JOIN 找出「僅在左表」的資料

SELECT u.id, u.name
FROM user u
LEFT JOIN user_action a ON u.id = a.user_id
WHERE a.user_id IS NULL;  -- a.user_id 必須 NOT NULL

多表隨機抽樣實戰方案

場景：從訂單表 + 用戶表 隨機抽 10 筆已付款訂單及用戶資訊

❌ 反模式：ORDER BY RAND() 直接 JOIN

SELECT o.*, u.name 
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY RAND() LIMIT 10;
-- 慢查詢：產生臨時表 + filesort，隨資料增長指數級惡化

✅ 方案 1：兩階段法 (推薦中大型資料)

1. 先隨機抽主鍵 ID (只掃描索引)：

   SELECT id FROM orders 
   WHERE status = 'paid' 
     AND id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM orders WHERE status='paid')))
   ORDER BY id LIMIT 10;

2. 應用層收集 ID 列表，再 JOIN 查詳情：

   SELECT o.*, u.name 
   FROM orders o JOIN users u ON o.user_id = u.id
   WHERE o.id IN (?, ?, ...);  -- 參數化 IN 列表

✅ 方案 2：覆蓋索引 + 延遲關聯 (單次 SQL)

SELECT o.*, u.name
FROM (
    SELECT id FROM orders
    WHERE status = 'paid'
      AND id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM orders WHERE status='paid')))
    ORDER BY id LIMIT 10
) rand_ids
JOIN orders o ON o.id = rand_ids.id
JOIN users u ON o.user_id = u.id;

**關鍵索引**：orders(status, id) 覆蓋子查詢，避免回表。

✅ 方案 3：系統函數 TABLESAMPLE (MySQL 8.0.31+ / MariaDB 10.3+)

SELECT * FROM orders TABLESAMPLE SYSTEM (1) WHERE status='paid' LIMIT 10;
-- SYSTEM (1) 表示 1% 頁面採樣，極快但非精確隨機

大表多表聯合查詢優化範本

SELECT M.*, SS.SensorCode, SS.SensorStatus, TP.PricingStrategyId
FROM (
    SELECT T.*, BS.ParkStatus, BS.ChangeTime, CA.CantonName, SE.SectionName
    FROM (
        SELECT A.*, B.BerthId, B.BerthCode, B.BerthAddress, B.BerthStatus, B.LineDirection, B.CantonId, B.SectionId
        FROM (SELECT AreaId, AreaCode, AreaName FROM SYS_Area WHERE AreaCode='110') A
        LEFT JOIN SYS_Berth B ON B.AreaId = A.AreaId
    ) T
    JOIN SYS_BerthStatus BS ON T.BerthCode = BS.BerthCode
    JOIN SYS_Canton CA ON T.CantonId = CA.CantonId
    JOIN SYS_Section SE ON T.SectionId = SE.SectionId
) M
LEFT JOIN SYS_Sensor SS ON M.BerthCode = SS.BerthCode
LEFT JOIN TRA_PricingBerth TP ON TP.BerthCode = M.BerthCode
-- 關鍵：最內層先過濾 (AreaCode='110')，逐層 JOIN，關鍵欄位皆有索引

優化核心原則

1. 先過濾、後關聯：WHERE 條件下推到最內層子查詢
2. 小表驅動大表：驅動表 (外層循環) 行數少，被驅動表有索引
3. 覆蓋索引：SELECT 欄位盡量包含在索引中，避免回表
4. 避免 SELECT *：只取需要欄位，減少網路/記憶體/磁碟 IO
5. 用 EXPLAIN 驗證：確認 type=ref/eq_ref、rows 合理、無 Using temporary/filesort