本文介紹 MySQL 和 PostgreSQL 的一些特性對比，讓大家了解二者的優(yōu)劣，更好的做出選擇。

[[333559]]

 

圖片來自 Pexels

當(dāng)前國內(nèi)的現(xiàn)狀，互聯(lián)網(wǎng)公司使用 MySQL 的較多，PostgreSQL 的使用比例反而不高，但相信看到 PG 的新特性后，你會愛上她。當(dāng)然 MySQL 作為最流行的數(shù)據(jù)庫，依然會吸引大部分人的眼球。

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

當(dāng)然 PG 功能完善和強大是最早始于 9 版本，在 10 版本快速發(fā)展，增加很多功能和特性。

PostgreSQL 是完全由社區(qū)驅(qū)動的開源項目，他的核心代碼，都是由社區(qū)維護，商用版本都是基于 PG 做的二次開發(fā)。

MySQL 聲稱自己是最流行的開源數(shù)據(jù)。看現(xiàn)在國內(nèi)的現(xiàn)狀，稱得上名副其實。MySQL 被賣幾次后，最終落到 Oracle 公司的囊中。

正是因此，MySQL 之父 Monty，修改了 MySQL 的源代碼，創(chuàng)立了 MariaDB 分支。

說到這里當(dāng)然不得不提另一個重要的分支，Percana 公司的 Percona Server。

Percona 公司更擅長 MySQL 運維，開發(fā)了很多非常實用運維工具，而且都已經(jīng)開源，并回饋給社區(qū)，像 XtraBackup 和 pt-Toolkits 工具。

簡單對比 MySQL 和 PostgreSQL 發(fā)現(xiàn)，MySQL 背后是成熟的商業(yè)公司(Oracle 有自己的 MySQL 企業(yè)版，收費，有許多社區(qū)版沒有的特性)，而 PostgreSQL 背后是一個龐大的志愿開發(fā)組。

相比而言，PostgreSQL 的商業(yè)性質(zhì)更少一些，他沒有所謂的 PostgreSQL 企業(yè)版，但是存在基于 PG 開發(fā)的一些企業(yè)級的 PG 數(shù)據(jù)庫。

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

1.開源方面

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

開源協(xié)議：PostgreSQL 基于自由的 BSD/MIT 許可，組織可以使用、復(fù)制、修改和重新分發(fā)代碼，只需要提供一個版權(quán)聲明即可。

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

MySQL：World’s Most Popular Open Source Database。

開源協(xié)議：核心代碼基于 GPL 或 Commercial License。

MySQL 的開源協(xié)議是基于 GPL 協(xié)議，任何公司都可以免費使用，不允許修改后和衍生的代碼做為閉源的商業(yè)軟件發(fā)布和銷售，MySQL 的版權(quán)在甲骨文手中，甲骨文可以推了其商業(yè)閉源版本。

 

如上圖所示，開源軟件協(xié)議

2.ACID 支持方面

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

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

3.SQL 標準的支持方面

PostgreSQL 幾乎支持所有的 SQL 標準，支持類型相當(dāng)豐富。

MySQL 只支持部分 SQL 標準，相比于 PG 支持類型稍弱。

4.復(fù)制

MySQL 的復(fù)制是基于 binlog 的邏輯異步復(fù)制，無法實現(xiàn)同步復(fù)制。

復(fù)制模式：

• 一主一備
• 一主多備
• 級聯(lián)復(fù)制
• 循環(huán)復(fù)制
• 主主復(fù)制

數(shù)據(jù)流轉(zhuǎn)優(yōu)勢：通過 Canal 增量數(shù)據(jù)的訂閱和消費，可以同步數(shù)據(jù)到 Kafka，通過 Kafka 做數(shù)據(jù)流轉(zhuǎn)。

MySQL 所有的高可用方案都是基于 binlog 做的同步，以及基于 MySQL 的分布式數(shù)據(jù)也是基于 MySQL 的 binlog 實現(xiàn)，binlog 是 MySQL 生態(tài)圈最基本技術(shù)實現(xiàn)。

PostgreSQL 可以做到同步，異步，半同步復(fù)制，以及基于日志邏輯復(fù)制，可以實現(xiàn)表級別的訂閱和發(fā)布。

復(fù)制模式：

• 一主一備
• 一主多備
• 級聯(lián)復(fù)制
• 熱備庫/流復(fù)制
• 邏輯復(fù)制

數(shù)據(jù)流轉(zhuǎn)優(yōu)勢：通過邏輯復(fù)制實現(xiàn)消息的訂閱和消費，可以同步數(shù)據(jù)到 Kafka，通過 Kafka 實現(xiàn)數(shù)據(jù)流轉(zhuǎn)。

5.并發(fā)控制

PostgreSQL 通過其 MVCC 實現(xiàn)有效地解決了并發(fā)問題，從而實現(xiàn)了非常高的并發(fā)性。

