概述#

良好的邏輯與物理設計是高效能的基石。Schema 設計必須針對具體的查詢模式進行取捨：反正規化可以加速某些查詢但拖慢其他查詢；計數器與摘要表能優化讀取但維護成本高。本章聚焦在 MySQL 特有的 schema 設計考量。

選擇最佳資料型別#

三大原則：Smaller is usually better、Simple is good、Avoid NULL if possible。

原則一：越小越好#

  • 使用能正確儲存資料的最小型別
  • 較小的型別佔用更少的磁碟、記憶體、CPU cache 空間，也需要更少的 CPU 週期
  • 但不要低估實際需要的值域範圍——事後擴大型別是痛苦的

原則二：簡單為上#

  • 整數比字元比較快（字元需要考慮字元集與排序規則）
  • 日期時間應使用 MySQL 內建型別，而非字串
  • IP 位址應使用整數儲存，而非字串

原則三：盡量避免 NULL#

  • NULL 讓索引、索引統計、值比較都更複雜
  • 可為 NULL 的欄位佔用更多儲存空間，需要額外處理邏輯
  • 在 MyISAM 中，NULL 欄位可能將固定大小索引轉為可變大小索引

InnoDB 使用單一位元 (single bit) 儲存 NULL，因此對於稀疏資料來說空間效率還不錯。效能改善通常很小，不必急著改現有 schema，但新欄位若要建索引，應盡量設為 NOT NULL。

整數型別 (Whole Numbers)#

型別儲存空間值域
TINYINT8 bits-128 ~ 127 / 0 ~ 255 (UNSIGNED)
SMALLINT16 bits-32,768 ~ 32,767
MEDIUMINT24 bits-8,388,608 ~ 8,388,607
INT32 bits約 ±21 億
BIGINT64 bits約 ±9.2 × 10¹⁸
  • UNSIGNED 屬性：禁止負數，正數上限約加倍。有號與無號佔用相同空間、效能一樣
  • 整數運算在內部通常使用 64-bit BIGINT 處理

INT(11) 中的寬度數字不影響儲存或運算，只影響顯示工具的呈現。INT(1) 與 INT(20) 完全相同。

實數型別 (Real Numbers)#

FLOAT 與 DOUBLE#

  • FLOAT：4 bytes，近似計算（CPU 原生浮點運算，速度快）
  • DOUBLE：8 bytes，更高精度與更大值域
  • MySQL 內部浮點運算統一使用 DOUBLE

DECIMAL#

  • 用於精確小數計算（如財務資料）
  • MySQL 5.0+ 支援精確數學運算（伺服器端軟體實作，比浮點慢）
  • 儲存格式：每 9 位數佔 4 bytes。例如 DECIMAL(18,9) = 9 bytes
  • 最多可達 65 位數

若要儲存財務資料且追求高效能，可考慮用 BIGINT 乘以適當倍數（例如乘以一百萬），避免 DECIMAL 的運算成本，同時保持精確性。

字串型別 (String Types)#

VARCHAR vs CHAR#

特性VARCHARCHAR
長度類型變動長度固定長度
儲存空間使用 1 或 2 bytes 記錄長度（最大長度 ≤ 255 bytes 用 1 byte，> 255 bytes 用 2 bytes）始終配置指定字元數的空間，儲存時去除尾端空白
更新行為節省空間，但更新時列可能增長，導致 MyISAM 碎片化或 InnoDB 分頁 (page split)固定長度不易碎片化
適用場景最大長度遠大於平均長度、更新不頻繁、使用 UTF-8 等變動長度字元集極短字串（如 CHAR(1) 只佔 1 byte，VARCHAR(1) 則佔 2 bytes）、長度一致的值（如 MD5 雜湊）、頻繁更新的欄位

VARCHAR 長度不要過度慷慨——VARCHAR(5) 與 VARCHAR(200) 儲存 'hello' 佔用相同磁碟空間，但 MySQL 在排序和暫存表中經常以最大長度配置記憶體。VARCHAR(200) 會浪費大量記憶體。策略：只配置你真正需要的空間。

BINARY 與 VARBINARY#

  • 儲存二進位字串（位元組），比較時逐位元組依數值比較，比字元比較更快
  • BINARY 用 \0 填充（不會在讀取時移除）

