關鍵詞：PostgreSQL 11、MySQL5.7
比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎） Oracle官方社群版
版權情況：PostgreSQL 11（免費開源）、MySQL5.7 Oracle官方社群版（免費開源）
運維了MySQL和PGSQL已經有一段時間了，最近接到一個資料庫選型需求，于是便開始收集資料整理了一下，然後就有了下面的對比表
比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎） Oracle官方社群版
1. CPU限制
PGSQL
沒有CPU核心數限制，有多少CPU核就用多少
MySQL
能用128核CPU，超過128核用不上
2. 配置檔案參數
一共有255個參數，用到的大概是80個，參數比較穩定，用上個大版本配置檔案也可以啟動目前大版本資料庫
一共有707個參數，用到的大概是180個，參數不斷增加，就算小版本也會增加參數，大版本之間會有部分參數不相容情況
3. 第三方工具依賴情況
隻有高可用叢集需要依靠第三方中間件，例如：patroni+etcd、repmgr
大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具指令太多，學習成本高，高可用叢集也需要第三方中間件，官方MGR叢集還沒成熟
4. 高可用主從複制底層原理
實體流複制，屬于實體複制，跟SQL Server鏡像/AlwaysOn一樣，嚴格一緻，沒有任何可能導緻不一緻，性能和可靠性上，實體複制完勝邏輯複制，維護簡單
主從複制，屬于邏輯複制，（sql_log_bin、binlog_format等參數設定不正确都會導緻主從不一緻）
大事務并行複制效率低，對于重要業務，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修複主從一緻
主從複制出錯嚴重時候需要重搭主從
MySQL的邏輯複制并不阻止兩個不一緻的資料庫建立複制關系
5. 從庫隻讀狀态
系統自動設定從庫預設隻讀，不需要人工介入，維護簡單
從庫需要手動設定參數super_read_only=on，讓從庫設定為隻讀，super_read_only參數有bug，連結：https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc
6. 版本分支
隻有社群版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社群版有所有功能，不像SQL Server和MySQL有标準版、企業版、經典版、社群版、開發版、web版之分
國内外還有一些基于PGSQL做二次開發的資料庫廠商，例如：Enterprise DB、瀚高資料庫等等，當然這些隻是二次開發并不算獨立分支
由于曆史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發展到目前為止各個分支基本互相不相容
Oracle官方分支還有版本之分，分為标準版、企業版、經典版、社群版
7. SQL特性支援
SQL特性支援情況支援94種，SQL文法支援最完善，例如：支援公用表表達式（WITH查詢）
SQL特性支援情況支援36種，SQL文法支援比較弱，例如：不支援公用表表達式（WITH查詢）
關于SQL特性支援情況的對比，可以參考：http://www.sql-workbench.net/dbms_comparison.html
8. 主從複制安全性
同步流複制、強同步（remote apply)、高安全，不會丢資料
PGSQL同步流複制：所有從庫當機，主庫會罷工，主庫無法自動切換為異步流複制（異步模式），需要通過增加從庫數量來解決，一般生産環境至少有兩個從庫
手動解決：在PG主庫修改參數synchronous_standby_names =''，并執行指令： pgctl reload ，把主庫切換為異步模式
主從資料完全一緻是高可用切換的第一前提，是以PGSQL選擇主庫罷工也是可以了解
增強半同步複制 ，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
9. 多字段統計資訊
支援多字段統計資訊
不支援多字段統計資訊
10. 索引類型
多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）
btree 索引，全文索引（低效），表達式索引(需要建虛拟列)，hash 索引隻在記憶體表
11. 實體表連接配接算法
支援 nested-loop join 、hash join 、merge join
隻支援 nested-loop join
12. 子查詢和視圖性能
子查詢，視圖優化，性能比較高
視圖謂詞條件下推限制多，子查詢上拉限制多
13. 執行計劃即時編譯
支援 JIT 執行計劃即時編譯，使用LLVM編譯器
不支援執行計劃即時編譯
14. 并行查詢
并行查詢（多種并行查詢優化方法），并行查詢一般多見于商業資料庫，是重量級功能
有限，隻支援主鍵并行查詢
15. 物化視圖
支援物化視圖
不支援物化視圖
16. 插件功能
支援插件功能，可以豐富PGSQL的功能，GIS地理插件，時序資料庫插件， 向量化執行插件等等
不支援插件功能
17. check限制
支援check限制
不支援check限制，可以寫check限制，但存儲引擎會忽略它的作用，是以check限制并不起作用（mariadb 支援）
18. gpu 加速SQL
可以使用gpu 加速SQL的執行速度
不支援gpu 加速SQL 的執行速度
19. 資料類型
資料類型豐富，如 ltree，hstore，數組類型，ip類型，text類型，有了text類型不再需要varchar，text類型字段最大存儲1GB
資料類型不夠豐富
20. 跨庫查詢
不支援跨庫查詢，這個跟Oracle 12C以前一樣
可以跨庫查詢
21. 備份還原
備份還原非常簡單，時點還原操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）
假如有一個三節點的PGSQL主從叢集，可以随便在其中一個節點做完整備份和wal歸檔備份
備份還原相對不太簡單，完整備份+binlog備份（增量）
完整備份需要percona的XtraBackup工具做實體備份，MySQL本身不支援實體備份
時點還原操作步驟繁瑣複雜
22. 性能視圖
需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統統計資訊等
不好的地方是，安裝插件需要重新開機資料庫，并且需要收集性能資訊的資料庫需要執行一個指令：create extension pg_stat_statements指令
否則不會收集任何性能資訊，比較麻煩
自帶PS庫，預設很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：記憶體占用導緻OOM bug）
23. 安裝方式
有各個平台的包rpm包，deb包等等，相比MySQL缺少了二進制包，一般用源碼編譯安裝，安裝時間會長一些，執行指令多一些
有各個平台的包rpm包，deb包等等，源碼編譯安裝、二進制包安裝，一般用二進制包安裝，友善快捷
24. DDL操作
加字段、可變長字段類型長度改大不會鎖表，所有的DDL操作都不需要借助第三方工具，并且跟商業資料庫一樣，DDL操作可以復原，保證事務一緻性
由于大部分DDL操作都會鎖表，例如加字段、可變長字段類型長度改大，是以需要借助percona-toolkit裡面的pt-online-schema-change工具去完成操作
将影響減少到最低，特别是對大表進行DDL操作
DDL操作不能復原
25. 大版本釋出速度
PGSQL每年一個大版本釋出，大版本釋出的第二年就可以上生産環境，版本疊代速度很快
PGSQL 9.6正式版推出時間：2016年
PGSQL 10 正式版推出時間：2017年
PGSQL 11 正式版推出時間：2018年
PGSQL 12 正式版推出時間：2019年
MySQL的大版本釋出一般是2年~3年，一般大版本釋出後的第二年才可以上生産環境，避免有坑，版本釋出速度比較慢
MySQL5.5正式版推出時間：2010年
MySQL5.6正式版推出時間：2013年
MySQL5.7正式版推出時間：2015年
MySQL8.0正式版推出時間：2018年
26. returning文法
支援returning文法，returning clause 支援 DML 傳回 Resultset，減少一次 Client <-> DB Server 互動
不支援returning文法
27. 内部架構
多程序架構，并發連接配接數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼很多優化方法也是相通的，例如：開啟大頁記憶體
多線程架構，雖然多線程架構，但是官方有限制連接配接數，原因是系統的并發度是有限的，線程數太多，反而系統的處理能力下降，随着連接配接數上升，反而性能下降
一般同時隻能處理200 ~300個資料庫連接配接
28. 聚集索引
不支援聚集索引，PGSQL本身的MVCC的實作機制所導緻
支援聚集索引
29. 空閑事務終結功能
通過設定 idle_in_transaction_session_timeout 參數來終止空閑事務，比如：應用代碼中忘記關閉已開啟的事務，PGSQL會自動清除這種類型的會話事務
不支援終止空閑事務功能
30. 應付超大資料量
不能應付超大資料量，由于PGSQL本身的MVCC設計問題，需要垃圾回收，隻能期待後面的大版本做優化
不能應付超大資料量，MySQL自身架構的問題
31. 分布式演進
HTAP資料庫：cockroachDB、騰訊Tbase
分片叢集： Postgres-XC、Postgres-XL
HTAP資料庫：TiDB
分片叢集： 各種各樣的中間件，不一一列舉
32. 資料庫的檔案名和命名規律
PGSQL在這方面做的比較不好，DBA不能在作業系統層面（停庫狀态下）看清楚資料庫的檔案名和命名規律，檔案的數量，檔案的大小
一旦作業系統發生檔案丢失或硬碟損壞，非常不利于恢複，因為連名字都不知道
PGSQL表資料實體檔案的命名/存放規律是： 在一個表空間下面，如果沒有建表空間預設在預設表空間也就是base檔案夾下，例如：/data/base/16454/3599
base：預設表空間pg_default所在的實體檔案夾
16454：表所在資料庫的oid
3599：就是表對象的oid，當然，一個表的大小超出1GB之後會再生成多個實體檔案，還有表的fsm檔案和vm檔案，是以一個大表實際會有多個實體檔案
由于PGSQL的資料檔案布局内容太多，大家可以查閱相關資料
當然這也不能全怪PGSQL，作為一個DBA，時刻做好資料庫備份和容災才是正道，做媒體恢複一般是萬不得已的情況下才會做
資料庫名就是檔案夾名，資料庫檔案夾下就是表資料檔案，但是要注意表名和資料庫名不能有特殊字元或使用中文名，每個表都有對應的frm檔案和ibd檔案，存儲中繼資料和表/索引資料，清晰明了，做媒體恢複或者表空間傳輸都很友善
33. 權限設計
PGSQL在權限設計這塊是比較坑爹，抛開執行個體權限和表空間權限，PGSQL的權限層次有點像SQL Server，db=》schema=》object
要說權限，這裡要說一下Oracle，用Oracle來類比
在ORACLE 12C之前，執行個體與資料庫是一對一，也就是說一個執行個體隻能有一個資料庫，不像MySQL和SQL Server一個執行個體可以有多個資料庫，并且可以随意跨庫查詢
而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個資料庫，跟ORACLE類比就是有多個執行個體（之前說的執行個體與資料庫是一對一）
一個資料庫相當于一個執行個體，因為PGSQL允許有多個執行個體，是以PGSQL單執行個體不叫一個執行個體，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關資料
PGSQL裡面一個執行個體/資料庫下面的schema相當于資料庫，是以這個schema的概念對應MySQL的database
注意點：正因為是一個資料庫相當于一個執行個體，PGSQL允許有多個執行個體/資料庫，是以資料庫之間是互相邏輯隔離的，導緻的問題是，不能一次對一個PGSQL集簇下面的所有資料庫做操作
必須要逐個逐個資料庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有資料庫都做性能收集的話，需要逐個資料庫去執行加載指令
又例如跨庫查詢需要dblink插件或fdw插件，兩個資料庫之間做查詢相當于兩個執行個體之間做查詢，已經跨越了執行個體了，是以需要dblink插件或fdw插件，是以道理非常簡單
權限操作也是一樣逐個資料庫去操作，還有一個就是PGSQL雖然像SQL Server的權限層次結構db=》schema=》object，但是實際會比SQL Server要複雜一些，還有就是建立的表還要另外授權
在PGSQL裡面，角色和使用者是一樣的，對新手使用者來說有時候會傻傻分不清，也不知道怎麼去用角色，是以PGSQL在權限設計這一塊确實比較坑爹
使用mysql庫下面的5個權限表去做權限映射，簡單清晰，唯一問題是缺少權限角色
user表
db表
host表
tables_priv表
columns_priv表
34. 發展曆史
在1995年，開發人員Andrew Yu和Jolly Chen在Postgres中添加了一個SQL（Structured Query Language，結構化查詢語言）翻譯程式，該版本叫做Postgres95，在開放源代碼社群發放。
在1996年，再次對Postgres95做了較大的改動，并将其命名為PostgresSQL 6.0版釋出，PostgresSQL 的名字就此定型，從1995年算起，大概有24年曆史
在1996年，MySQL 1.0釋出，它隻面向一小撥人，相當于内部釋出。
到了1996年10月，MySQL 3.11.1釋出(MySQL沒有2.x版本)，最開始隻提供Solaris作業系統下的二進制版本，一個月後，Linux版本出現
從1996年算起，大概有23年曆史
上面的對比表還不是很完善，隻有一些本人認為比較關鍵的特性拿出來對比
總的來說，兩種資料庫都有優缺點，大家在選型的時候需要謹慎選擇，MySQL需要多折騰，PGSQL讓你少折騰，因為PGSQL本身已經做的比較完善，不太需要依賴一些第三方工具
當然，如果在MySQL上選擇Percona 分支，MariaDB分支，或者Oracle官方的MySQL企業版就另當别論
MySQL因為需要支援更換存儲引擎，是以某些功能都要受制于存儲引擎層，例如：實體複制
而PGSQL不支援更換存儲引擎（在PGSQL V12開始也支援可插撥的表存取接口），而且一直由官方統一開發和維護，是以相對比較穩定，功能也比較完善，對得上它的稱号：《世界上功能最為強大的開源資料庫》
PGSQL V12 支援可插撥的表存取接口之後，有可能由第三方存儲引擎來改進PGSQL本身的MVCC實作機制，而不需要等待官方去解決，聚集索引、undo表空間這些都不再是問題
如果業務複雜的話，其實PGSQL是最佳選擇，分享一篇文章：為什麼“去O”唯有PG
如有不對的地方，歡迎大家拍磚o(∩_∩)o
本文版權歸作者所有，未經作者同意不得轉載。
![Nacos 2.0 更新前後性能對比壓測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)