從“字段拆分”到“架構分層”：IM 系統消息狀態更新的演進之路詳情 - 後端 blossom 博客

摘要：在 IM 系統開發中，發送圖片或視頻是一個涉及長耗時 I/O 的過程，系統需要頻繁更新消息的流轉狀態（Pending -\> Uploading -\> Sent）。許多開發者為了追求 Schema 的簡潔性，傾向於將這些狀態字段放入 JSON Payload 中。本文將從數據庫底層原理（MVCC、Row Copy、TOAST）出發，剖析這種設計為何是性能的“隱形殺手”，並展示如何通過架構演進實現高性能的狀態管理。

1. 引言：一個 `UPDATE` 引發的蝴蝶效應

在開發類似微信的消息表（WxMessage）時，典型的業務流程如下：用户發送一張圖片，服務端先落庫佔位，隨後異步上傳文件，最後將狀態更新為“發送成功”。

直覺上，開發者往往認為 UPDATE 操作就像 C 語言修改內存變量一樣，是原地修改，代價極小。但現實是殘酷的——在 PostgreSQL 或 MySQL (InnoDB) 等現代關係型數據庫中，UPDATE 的物理代價遠比想象中昂貴。

特別是當你把一個高頻變化的狀態字段（如 media_status）藏在一個包含大量數據的寬表或者JSON 大對象（如 payload）中時，你正在親手製造系統的性能瓶頸。

2. 第一階段：把狀態藏在 JSON 裏（性能災難的開始）

最常見的“偷懶”設計是將所有非核心字段打包存儲：

-- 表結構：假設這張表還有其他 50 個業務字段
CREATE TABLE wx_message (
    id BIGINT PRIMARY KEY,
    -- 包含：{ "url": "...", "width": 100, "mediaStatus": "pending", "ocr": "..." }
    payload JSONB,
    ... 
);

-- 更新狀態
UPDATE wx_message 
SET payload = jsonb_set(payload, '{mediaStatus}', '"ready"') 
WHERE id = 1001;

這種設計面臨着 CPU、I/O 和索引的三重打擊。

2.1 底層機制：MVCC 帶來的強制 Row Copy

在 PostgreSQL 中，UPDATE 並非原地修改，而是遵循以下公式：
$$UPDATE = INSERT(新版本) + DELETE(舊版本)$$

當你執行上述 SQL 時，數據庫底層發生了什麼？

整行復制 (Row Copy)：哪怕你只改了 payload 裏的 5 個字節，數據庫必須把這一整行數據（包括 ID 和其他 50 個未修改的字段）全部複製一份，生成一個新的 Tuple（元組）。
WAL 日誌暴漲：物理層面的整行復制，意味着事務日誌（WAL）也要記錄這整行的數據，導致磁盤空間和 I/O 壓力驟增。

2.2 隱形殺手一：CPU 的無效燃燒

雖然 jsonb 存儲的是二進制格式，比純文本 json 快，但它依然不是可以直接修改的內存結構。執行 jsonb_set 時：

解碼 (Decoding)：遍歷二進制流，定位目標節點。
重組 (Repacking)：數據庫無法原地修改二進制流中間的位。它必須創建一個全新的二進制容器，將舊數據拷貝過來，插入新值，再封裝。

結論：在 JSONB 內部更新狀態 = 全量 Row Copy (I/O) + 二進制重組 (CPU)。

2.3 隱形殺手二：TOAST 機制帶來的“寫放大”災難

如果説上述問題只是“慢”，那麼 TOAST 機制則可能導致“崩”。

當 payload 超過數據庫頁閾值（PostgreSQL 默認為 2KB）時，它會被壓縮並切片存儲到獨立的 TOAST 表 中。

此時，修改 payload 裏的一個小狀態，將觸發驚人的寫放大 (Write Amplification)：

全量讀取：從 TOAST 表讀出所有切片（假設 10KB）。
解壓 (De-toast)：解壓為原始數據。
修改與重壓縮：修改狀態後重新壓縮。
全量寫入：在 TOAST 表中寫入全新的 10KB 數據。