PG 新老數(shù)據(jù)一起存放的基于 XID 的 MVCC 機制，新老數(shù)據(jù)一起存放，需要定時觸發(fā) VACUUM，會帶來多余的 IO 和數(shù)據(jù)庫對象加鎖開銷，引起數(shù)據(jù)庫整體的并發(fā)能力下降。而且 VACUUM 清理不及時，還可能會引發(fā)數(shù)據(jù)膨脹。

當(dāng)然 PostgreSQL 還有一點影響比較，為了保證事務(wù)的強一致性，未決事務(wù)會影響所有表 VACUUM 清理，導(dǎo)致表膨脹。

MySQL 僅在 InnoDB 中支持 MVCC。InnoDB 的基于回滾段實現(xiàn)的 MVCC 機制，但是 MySQL 的間隙鎖影響較大，鎖定數(shù)據(jù)較多。

6.性能

PostgreSQL

①PostgreSQL 廣泛用于讀寫速度高和數(shù)據(jù)一致性高的大型系統(tǒng)。此外，它還支持各種性能優(yōu)化，當(dāng)然這些優(yōu)化僅在商業(yè)解決方案中可用，例如地理空間數(shù)據(jù)支持，沒有讀鎖定的并發(fā)性等等。

②PostgreSQL 性能最適用于需要執(zhí)行復(fù)雜查詢的系統(tǒng)。

③PostgreSQL 在 OLTP/OLAP 系統(tǒng)中表現(xiàn)良好，讀寫速度以及大數(shù)據(jù)分析方面表現(xiàn)良好，基于 PG 的 GP 數(shù)據(jù)庫，在數(shù)據(jù)倉庫領(lǐng)域表現(xiàn)良好。

④PostgreSQL 也適用于商業(yè)智能應(yīng)用程序，但更適合需要快速讀/寫速度的數(shù)據(jù)倉庫和數(shù)據(jù)分析應(yīng)用程序。

MySQL

①MySQL 是廣泛選擇的基于 Web 的項目，需要數(shù)據(jù)庫只是為了簡單的數(shù)據(jù)事務(wù)。但是，當(dāng)遇到重負載或嘗試完成復(fù)雜查詢時，MySQL 通常會表現(xiàn)不佳。

②MySQL 的讀取速度，在 OLTP 系統(tǒng)中表現(xiàn)良好。

③MySQL+InnoDB 為 OLTP 場景提供了非常好的讀/寫速度?？傮w而言，MySQL 在高并發(fā)場景下表現(xiàn)良好。

④MySQL 是可靠的，并且與商業(yè)智能應(yīng)用程序配合良好，因為商業(yè)智能應(yīng)用程序通常讀取很多。

7.高可用技術(shù)的實現(xiàn)

PostgreSQL

①基于流復(fù)制的異步、同步主從。

②基于流復(fù)制的 Keepalive。

③基于流復(fù)制的 Repmgr。

④基于流復(fù)制的 patroni+etcd。

⑤共享存儲 HA(corosync+pacemaker)。

⑥Postgres-XC。

⑦Postgres-XL。

⑧中間件實現(xiàn)：pgpool、pgcluster、slony、plploxy。

MySQL

①主從復(fù)制。

②主主復(fù)。

③MHA。

④LVS+KEEPALIVE。

⑤MGR 分布式數(shù)據(jù)庫，多點寫入[不建議]，基于 Paxos 協(xié)議。

⑥PXC 分布式數(shù)據(jù)庫，多點寫入[不建議]，基于令牌環(huán)協(xié)議。

⑦INNODB CLUSTER[8.0 新技術(shù)，基于 MGR 實現(xiàn)，上層封裝命令]，基于 Paxos 協(xié)議。

⑧中間件實現(xiàn)：Mycat。

8.外部數(shù)據(jù)源

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

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

MySQL：無。

9.數(shù)據(jù)存儲和數(shù)據(jù)類型

PG 主表采用堆表存放，存放的數(shù)據(jù)量較大，數(shù)據(jù)訪問方式類似于 Oracle 的堆表。

MySQL 采用索引組織表，MySQL 必須有主鍵索引，所有的數(shù)據(jù)訪問都是通過主鍵實現(xiàn)，二級索引訪問時，需要掃描兩遍索引(主鍵和二級索引)。

10.PostgreSQL 與 MySQL 優(yōu)劣對比

PostgreSQL 相對于 MySQL 的優(yōu)勢

①在 SQL 的標準實現(xiàn)上要比 MySQL 完善，而且功能實現(xiàn)比較嚴謹。

②存儲過程的功能支持要比 MySQL 好，具備本地緩存執(zhí)行計劃的能力。

③對表連接支持較完整，優(yōu)化器的功能較完整，支持的索引類型很多，復(fù)雜查詢能力較強。

