Schema 與型別的代價：改一個欄位長度鎖住整張表 40 分鐘

先回憶 backend ch01 的那個事實：在 InnoDB 裡，表本身就是主鍵索引（聚簇索引），資料列按主鍵順序實際存放在頁裡。

這件事推出一個很多人沒想過的結論：主鍵的取值方式，決定了資料是「順著寫」還是「隨機插」。

自增主鍵：順著寫

新資料的主鍵永遠最大 → 永遠插在最後一頁的尾端
→ 那一頁寫滿了就開新的一頁
→ 頁利用率接近 100%，幾乎不需要搬動既有資料

隨機 UUID：插在中間

新資料的主鍵是隨機值 → 該插在哪一頁完全隨機
→ 那一頁如果滿了，必須「頁分裂」：
     把原本那頁的資料搬一半到新頁，再插入
→ 一次寫入變成：讀舊頁 + 寫兩頁 + 更新父節點

四個連鎖後果

如果業務上真的需要 UUID

需求通常是「id 不要能被猜到」或「要在寫入資料庫之前就有 id」。這兩個需求都可以滿足，而且不必付上面的帳：

• 用 BINARY(16) 存，不要用 CHAR(36)：36 個字元的字串換成 16 bytes 的二進位，省下 8 成空間。
• 用有序的 UUID：MySQL 8.0 的 UUID_TO_BIN(uuid, 1) 會把時間戳的高低位對調，讓值大致遞增——順序性回來了，頁分裂就消失了。UUIDv7／ULID 也是同樣的思路。
• 或者：內部主鍵用自增，對外另外給一個唯一的隨機碼。兩個目標分開達成，各自用最適合的形式——這通常是最乾淨的解法。

-- 有序 UUID 的建表方式
id BINARY(16) NOT NULL PRIMARY KEY,
-- 寫入：INSERT ... VALUES (UUID_TO_BIN(UUID(), 1), ...)
-- 讀出：SELECT BIN_TO_UUID(id, 1) FROM ...

「VARCHAR 只按實際長度儲存，所以宣告長一點沒差」——這句話對了一半，而錯的那一半會在最意外的地方咬你。

對的部分

在資料頁裡，VARCHAR(50) 存 'abc' 確實只佔 3 bytes 加上長度前綴。宣告長度不影響磁碟上的實際佔用。

錯的部分（三個）

• 記憶體暫存表按「最大可能長度」配置。回扣 ch04：需要暫存表或排序時，MySQL 會依欄位的宣告長度來算每列要多少空間。一個 VARCHAR(255) 的 utf8mb4 欄位＝1020 bytes，十個這種欄位就是每列 10KB——於是本來能在記憶體完成的操作，改成落磁碟。
• 索引長度受限。InnoDB 單一索引鍵最長 3072 bytes。VARCHAR(255) 在 utf8mb4 下就是 1020 bytes，複合索引很容易撞到上限。
• 長度前綴會跳級。欄位最大位元組數小於 256 時，長度前綴只要 1 byte；達到 256 就要 2 bytes。而跨過這條線的 ALTER 必須重建整張表——這正是本章那個失敗場景。

其他幾個實務上會痛的選擇

「欄位不要允許 NULL，會影響效能」是流傳很廣的說法。它有道理，但理由常常被講錯。

被誇大的部分

• 儲存成本幾乎可以忽略：InnoDB 每列有一個 null bitmap，每個可為 NULL 的欄位佔 1 個 bit。16 個這種欄位才多 2 bytes。
• NULL 值可以進索引（與 Oracle 不同），WHERE col IS NULL 也可以走索引。這點常被誤傳成「有 NULL 就不走索引」。

真的要小心的三件事

ch03 提過「與其優化那個 JOIN，不如把欄位冗餘過來」。這一節把這筆帳算清楚——這是「代價守恆」最赤裸的一次。

反範式買到的

• 少一次 JOIN（尤其是列表頁那種一次要顯示幾十筆的場景）。
• 可以在冗餘欄位上建索引、可以直接排序——這是單靠 JOIN 拿不到的能力。例如「按商家名稱排序訂單」，欄位在別張表時幾乎不可能有效率地做。

