PostgreSQL 和 MySQL 技術全面剖析

本文介紹 MySQL 和 PostgreSQL 的一些特性對比，讓大家瞭解二者的優劣，更好的做出選擇。當前國內的現狀，互聯網公司使用 MySQL 的較多，PostgreSQL 的使用比例反而不高，但相信看到 PG 的新特性後，你會愛上她。當然 MySQL 作爲最流行的數據庫，依然會吸引大部分人的眼球。

PostgreSQL 標榜自己是世界上最先進的開源數據庫, 甚至 PG 粉絲或者一些 PGER 宣稱，她可以和 Oracle 相媲美（雖然 PG 很強大，但是和 Oracle 還是有差距的，當然 PG 優勢也是顯而易見的），而且沒有那麼昂貴的價格和傲慢的客服。

當然 PG 功能完善和強大是最早始於 9 版本，在 10 版本快速發展，增加很多功能和特性。PostgreSQL 是完全由社區驅動的開源項目，他的核心代碼，都是由社區維護，商用版本都是基於 PG 做的二次開發。

MySQL 聲稱自己是最流行的開源數據。看現在國內的現狀，稱得上名副其實。MySQL 被賣幾次後，最終落到 Oracle 公司的囊中。正是因此，MySQL 之父 Monty，修改了 MySQL 的源代碼，創立了 MariaDB 分支。

說到這裏當然不得不提另一個重要的分支，Percana 公司的 Percona Server。Percona 公司更擅長 MySQL 運維，開發了很多非常實用運維工具，而且都已經開源，並回饋給社區，像 XtraBackup 和 pt-Toolkits 工具。

簡單對比 MySQL 和 PostgreSQL 發現，MySQL 背後是成熟的商業公司（Oracle 有自己的 MySQL 企業版，收費，有許多社區版沒有的特性），而 PostgreSQL 背後是一個龐大的志願開發組，相比而言，PostgreSQL 的商業性質更少一些，他沒有所謂的 PostgreSQL 企業版，但是存在基於 PG 開發的一些企業級的 PG 數據庫。

下面我將從以下幾個方面闡述 MySQL 和 PostgreSQL 的異同和優劣，由於筆者水平的限制，不當之處，還請大家多提意見。

一、開源方面

PostgreSQL: The world’s most advanced open source database。

**開源協議：**PostgreSQL 基於自由的 BSD/MIT 許可，組織可以使用、複製、修改和重新分發代碼，只需要提供一個版權聲明即可。

PG 的開源協議特別靈活，任何公司的和個人都可以把 PG 作爲一個產品銷售，而不需要像 MySQL 那樣必須修改大部分代碼纔可以作爲公司的產品。

**MySQL：**World’s Most Popular Open Source Database。

**開源協議：**核心代碼基於 GPL 或 Commercial License。

MySQL 的開源協議是基於 GPL 協議，任何公司都可以免費使用，不允許修改後和衍生的代碼做爲閉源的商業軟件發佈和銷售，MySQL 的版權在甲骨文手中，甲骨文可以推了其商業閉源版本。

如上圖所示，開源軟件協議

二、ACID 支持方面

PostgreSQL 支持事務的強一致性，事務保證性好，完全支持 ACID 特性。

MySQL 只有 innodb 引擎支持事務，事務一致性保證上可根據實際需求調整，爲了最大限度的保護數據，MySQL 可配置雙一模式，對 ACID 的支持上比 PG 稍弱弱。

三、SQL 標準的支持方面

PostgreSQL 幾乎支持所有的 SQL 標準，支持類型相當豐富。MySQL 只支持部分 SQL 標準，相比於 PG 支持類型稍弱。

四、複製

MySQL 的複製是基於 binlog 的邏輯異步複製，無法實現同步複製。

複製模式：

• 一主一備。

• 一主多備。

• 級聯複製。

• 循環複製。

• 主主複製。

**數據流轉優勢：**通過 canal 增量數據的訂閱和消費，可以同步數據到 kafka，通過 kafka 做數據流轉。

MySQL 所有的高可用方案都是基於 binlog 做的同步，以及基於 MySQL 的分佈式數據也是基於 MySQL 的 binlog 實現，binlog 是 MySQL 生態圈最基本技術實現。

