MySQL 大數據量優化策略：分表、分庫、讀寫分離、冗余與硬件

MySQL 大數據量優化策略：分表、分庫、讀寫分離、冗余與硬件

當單表超過 2000 萬行、單庫超過 500GB，單機 MySQL 會面臨 IOPS 瓶頸、備份恢復慢、DDL 鎖表久、統計信息失準等問題。本文系統總結 8 大架構優化方向、MySQL 參數調優、硬件選型與大流量應對策略。

架構層面 8 大優化方向

1. 橫向切分 (水平分表)：按時間 (按月/周)、用戶 ID 取模、範圍、Hash 將大表拆為多張同結構小表 (order_202401, order_202402...)，配合分區表或中間件 (ShardingSphere、MyCat、Vitess) 路由。
2. 縱向切分 (垂直分表)：將大字段 (TEXT/BLOB/JSON)、低頻列拆到擴展表 (user_profile_ext)，主表只保高頻列，減少行大小、提升 Buffer Pool 命中率。
3. 分庫分表：將分表分散到多個 MySQL 實例，解決單實例 CPU/IO/連接數瓶頸，需解決分布式事務、跨庫 JOIN、全局唯一 ID (Snowflake/段號模式)。
4. 讀寫分離：主庫寫、從庫讀，中間件 (ProxySQL、MaxScale、Atlas) 或應用層路由，從庫可掛掛多個只讀實例承擔報表/大屏查詢。
5. 冗余字段/預聚合表：將常用統計 (COUNT/SUM/AVG) 寫入冗余列或單獨統計表 (daily_order_stats)，避免大表實時聚合。
6. 數據歸檔/冷熱分離：歷史數據 (超過 6-12 月) 遷移至歸檔庫 (壓縮存儲、低配機型)、或對象存儲 (Parquet/ORC) + ClickHouse/Doris 做 OLAP。
7. 多盤分離：數據文件 (datadir)、Redo Log (innodb_log_group_home_dir)、Binlog、臨時目錄 (tmpdir) 掛載不同物理盤 (NVMe/SSD/HDD)，消除 IO 爭用。
8. 緩存層前置：Redis/Memcached 緩存熱點數據、會話、計數器，MySQL 只承擔持久化寫入與冷數據查詢。

MySQL 參數調優 (核心 6 參數)

硬件選型建議

• CPU：高主頻 (≥3.0GHz) 多核 (≥16 核)，MySQL 單線程性能依賴主頻。
• 內存：≥128GB，innodb_buffer_pool_size ≥ 96GB。
• 存儲：NVMe SSD (數據/Redo Log)、普通 SSD (Binlog/Temp)、HDD (歸檔/備份)。RAID 10 或單盤 NVMe (雲盤通常三副本)。
• 網卡：萬兆或 25Gb，啟用 RSS/RPS/RFS 多隊列。

為什麼字段盡量 NOT NULL

1. NULL 列需額外 1 字節標記 (NULL bitmap)，增加存儲。
2. COUNT(col) 忽略 NULL，COUNT(*) 不忽略，語義易混淆。
3. 複合索引中含 NULL 列可能導致索引失效或基數統計不準。
4. NOT NULL 列優化器可直接套用等值/範圍推導，生成更優計劃。

大流量網站應對策略 (6 步)

1. 硬件評估：CPU/內存/磁盤/網卡是否達標。
2. SQL/索引/架構優化：慢查詢分析、讀寫分離、分庫分表、緩存。
3. 防盜鏈、限流大文件下載 (Nginx limit_rate、Token 桶)。
4. 多機房/多可用區部署，DNS/GSLB 就近接入、故障切換。
5. 流量分析：Prometheus + Grafana + 慢查詢日誌 + performance_schema 全鏈路可觀測。
6. 壓測預演：tcpcopy/GoReplay 引流生產流量到預發環境，驗證容量上限。

大數據量優化沒有銀彈，只有「分而治之」：垂直拆表減行寬、水平分表減行數、分庫分散壓力、讀寫分離擴讀力、冗余預聚合減計算、冷熱分離降成本、多盤分離減爭用、緩存前置減穿透。每一步都要有監控驗證，切忌為優化而優化。