付出的

• 一致性維護：來源改了誰負責同步？同步失敗怎麼辦？漏同步的資料怎麼補？
• 每一個寫入路徑都要記得：新功能、資料修補腳本、匯入批次——只要有一條路徑忘了，資料就開始不一致，而且不會有人發現。
• 對帳成本：實務上必須另外寫一支定期比對的排程，否則你永遠不知道它歪了沒。

大欄位拆表：一個常被低估的優化

InnoDB 的頁是 16KB。一張表如果有 TEXT 型態的備註、JSON 設定、序列化內容，會有兩個效應：

• 超長的欄位會存到頁外（off-page），讀它要多一次 I/O。
• 更重要的是：每一頁裝得下的列數變少。列變寬 → 同樣的查詢要讀更多頁 → 全表掃描與範圍掃描一起變慢，即使你根本沒有 SELECT 那個大欄位。

-- 拆之前：一頁只裝得下 8 列
orders(id, user_id, amount, status, created_at, remark TEXT, snapshot JSON)

-- 拆之後：主表一頁裝得下 100+ 列，範圍查詢快一個數量級
orders(id, user_id, amount, status, created_at)
order_details(order_id PK, remark TEXT, snapshot JSON)

代價是取詳情時要多一次查詢（或一次 JOIN）——但詳情頁一次只看一筆，而列表頁一次要看幾十筆。把成本從「常做的操作」搬到「少做的操作」，這是這類優化的通用形式。

順帶一提：這也是 SELECT * 的第三個代價

ch04 已經說過兩個（推向雙路排序、暫存表落磁碟）。第三個是：把 off-page 的大欄位一起撈出來，多一次 I/O、多一份網路傳輸、多一份 ORM 映射。三個代價都不會出現在 EXPLAIN 上。

MySQL 8.0 的 DDL 有三種演算法，成本差了好幾個數量級。而預設行為是「自己挑一個能用的」——所以你可能在完全不知情的狀況下觸發了最貴的那種。

三種演算法

• INSTANT：只改資料字典（metadata），不碰資料。幾乎瞬間完成，不論表多大。8.0.12 起支援在尾端加欄位，8.0.29 起支援任意位置加欄位。
• INPLACE：在原表上重建（例如加索引），不複製整張表，過程中允許併發 DML。但開始與結束時仍需要短暫的 exclusive MDL——這正是危險所在。
• COPY：建一張新表、把資料一列一列複製過去、再換名。過程中阻塞寫入，時間與表大小成正比。

最重要的一招：明確宣告，不要讓它默默降級

ALTER TABLE orders
  MODIFY COLUMN memo VARCHAR(100),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 不支援就會得到：
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
--   Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

常見操作各屬於哪一種

大表怎麼辦：影子表工具

當變更無法避開 COPY，而表又大到不能鎖，就用 gh-ost 或 pt-online-schema-change：

代價：需要一倍的額外磁碟空間、整體耗時更長（幾小時）、外鍵支援不好。但它把「不可控的 40 分鐘鎖表」換成了「可控的幾小時背景作業」，在正式環境幾乎永遠是對的取捨。

徵狀

需求很小：會員暱稱欄位不夠長，從 VARCHAR(60) 改成 VARCHAR(100)。工程師確認過「VARCHAR 加長是 INPLACE，不會鎖表」，於是排在週三下午上線。

ALTER TABLE members MODIFY COLUMN nickname VARCHAR(100) NOT NULL;

• 指令下去之後沒有回應。
• 兩分鐘內，所有會員相關的 API 全部逾時——連純 SELECT 也是。
• 接著連線池耗盡，整個服務掛掉，包括跟會員無關的功能。
• 總共 40 分鐘。

診斷

SHOW PROCESSLIST;
-- id   time  state
-- 88   2410  copy to tmp table          ← ALTER 正在複製整張表
-- 91   2380  Waiting for table metadata lock
-- 92   2380  Waiting for table metadata lock
-- ...（幾百個）

兩件事同時發生了，而且互相加乘。

根因一：VARCHAR 加長跨過了 255 bytes 界線

