postgresql和mysql的差別是什麼

區別：1、PGSQL沒有CPU核心數限制，而mysql有限制；2、PGSQL的設定檔參數一共有255個，MySQL一共有707個；3、PGSQL支援多字段統計信息，而MySQL不支援；4、PGSQL支援執行計劃即時編譯，MySQL不支援。

本教學操作環境：windows7系統、PostgreSQL 11&&MySQL5.7版本、Dell G3電腦。

PostgreSQL是一種特性非常齊全的自由軟體的物件-關聯式資料庫管理系統（ORDBMS）。

PostgreSQL支援大部分的SQL標準並且提供了許多其他現代特性，如複雜查詢、外鍵、觸發器、視圖、交易完整性、多版本並發控制等。同樣，PostgreSQL也可以用許多方法擴展，例如增加新的資料類型、函數、運算子、聚集函數、索引方法、過程語言等。另外，因為許可證的靈活，任何人都可以以任何目的免費使用、修改和分發PostgreSQL。

postgresql與mysql的區別比較

比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎） Oracle官方社群版版權狀況：PostgreSQL 11（免費開源）、MySQL5.7 Oracle官方社群版（免費開源）

1. CPU限制

PGSQL沒有CPU核心數限制，有多少CPU核就用多少

MySQL能用128核CPU，超過128核用不上

2. 設定檔參數

PGSQL一共有255個參數，用到的大概是80個，參數比較穩定，用上個大版本設定檔也可以啟動目前大版資料庫

MySQL一共有707個參數，用到的大概是180個，參數不斷增加，就算小版本也會增加參數，大版本之間會有部分參數不相容情況

3. 第三方工具依賴情況

PGSQL只有高可用叢集需要依賴第三方中介軟體，例如：patroni etcd、repmgr

MySQL大部分操作都要依賴percona公司的第三方工具（percona-toolkit，XtraBackup），工具指令太多，學習成本高，高可用叢集也需要第三方中間件，官方MGR叢集還沒成熟

4. 高可用主從複製底層原理

PGSQL物理流複製，屬於物理複製，跟SQL Server鏡像/AlwaysOn一樣，嚴格一致，沒有任何可能導致不一致，性能和可靠性上，物理複製完勝邏輯複製，維護簡單

MySQL主從複製，屬於邏輯複製，（sql_log_bin、binlog_format等參數設定不正確都會導致主從不一致）大事務並行複製效率低，對於重要業務，需要依賴percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復主從一致主從複製出錯嚴重時候需要重搭主從MySQL的邏輯複製並不阻止兩個不一致的資料庫建立複製關係

5. 從庫只讀狀態

PGSQL系統自動設定從函式庫預設只讀，不需要人工介入，維護簡單

MySQL從函式庫需要手動設定參數super_read_only=on，讓從函式庫設定為唯讀，super_read_only參數有bug，連結：https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc

#6. 版本分支

PGSQL只有社群版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社群版有所有功能，不像SQL Server和MySQL有標準版、企業版、經典版、社群版、開發版、web版之分國內外還有一些基於PGSQL做二次開發的資料庫廠商，例如：Enterprise DB、瀚高資料庫等等，當然這些只是二次開發並不算獨立分支

MySQL由於歷史原因，分割為三個分支版本，MariaDB分支、Percona分支、Oracle官方分支，發展到目前為止各個分支基本上互相不兼容Oracle官方分支還有版本之分，分為標準版、企業版、經典版、社區版

7. SQL特性支援

PGSQLSQL特性支援情況支援94種，SQL語法支援最完善，例如：支援公用表表達式（WITH查詢）

MySQLSQL特性支持情況支援36種，SQL語法支援比較弱，例如：不支援公用表表達式（WITH查詢）關於SQL特性支援情況的對比，可以參考：http://www.sql-workbench.net/dbms_comparison.html

8. 主從複製安全性

PGSQL同步流複製、強同步（remote apply)、高安全，不會失去資料PGSQL同步流複製：所有從庫宕機，主庫會罷工，主庫無法自動切換為非同步流複製（非同步模式），需要透過增加從庫數量來解決。

