MySQL 性優化筆記全集：表設計、索引、分表、讀寫分離、配置、備份、碎片整理

MySQL 性能優化知識體系全景圖：從表設計到架構演進

優化遵循「由輕到重、由內到外」：**SQL/索引 → 表設計 → 參數配置 → 架構擴展 → 硬件升級**。前者投入產出比最高。

一、表設計規範化與反範式化

三大範式落地

• 1NF：列不可再分（關係型天然滿足）
• 2NF：非主鍵列完全依賴主鍵（單列主鍵天然滿足）
• 3NF：非主鍵列不傳遞依賴（消除冗余）

適度反範式化：以空間換時間

-- 反面：活動表冗余單位名稱（500 vs 200萬），更新放大 4000 倍
-- 正面：考試總分表冗余總分（1:100），低頻更新、高頻查詢，避免 JOIN

-- 原則：
-- 1. 冗余字段更新頻率極低
-- 2. 查詢頻率極高且避免多表 JOIN
-- 3. 數據一致性由應用層/觸發器保證
-- 4. 必須有充分壓測數據支撐

二、字段類型選擇：保小不保大

• 主鍵：BIGINT UNSIGNED AUTO_INCREMENT（8B）優於 UUID（36B）
• 時間：TIMESTAMP（4B，1970-2037）優於 DATETIME（8B）
• 枚舉：TINYINT + 常量類，避免 ENUM 擴展困難
• IP：INT UNSIGNED 存 INET_ATON() 結果
• 定長優於變長：CHAR 適合固定長度（MD5、手機號），減少碎片

三、存儲引擎選型

四、索引策略：B+樹最左前綴原則

四類索引

• 主鍵索引：聚簇索引，葉子存完整行
• 唯一索引：允許 NULL，不可重複
• 全文索引：FULLTEXT，5.6+ InnoDB 原生支持
• 普通索引：輔助索引，葉子存主鍵值

建索引黃金法則

1. 高頻 WHERE/JOIN/ORDER BY/GROUP BY 字段
2. 聯合索引遵循最左前綴，區分度高的列靠前
3. 區分度 < 0.1 不建單列索引（性別、狀態）
4. 長字符串用前綴索引 email(20)
5. 覆蓋索引避免回表：INDEX(a,b,c) 滿足 SELECT a,b FROM t WHERE a=1

索引失效常見場景

• LIKE '%abc%' 導模糊匹配
• 隱式類型轉換：WHERE phone=13800000000（phone 是 VARCHAR）
• OR 條件兩側索引不同
• 對索引列運算：WHERE YEAR(dt)=2024 → WHERE dt>='2024-01-01'

五、分表技術：水平 vs 垂直

水平分表（分片）

• 結構相同，數據按規則分散：user_id % 10、時間範圍、地域
• 分片鍵選擇決定查詢路由效率：按月分表適合「按月查」，按地域分表若跨區查詢反而慢
• 中間件路由：ShardingSphere、MyCat、Vitess

垂直分表

• 大字段/低頻字段拆出：content TEXT、blob_data BLOB
• 主表窄，列表查詢快；詳情頁關聯讀大字段表
• 1:1 關聯，主鍵同步

六、讀寫分離與主從複製

Master (Write) → Binlog → Slave1/2/3 (Read)
     ↓                              ↑
   應用層路由或中間件 (ProxySQL/MyCat) 透明分發

• 異步複製：性能最好，有延遲風險
• 半同步複製：rpl_semi_sync_master=ON，至少一從確認返回，平衡性能與安全
• GTID 複制：GTID_MODE=ON，自動定位、故障切換無誤

七、關鍵配置參數調優

八、碎片整理與維護

-- InnoDB 表碎片整理（在線，5.6+ 支持 Online DDL）
OPTIMIZE TABLE tbl_name;
-- 或
ALTER TABLE tbl_name ENGINE=InnoDB;  -- 重建表

-- 監控碎片
SELECT TABLE_NAME, 
       ROUND(DATA_FREE/1024/1024,2) AS Free_MB,
       ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100,2) AS Frag_Pct
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='db' AND DATA_FREE > 100*1024*1024;

九、備份恢復體系

• 邏輯備份：mysqldump --single-transaction --routines --triggers --events 夜間低峰
• 物理熱備：Percona XtraBackup 非阻塞、增量、流式傳輸
• Binlog 增量：mysqlbinlog PITR 時間點恢復
• 定期演練：月度恢復測試，驗證 RPO/RTO

十、硬件與架構擴展

1. 內存 > CPU > SSD > 網絡：優先加大內存（Buffer Pool）、NVMe SSD
2. 64位 OS + 64位 MySQL：突破 32位 3-4GB 進程限制
3. 讀寫分離 → 分庫分表 → 多活/多雲：按業務增長階段演進

優化無終點，只有權衡。建立「慢查詢發現 → EXPLAIN 分析 → 索引/SQL/架構優化 → 壓測驗證 → 監控固化」閉環。工具鏈：pt-query-digest、sys schema、Performance Schema、Grafana+Prometheus 監控大盤。

1. 上一篇文章： MySQL "server has gone away" 錯誤原因深度分析與四大解決方案
2. 下一篇文章： PHP 以表格形式輸出 MySQL 資料表資料的實作方法

MySQL 綜合教學

InnoDB 表空間管理源碼級解析：Space、Segment、Extent、Page 四層架構

概覽 InnoDB 表空間採用邏輯分層：**Space (表

MySQL 客戶端連線指南：標準登入參數與常見認證錯誤排查

MySQL 命令列客戶端連線完全指南：參數詳解、認證流程與常

NetBeans 連接 MySQL 完整圖文教程：從建立資料庫到桌面應用程式產生

環境準備 NetBeans 6.5 JDK 6 Updat

MySQL Events 定時器實戰：百萬級數據遷移、權限配置、主從避坑

業務背景 表 A 達 1.6 億行，線上業務不能停，需將歷史