知識冒險者

MySQL 與 PostgreSQL 怎麼選？

Properties1
tagsdatabase, mysql, postgresql, 選型

閱讀時間約 16 分鐘

MySQL 與 PostgreSQL 怎麼選？

統整

文章的主張：從儲存結構的底層視角分析，是區分這兩者最本質、也最硬派的方式。

MySQL（InnoDB）是「索引組織表」（Index-Organized Table），PostgreSQL 是「堆疊表」（Heap Table）。 這個根本差異決定了它們在讀取速度、寫入效能、磁碟空間維護上的截然不同。

一、核心結構：數據長什麼樣子

MySQL (InnoDB)：Clustered Index（聚簇索引）

表就是索引，索引就是表。數據行直接存在主鍵 B+ Tree 的葉子節點裡，所以數據是按主鍵順序物理排列的。

  • 優點：用主鍵查詢極快——找到索引就等於找到數據，不需要二次跳轉
  • 缺點：主鍵很長或很亂（例如 UUID）會讓整個表結構碎片化，寫入效能大幅下降

PostgreSQL：Heap Table（堆疊表）

數據和索引分開存。數據像一堆雜亂的文件被丟進一個大桶子（Heap），沒有特定順序。所有索引（包括主鍵索引）都只是「目錄」，存一個指針（CTID）指向 Heap 中的物理位置。

  • 優點：寫入非常快（直接丟進 Heap）；主鍵與二級索引是平權的，沒有主從之分
  • 缺點：即使用主鍵查詢，也要先查索引、拿到位置再去 Heap 撈（多一次 I/O 跳轉）

二、查詢效能：讀取路徑的差異

場景MySQLPostgreSQL
主鍵查詢🏆 搜尋一次 B+ Tree 就拿到數據搜尋 B+ Tree 拿指針 → 再讀 Heap（理論上多一次 I/O）
二級索引查詢二級索引不存數據地址，存「主鍵值」：二級索引 → 主鍵索引 → 數據，即回表（Double Lookup），大數據量下比 PG 慢🏆（通常）二級索引直接指向 Heap：二級索引 → Heap

決策點：業務充滿複雜查詢組合（大量二級索引）→ PostgreSQL 的結構通常更穩定；業務 90% 都是 WHERE id = ? → MySQL 更快。

三、寫入與更新：MVCC 與寫入放大

這是從儲存結構看最痛的差異點，主要體現在 UPDATE。

MySQL：原地更新（In-place Update） 更新時嘗試在原本位置直接改（搭配 Undo Log 做版本控制）。只要索引欄位沒變，二級索引不需要修改 → 對寫入效能非常友善。

PostgreSQL：新版插入（Append-only） PG 的 MVCC 是把更新視為插入一筆新 Tuple，並標記舊數據過期。因為數據換了新位置（新的 CTID），這張表上所有的索引都要更新指向新位置。

後果（寫入放大 Write Amplification）：表上有 10 個索引，你只改了一個無關緊要的欄位，這 10 個索引全部要跟著改寫一次，造成巨大 I/O 壓力。 註：PG 有 HOT（Heap Only Tuples） 可以緩解，但仍有限制。

四、空間維護：膨脹與清理

  • PostgreSQL（VACUUM）：更新是「生成新數據、保留舊數據」→ 產生大量死 Tuple，需要背景程序 AutoVacuum 定期清理。配置不當或寫入太快 → 表迅速膨脹（Bloat），吃光磁碟、查詢變慢
  • MySQL（Purge）：舊版本存在 Undo Log（獨立區域），主表空間通常比較緊湊。Undo Log 也要清（Purge），但不會直接導致主表結構快速膨脹

五、儲存結構層的小結

選 MySQL，如果：

  • 讀多寫少且依賴主鍵查詢（使用者系統、訂單流水，大多是 SELECT * FROM t WHERE id = ...）
  • 更新頻繁且表上有大量索引——原地更新能避開 PG 那種寫入放大災難
  • 不想花時間維護資料庫（不想處理 Vacuum 調優、表膨脹）

選 PostgreSQL，如果：

  • 複雜查詢與多種索引路徑：查詢條件靈活、要組合多個二級索引，Heap 結構配合強大的優化器（Bitmap Scan）表現更好
  • 寫入以 INSERT 為主、較少 UPDATE：Append-only 對純新增的日誌型數據非常快
  • 要存大量長文本／JSON：TOAST 機制處理大欄位（超過 8KB）比 MySQL 更高效聰明

