MySQL 資料庫優化系列（一）：表設計三範式、分表技術與慢查詢定位

MySQL 資料庫優化系列（一）：表設計三範式、分表技術與慢查詢定位

資料庫優化是系統工程，涵蓋設計、索引、架構、硬體多維度。本文為系列首篇，聚焦**表設計規範化、分表策略、慢查詢發現**，為後續索引優化、參數調優奠基。

一、優化維度全景

1. 表設計合理化（三範式）
2. 合適索引與索引使用
3. 分表技術（水平/垂直）
4. 定時清理垃圾資料、碎片整理
5. 多用預存程序/觸發器減少網路往返
6. MySQL 參數調優
7. 讀寫分離架構
8. 硬體升級（SSD、記憶體、CPU）

二、資料庫設計流程

1. 收集資訊：與業務方溝通，梳理核心實體與業務流程。
2. 識別實體：名詞即實體（用戶、貼文、回覆、版塊）。
3. 識別屬性：實體擁有的屬性（用戶：ID、姓名、郵箱、註冊時間…）。
4. 識別關係：
   • 1:1 — 共用主鍵
   • 1:N — 主鍵對非主鍵
   • N:1 — 非主鍵對主鍵
   • N:M — 中間表，雙非主鍵組合主鍵
5. E-R 轉表：實體→表，屬性→欄位，關係→外鍵/中間表；無天然主鍵加 BIGINT UNSIGNED AUTO_INCREMENT。

三、三範式規範化

⚠️ **反範式化**：為查詢效能，適度冗余非鍵列（如訂單表冗余用戶姓名），需在應用層保證一致性。

四、分表技術

垂直分表

• 將低頻、大欄位（TEXT/BLOB、last_login、address）移至擴展表。
• 主表變窄 → 更多行放入一頁 → 緩存命中率↑、掃描速度↑。
• 避免頻繁 JOIN 回大表，否則得不償失。

水平分表（分片）

• 按範圍（時間、ID 區間）、Hash（user_id % N）、地理位置拆分。
• 單表行數建議 < 2000 萬，單表大小 < 5-10 GB。
• 中間件支援：ShardingSphere、MyCat、Vitess、自研 Proxy。

五、慢查詢定位三板斧

1. 開啟慢查詢日誌：

   slow_query_log=ON
   long_query_time=2
   log_queries_not_using_indexes=ON

2. 分析工具：
   • mysqldumpslow -s t /var/log/mysql/slow.log（官方，按時間排序）
   • pt-query-digest /var/log/mysql/slow.log（Percona Toolkit，專業報表：Top SQL、執行計劃、索引建議）
3. EXPLAIN 深度剖析：關注 type（ALL/index/range/ref/eq_ref/const/system）、key（實際用到的索引）、rows（預估掃描行）、Extra（Using where/Using index/Using temporary/Using filesort）。

下期預告

系列（二）將深入 **索引設計原則、覆蓋索引、最左前綴、索引下推、直方圖統計** 等進階優化技巧。

優化無終點，只有在當前負載下的最優解。建立標準化設計評審、慢查詢周報、參數基線管理，才能持續收益。