Web 2.0 架構下 MySQL 性能優化全景指南：瓶頸判斷、分庫分表、索引優化、主從分離、緩存策略、設計權衡

如何確認數據庫是瓶頸？

1. 應用層統計每請求 DB 耗時，平均超過容忍閾值 → 瓶頸。
2. top 觀察 mysqld 佔用 CPU 近 100%、負載極高。
3. iostat 發現磁碟 I/O 大量阻塞（%iowait 高）。
4. 開啟慢查詢日誌，grep SELECT 發現大量慢查詢。
5. 單表突破 32 位系統 2^32 行限制導致無法寫入。
6. 建議：接入層埋點統計 DB 耗時 + 監控報警，早發現早優化。

優化策略五大支柱

1. 分庫分表（數據量大、單表寫入慢）

• 按規則分區存儲，實現負載均衡、突破單表/單庫容量上限。
• 架構設計初期即抽離 DB 操作層，便於後期水平擴展。
• 風險：需改造既有代碼，建議早期規劃。

2. 索引優化（表不大但查詢慢）

• EXPLAIN 分析執行計劃，確認是否用上索引。
• 建索引原則：
  1. 越少越好（每次寫入需維護索引）。
  2. 最窄欄位置左（提高基數、索引密度）。
  3. 避免 filesort、臨時表、全表掃描。
  4. 大表慎建字符串索引（改用 Hash 整型索引）。

3. 主從複製讀寫分離（寫少讀多）

• 主庫承擔寫，多從庫分擔讀，實現讀負載均衡。
• 適用於「讀響應允許微秒級延遲」場景。

4. 多層緩存體系（二八定律）

• MySQL 內部：調大 key_cache、thread_cache、query_cache。
• 應用層：引入 memcached/Redis 通用緩存。
• 熱點小表：用 HEAP (MEMORY) 引擎極速讀寫，配合持久化工具落盤。

5. 設計層面權衡（範式 vs 性能）

• 嚴格三範式導致頻繁多表 JOIN，大數據量下性能崩塌。
• 適度反範式化：高頻關聯欄位（如用戶名）冗余存儲，減少 JOIN。
• 核心：在「數據一致性」與「查詢性能」間找到業務可接受的平衡點。

工程化落地清單

• DB 操作分層：邏輯層 + 緩存層，支援未來分庫分表平滑遷移。
• 監控體系：慢查詢日誌、show processlist 鎖分析、show slave status 主從延遲、show status 吞吐指標。
• 引擎選型：MyISAM（高速讀寫、表鎖）、InnoDB（事務、行鎖、表空間大、無全文索引）、HEAP（極速、非持久）。