MySQL 字符串類型效能深度解析：CHAR vs VARCHAR vs TEXT vs BLOB

MySQL 字符串類型效能深度解析：CHAR vs VARCHAR vs TEXT vs BLOB

類型定義與儲存特性對比

CHAR vs VARCHAR 核心效能對比

儲存空間

-- 假設存儲 'abc' (3 字符)
CHAR(10)  : 佔用 10 字節 (填充 7 個空格)
VARCHAR(10): 佔用 3 + 1 = 4 字節 (長度前綴 1 字節)
-- 結論：VARCHAR 節省空間，尤其長度變化大時

查詢效能 (SELECT)

• VARCHAR 勝出：資料較小，I/O 少，Buffer Pool 容納更多行
• 定長 CHAR 需讀取固定塊，即使實際內容短
• 但：CHAR 無碎片，全表掃描時 CPU 計算略快 (無長度解析)

更新效能 (UPDATE)

• CHAR 勝出：原地更新，長度固定，無頁分裂/行遷移
• VARCHAR 變長更新：若新值更長，可能導致行遷移 (頁分裂、碎片、額外 I/O)
• InnoDB 中：更新非索引列且長度不超過頁剩餘空間，原地更新；否則刪除舊行+插入新行

索引效能

• CHAR 索引條目固定長，B+Tree 分裂可預測
• VARCHAR 索引條目變長，壓縮前綴技術 (Prefix Compression) 減少空間
• 實測：前綴長度相近時差異微小，VARCHAR 因體積小通常更優

TEXT/BLOB 特殊行為與限制

關鍵差異

InnoDB 行溢出機制

-- 行大小限制 ~8KB (半頁)
-- VARCHAR/TEXT/BLOB 超過 768 字節時：
-- 1. 前 768 字節存主頁
-- 2. 剩餘存溢出頁 (Uncompressed BLOB 頁)
-- 3. 主頁保留 20 字節指標指向溢出頁鏈表

-- Barracuda 格式 (file_per_table + ROW_FORMAT=DYNAMIC/COMPRESSED)：
-- 完全將長列移至溢出頁，主頁只存 20 字節指標
-- 啟用：innodb_file_per_table=1 + ROW_FORMAT=DYNAMIC

選型決策指南

長度固定且短 (≤ 20 字符)？
  ├─ 是 → CHAR (MD5、UUID、代碼、標誌位)
  └─ 否
      長度 ≤ 65KB 且需索引/排序/預設值？
        ├─ 是 → VARCHAR (最通用選擇)
        └─ 否
            需存儲二進制 (圖片、加密數據)？
              ├─ 是 → BLOB 系列 (按大小選 TINY/MEDIUM/LONG)
              └─ 否 → TEXT 系列 (按大小選)
                  注意：TEXT/BLOB 會強制磁碟臨時表、需前綴索引、影響排序

實戰避坑案例

案例 1：用 VARCHAR(255) 存 MD5 → 應用 CHAR(32)

-- MD5 固定 32 字符，CHAR(32) 節省 1 字節長度前綴，無碎片
ALTER TABLE t MODIFY md5_hash CHAR(32) NOT NULL;

案例 2：TEXT 欄位加全長索引報錯

-- 錯誤
ALTER TABLE articles ADD INDEX idx_content (content);
-- Error: BLOB/TEXT column 'content' used in key specification without a key length

-- 正確：指定前綴長度 (業務查詢前 100 字符足夠區分)
ALTER TABLE articles ADD INDEX idx_content (content(100));

案例 3：大量 TEXT 導致 Using temporary; Using filesort

-- 查詢包含 TEXT 欄位且 ORDER BY
SELECT id, title, content FROM articles ORDER BY created_at LIMIT 10;
-- EXPLAIN 顯示 Using temporary; Using filesort (磁碟臨時表)

-- 優化：延遲關聯 / 只查主鍵排序再回表
SELECT a.id, a.title, a.content
FROM articles a
JOIN (SELECT id FROM articles ORDER BY created_at LIMIT 10) b USING (id);

案例 4：JSON 資料存 TEXT vs JSON 類型 (5.7+)

-- 5.7+ 原生 JSON 類型優勢：
-- 1. 二進制儲存，解析快
-- 2. 支援 JSON_PATH 索引 (虛擬欄位索引)
-- 3. 驗證合法性
-- 4. JSON_EXTRACT、->、->> 操作符

CREATE TABLE config (
  id INT PRIMARY KEY,
  data JSON,  -- 推薦
  -- data TEXT,  -- 不推薦
  INDEX idx_data_name ((JSON_EXTRACT(data, '$.name')))
);

效能調優參數

總結建議

• ✅ 90% 場景用 VARCHAR，長度設業務實際最大值 + 余量
• ✅ 固定長度代碼/雜湊值用 CHAR
• ✅ 大文本/二進制用 TEXT/BLOB，但避免索引、排序、臨時表
• ✅ 5.7+ 結構化文本**優先用 JSON 類型**
• ✅ 啟用 ROW_FORMAT=DYNAMIC 減少行溢出開銷
• ❌ 不要用 `VARCHAR(255)` 當萬能類型，評估實際長度
• ❌ 不要在 TEXT/BLOB 上做 `DISTINCT`/`GROUP BY`/`ORDER BY`