MySQL 建表優化策略全總結：字符集、主鍵、索引、特殊字段與分表實戰

MySQL 建表優化策略完整指南

優秀的表結構設計是高效能資料庫的基石。本文從字符集選擇、主鍵設計、外鍵權衡、索引策略、特殊字段處理、分表分庫六大維度，系統總結建表優化最佳實踐。

一、字符集選擇

• 全新項目：utf8mb4（支援 Emoji，4 字節），排序規則 utf8mb4_general_ci 或 utf8mb4_0900_ai_ci（8.0+）。
• 純中文、極度追求空間：GBK（2 字節/漢字）較 UTF8（3 字節）節省 33% 空間，但不支援 Emoji、國際化擴展受限。
• 統一全鏈路：庫、表、列、連接、應用編碼全鏈路一致。

二、主鍵設計

注意： 主鍵自帶聚簇索引，無需額外建單列索引；允許在二級索引中包含主鍵列（覆蓋索引）。

三、外鍵權衡

• 優點：數據完整性由資料庫保證，開發簡單。
• 缺點：插入/更新需檢查父表，鎖競爭大；大批量導入需關閉 FOREIGN_KEY_CHECKS；跨庫/分表不支援。
• 建議：
  • 核心強一致性業務（訂單-訂單項、用戶-賬戶）可用外鍵。
  • 高併發、大數據量、微服務架構下，建議應用層維護完整性，資料庫層不建外鍵。
  • 若用外鍵，引用列必須為主鍵或唯一鍵，類型長度完全一致。

四、索引策略

✅ 適合建索引

• WHERE、JOIN、ORDER BY、GROUP BY 高頻列
• 主鍵、唯一鍵（自動建索引）
• 外鍵列（建議手動建，InnoDB 不自動建外鍵索引）
• 範圍查詢列（索引有序）
• 高選擇性列（基數大，如用戶 ID、手機號）

❌ 不適合建索引

• 極少查詢的列
• 低選擇性列（性別、狀態、布爾值，基數極小）
• TEXT/BLOB/JSON 大字段（需前綴索引，效果有限）
• 頻繁更新、極少查詢的表（寫多讀少）
• 數據量極小表（<2000 行，全表掃描更快）

🔗 聯合索引（最左前綴原則）

INDEX idx_a_b_c (a, b, c)
-- 可匹配：a | a,b | a,b,c
-- 不可匹配：b | b,c | c

聯合索引等價於建立 (a)、(a,b)、(a,b,c) 三個索引，節省空間、提升組合查詢效能。

✂️ 索引長度截斷

CREATE INDEX idx_name_prefix ON t_main(name(3));
-- 僅索引前 3 字符，適用於長字符串高重複前綴（如 URL、郵箱域名）
-- 最大 1000 字節（InnoDB 767/3072），GBK 下約 500 漢字

五、特殊字段處理

六、進階優化

1. 垂直拆表

大表中高頻列（id、name、status）與低頻列（detail、blob、log）分離，主表瘦身，熱數據緩存命中率提升。

2. 引擎選擇

• InnoDB：事務、行鎖、外鍵、崩潰恢復，通用首選。
• MyISAM：無事務、表鎖、全文索引（5.6 前）、壓縮表，適合唯讀/歸檔/日誌表。
• 混用注意：事務中操作 MyISAM 表不支援回滾，但不影響 InnoDB 表提交/回滾。

3. 分區表

按範圍（時間）、哈希（ID）、列表（地區）分區，實現分區裁剪、歸檔快速 DROP PARTITION。