除了儲存結構，文章再補四個層面：

六、功能豐富度：夠用 vs 全能

  • MySQL — Relational Only。哲學：複雜邏輯留給程式碼，資料庫只做存取
    • 8.0 加了 CTE 與 Window Functions，但複雜查詢優化仍不如 PG
    • Join 演算法長期依賴 Nested Loop Join（簡單暴力），多表關聯大數據查詢效率較差（8.0.18 加入 Hash Join，但 PG 更成熟）
  • PostgreSQL — Object-Relational。哲學：資料庫可以處理複雜計算
    • PostGIS：做地圖／LBS，PG 是唯一解
    • JSONB：二進制儲存 ＋ 完整索引，部分場景效能超越 MongoDB
    • 豐富型別：原生 Array、IP address、Range，大幅簡化程式碼

結論：大量複雜報表、地理資訊、JSON 當主力 → PostgreSQL；標準 CRUD → MySQL 簡單好用。

七、併發控制：鎖的機制

  • MySQL（InnoDB）：Gap Lock。為了在 RR 級別解決幻讀，用 Next-Key Lock（含 Gap Lock）
    • 風險：高併發寫入時鎖定一個不存在的範圍，可能意外鎖住其他無辜的數據插入，死鎖機率較高
  • PostgreSQL：SSI（Serializable Snapshot Isolation），鎖機制相對輕量
    • 優勢：DDL 非常友善——ADD COLUMN（除了設 default）通常瞬間完成且不鎖表；MySQL 舊版做 DDL 往往鎖全表（8.0 有改善，但 PG 依然是運維最愛）

八、連接處理模型：最容易踩的雷

  • MySQL：多執行緒。每個連接對應一個 Thread，切換成本低、記憶體佔用少 → 輕鬆支撐上千並發連接，不需要太複雜的中間層
  • PostgreSQL：多進程。每個連接對應一個獨立 Process，非常吃記憶體且建立連線開銷大
    • 必備技能：應用併發很高（微服務、幾千個 Pod 連進來），裸連 PG 必死無疑，必須搭配 PgBouncer 這類連線池中間件

結論：MySQL 對連線管理比較寬容；用 PG 則必須具備配置 PgBouncer 的運維能力。

九、高可用與複製：可靠性 vs 靈活度

  • MySQL：Binlog（邏輯複製）
    • 優點：非常靈活——Master-Slave、雙向複製，甚至手動解析 Binlog 做 CDC（如 Debezium）
    • 缺點：傳統非同步複製可能丟數據（主庫掛掉時 Binlog 還沒傳過去）；有 Group Replication 但配置複雜
  • PostgreSQL：Streaming Replication（物理複製，基於 WAL）
    • 優點：數據一致性極高，主從延遲通常比 MySQL 低且穩定
    • 缺點：版本要求嚴格（主從版本必須一致），升級麻煩；PG 10 後也支援邏輯複製，但物理複製仍是主流高可用方案

十、文章最後的決策表（原文為圖片，轉錄）

分析層面情境特徵建議選擇原因
功能層需處理 GPS、地圖、幾何運算PostgreSQLPostGIS 無可替代
功能層資料結構多變，需存大量 JSONPostgreSQLJSONB 索引效能極佳
架構層團隊沒有專職 DBA，想開箱即用MySQL社群資源多，雲端支援最成熟，連線管理簡單
架構層微服務架構，連線數極高（>5000）MySQL（或 PG + PgBouncer）MySQL 原生抗高併發連線能力較強
運維層業務需頻繁修改表結構（DDL）PostgreSQLTransactional DDL 讓改表更安全，不鎖表
性能層複雜的 OLAP 報表查詢PostgreSQLHash Join 與強大的優化器適合複雜計算

反覆出現的關鍵字