BLOB 與 TEXT#

  • TEXT 家族：TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT（字元型）
  • BLOB 家族：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB（二進位型）
  • InnoDB 對大值使用獨立的外部儲存區
  • 排序只比較前 max_sort_length bytes
  • 無法對全長建索引、無法用索引排序

Memory 引擎不支援 BLOB/TEXT，使用這些型別的查詢若需要暫存表，會被迫使用磁碟上的 MyISAM 暫存表，效能大幅下降。可用 SUBSTRING(column, length) 轉換為短字串來避免。

ENUM#

  • 內部以整數儲存（1~2 bytes），字串定義保存在 .frm 檔
  • 排序依內部整數值，不是依字串字母順序——定義時應按需要的排序順序排列
CREATE TABLE enum_test(
   e ENUM('fish', 'apple', 'dog') NOT NULL
);
-- 內部儲存: fish=1, apple=2, dog=3
-- ORDER BY e 的結果: fish, apple, dog（依整數順序）
  • 新增/移除值需要 ALTER TABLE（MySQL 5.1+ 在末尾新增可免完整重建）
  • ENUM 欄位 JOIN CHAR/VARCHAR 欄位會比較慢；ENUM JOIN ENUM 則更快

ENUM 可使表格縮小約 1/3，對 InnoDB 來說主鍵變小會連帶縮小所有次要索引。當值集合穩定且有限時值得考慮使用。

日期與時間型別#

DATETIME vs TIMESTAMP#

特性DATETIMETIMESTAMP
儲存空間8 bytes4 bytes
值域1001 ~ 9999 年1970 ~ 2038 年
時區儲存文字表示，與時區無關儲存 UTC 秒數，依時區顯示
格式YYYYMMDDHHMMSS（內部整數）Unix timestamp
自動更新無預設自動更新第一個 TIMESTAMP 欄位
NULL 預設可為 NULL預設 NOT NULL

能用 TIMESTAMP 就用，因為空間效率是 DATETIME 的一半。多時區應用中兩者行為差異很大：TIMESTAMP 自動處理時區轉換，DATETIME 保存的是原始文字。

  • 亞秒精度：MySQL 原生不支援（MariaDB 支援），可用 BIGINT 儲存微秒級 timestamp，或用 DOUBLE 在小數點後存亞秒

位元型別 (Bit-Packed Data Types)#

BIT#

  • MySQL 5.0+ 中 BIT(1) ~ BIT(64) 儲存位元值
  • MyISAM 會將多個 BIT 欄位緊密打包；InnoDB/Memory 則以最小整數型別儲存（不省空間）
  • 行為令人困惑：取出的值是二進位字串，在數值上下文中才是數字

BIT 型別容易造成混淆，大多數應用建議避免使用。儲存 true/false 可改用 nullable CHAR(0)（NULL 或空字串）。

SET#

  • 可儲存多個 true/false 值的打包位元集
  • 有 FIND_IN_SET() 等函式方便查詢
  • 缺點：修改定義需要 ALTER TABLE，無法用索引查找

整數位元運算（替代方案）#

  • 用 TINYINT/INT 搭配位元運算子 (&, |, <<)
  • 優點：不需要 ALTER TABLE 就能新增「列舉值」
  • 缺點：查詢較難閱讀與維護

識別碼選擇 (Choosing Identifiers)#

識別碼欄位的型別選擇極為關鍵——它會被用在 JOIN、外鍵、索引查找中，且型別會傳播到所有相關表格。相關表格必須使用完全相同的型別（包含 UNSIGNED 等屬性）。

整數型別（首選）#

  • 快速、支援 AUTO_INCREMENT
  • 選擇能容納值域的最小型別（如美國州 ID 用 TINYINT 就夠，省 3 bytes）

字串型別（盡量避免）#

  • 佔空間大、比較慢
  • MyISAM packed indexes 對字串索引可能慢達 6 倍

隨機字串（UUID / MD5 / SHA1）的問題#

  • 插入位置隨機 → 索引分頁、隨機磁碟存取、叢集索引碎片化
  • 相鄰邏輯列在磁碟上分散 → SELECT 慢
  • 破壞快取的區域性 (locality of reference) → 快取命中率低

若必須使用 UUID，用 UNHEX() 轉為 16 bytes 二進位，存在 BINARY(16) 欄位中，讀取時用 HEX() 轉回。移除 UUID 中的短橫線。

ENUM / SET 作為識別碼#

  • 一般不建議。適合靜態的「定義表」（如訂單狀態、性別等）

