MySQL Schema 設計優化指南：數據類型選擇、字符集、拆分冗余權衡

MySQL Schema 設計優化指南：數據類型選擇、字符集、拆分冗余權衡

MySQL 基於行存儲，頁面級 I/O 意味著單行體積越小，每頁容納行數越多，I/O 次數越少，緩存命中率越高。本文系統梳理數據類型選擇原則、字符集策略、大字段拆分、適度冗余等 Schema 級優化手法。

核心原則：越小越簡單越好

• 更小的類型：佔用更少磁盤/內存/CPU 緩存，比較運算更快
• 更簡單的類型：整數比較 < 字符串比較 < 日期時間比較
• 避免 NULL：NULL 需額外標誌位、復雜化索引統計、組合索引效率降低，優先 NOT NULL DEFAULT '' 或特殊值
• 拒絕 SELECT *：尤其含 TEXT/BLOB 時會污染 Buffer Pool
• 長字符串建前綴索引：INDEX(name(20))，平衡區分度與索引大小
• 定期清理重複索引：pt-duplicate-key-checker 檢測 idx(a,b) 覆蓋 idx(a)
• 聯合索引列序：WHERE 條件順序可不一致，但 ORDER BY/GROUP BY 必須與索引列序一致
• InnoDB 必須有自增/單調主鍵：避免頁分裂、減小二級索引體積
• 字符集按需選擇：純拉丁字符用 latin1，多語言用 utf8mb4
• 低基數列不建索引：性別、狀態等，組合索引優於單列索引

數據類型存儲大小速查

類型選擇決策樹

整數類型

• 無負數 → UNSIGNED（雙倍正數範圍）
• 主鍵/外鍵 → INT UNSIGNED AUTO_INCREMENT 或 BIGINT（分庫分表）
• IPv4 存儲 → INT UNSIGNED + INET_ATON()/INET_NTOA()
• 定義寬度 INT(11) 僅影響客戶端顯示，**不限制存儲範圍**

浮點與定點

• 金融金額 → 整數分存儲（amount_cents BIGINT），避免浮點誤差、DECIMAL 開銷
• 科學計算/近似值 → DOUBLE（內部以 DOUBLE 計算）
• 固定精度小數 → DECIMAL(19,4) 等，僅在無法整數化時使用

字符串類型

• 定長且極短（≤ 20） → CHAR（MD5、UUID、國家代碼）
• 變長、平均長度遠小於最大長度 → VARCHAR(N)，N 設為業務實際上限
• 超長文本 → **拆分到單獨表** 或 **對象存儲（S3/MinIO）+ 數據庫存 URL**
• 二進制數據 → VARBINARY/BLOB，同樣建議外部存儲
• 固定枚舉 → ENUM('a','b','c')（內部存整數，極其緊湊）
• 多選標籤 → SET('a','b','c') + FIND_IN_SET() 或位掩碼 INT

日期時間類型

字符集策略

• 純英文/數字/拉丁字符 → latin1（1 字節/字符，節省 50-75% 空間 vs utf8mb4）
• 多語言、Emoji、用戶生成內容 → utf8mb4（4 字節/字符，完整 Unicode）
• 表/列級指定：CREATE TABLE t (c VARCHAR(100) CHARSET utf8mb4) CHARSET latin1;
• 連接層統一：SET NAMES utf8mb4 / JDBC characterEncoding=utf8mb4

大字段垂直拆分

場景：用戶表含 bio TEXT、avatar BLOB，95% 查詢不需要這兩列。

-- 拆分前
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  bio TEXT,              -- 大字段
  avatar BLOB            -- 大字段
) ENGINE=InnoDB;

-- 拆分後
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE users_profile (
  user_id BIGINT PRIMARY KEY,
  bio TEXT,
  avatar BLOB,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

收益：

• 主表行長大幅縮小 → 每頁容納行數 ↑ 10 倍+
• Buffer Pool 緩存熱點數據量 ↑，命中率 ↑
• 索引掃描、全表掃描 I/O ↓ 數量級
• 大字段表可獨立放置慢盤/壓縮存儲

適度冗余：空間換時間

場景：訂單表頻繁 JOIN 用戶表只為取 user_name、user_phone，用戶表 5000 萬行，JOIN 代價極高。

-- 冗余設計
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  user_name VARCHAR(50),      -- 冗余
  user_phone VARCHAR(20),     -- 冗余
  amount DECIMAL(10,2),
  created_at DATETIME,
  INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

一致性保障：

• 應用層雙寫：更新用戶信息同步更新訂單冗余列（事務內）
• 觸發器：AFTER UPDATE ON users FOR EACH ROW UPDATE orders SET user_name=NEW.name WHERE user_id=OLD.id;
• 異步修正：定時任務對賬修正，允許秒級不一致

適用判斷：冗余列**讀頻極高、寫頻極低、長度短、業務容許最終一致**

優化清單

總結

Schema 優化是「一次設計，長期受益」的高杠桿活動。核心心法：**小而精的類型、按需的字符集、垂直拆分大字段、克制的適度冗余**。每個字節的節省，在億級數據下都會轉化為可量化的 I/O 減少、緩存命中提升、查詢加速。建議在表設計評審階段引入「Schema 體檢清單」，將優化前置到開發周期。