PostgreSQL 可以做到同步，異步，半同步複製，以及基於日誌邏輯複製，可以實現表級別的訂閱和發佈。

複製模式：

• 一主一備。

• 一主多備。

• 級聯複製。

• 熱備庫 / 流複製。

• 邏輯複製。

數據流轉優勢：通過邏輯複製實現消息的訂閱和消費，可以同步數據到 kafka，通過 kafka 實現數據流轉。

五、併發控制

PostgreSQL 通過其 MVCC 實現有效地解決了併發問題，從而實現了非常高的併發性。

PG 新老數據一起存放的基於 XID 的 MVCC 機制, 新老數據一起存放，需要定時觸 發 VACUUM，會帶來多餘的 IO 和數據庫對象加鎖開銷，引起數據庫整體的併發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹。

當然 PostgreSQL 還有一點影響比較，爲了保證事務的強一致性，未決事務會影響所有表 VACUUM 清理，導致表膨脹。

MySQL 僅在 InnoDB 中支持 MVCC。innodb 的基於回滾段實現的 MVCC 機制, 但是 MySQL 的間隙鎖影響較大，鎖定數據較多。

六、性能

1、PostgreSQL

1）PostgreSQL 廣泛用於讀寫速度高和數據一致性高的大型系統。此外，它還支持各種性能優化，當然這些優化僅在商業解決方案中可用，例如地理空間數據支持，沒有讀鎖定的併發性等等。

2）PostgreSQL 性能最適用於需要執行復雜查詢的系統。

3）PostgreSQL 在 OLTP/ OLAP 系統中表現良好，讀寫速度以及大數據分析方面表現良好，基於 PG 的 GP 數據庫，在數據倉庫領域表現良好。

4）PostgreSQL 也適用於商業智能應用程序，但更適合需要快速讀 / 寫速度的數據倉庫和數據分析應用程序。

2、MySQL

1）MySQL 是廣泛選擇的基於 Web 的項目，需要數據庫只是爲了簡單的數據事務。但是，當遇到重負載或嘗試完成複雜查詢時，MySQL 通常會表現不佳。

2）MySQL 的讀取速度，在 OLTP 系統中表現良好。

3）MySQL + InnoDB 爲 OLTP 場景提供了非常好的讀 / 寫速度。總體而言，MySQL 在高併發場景下表現良好。

4）MySQL 是可靠的，並且與商業智能應用程序配合良好，因爲商業智能應用程序通常讀取很多。

七、高可用技術的實現

1、PostgreSQL

1）基於流複製的異步、同步主從。

2）基於流複製的–keepalive。

3）基於流複製的 –repmgr。

4）基於流複製的 –patroni+etcd。

5）共享存儲 HA（corosync+pacemaker）。

6）Postgres-XC。

7）Postgres-XL。

8）中間件實現：pgpool、pgcluster、slony、plploxy。

2、MySQL

1）主從複製。

2）主主復。

3）MHA。

4）LVS+KEEPALIVE。

5）MGR 分佈式數據庫，多點寫入 [不建議]，基於 paxos 協議。

6）PXC 分佈式數據庫，多點寫入 [不建議]，基於令牌環協議。

7）INNODB CLUSTER[8.0 新技術，基於 MGR 實現，上層封裝命令], 基於 paxos 協議。

8）中間件實現：mycat。

八、外部數據源