特殊資料型別#

IP 位址#

  • 常見錯誤：用 VARCHAR(15) 儲存
  • 正確做法：使用 INT UNSIGNED（IPv4 本質是 32-bit 無號整數）
-- 儲存
INSERT INTO tbl (ip) VALUES (INET_ATON('192.168.1.1'));
-- 讀取
SELECT INET_NTOA(ip) FROM tbl;

Schema 設計陷阱#

太多欄位#

  • MySQL 伺服器與儲存引擎之間透過 row buffer 傳遞資料，需要解碼成欄位
  • 極寬表（數百欄位）的欄位解碼成本很高，即使查詢只用到少數欄位

太多 JOIN#

  • MySQL 限制每次查詢最多 61 個表格 JOIN
  • EAV (Entity-Attribute-Value) 設計模式需要大量 self-join，容易超限
  • 經驗法則：高並發場景下，每個查詢最好控制在 12 個表格以內

過度使用 ENUM#

  • 不要用 ENUM 儲存數字代碼（如 ENUM('0','1','2',...,'31')），應改用整數加外鍵
  • 新增值需要 ALTER TABLE（MySQL 5.0 是阻塞操作）

SET 與 ENUM 混淆#

  • SET('Y','N') 幾乎肯定應該是 ENUM('Y','N')——除非值可以同時為 Y 和 N

過度避免 NULL#

  • 不要為了避免 NULL 而使用魔術常數（如 -1 代表未知整數、'0000-00-00 00:00:00' 代表無日期）——這會增加程式碼複雜度並引入 bug
  • 當確實需要表達「無值」時，NULL 通常是較好的選擇

MySQL 會對 NULL 建索引（Oracle 不會）。所以在 MySQL 中 NULL 欄位仍可被索引利用。

正規化與反正規化#

面向正規化反正規化
更新效率較快（資料不重複，只需改一處）較慢（冗餘資料需同步更新多處）
表格大小較小，更容易放入記憶體較大（含冗餘欄位）
唯一值查詢不需要 DISTINCT 或 GROUP BY 來取得唯一值清單可能需要額外去重
JOIN 需求非平凡查詢幾乎都需要 JOIN所有資料在同一表格，避免 JOIN
索引策略JOIN 使某些索引策略不可行（欄位被分散到不同表格）允許更有效的複合索引策略
最差情況效能JOIN 可能產生隨機 I/O全表掃描（對不適合記憶體的資料，比 JOIN 的隨機 I/O 快）
-- 正規化：需要 JOIN，無法同時排序與過濾
SELECT message_text, user_name
FROM message
   INNER JOIN user ON message.user_id = user.id
WHERE user.account_type = 'premium'
ORDER BY message.published DESC LIMIT 10;

-- 反正規化：單表查詢，可用 (account_type, published) 索引
SELECT message_text, user_name
FROM user_messages
WHERE account_type = 'premium'
ORDER BY published DESC LIMIT 10;

混合策略（實務做法）#

現實中幾乎不會使用完全正規化或完全反正規化。最常見的做法是選擇性複製欄位——在子表中冗餘存放父表的某些欄位（如在 message 表中冗餘存 account_type），可用 trigger 同步更新。

  • 冗餘欄位可避免 JOIN，也不會因完全反正規化而丟失資訊
  • 衍生值快取（如 num_messages 欄位）可避免昂貴的子查詢

快取表與摘要表 (Cache and Summary Tables)#

術語區分#

  • 快取表 (Cache Table)：儲存可從 schema 中慢速取得的冗餘資料（邏輯上冗餘）
  • 摘要表 (Summary Table / Roll-up Table)：儲存 GROUP BY 聚合結果（邏輯上不冗餘）

摘要表範例：每小時訊息計數#

CREATE TABLE msg_per_hr (
   hr DATETIME NOT NULL,
   cnt INT UNSIGNED NOT NULL,
   PRIMARY KEY(hr)
);
  • 每小時產生一筆摘要，比即時計算高效得多
  • 可結合「23 個完整小時 + 頭尾部分小時的精確計數」來達到精確的 24 小時統計

快取表用途#

  • 為搜尋/檢索建立不同的表結構與索引組合
  • 可使用不同儲存引擎（如主表 InnoDB、快取表 MyISAM 以支援全文搜尋）

影子表 (Shadow Table) 重建策略#