根因二：MDL 佇列把局部問題變成全站故障

如果只是 COPY，那是「這張表 40 分鐘不能寫」。但實際上連 SELECT 都掛了，原因是 ch05 那個機制：

• ALTER 持有／等待 MDL 寫鎖。
• MDL 佇列是 FIFO——排在 ALTER 後面的每一個新查詢，即使只是讀，也要排隊。
• 請求塞住 → 佔住連線 → 連線池耗盡 → 跟會員無關的 API 也拿不到連線（ch05 最後一節那條外溢路徑）。

所以真正的災難不是「一張表被鎖 40 分鐘」，是「一張表的 DDL 讓整個服務掛掉」。

修法

這個案例的教訓

把前面幾節收成一套可以直接照著跑的流程。schema 變更與查詢優化最大的差別是：它幾乎不能回滾（改回去一樣要重建），所以流程比技巧更重要。

上線前

相容性：先加後刪，分兩次上線

應用程式與資料庫不會同時更新，所以每個 schema 變更都要能與新舊兩版程式碼同時共存：

• 加欄位：先加（可為 NULL 或有預設值）→ 部署會寫入它的程式 → 回填舊資料 → 最後才加 NOT NULL 約束。
• 刪欄位：先部署「不再使用它」的程式 → 觀察一段時間 → 才真的 DROP。不要在同一次上線裡同時改程式和刪欄位，否則回滾程式碼時會直接爆炸。
• 改名：等於「加新的 + 雙寫 + 遷移 + 刪舊的」四步，不要用一次 RENAME 解決。

什麼時候「不要改 schema」

• 只是為了省空間：BIGINT 改 INT 省下的空間，通常遠不值得那次 COPY 的風險。
• 只是為了美觀：欄位名不好看、順序不對——這些沒有實質收益，卻要付全額風險。
• 問題其實出在查詢上：先確認不是索引或 SQL 的問題（前五章）。schema 是最後才動的東西，因為它最貴、最難回頭。

把這一章收成三句

剩下兩章要把前面六章串起來：ch07 是「線上變慢了，從哪裡開始查」的完整流程；ch08 則回到 Java 這邊，看 ORM 到底發出了什麼 SQL——因為前面所有的分析，前提都是「SQL 真的長你以為的那樣」。

五筆帳

• 自增主鍵 → 順序寫入很理想，但高併發插入時最後一頁會成為熱點（所有交易都在搶同一頁的鎖，回扣 ch05）。極高寫入量的場景反而要考慮分段或有序 UUID。另外自增值是可預測的，對外暴露 id 要另外考慮。
• 有序 UUID（UUID_TO_BIN(x, 1)） → 解決了頁分裂，但仍是 16 bytes（自增 BIGINT 是 8），每個二級索引還是比較胖；而且值不再是「完全隨機」，時間資訊可以被還原出來。
• 大欄位拆表 → 列表查詢快一個數量級，但取詳情多一次查詢，而且寫入時要維護兩張表的一致性（沒有外鍵時尤其要小心孤兒資料）。
• 反範式 → 買到讀取速度與排序能力，付出每一條寫入路徑的維護成本，還要多寫一支對帳排程。
• gh-ost → 把不可控的鎖表換成可控的背景作業，但需要一倍磁碟空間、耗時更長、外鍵支援不好。

這一章的判斷看不到的東西

• DDL 在複本上也要跑一次。主庫 40 分鐘的 ALTER，複本也要花 40 分鐘——期間複本延遲會一路累積。如果有讀寫分離，那段時間讀到的都是舊資料（ch07 會談複本延遲）。
• 型別變更可能改變語意。VARCHAR 縮短會截斷資料；collation 變更會改變排序與比較結果（'a' = 'A' 在某些定序下成立、某些不成立），甚至讓原本沒有衝突的唯一索引突然有衝突。
• schema 沒有「這句 SQL 慢」這種訊號。它的代價分散在每一次寫入、每一個索引、每一頁的利用率上——沒有任何一條慢查詢日誌會指向它。要靠定期檢視表大小、索引大小、頁利用率主動發現。