PostgreSQL 與 MySQL 深度對比：架構、特性、優劣勢與選型指南
PostgreSQL 與 MySQL 是兩大最流行的開源關係型數據庫。本文從架構模型、核心特性、SQL 支持、性能表現、運維生態等維度系統對比，幫助技術選型決策。
1. 核心架構差異
| 維度 | MySQL | PostgreSQL |
|---|
| 進程/線程模型 | 單進程多線程 | 多進程 (每連接一進程) |
| 多核利用率 | 線程調度依賴 OS，高併發下上下文切換開銷大 | 進程隔離，自然利用多核，高併發穩定性更好 |
| 連接數承載 | 輕量線程，支持極大連接數 (萬級) | 進程較重，需連接池 (PgBouncer/PgPool) 支撐高連接 |
| 共享內存 | 線程間自然共享全局變量、緩存 | 需顯式共享內存區 (shared_buffers 等) |
| 崩潰隔離 | 單進程崩潰影響所有連接 | 單進程崩潰不影響其他進程，可用性更高 |
2. 存儲引擎 vs 表存儲
- MySQL： 插件式存儲引擎架構。InnoDB (默認、事務、行鎖、MVCC、聚簇索引)、MyISAM (非事務、表鎖、全文)、Memory、RocksDB 等可混用。
- PostgreSQL： 單一存儲引擎 (Heap + TOAST)，所有表統一結構。通過表分區 (Partitioning)、表空間 管理物理存儲。
3. 事務與 MVCC 實現
| 特性 | MySQL (InnoDB) | PostgreSQL |
|---|
| ACID 支持 | 完整 | 完整 |
| 隔離級別 | 4 級全支持，默認 REPEATABLE READ | 4 級全支持，默認 READ COMMITTED |
| MVCC 實現 | Undo Log 回滾段 + Read View，舊版本在 Undo 表空間 | 多版本元組鏈直接存主表，無單獨回滾段，VACUUM 清理 |
| 優勢 | 索引指向主鍵，二級索引查詢需回表；回滾段故障風險 | 無回滾段故障風險；VACUUM 可並行、增量；索引無版本信息，不支持 Index Only Scan (需 visibility map) |
| 異常恢復 | Redo + Undo 雙階段，較複雜 | 僅 WAL (Redo)，恢復更簡單快速 |
4. SQL 功能與擴展性對比
| 功能 | MySQL | PostgreSQL |
|---|
| 複雜查詢/優化器 | 較弱，僅 Nested Loop Join | 極強，支持 Hash Join、Merge Join、並行查詢 |
| 窗口函數 / CTE | 8.0+ 支持 | 早期完整支持 |
| 遞歸查詢 | 8.0+ 支持 | 長期支持 |
| 部分索引 / 條件索引 | 不支持 | 支持 (CREATE INDEX ... WHERE condition) |
| 函數索引 / 表達式索引 | 8.0+ 支持 | 長期支持 |
| 物化視圖 | 不支持 | 通過表+觸發器/規則實現 |
| 存儲過程/函數語言 | SQL、C++ | PL/pgSQL、PL/Python、PL/Perl、PL/Tcl、SQL、C |
| 觸發器 | 行/語句級，BEFORE/AFTER | 行/語句級，BEFORE/AFTER/INSTEAD OF (視圖) |
| 自定義類型/域 | 不支持 | 支持 (CREATE TYPE/DOMAIN) |
| Sequence 對象 | 不支持 (借用 AUTO_INCREMENT) | 原生支持 |
| 數據庫鏈 | Federated 引擎 (脆弱) | dblink、postgres_fdw、oracle_fdw、mysql_fdw |
| JSON 支持 | 5.7+ JSON 類型、函數、索引 | 9.2+ JSON/JSONB、GIN 索引、功能更豐富 |
| 全文搜索 | MyISAM/InnoDB 支持 | 內建 tsvector/tsquery、GIN/GiST 索引、功能更強 |
5. 索引與約束
- 索引類型： MySQL 僅 B+Tree (InnoDB/MyISAM) + Hash (Memory) + Fulltext + Spatial；PostgreSQL 支持 B-Tree、Hash、GiST、SP-GiST、GIN、BRIN，可擴展。
- 約束： 兩者均支持 PK、FK、Unique、Not Null、Check。MySQL 解析 Check 但不強制執行 (8.0.16+ 強制)；PostgreSQL 完整強制。
6. 備份、恢復與高可用
| 特性 | MySQL | PostgreSQL |
|---|
| 熱備份工具 | xtrabackup (物理)、mysqldump (邏輯) | pg_basebackup (物理)、pg_dump (邏輯)、Barman |
| 時間點恢復 (PITR) | binlog + 全量備份 | WAL 歸檔 + 基礎備份，原生支持 |
| 流式複製 | 異步/半同步/組複製 (MGR) | 流式物理複製 (異步/同步)、邏輯複製 (10+) |
| 高可用方案 | MGR、Orchestrator、MHA、GTID | Patroni、repmgr、Pacemaker、同步複製 |
| 讀寫分離/分片中間件 | MyCat、ShardingSphere、ProxySQL | PgPool-II、Citus (原生分片)、PgBouncer |
7. 許可證與生態
- MySQL： GPLv2 (社區版)，商業版由 Oracle 掌控。核心引擎 InnoDB 早被 Oracle 收購，長期路線受商業利益影響。
- PostgreSQL： BSD/MIT 許可證，極其自由，可閉源商用、二次開發無限制。社區驅動，無單一商業公司壟斷。
8. 選型建議
| 場景 | 推薦 | 理由 |
|---|
| 高併發簡單 CRUD、讀多寫少、團隊熟悉 MySQL | MySQL | 線程模型輕量、生態成熟、運維工具豐富、人才易招 |
| 複雜報表/OLAP、多表關聯、窗口函數、遞歸查詢 | PostgreSQL | 優化器強大、並行查詢、完整 SQL 標準支持 |
| GIS 地理空間、全文搜索、JSON 文檔混合負載 | PostgreSQL | PostGIS 擴展業界標準、GIN/GiST 索引、JSONB 功能豐富 |
| 需要數據庫鏈、異構數據集成 (Oracle、Mongo、MySQL) | PostgreSQL | FDW (Foreign Data Wrapper) 生態極豐富 |
| 極高連接數 (數萬)、簡單鍵值訪問 | MySQL | 線程模型天然優勢，或配合 ProxySQL |
| 嚴格數據完整性、複雜約束、自定義類型 | PostgreSQL | Check 約束強制、Domain/Type 系統、觸發器功能全 |
| 團隊願意投入學習、追求長期技術演進自由度 | PostgreSQL | BSD 許可、社區驅動、功能創新快 (如並行、JIT、邏輯複製) |
9. 遷移注意事項 (MySQL → PostgreSQL)
- 數據類型映射：
TINYINT→SMALLINT, DATETIME→TIMESTAMP, ENUM→CHECK 或 DOMAIN, AUTO_INCREMENT→SERIAL/IDENTITY.
- SQL 方言差異：
LIMIT offset, rows → LIMIT rows OFFSET offset, IFNULL→COALESCE, BACKTICK→雙引號, 隱式類型轉換更嚴格.
- 大小寫敏感： PostgreSQL 未加引號識別符自動折疊為小寫，MySQL 視系統而定。
- 工具輔助：
pgloader、ora2pg (支持 MySQL 源) 可自動化大部分遷移。
總結
MySQL 以輕量、易用、高連接數、成熟生態贏得海量中小應用；PostgreSQL 以標準合規、功能豐富、擴展性強、BSD 許可、複雜查詢性能優異，成為企業級核心系統、數據倉庫、GIS、多模應用的首選。沒有絕對的最好，只有最適合當前業務與團隊的選擇。