PostgreSQL FDW –[foreign-data wrapper 的一個簡稱，可以叫外部封裝。

PostgreSQL 不支持多數據引擎。但支持 Extension 組件擴充，以及通過名爲 FDW 的技術將 Oracle、Hadoop、MongoDB、SQLServer、Excel、CSV 文件等作爲外部表進行讀寫操作，因此，可以爲大數據與關係型數據庫提供良好對接。

MySQL：無。

九、數據存儲和數據類型

PG 主表採用堆表存放，存放的數據量較大，數據訪問方式類似於 Oracle 的堆表。

MySQL 採用索引組織表，MySQL 必須有主鍵索引，所有的數據訪問都是通過主鍵實現，二級索引訪問時，需要掃描兩遍索引（主鍵和二級索引）。

十、PostgreSQL 與 MySQL 優劣對比

1、PostgreSQL 相對於 MySQL 的優勢

1）在 SQL 的標準實現上要比 MySQL 完善，而且功能實現比較嚴謹。 

2）存儲過程的功能支持要比 MySQL 好，具備本地緩存執行計劃的能力。

3）對錶連接支持較完整，優化器的功能較完整，支持的索引類型很多，複雜查詢能力較強。

4）PG 主表採用堆表存放，MySQL 採用索引組織表，能夠支持比 MySQL 更大的數據量。 

5）PG 的主備複製屬於物理複製，相對於 MySQL 基於 binlog 的邏輯複製，數據的一致性更加可靠，複製性能更高，對主機性能的影響也更小。

6）MySQL 的存儲引擎插件化機制，存在鎖機制複雜影響併發的問題，而 PG 不存在。 

7）PG 對可以實現外部數據源查詢，數據源的支持類型豐富。 

8）PG 原生的邏輯複製可以實現表級別的訂閱發佈，可以實現數據通過 kafka 流轉，而不需要其他的組件。 

9）PG 支持三種表連接方式，嵌套循環，哈希連接，排序合併，而 MySQL 只支持嵌套循環。 

10）PostgreSQL 源代碼寫的很清晰，易讀性比 MySQL 強太多了。 

11）PostgreSQL 通過 PostGIS 擴展支持地理空間數據。地理空間數據有專用的類型和功能，可直接在數據庫級別使用，使開發人員更容易進行分析和編碼。 

12）可擴展型系統，有豐富可擴展組件，作爲 contribute 發佈。 

13）PostgreSQL 支持 JSON 和其他 NoSQL 功能，如本機 XML 支持和使用 HSTORE 的鍵值對。它還支持索引 JSON 數據以加快訪問速度，特別是 10 版本 JSONB 更是強大。 

14）PostgreSQL 完全免費，而且是 BSD 協議，如果你把 PostgreSQL 改一改，然後再拿去賣錢，也沒有人管你，這一點很重要，這表明了 PostgreSQL 數據庫不會被其它公司控制。相反，MySQL 現在主要是被 Oracle 公司控制。

2、MySQL 相對於 PG 的優勢

1）innodb 的基於回滾段實現的 MVCC 機制，相對 PG 新老數據一起存放的基於 XID 的 MVCC 機制，是佔優的。新老數據一起存放，需要定時觸 發 VACUUM，會帶來多餘的 IO 和數據庫對象加鎖開銷，引起數據庫整體的併發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹。

2）MySQL 採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對錶結構設計存在約束。

3）MySQL 的優化器較簡單，系統表、運算符、數據類型的實現都很精簡，非常適合簡單的查詢操作。

4）MySQL 相對於 PG 在國內的流行度更高，PG 在國內顯得就有些落寞了。

5）MySQL 的存儲引擎插件化機制，使得它的應用場景更加廣泛，比如除了 innodb 適合事務處理場景外，myisam 適合靜態數據的查詢場景。

總結

總體上來說，開源數據庫都不是很完善，商業數據庫 oracle 在架構和功能方面都還是完善很多的。從應用場景來說，PG 更加適合嚴格的企業應用場景（比如金融、電信、ERP、CRM），但不僅僅限制於此，PostgreSQL 的 json，jsonb，hstore 等數據格式，特別適用於一些大數據格式的分析；而 MySQL 更加適合業務邏輯相對簡單、數據可靠性要求較低的互聯網場景（比如 google、facebook、alibaba），當然現在 MySQL 的在 innodb 引擎的大力發展，功能表現良好。

MySQL 和 PostgreSQL 複雜的開源關係型數據庫，本文只是作者根據自己經驗寫的對 PG 和 MySQL 的理解，難免有不當之處，不當之處還請大家多多指正。

MySQL 在國內的發展已然很成熟，但是如果你轉向 PostgreSQL，會發現不一樣的天地，學院派的風格，豐富的功能，肯定會給你帶來不一樣的驚喜。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/jJfRMvYPQlaQYb1VvdJHOA