一般生產環境至少有兩個從庫手動解決：在PG主庫修改參數synchronous_standby_names =''，並執行指令：pgctl reload ，把主庫切換為非同步模式主從資料完全一致是高可用切換的第一前提。

所以PGSQL選擇主函式庫罷工也是可以理解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

9. 多字段統計資料

PGSQL支援多字段統計資訊

MySQL不支援多字段統計資訊

10. 索引類型

PGSQL多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）

MySQLbtree 索引，全文索引（低效），表達式索引(需要建立虛擬列)，hash 索引只在記憶體表

11. 實體表連接演算法

PGSQL支援nested-loop join 、hash join 、merge join

MySQL只支援nested-loop join

12.子查詢與檢視效能

PGSQL子查詢，視圖優化，效能比較高

MySQL視圖謂詞條件下推限制多，子查詢上拉限制多

13.執行計劃即時編譯

PGSQL支援JIT 執行計劃即時編譯，使用LLVM編譯器

MySQL不支援執行計劃即時編譯

14. 並行查詢

PGSQL並行查詢（多種平行查詢最佳化方法），並行查詢一般多見於商業資料庫，是重量級功能

MySQL有限，只支援主鍵並行查詢

15. 物化視圖

PGSQL支援物化視圖

MySQL不支援物化視圖

16. 外掛程式功能

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

MySQL不支援插件功能

17. check約束

PGSQL支援check約束

MySQL不支援check約束，可以寫check約束，但儲存引擎會忽略它的作用，因此check約束並不起作用（mariadb 支援）

18. gpu 加速SQL

PGSQL可以使用gpu 加速SQL的執行速度

##MySQL不支援gpu 加速SQL的執行速度

19. 資料型別

PGSQL資料型態豐富，如ltree，hstore，陣列類型，ip型，text類型，有了text類型不再需要varchar，text類型欄位最大儲存1GB

MySQL資料類型不夠豐富

#20. 跨庫查詢

PGSQL不支援跨庫查詢，這個跟Oracle 12C以前一樣

MySQL可以跨庫查詢

#21.備份還原

PGSQL備份還原非常簡單，時點還原作業比SQL Server還要簡單，完整備份wal歸檔備份（增量）假如有一個三節點的PGSQL主從集群，可以隨便在其中一個節點做完整備份和wal歸檔備份

MySQL備份還原相對不太簡單，完整備份binlog備份（增量）完整備份需要percona的XtraBackup工具做實體備份，MySQL本身不支援實體備份時點還原作業步驟繁瑣複雜

22. 效能視圖

PGSQL需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的效能視圖：如：等待事件，系統統計資訊等不好的地方是，安裝插件需要重新啟動資料庫，並且需要收集效能資訊的資料庫需要執行一個命令：create extension pg_stat_statements命令否則不會收集任何性能信息，比較麻煩

MySQL自帶PS庫，默認很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：記憶體佔用導致OOM bug）

23. 安裝方式

PGSQL有各個平台的包rpm包，deb包等等，比較MySQL缺少了二進制包，一般用原始碼編譯安裝，安裝時間會長一些，執行指令多一些

MySQL有各個平台的包rpm包，deb包等等，原始碼編譯安裝、二進位包安裝，一般用二進位包安裝，方便快速

24. DDL操作

PGSQL加欄位、可變長欄位類型長度改大不會鎖定表，所有的DDL操作都不需要借助第三方工具，並且跟商業資料庫一樣，DDL操作可以回滾，保證事務一致性

MySQL由於大部分DDL操作都會鎖定表，例如加字段、可變長字段類型長度改大，所以需要藉助percona-toolkit裡面的pt-online-schema-change工具去完成操作將影響減少到最低，特別是對大表進行DDL操作DDL操作不能回滾

25. 大版本發布速度#

PGSQLPGSQL每年一個大版本發布，大版發布的第二年就可以上生產環境，版本迭代速度很快PGSQL 9.6正式版推出時間：2016年PGSQL 10 正式版推出時間：2017年PGSQL 11 正式版推出時間：2018年PGSQL 12 正式版推出時間：2019年