④PG 主表采用堆表存放，MySQL 采用索引組織表，能夠支持比 MySQL 更大的數(shù)據(jù)量。

⑤PG 的主備復(fù)制屬于物理復(fù)制，相對于 MySQL 基于 binlog 的邏輯復(fù)制，數(shù)據(jù)的一致性更加可靠，復(fù)制性能更高，對主機性能的影響也更小。

⑥MySQL 的存儲引擎插件化機制，存在鎖機制復(fù)雜影響并發(fā)的問題，而 PG 不存在。

⑦PG 對可以實現(xiàn)外部數(shù)據(jù)源查詢，數(shù)據(jù)源的支持類型豐富。

⑧PG 原生的邏輯復(fù)制可以實現(xiàn)表級別的訂閱發(fā)布，可以實現(xiàn)數(shù)據(jù)通過 Kafka 流轉(zhuǎn)，而不需要其他的組件。

⑨PG 支持三種表連接方式，嵌套循環(huán)，哈希連接，排序合并，而 MySQL 只支持嵌套循環(huán)。

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

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

⑫可擴展型系統(tǒng)，有豐富可擴展組件，作為 Contribute 發(fā)布。

⑬PostgreSQL 支持 JSON 和其他 NoSQL 功能，如本機 XML 支持和使用 HSTORE 的鍵值對。

它還支持索引 JSON 數(shù)據(jù)以加快訪問速度，特別是 10 版本 JSONB 更是強大。

⑭PostgreSQL 完全免費，而且是 BSD 協(xié)議，如果你把 PostgreSQL 改一改，然后再拿去賣錢，也沒有人管你。

這一點很重要，這表明了 PostgreSQL 數(shù)據(jù)庫不會被其它公司控制。相反，MySQL 現(xiàn)在主要是被 Oracle 公司控制。

MySQL 相對于 PG 的優(yōu)勢

①InnoDB 的基于回滾段實現(xiàn)的 MVCC 機制，相對 PG 新老數(shù)據(jù)一起存放的基于 XID 的 MVCC 機制，是占優(yōu)的。

新老數(shù)據(jù)一起存放，需要定時觸發(fā) VACUUM，會帶來多余的 IO 和數(shù)據(jù)庫對象加鎖開銷，引起數(shù)據(jù)庫整體的并發(fā)能力下降。而且 VACUUM 清理不及時，還可能會引發(fā)數(shù)據(jù)膨脹。

②MySQL 采用索引組織表，這種存儲方式非常適合基于主鍵匹配的查詢、刪改操作，但是對表結(jié)構(gòu)設(shè)計存在約束。

③MySQL 的優(yōu)化器較簡單，系統(tǒng)表、運算符、數(shù)據(jù)類型的實現(xiàn)都很精簡，非常適合簡單的查詢操作。

④MySQL 相對于 PG 在國內(nèi)的流行度更高，PG 在國內(nèi)顯得就有些落寞了。

⑤MySQL 的存儲引擎插件化機制，使得它的應(yīng)用場景更加廣泛，比如除了 InnoDB 適合事務(wù)處理場景外，Myisam 適合靜態(tài)數(shù)據(jù)的查詢場景。

11.總結(jié)

總體上來說，開源數(shù)據(jù)庫都不是很完善，商業(yè)數(shù)據(jù)庫 Oracle 在架構(gòu)和功能方面都還是完善很多的。

從應(yīng)用場景來說，PG 更加適合嚴格的企業(yè)應(yīng)用場景(比如金融、電信、ERP、CRM)，但不僅僅限制于此，PostgreSQL 的 json，jsonb，hstore 等數(shù)據(jù)格式，特別適用于一些大數(shù)據(jù)格式的分析。

而 MySQL 更加適合業(yè)務(wù)邏輯相對簡單、數(shù)據(jù)可靠性要求較低的互聯(lián)網(wǎng)場景(比如 Google、Facebook、Alibaba)，當(dāng)然現(xiàn)在 MySQL 的在 InnoDB 引擎的大力發(fā)展，功能表現(xiàn)良好。

MySQL 和 PostgreSQL 復(fù)雜的開源關(guān)系型數(shù)據(jù)庫，本文只是作者根據(jù)自己經(jīng)驗寫的對 PG 和 MySQL 的理解，難免有不當(dāng)之處，不當(dāng)之處還請大家多多指正。

MySQL 在國內(nèi)的發(fā)展已然很成熟，但是如果你轉(zhuǎn)向 PostgreSQL，會發(fā)現(xiàn)不一樣的天地，學(xué)院派的風(fēng)格，豐富的功能，肯定會給你帶來不一樣的驚喜。

作者：郭慶慧

編輯：陶家龍

出處：轉(zhuǎn)載自微信公眾號東方龍馬(ID：OLM-FWH)。