MySQL 效能優化全景指南 (一)：優化方向與資料庫建模三大範式實戰

優化全景圖：五大層次由內而外

1. 硬體與 OS：CPU 核心數、記憶體大小、NVMe SSD、RAID 卡快取策略、NUMA 綁核、檔案系統 (XFS/ext4)、網卡多隊列。
2. MySQL 核心配置：innodb_buffer_pool_size (記憶體 60-70%)、innodb_log_file_size (1-4GB)、innodb_flush_log_at_trx_commit=1、sync_binlog=1、max_connections、thread_cache_size。
3. Schema 設計與建模：範式與反範式平衡、欄位類型最小化、索引策略、分表分庫、大物件外置。
4. SQL 與索引優化：執行計劃分析、覆蓋索引、避免隱式轉換、JOIN 優化、分頁優化、慢查詢治理。
5. 架構層：讀寫分離、分庫分表 (Sharding)、雙主/多主、MHA/Orchestrator 高可用、ProxySQL 讀寫分離、備份驗證體系。

建模核心：三大範式 (3NF) 深度解析與取捨

第一範式 (1NF)：屬性原子性

• 定義：每個欄位不可再分，不包含集合、陣列、多值。
• 關係型資料庫天然滿足 (無法存陣列)；違反案例：tags VARCHAR(255) 'tag1,tag2,tag3'、phones JSON (8.0 JSON 類型雖支援但查詢效率低)。
• 修正：拆分關聯表 article_tags(article_id, tag_id)、user_phones(user_id, phone)。

第二範式 (2NF)：非主鍵屬性完全依賴主鍵

• 前提：有複合主鍵 (如 PRIMARY KEY (student_id, course_id))。
• 違反：student_name 僅依賴 student_id (部分依賴) → 冗餘、更新異常。
• 修正：拆分 students(student_id, name)、courses(course_id, name)、scores(student_id, course_id, score)。
• 實務建議：業務主鍵改用單列自增 BIGINT (代理鍵)，天然滿足 2NF，避免複合主鍵帶來的二級索引膨脹 (InnoDB 二級索引葉子節點存主鍵值)。

第三範式 (3NF)：非主鍵屬性不傳遞依賴主鍵

• 經典案例：students(sno, name, dept_name, dept_addr) → dept_addr 依賴 dept_name 再依賴 sno (傳遞依賴)。
• 修正：拆分 departments(dept_id, name, addr)、students(sno, name, dept_id)。
• 代價：查詢需 JOIN，寫入需維護多表一致性。

BCNF (Boyce-Codd 範式)：主鍵屬性間無部分依賴

• 更嚴格：student_course(sno, id_card, course)，(sno, id_card) 為候選鍵，但 sno→id_card、id_card→sno，任一單列即可唯一標識 → 違反 BCNF。
• 修正：拆分 students(sno, id_card)、student_course(sno, course)。

反範式化：為讀效能妥協的藝術

欄位類型選型黃金法則

-- 整數：按範圍選最小
TINYINT UNSIGNED (0-255)       -- 狀態、類型、性別
SMALLINT UNSIGNED (0-65535)    -- 郵編、小範圍計數
MEDIUMINT UNSIGNED (0-16M)     -- 中等計數
INT UNSIGNED (0-4B)            -- 通用 ID、外鍵
BIGINT UNSIGNED (0-18EB)       -- 訂單號、流水號、主鍵

-- 定點數：金額、精度敏感
DECIMAL(10,2)  -- 99,999,999.99
DECIMAL(12,4)  -- 匯率、單價

-- 字串：定長 vs 變長
CHAR(32)       -- MD5、UUID、固定編碼
VARCHAR(255)   -- 通用短文本
TEXT/MEDIUMTEXT/LONGTEXT  -- 長文本 (離頁存儲，慎用)

-- 時間：統一 DATETIME(3) 或 TIMESTAMP(3)
DATETIME(3)    -- 8 字節，範圍 1000-9999，不受時區影響
TIMESTAMP(3)   -- 4 字節，1970-2038，自動時區轉換

索引設計原則

1. 最左前綴：複合索引 (a,b,c) 支援 a/a,b/a,b,c 查詢。
2. 高選擇性優先：COUNT(DISTINCT col)/COUNT(*) > 0.8 適合建索引；性別、狀態低選擇性不建單列索引。
3. 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表。
4. 避免冗餘索引：KEY(a) + KEY(a,b) → 刪除前者。
5. 外鍵索引必建：InnoDB 不自動建立 FK 索引，需手動 KEY(fk_id)。

分表分庫判斷標準

• 垂直拆分：業務解耦 (用戶/訂單/商品分庫)、熱冷分離 (基本資訊/擴展屬性分表)。
• 水平拆分：單表 > 2000 萬行或 > 10GB、單庫 QPS 瓶頸。分片鍵選取：user_id (用戶維度)、order_id (訂單維度)、DATE(created_at) (時間維度)。
• 中間件：ShardingSphere、MyCat、Vitess、自研 Proxy。

本章小結

優化是系統工程，**建模是基石**。堅持「先 3NF 後反範式、代理鍵主鍵、欄位類型最小化、索引服務查詢」四大原則，配合硬體資源合理配置，再針對慢 SQL 精準施策，才能構建高效穩定的 MySQL 系統。下一章將深入索引內部原理與進階優化技巧。