MySQL 查詢優化實戰：從索引失效到分段查詢與二次預聚合

面對單表 4000 萬行、單日 8GB 的 HTTP 協議流水日誌表，多欄位組合查詢耗時 20-30 分鐘。常規索引優化收效有限，最終透過業務流程細節分析，採用分段查詢解決明細查詢、二次預聚合解決統計查詢，將響應時間壓縮至 30 秒內。本文還原完整優化思路。

場景與痛點

• 表結構：http_log_YYYYMMDD 分表，單表 ~4000 萬行，.MYD ~8GB。
• 查詢模式：用戶可任意組合 5-6 個欄位 (來源 IP、目的 IP、URL、User-Agent、狀態碼) 條件查詢，部分欄位 VARCHAR(256)。
• 索引困境：單欄索引選擇度低，複合索引排列組合爆炸 (C(6,2)=15 組)，全建索引大小逼近原表，寫入效能崩潰。
• 統計需求：跨天、跨協議聚合 (PV、UV、流量、Top N)，預聚合表月表仍達 4000 萬行。

優化演進路徑

階段一：常規索引嘗試 (效果微弱)

• 建立 log_time 索引 (8 bytes × 4000萬 = 320MB)，查詢稍快但仍需回表掃描大量行。
• 嘗試複合索引 (src_ip, log_time) 等，維護成本高，查詢計畫常走全表掃描。

階段二：物理分表已達極限 (日表、協議表共 2700+ 表)

• 無法再細分物理表，Partitioning 同樣受限於索引選擇度。

階段三：分段查詢 (解決明細查詢)

核心思想：將 4000 萬行按主鍵 ID 範圍均分為 10 段 (每段 400 萬)，前端提供下拉選單讓用戶選擇段區，後端加 WHERE id BETWEEN start AND end。

-- 前端傳入 segment=3 (對應 800萬-1200萬)
SELECT * FROM http_log_20240115
WHERE id BETWEEN 8000000 AND 12000000
  AND src_ip = '1.2.3.4'
  AND url LIKE '%login%'
LIMIT 100;

效果：單段掃描 400 萬行，索引命中後回表極少，耗時 20-30 秒。犧牲「一次查全表」便利性，換取可接受的響應速度。

階段四：二次預聚合 (解決統計查詢)

現狀：一級預聚合 (月表) 仍過大，跨維度聚合慢。

方案：針對高頻統計維度 (如「每小時各狀態碼計數」、「每日 Top 100 URL」)，建立二級預聚合表，粒度更細、欄位更少、行數極少 (千行級)。

-- 二級預聚合表示例：每小時狀態碼統計
CREATE TABLE http_log_hourly_status (
    log_date DATE NOT NULL,
    log_hour TINYINT UNSIGNED NOT NULL,
    status_code SMALLINT UNSIGNED NOT NULL,
    req_count BIGINT UNSIGNED NOT NULL,
    bytes_sum BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (log_date, log_hour, status_code)
) ENGINE=InnoDB;

-- 定時任務 (每小時跑上小時資料)
INSERT INTO http_log_hourly_status
SELECT DATE(log_time), HOUR(log_time), status_code, COUNT(*), SUM(bytes)
FROM http_log_20240115
WHERE log_time >= '2024-01-15 13:00:00' AND log_time < '2024-01-15 14:00:00'
GROUP BY DATE(log_time), HOUR(log_time), status_code
ON DUPLICATE KEY UPDATE req_count=VALUES(req_count), bytes_sum=VALUES(bytes_sum);

效果：用戶查詢「過去 7 天 404 趨勢」直接掃描 http_log_hourly_status (7×24×幾百狀態碼 ≈ 5 萬行)，耗時 < 1 秒。

核心心得

1. 業務流程細節決定技術方案：用戶真正需要「全表隨機查」嗎？多數場景只看近期、特定段落。與產品經理溝通，將「全表查」改為「分段查」獲利巨大。
2. 預聚合分層：一級 (月) 解決存儲、二級 (小時/維度) 解決查詢，三級 (即時) 由應用層快取解決。
3. 團隊協作模式：DBA 負責索引/架構、開發負責分段邏輯/預聚合 ETL、測試負責壓測對比，每日站會同步進度，一週攻克核心難點。
4. 知己知彼：EXPLAIN、HandlerSocket、Perf4j 量化每條 SQL 耗時，避免主觀臆測。

避坑指南

• 分段查詢前端需友善提示：「資料量大，請選擇時間段或 ID 範圍查詢」。
• 預聚合 ETL 必須冪等 (用 ON DUPLICATE KEY UPDATE 或 REPLACE INTO)，支援重跑補數。
• 監控預聚合任務延遲，設定告警 (如超過 30 分鐘未完成)。
• 保留原始明細表至少 3 個月 (合規/回溯/重算)，冷數據歸檔至 ClickHouse/Parquet/對象存儲。