作者一再回到的詞（判斷文章的重心，而不是我覺得有趣的部分）：

  • 儲存結構 — 全篇的立論基礎，第一句就宣告「這是最本質、最硬派的區分方式」
  • Clustered Index／索引組織表 vs Heap Table／CTID — 一切差異的源頭
  • 二級索引 — 幾乎每一節都回到它：讀路徑、回表、寫入放大、索引全改寫
  • 回表（Double Lookup） — MySQL 讀的代價
  • MVCC／寫入放大（Write Amplification） — PG 寫的代價，作者稱之為「最痛的差異點」
  • VACUUM／Bloat／死 Tuple — 寫入放大的後續帳單
  • 連線模型（Thread vs Process）／PgBouncer — 作者標成「最容易踩的雷」
  • JSONB／PostGIS／TOAST — PG 的三個殺手級賣點
  • Gap Lock vs SSI、DDL 鎖不鎖表
  • Binlog（邏輯）vs WAL／Streaming（物理）

矛盾點

文章內部

  1. 「PG 二級索引通常勝」與「PG 的索引會被更新全改寫」擺在一起沒有調和。 第二節說 PG 二級索引直指 Heap、路徑較短所以勝；第三、四節又說 PG 的每次 UPDATE 會讓所有索引重寫、表會 Bloat、查詢變慢。 （推論）兩者其實不衝突，條件藏在小結裡：PG 的讀取優勢建立在這張表不常 UPDATE 的前提上——文章把這個前提寫在「選 PostgreSQL，如果：寫入主要是 INSERT、較少 UPDATE」，但沒有在第二節當場講明。

  2. 「PG 寫入非常快」與「PG 寫入放大災難」並列。 第一節說 PG「數據寫入非常快（直接丟進 Heap）」，第三節說 PG 寫入會造成巨大 I/O 壓力。 （推論）兩句話的主語不同：前者是 INSERT，後者是 UPDATE。文章從頭到尾沒有在同一處把這兩個動詞分開，容易讀成矛盾。

  3. MySQL 的 Join 能力評價前後不一致。 第六節說 MySQL「長期依賴 Nested Loop Join，8.0.18 才加入 Hash Join」；但決策表把「複雜 OLAP 報表」判給 PG 的理由寫成「Hash Join 與強大的優化器」——（推論）這裡的 Hash Join 應指 PG 的實作成熟度，而不是 MySQL 沒有 Hash Join。

與 vault 既有筆記

  1. 與 叢集索引一張表只有一個，非叢集索引是另一份帶 pointer 的結構 直接衝突。 那篇寫「非叢集索引 ＝ 索引欄位 ＋ 一個指向原始資料的 pointer」。本文說得更細：那是 PostgreSQL 的情況（CTID 指標）；InnoDB 的二級索引存的是「主鍵值」而不是位址，所以才需要回表。 → 既有筆記的描述對 PG 成立、對 MySQL 不成立，需要修正。（那篇的來源是 iThome 文章，當時就已經標記「定義偏薄」。）

  2. 與 建索引的代價是毫秒，收益可能是秒 的預設相反。 那篇寫「UPDATE 只有改到索引包含的欄位時才要更新索引；只改無關欄位時 PostgreSQL 的 HOT 機制可能連索引都不用碰」——把 HOT 當成常態。 本文主線剛好相反：PG 更新所有索引都要改寫，HOT 只在註腳裡出現且「仍有限制」。 （推論）差別在 HOT 的成立條件（同一個 page 內要有空位、且不能改到索引欄位），兩篇其實在講同一機制的兩端，但沒有一篇說清楚條件何時失效。

  3. 與 jsonb 能比資料模型，不能比擴展架構 一致但範圍更大。 那篇已經寫了「改 jsonb 裡一個小欄位 → 整份文件被重寫 → bloat 與 vacuum 壓力」。本文指出這不是 jsonb 的特性，是 PG MVCC 的通性，任何有多個索引的表都會中。

  4. 與 索引是拿寫入換查詢，寫多讀少的表加索引是純虧 可以接起來。 那篇的帳是「索引的成本 ＝ 每次寫入多維護幾棵樹」。本文補上一個那篇沒算到的乘數：在 PG，連「沒改到索引欄位」的 UPDATE 都可能要重寫所有索引。

抽出的原子筆記

未抽成筆記、留在本篇的部分：功能豐富度（PostGIS／JSONB／豐富型別，與 jsonb 能比資料模型，不能比擴展架構 重疊）、Gap Lock vs SSI 與 DDL 鎖表（材料只有兩三句、缺機制）、最後那張選型決策表。

關係圖譜

反向連結