MySQL Schema 優化全攻略：數據類型選擇、索引策略與存儲引擎最佳實踐

MySQL Schema 優化全攻略：數據類型選擇、索引策略與存儲引擎最佳實踐

MySQL 採用行式存儲，磁盤 I/O 以頁為單位。減小單行存儲空間，可提升每頁容納行數，進而降低 I/O 次數、提高緩存命中率。以下從數據類型、索引、字符集、表結構設計等維度系統闡述優化原則。

一、核心優化原則

1. 越小越簡單越好：小類型佔用少磁盤、內存、CPU 緩存；整數比較快於字符串（無字符集排序開銷）。
2. 盡量 NOT NULL：NULL 列難優化、佔額外空間、導致組合索引失效、統計信息不準。若需「無值」語義，用 0、空串或特殊值替代。
3. 避免 SELECT *：尤其含 TEXT/BLOB 大列時，會污染 Buffer Pool，擠出熱點數據。
4. 長字符串用前綴索引：超過 20 字符的列建前綴索引（如 INDEX(name(20))），提升索引利用率，但無法用於排序。
5. 定期清理重複索引：用 pt-duplicate-key-checker 檢測，如 idx(a,b) 已覆蓋 idx(a) 則刪除後者。
6. 聯合索引與排序/分組順序一致：WHERE 條件順序可不一致，但 ORDER BY/GROUP BY 必須與索引列順序一致才能用到索引。
7. 默認 InnoDB + 自增主鍵：確保聚簇索引順序寫入，減少頁分裂與碎片。
8. 字符集按需選擇：純拉丁字符用 latin1 節省空間；多語言場景用 utf8mb4 保證通用性。
9. 低基數列不建單列索引：優先考慮組合索引提升選擇性。

二、數值類型深度解析

整數類型

• INT(11) 中的寬度僅影響顯示，不限制存儲範圍；INT(1) 與 INT(20) 存儲完全一致。
• IPv4 地址建議用 INT UNSIGNED 存儲，配合 INET_ATON()/INET_NTOA() 轉換，比 CHAR(15) 節省空間且查詢更快。
• 無負數場景（如自增 ID）優先用 UNSIGNED，擴大正數範圍。

實數類型

• FLOAT/DOUBLE：近似計算，存儲同樣精度值空間較小、範圍更廣，但存在精度問題；MySQL 內部用 DOUBLE 計算。
• DECIMAL：精確計算，佔用空間大、計算開銷高。僅在金融等必須精確場景使用。
• 固定精度小數（如價格）建議 乘以固定倍數轉整數存儲（如分為單位），大幅節省空間並避免精度問題。

三、字符串類型選擇指南

VARCHAR vs CHAR

• VARCHAR：變長，佔用實際長度 + 1-2 字節長度前綴。適合最大長度遠大於平均長度、較少 UPDATE 的列。UTF-8 下每字符佔用字節不固定，VARCHAR 更靈活。
• CHAR：定長，自動截尾空格。適合長度固定（如 MD5 哈希）、頻繁更新（無碎片）、極短列（CHAR(1) 1 字節 vs VARCHAR(1) 2 字節）。

避坑指南：VARCHAR(5) 和 VARCHAR(200) 存 "hello" 佔用空間相同，但後者會導致 MySQL 分配更大固定內存塊用於排序/臨時表，嚴重影響性能。長度定義應精準適度，切勿隨意設大值。

BLOB/TEXT 大對象

• MySQL 將其視為獨立對象處理，InnoDB 大值用「外部存儲」，行內留 1-4 字節指針。
• 排序僅按 MAX_SORT_LENGTH 前綴，無法用索引排序。
• 強烈不建議在資料庫存 BLOB（圖片、文件等），應存文件系統/對象存儲，庫中只存路徑。

ENUM 與 SET

• ENUM 內部用 TINYINT/SMALLINT 存儲，極其緊湊（1-2 字節存 65535 個值），但修改枚舉值需 ALTER TABLE，不適合頻繁變動的業務枚舉。
• ENUM 聯接 CHAR/VARCHAR 可能比同類型聯接慢（需查找表轉換）。
• 固定屬性集（性別、省份、狀態）優先用 ENUM/SET，切勿用字符串存枚舉值。

四、日期時間類型抉擇

結論：範圍查詢多用 INT；創建時間用 DATETIME；修改時間用 TIMESTAMP。

五、標識符（主鍵/外鍵）選型鐵律

1. 整數優先：速度快、支持 AUTO_INCREMENT、CPU 比較高效。
2. 最小足夠類型：省份 ID 用 TINYINT 而非 INT，節省 3 字節；主外鍵類型必須精確匹配（含 UNSIGNED），否則隱式轉換導致索引失效與難查的 Bug。
3. 拒絕字符串主鍵：佔空間大、比較慢、MyISAM 默認壓縮字符串索引更慢。
4. ENUM/SET 適合「定義表」類靜態標識，不適合業務主鍵。

六、特殊數據類型處理

• IP 地址：本質是無符號 32 位整數，用 INT UNSIGNED + INET_ATON/NTOA 存取，勿用 VARCHAR(15)。
• 亞秒級時間戳：MySQL 5.6+ 支持 DATETIME(3)/TIMESTAMP(3) 毫秒精度；舊版本用 BIGINT 存微秒數。

七、字符集與存儲優化

• 純拉丁字符業務堅決用 latin1，避免 UTF-8 多字節浪費空間。
• MySQL 支持列級字符集，大表核心列用 latin1，少數多語言列用 utf8mb4，最大化節省空間。

八、垂直拆分與適度冗餘：空間換時間的藝術

垂直拆分（適當拆分）

大字段（TEXT/長 VARCHAR）若非高頻訪問，拆到獨立表（一對一關聯）。效果：主表行長大幅縮減 → 每頁容納更多行 → 物理 I/O 驟減、Buffer Pool 命中率飛升。

適度冗餘

場景：頻繁 JOIN 多張大表只為取一個小字段。冗餘該字段到主表，雖增大行長、降低頁容納行數，但省去昂貴 JOIN I/O，整體吞吐顯著提升。關鍵：務必保證冗餘字段更新一致性（觸發器/應用層雙寫/消息隊列異步同步）。

九、MySQL 數據類型存儲大小速查表

TINYINT=1, SMALLINT=2, MEDIUMINT=3, INT=4, BIGINT=8
FLOAT=4 (M<=24) 或 8, DOUBLE=8
DECIMAL: M0? M+2 : M+1)
DATE=3, DATETIME=8, TIMESTAMP=4, TIME=3, YEAR=1
CHAR=M, VARCHAR=M+1
BLOB/TEXT 家族: TINY*=M+1, *=M+2, MEDIUM*=M+3, LONG*=M+4