淺談PostgreSQL和MySQL的差別，如何選擇？

為了弄明白PostgreSQL和MySQL的差别，我搜尋了關鍵字：MySQL vs PostgreSQL，并看了第一頁的幾個文章。以下是簡單總結：

比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎）

搜尋量

首先根據百度搜尋量可以看出，近一年内MySQL的平均搜尋量是pg的4倍多，給人的第一感覺就是MySQL的使用量更高一點

搜尋量

協定

協定上，pg的協定更加寬容，不要求基于pg開發的軟體也必須開源，MySQL的GPL協定要求使用它開發的軟體也必須開源，否則就需要付費。雖然這看起來PG對各類型的使用者都更友好，但這一點也是缺點，由于使用PG可以不開源，是以如果基于pg開發的軟體最後都閉環發展，就會導緻好的技術不會吸收到主幹道上，而MySQL就沒有這一弊端，長遠來看，MySQL的生命力就會越來越強。

版本分支

1. PGSQL隻有社群版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社群版有所有功能，不像SQL Server和MySQL有标準版、企業版、經典版、社群版、開發版、web版之分國内外還有一些基于PGSQL做二次開發的資料庫廠商，例如：Enterprise DB、瀚高資料庫等等，當然這些隻是二次開發并不算獨立分支
2. MySQL由于曆史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發展到目前為止各個分支基本互相不相容Oracle官方分支還有版本之分，分為标準版、企業版、經典版、社群版

安裝方式

1. PGSQL有各個平台的包rpm包，deb包等等，相比MySQL缺少了二進制包，一般用源碼編譯安裝，安裝時間會長一些，執行指令多一些
2. MySQL有各個平台的包rpm包，deb包等等，源碼編譯安裝、二進制包安裝，一般用二進制包安裝，友善快捷

架構

1. MySQL是多線程。雖然多線程架構，但是官方有限制連接配接數，原因是系統的并發度是有限的，線程數太多，反而系統的處理能力下降，随着連接配接數上升，反而性能下降一般同時隻能處理200 ~300個資料庫連接配接
2. PG是多程序。并發連接配接數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼很多優化方法也是相通的，例如：開啟大頁記憶體
3. 多線程架構和多程序架構之間沒有絕對的好壞，例如oracle在unix上是多程序架構，在windows上是多線程架構。

對存儲過程及事務的支援能力

1. MySQL對于無事務的MyISAM表，采用表鎖定，一個長時間運作的查詢很可能會長時間地阻礙對表的更新，而PostgreSQL不存在這樣的問題。
2. PostgreSQL支援存儲過程，要比MySQL好，具備本地緩存執行計劃的能力；
3. MySQL 4.0.2-alpha開始支援事務的概念，保留無事務的表類型， 為使用者提供了更多的選擇。

穩定性及性能

1. 高并發讀寫，負載逼近極限下，PG的性能名額高于mysql
2. mysql的innodb引擎可以優化利用系統所有記憶體，超大記憶體下PG對記憶體的使用不那麼充分。
3. PostgreSQL 的穩定性極強， Innodb 等引擎在崩潰、斷電之類的災難場景下抗打擊能力有了長足進步，然而很多 MySQL 使用者都遇到過Server級的資料庫丢失的場景——mysql系統庫是MyISAM的，相比之下，PG資料庫這方面要好一些。

資料同步方式

1. mysql到現在也是異步複制，pgsql可以做到同步，異步，半同步複制。
2. mysql的同步是基于binlog複制，類似oracle golden gate,是基于stream的複制，做到同步很困難，這種方式更加适合異地複制；pgsql的複制基于wal，可以做到同步複制。同時，pgsql還提供stream複制。
3. MySQL的複制可以用多級從庫，但是在9.2之前，PGSQL不能用從庫帶從庫。
4. PG的主備複制屬于實體複制，相對于MySQL基于binlog的邏輯複制，資料的一緻性更加可靠，複制性能更高，對主機性能的影響也更小。

對SQL語句的支援

1. PG在SQL的标準實作上要比MySQL完善，而且功能實作比較嚴謹；
2. PG對表連接配接支援較完整，優化器的功能較完整，支援的索引類型很多，複雜查詢能力較強
3. MySQL的Join操作的性能非常的差，隻支援Nest Join，是以一旦資料量大，性能就非常的差。PostgreSQL除了支援nest join外，還支援hash join和 sort merge join；PostgreSQL支援正規表達式查找，MySQL不支援

資料類型方面

1. PGSQL資料類型豐富，如 ltree，hstore，數組類型，ip類型，text類型，有了text類型不再需要varchar，text類型字段最大存儲1GB
2. MySQL資料類型不夠豐富

索引類型

1. PGSQL多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）
2. MySQLbtree 索引，全文索引（低效），表達式索引(需要建虛拟列)，hash 索引隻在記憶體表

主從複制安全性

1. PGSQL同步流複制、強同步（remote apply)、高安全，不會丢資料PGSQL同步流複制：所有從庫當機，主庫會罷工，主庫無法自動切換為異步流複制（異步模式），需要通過增加從庫數量來解決，一般生産環境至少有兩個從庫手動解決：在PG主庫修改參數synchronous_standby_names =''，并執行指令：pgctl reload ，把主庫切換為異步模式主從資料完全一緻是高可用切換的第一前提，是以PGSQL選擇主庫罷工也是可以了解。
2. MySQL增強半同步複制 ，mysql5.7版本增強半同步才能保證主從複制時候不丢資料mysql5.7半同步複制相關參數：參數rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才送出事務，一般設定為1，性能最高參數rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應自動切換為異步模式，一般設定為無限大，不讓主庫自動切換為異步模式所有從庫當機，主庫會罷工，因為無法收到任何從庫的應答包手動解決：在MySQL主庫修改參數rpl_semi_sync_master_wait_for_slave_count=0

備份恢複

1. PGSQL備份恢複非常簡單，時點恢複操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）假如有一個三節點的PGSQL主從叢集，可以随便在其中一個節點做完整備份和wal歸檔備份
2. MySQL備份恢複相對不太簡單，完整備份+binlog備份（增量）完整備份需要percona的XtraBackup工具做實體備份，MySQL本身不支援實體備份時點恢複操作步驟繁瑣複雜。

插件功能

1. PGSQL支援插件功能，可以豐富PGSQL的功能，GIS地理插件，時序資料庫插件， 向量化執行插件等等
2. MySQL不支援插件功能

總結

PG和MySQL的差別仍然還有很多沒有列出，簡單點總結，pg更重視功能，擴充性強，可閉環發展，是以在政府、金融、銀行用的比較多。MySQL重在速度，速度高于pg，使用更加簡單便捷，是以它在網際網路上使用的比較多，建議在選擇這兩種資料庫時，考慮不要隻停留在資料庫層面，還需要考慮到團隊的技術棧、應用的規模和複雜性、預期的使用者負載等。