MySQLMySQL的大版本發布一般是2年~3年，一般大版本發布後的第二年才可以上生產環境，避免有坑，版本發表速度比較慢MySQL5.5正式版推出時間：2010年MySQL5.6正式版推出時間：2013年MySQL5.7正式版推出時間：2015年MySQL8.0正式版推出時間：2018年

26. returning語法

PGSQL支援returning語法，returning clause 支援DML 回傳Resultset，減少一次Client <-> DB Server 互動

MySQL不支援returning語法

27. 內部架構

PGSQL多進程架構，並發連線數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼多最佳化方法也是相通的，例如：開啟大頁記憶體

MySQL多執行緒架構，雖然多執行緒架構，但是官方有限制連線數，原因是系統的並發度是有限的，執行緒數太多，反而係統的處理能力下降，隨著連線數上升，反而效能下降一般同時只能處理200 ~300個資料庫連線

#28. 聚集索引

PGSQL不支持聚集索引，PGSQL本身的MVCC的實作機制所導致

MySQL支援聚集索引

29.空閒事務終結功能

PGSQL透過設定idle_in_transaction_session_timeout參數來終止空閒事務，例如：應用程式碼中忘記關閉已開啟的事務，PGSQL會自動查殺這種類型的會話事務

MySQL不支援終止空閒事務功能

30. 應付超大資料量

PGSQL不能應付超大資料量，由於PGSQL本身的MVCC設計問題，需要垃圾回收，只能期待後面的大版本做最佳化

# MySQL無法應付超大資料量，MySQL自身架構的問題

31. 分散式演進

PGSQLHTAP資料庫：cockroachDB、騰訊Tbase分片叢集：Postgres-XC、 Postgres-XLMySQLHTAP資料庫：TiDB分片叢集：各種各樣的中間件，不一一列舉

32. 資料庫的檔案名稱和命名規律

#PGSQLPGSQL在這方面做的比較不好，DBA不能在作業系統層面（停庫狀態下）看清楚資料庫的檔案名稱和命名規律，檔案的數量，檔案的大小一旦作業系統發生檔案遺失或硬碟損壞，非常不利於恢復，因為連名字都不知道PGSQL表資料物理檔案的命名/存放法則是：在一個表空間下面，如果沒有建表空間預設在預設表空間也就是base資料夾下，例如：/data/base/16454 /3599base：預設表空間pg_default所在的物理資料夾16454：表所在資料庫的oid3599：就是表物件的oid，當然，一個表的大小超出1GB之後會再產生多個物理文件，還有表的fsm文件和vm文件，所以一個大表實際上會有多個實體文件由於PGSQL的資料文件佈局內容太多，大家可以查閱相關資料當然這也不能全怪PGSQL，作為一個DBA，時刻做好資料庫備份和容災才是正道，做媒體恢復一般是萬不得已的情況下才會做

MySQL數據庫名就是文件夾名，數據庫文件夾下就是表數據文件，每個表都有對應的frm文件和ibd文件，存儲元數據和表/索引數據，清晰明了，做介質恢復或表空間傳輸都很方便

#33. 權限設計

PGSQLPGSQL在權限設計這塊是比較坑爹，拋開實例權限和表空間權限，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使用mysql庫下面的5個權限表去做權限映射，簡單清晰，唯一問題是缺少權限角色user表db表host表tables_priv表columns_priv表

34. 發展歷史

PGSQL在1995年，開發人員Andrew Yu和Jolly Chen在Postgres中加入了一個SQL（Structured Query Language，結構化查詢語言）翻譯程序，該版本叫做Postgres95，在開放原始碼社群發放。在1996年，再次對Postgres95做了較大的改動，並將其命名為PostgresSQL 6.0版發布，PostgresSQL 的名字就此定型，從1995年算起，大概有24年歷史

MySQL在1996年，MySQL 1.0發布，它只面向一小撥人，相當於內部發布。到了1996年10月，MySQL 3.11.1發布(MySQL沒有2.x版本)，最開始只提供Solaris作業系統下的二進位版本，一個月後，Linux版本出現從1996年算起，大概有23年歷史

【相關推薦：mysql影片教學】

#

以上是postgresql和mysql的差別是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！