為了改 5 個字節的狀態，產生了 20KB 的磁盤 I/O（讀+寫）。放大倍數高達 4000 倍！

3. 第二階段：將狀態提取為獨立列（顯著優化）

為了止損，我們將 media_status 提取出來作為獨立列。

ALTER TABLE wx_message ADD COLUMN media_status VARCHAR(20);

-- 更新狀態
UPDATE wx_message SET media_status = 'ready' WHERE id = 1001;

優化了什麼？

TOAST 指針複用：這是最大的收益。當更新獨立列時，新行數據會直接複用舊行指向 payload 的 TOAST 指針（OID）。這意味着我們完全避免了那 10KB 大對象的讀寫 I/O。
HOT Update (Heap Only Tuple)：如果 media_status 沒有索引，PostgreSQL 甚至可以在當前數據頁內完成更新，無需觸碰任何索引，性能極高。

依然存在的痛點

雖然避開了 TOAST 災難，但 MVCC 的 Row Copy 依然存在。

主表 I/O 依舊：主表（Heap）裏的那一行（包含 50 個字段的元組）依然要被完整複製一遍。
鎖競爭：消息表是核心高頻讀取表。狀態更新會產生行鎖（Row Lock），可能阻塞用户的併發操作（如撤回、刪除）。

4. 第三階段：終極方案——資源與信令分離

問題的根源在於：我們把 “易變的狀態” 放在了 “笨重的寬表” 裏。

消息表：字段多、體積大、讀取頻次高。它的每一行都像一輛重型卡車。
狀態更新：這是一個極高頻、極輕量的動作（更換螺絲）。

每次狀態更新，都相當於為了換一顆螺絲，把整輛卡車拆了重裝一遍（Row Copy）。

解決辦法是：不要動卡車。我們將系統拆分為兩張表：

4.1 資源表 (`wx_media_resource`)

這張表只關心“物理文件”，生命週期與文件上傳綁定。

CREATE TABLE wx_media_resource (
    file_hash VARCHAR(64) PRIMARY KEY, -- MD5去重
    oss_url VARCHAR(255),
    upload_status VARCHAR(20) -- 更新頻繁：PENDING -> UPLOADED
);

4.2 消息表 (`wx_message`)

這張表只關心“業務關係”，引用資源。

CREATE TABLE wx_message (
    id BIGINT PRIMARY KEY,
    content VARCHAR(64), -- 僅存儲引用 file_hash
    ... -- 其他 50 個字段
);

4.3 架構收益

徹底消除消息表的 Row Copy：
- 消息插入後，wx_message 表幾乎變成只讀（Immutable）。
- 無論文件上傳狀態怎麼變，消息表的那一行數據紋絲不動。沒有 Row Copy，沒有索引更新，沒有 WAL 膨脹。
輕量級更新：
- 狀態流轉只發生在 wx_media_resource 表。這張表字段極少（輕量級小車），Update 的代價極低。
秒傳與去重：
- 1000 人轉發同一個熱門視頻，消息表有 1000 行，但資源表只有 1 行。
- 當這 1 行狀態變為 UPLOADED，引用它的 1000 條消息瞬間全部“生效”，無需逐行 Update。

5. 總結與最佳實踐

從一個簡單的 UPDATE 語句出發，我們推導出了系統架構設計的三個層次：

反模式：把高頻狀態放在 JSON 裏。
- 代價：全量 Row Copy + CPU 重組 + TOAST 寫放大。
優化模式：字段獨立（Column Extraction）。
- 優勢：複用 TOAST 指針。
- 代價：全量 Row Copy（主表）。
架構模式：分表設計（Normalization）。
- 優勢：零 Row Copy（針對主業務表），實現真正的動靜分離。

一句話建議：
在設計數據庫 Schema 時，請遵循 “動靜分離” 原則——不要讓一個頻繁跳動的心臟（狀態字段），長在一個笨重的身體（大寬表/大JSON）裏。

本文由mdnice多平台發佈

blossom 博客

blossom 博客

博客 / 詳情