MySQL 單表 400 萬數據性能優化實戰：從索引調優到業務層拆分

項目背景與性能瓶頸

近期在呼叫中心項目中，MySQL 單表數據量突破 400 萬行，導致增刪改查操作明顯變慢。業務場景要求每秒處理約 20 個呼叫，換算下來每秒需對單表執行至少 20 次寫入，查詢操作更多，對數據庫響應速度要求極高。

優化路徑總覽

我們按「索引優化 → 參數調優 → 分表策略 → 業務層曲線救國」四個階段逐步推進，最終在業務層找到最佳平衡點。

1. 索引深度優化

將所有慢 SQL 拿出來，逐條用 EXPLAIN 分析執行計劃，確保核心查詢全部命中索引。重點排查 type 列是否為 ref 或 range，key 列是否顯示預期索引，rows 掃描行數是否可控。

2. my.cnf 參數調優（4 核 CPU / 4 GB 內存）

調整重點參數如下，但實測效果有限，建議作為輔助手段：

[mysqld]
innodb_buffer_pool_size=512M          # 核心緩衝池，建議設為物理內存 60%-70%
innodb_log_file_size=24M              # 重做日誌大小
innodb_flush_log_at_trx_commit=1      # 數據安全與性能折衷
query_cache_size=120M                 # 查詢緩存（高併發寫入場景建議關閉）
thread_cache_size=64                  # 線程緩存減少創建開銷
table_open_cache=512                  # 表緩存（注意單位為個數而非 MB）
max_connections=1000                  # 最大連接數
skip-name-resolve                     # 跳過 DNS 解析加速連接

避坑指南：修改配置後務必分步驗證，切勿大量改動後直接重啟，以免啟動失敗無法回滾。

3. 分表方案評估與放棄

單表 400 萬即達性能瓶頸，但日增量 200 萬行，若按天分表，1-2 年將產生上萬張表，維護成本極高（DDL 變更、備份恢復、跨表查詢均成噩夢），故放棄純分表路線。

4. 業務層「雙表分流」終極方案

結合業務特性——每小時將流水數據結轉為統計數據——設計雙表架構：

• 存檔流水表：僅負責寫入，不讀不改，作為不可變備份。
• 計算流水表：結轉統計後立即刪除已處理數據，保持數據量恆定在百萬級以內。

此舉將「無限增長」轉化為「固定窗口」，徹底解決分頁、索引維護、鎖競爭等連帶問題，且零代碼侵入業務邏輯層。

核心啟示

當基礎設施優化觸及天花板時，回歸業務語義重新建模往往能以最小代價撬動最大增益。沒有專職 DBA 的團隊，更應善用「曲線救國」思維。

作者：xsxjb