MySQL 查詢效能優化三部曲（二）：隨機抽樣查詢 LIMIT 優化實戰

大數據量隨機抽樣：從全表掃描到 LIMIT 兩階段查詢的效能躍遷

統計分析、A/B 測試、推薦系統常需從百萬級表中「隨機抽取 N 條符合條件的記錄」。本文對比傳統「全查後記憶體隨機」與「兩階段 LIMIT」方案，實測效能提升 10 倍以上。

場景定義

表 HostCache，千萬級資料。需按 ChannelID、ISPType 篩選後，隨機返回最多 100 條記錄。

方案一：傳統全查 + 記憶體隨機（效能陷阱）

// 1. 查詢所有符合條件記錄
SELECT * FROM HostCache WHERE ChannelID = ? AND ISPType = ?;
// 2. 記憶體中隨機挑選 N 條
// 問題：結果集 10 萬行 → 傳輸 50MB+、記憶體佔用高、排序/隨機存取慢

實測：10 萬條記錄，查詢 140ms，獲取結果集 500ms，總耗時 > 600ms。

方案二：兩階段 LIMIT 查詢（優化核心）

核心思想：
1. COUNT(*) 獲取總符合行數 total
2. 計算隨機偏移 offset = RAND() * (total - limit)
3. LIMIT offset, limit 直接從引擎層取樣

完整實現對比

// 方案二核心邏輯
int queryWithLimit(MYSQL* conn, const char* channelId, int ispType, Result* out) {
    // 階段 1: 計數
    char countSql[256];
    snprintf(countSql, sizeof(countSql),
        "SELECT COUNT(*) FROM HostCache WHERE ChannelID = '%s' AND ISPType = %d",
        channelId, ispType);
    if (mysql_real_query(conn, countSql, strlen(countSql))) return 0;
    MYSQL_RES* res = mysql_store_result(conn);
    MYSQL_ROW row = mysql_fetch_row(res);
    int total = atoi(row[0]);
    mysql_free_result(res);

    // 計算隨機範圍
    const int LIMIT_NUM = 100;
    int offset = (total <= LIMIT_NUM) ? 0 : (rand() % (total - LIMIT_NUM));
    int limit = (total <= LIMIT_NUM) ? total : LIMIT_NUM;

    // 階段 2: 取樣
    char selectSql[512];
    snprintf(selectSql, sizeof(selectSql),
        "SELECT SessionID, ExternalIP, ExternalPort, InternalIP, InternalPort "
        "FROM HostCache WHERE ChannelID = '%s' AND ISPType = %d LIMIT %d, %d",
        channelId, ispType, offset, limit);
    if (mysql_real_query(conn, selectSql, strlen(selectSql))) return 0;
    res = mysql_store_result(conn);
    // 遍歷結果集填充 out...
    mysql_free_result(res);
    return limit;
}

效能對比實測

關鍵優化細節

1. COUNT(*) 必須走覆蓋索引：INDEX idx_channel_isp (ChannelID, ISPType) → Using index，毫秒級返回
2. LIMIT offset, limit 大偏移仍需掃描前 offset 行（InnoDB 特性），極大偏移可改用 WHERE id > (SELECT id FROM ... ORDER BY id LIMIT offset, 1) 遞歸定位
3. RAND() 在應用層生成而非 SQL 層，避免引擎無法使用索引
4. 結果集字段**只選需要的列**，禁止 SELECT *

進階：真正均勻隨機抽樣

若業務要求嚴格統計學均勻（如審計抽樣），可用 TABLESAMPLE（MySQL 8.0.23+ 支援 BERNOULLI/SYSTEM）或 WHERE RAND() < threshold 配合索引下推：

SELECT * FROM HostCache 
WHERE ChannelID = ? AND ISPType = ? 
  AND RAND() < 0.001  -- 預期掃描 0.1% 行
LIMIT 100;

但需注意 RAND() 導致全索引掃描，適合小比例抽樣。

總結

「兩階段 COUNT + LIMIT」是大數據量隨機抽樣的**黃金標準**：工程簡單、效能穩定、資源佔用極低。核心前提是**覆蓋索引支援極速 COUNT**與**合理偏移計算**。