DROP TABLE IF EXISTS my_summary_new, my_summary_old;
CREATE TABLE my_summary_new LIKE my_summary;
-- 填充 my_summary_new 的資料...
RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
  • 原子性 RENAME 確保重建期間資料持續可用
  • 保留舊表以便快速回滾

Materialized Views (Flexviews)#

  • MySQL 不原生支援 materialized views
  • Flexviews 工具透過讀取 binary log 的變更 (CDC)，增量更新 materialized view
  • 不需要查詢原始資料表，利用 row-based binary log 中的 before/after row images 計算 delta

計數器表 (Counter Tables)#

單列計數器的並發問題#

-- 這是全域 mutex，會序列化所有更新交易
UPDATE hit_counter SET cnt = cnt + 1;

解法：多 slot 隨機更新#

CREATE TABLE hit_counter (
   slot TINYINT UNSIGNED NOT NULL PRIMARY KEY,
   cnt INT UNSIGNED NOT NULL
) ENGINE=InnoDB;

-- 預先填充 100 列，更新時隨機選擇
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

-- 讀取時聚合
SELECT SUM(cnt) FROM hit_counter;

每日計數器（含自動建立 slot）#

CREATE TABLE daily_hit_counter (
   day DATE NOT NULL,
   slot TINYINT UNSIGNED NOT NULL,
   cnt INT UNSIGNED NOT NULL,
   PRIMARY KEY(day, slot)
) ENGINE=InnoDB;

-- 使用 ON DUPLICATE KEY UPDATE 自動建立或累加
INSERT INTO daily_hit_counter(day, slot, cnt)
   VALUES(CURRENT_DATE, RAND() * 100, 1)
   ON DUPLICATE KEY UPDATE cnt = cnt + 1;
  • 可定期將所有 slot 合併到 slot 0 並清理多餘列，保持表格精簡

ALTER TABLE 加速技巧#

問題#

  • MySQL 大多數 ALTER TABLE 操作會建立新表 → 複製全部資料 → 刪除舊表
  • 大表可能需要數小時甚至數天

解法一：影子複製#

  • 在另一張表上建立新結構 → 搬移資料 → atomic RENAME 交換
  • 工具：Facebook Online Schema Change、Percona Toolkit (pt-online-schema-change)、openark toolkit

解法二：只修改 .frm 檔案#

某些操作其實不需要重建表格：

-- 慢（重建整張表）：
ALTER TABLE sakila.film
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

-- 快（只修改 .frm 檔）：
ALTER TABLE sakila.film
ALTER COLUMN rental_duration SET DEFAULT 5;

注意 MODIFY COLUMN 與 ALTER COLUMN 的差別。即使只改 DEFAULT 值，MODIFY COLUMN 仍會觸發完整重建。改預設值請用 ALTER COLUMN ... SET DEFAULT。

解法三：.frm 檔案置換（非官方）#

可免重建的操作：

  • 移除 AUTO_INCREMENT 屬性
  • 新增/移除/變更 ENUM 和 SET 常數（新值必須加在末尾）

步驟：

  1. 建立結構相同但帶有修改的空表
  2. FLUSH TABLES WITH READ LOCK
  3. 交換兩張表的 .frm 檔案
  4. UNLOCK TABLES

此技巧未經官方文件記載，使用前務必備份資料。ENUM 新增常數必須放在清單末尾，放中間會改變現有資料的意義。

快速建立 MyISAM 索引#

ALTER TABLE test.load_data DISABLE KEYS;
-- 載入資料...
ALTER TABLE test.load_data ENABLE KEYS;
  • MyISAM 延遲建立索引，最後透過排序一次建完——更快且產生緊湊的索引樹
  • DISABLE KEYS 只適用於非唯一索引

本章總結#

  • 使用小而簡單且適當的資料型別，盡量避免 NULL
  • 相關表格使用相同型別（尤其是 JOIN 欄位）
  • 注意變動長度字串在暫存表和排序中的悲觀記憶體配置
  • 識別碼優先使用整數
  • 避免 MySQL 遺留慣例（如浮點精度指定、整數顯示寬度）
  • ENUM/SET 好用但別濫用；BIT 最好避免
  • 正規化為主，選擇性反正規化；善用快取表與摘要表
  • ALTER TABLE 很痛苦——善用工具與技巧降低影響
Backward Profiling Server Performance Indexing for High Performance Forward