postgresql和mysql的區(qū)別有哪些

這篇文章主要為大家展示了“postgresql和mysql的區(qū)別有哪些”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“postgresql和mysql的區(qū)別有哪些”這篇文章吧。

區(qū)別：1、PGSQL沒有CPU核心數(shù)限制，而mysql有限制；2、PGSQL的配置文件參數(shù)一共有255個，MySQL一共有707個；3、PGSQL支持多字段統(tǒng)計(jì)信息，而MySQL不支持；4、PGSQL支持執(zhí)行計(jì)劃即時編譯，MySQL不支持。

本教程操作環(huán)境：windows7系統(tǒng)、PostgreSQL 11&&MySQL5.7版本、Dell G3電腦。

PostgreSQL是一種特性非常齊全的自由軟件的對象-關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（ORDBMS）。

PostgreSQL支持大部分的SQL標(biāo)準(zhǔn)并且提供了很多其他現(xiàn)代特性，如復(fù)雜查詢、外鍵、觸發(fā)器、視圖、事務(wù)完整性、多版本并發(fā)控制等。同樣，PostgreSQL也可以用許多方法擴(kuò)展，例如通過增加新的數(shù)據(jù)類型、函數(shù)、操作符、聚集函數(shù)、索引方法、過程語言等。另外，因?yàn)樵S可證的靈活，任何人都可以以任何目的免費(fèi)使用、修改和分發(fā)PostgreSQL。

postgresql和mysql的區(qū)別對比

比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎） Oracle官方社區(qū)版版權(quán)情況：PostgreSQL 11（免費(fèi)開源）、MySQL5.7 Oracle官方社區(qū)版（免費(fèi)開源）

1. CPU限制

PGSQL沒有CPU核心數(shù)限制，有多少CPU核就用多少

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

2. 配置文件參數(shù)

PGSQL一共有255個參數(shù)，用到的大概是80個，參數(shù)比較穩(wěn)定，用上個大版本配置文件也可以啟動當(dāng)前大版本數(shù)據(jù)庫

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

3. 第三方工具依賴情況

PGSQL只有高可用集群需要依靠第三方中間件，例如：patroni+etcd、repmgr

MySQL大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，學(xué)習(xí)成本高，高可用集群也需要第三方中間件，官方MGR集群還沒成熟

4. 高可用主從復(fù)制底層原理

PGSQL物理流復(fù)制，屬于物理復(fù)制，跟SQL Server鏡像/AlwaysOn一樣，嚴(yán)格一致，沒有任何可能導(dǎo)致不一致，性能和可靠性上，物理復(fù)制完勝邏輯復(fù)制，維護(hù)簡單

MySQL主從復(fù)制，屬于邏輯復(fù)制，（sql_log_bin、binlog_format等參數(shù)設(shè)置不正確都會導(dǎo)致主從不一致）大事務(wù)并行復(fù)制效率低，對于重要業(yè)務(wù)，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復(fù)主從一致主從復(fù)制出錯嚴(yán)重時候需要重搭主從MySQL的邏輯復(fù)制并不阻止兩個不一致的數(shù)據(jù)庫建立復(fù)制關(guān)系

5. 從庫只讀狀態(tài)

PGSQL系統(tǒng)自動設(shè)置從庫默認(rèn)只讀，不需要人工介入，維護(hù)簡單

MySQL從庫需要手動設(shè)置參數(shù)super_read_only=on，讓從庫設(shè)置為只讀，super_read_only參數(shù)有bug，鏈接：https://baijiahao.baidu.com/s?id=1636644783594388753&wfr=spider&for=pc

6. 版本分支

PGSQL只有社區(qū)版，沒有其他任何分支版本，PGSQL官方統(tǒng)一開發(fā)，統(tǒng)一維護(hù)，社區(qū)版有所有功能，不像SQL Server和MySQL有標(biāo)準(zhǔn)版、企業(yè)版、經(jīng)典版、社區(qū)版、開發(fā)版、web版之分國內(nèi)外還有一些基于PGSQL做二次開發(fā)的數(shù)據(jù)庫廠商，例如：Enterprise DB、瀚高數(shù)據(jù)庫等等，當(dāng)然這些只是二次開發(fā)并不算獨(dú)立分支

MySQL由于歷史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發(fā)展到目前為止各個分支基本互相不兼容Oracle官方分支還有版本之分，分為標(biāo)準(zhǔn)版、企業(yè)版、經(jīng)典版、社區(qū)版

7. SQL特性支持

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

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

8. 主從復(fù)制安全性

PGSQL同步流復(fù)制、強(qiáng)同步（remote apply)、高安全，不會丟數(shù)據(jù)PGSQL同步流復(fù)制：所有從庫宕機(jī)，主庫會罷工，主庫無法自動切換為異步流復(fù)制（異步模式），需要通過增加從庫數(shù)量來解決。

一般生產(chǎn)環(huán)境至少有兩個從庫手動解決：在PG主庫修改參數(shù)synchronous_standby_names =''，并執(zhí)行命令：pgctl reload ，把主庫切換為異步模式主從數(shù)據(jù)完全一致是高可用切換的第一前提。

所以PGSQL選擇主庫罷工也是可以理解MySQL增強(qiáng)半同步復(fù)制

mysql5.7版本增強(qiáng)半同步才能保證主從復(fù)制時候不丟數(shù)據(jù)mysql5.7半同步復(fù)制相關(guān)參數(shù)：參數(shù)rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才提交事務(wù)

一般設(shè)置為1，性能最高參數(shù)rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應(yīng)自動切換為異步模式

一般設(shè)置為無限大，不讓主庫自動切換為異步模式所有從庫宕機(jī)，主庫會罷工

因?yàn)闊o法收到任何從庫的應(yīng)答包手動解決：在MySQL主庫修改參數(shù)rpl_semi_sync_master_wait_for_slave_count=0

9. 多字段統(tǒng)計(jì)信息

PGSQL支持多字段統(tǒng)計(jì)信息

MySQL不支持多字段統(tǒng)計(jì)信息

10. 索引類型

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

MySQLbtree 索引，全文索引（低效），表達(dá)式索引(需要建虛擬列)，hash 索引只在內(nèi)存表

11. 物理表連接算法

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

MySQL只支持 nested-loop join

12. 子查詢和視圖性能

PGSQL子查詢，視圖優(yōu)化，性能比較高

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

13. 執(zhí)行計(jì)劃即時編譯

PGSQL支持 JIT 執(zhí)行計(jì)劃即時編譯，使用LLVM編譯器

MySQL不支持執(zhí)行計(jì)劃即時編譯

14. 并行查詢

PGSQL并行查詢（多種并行查詢優(yōu)化方法），并行查詢一般多見于商業(yè)數(shù)據(jù)庫，是重量級功能

MySQL有限，只支持主鍵并行查詢

15. 物化視圖

PGSQL支持物化視圖

MySQL不支持物化視圖

16. 插件功能

PGSQL支持插件功能，可以豐富PGSQL的功能，GIS地理插件，時序數(shù)據(jù)庫插件， 向量化執(zhí)行插件等等

MySQL不支持插件功能

17. check約束

PGSQL支持check約束

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

18. gpu 加速SQL

PGSQL可以使用gpu 加速SQL的執(zhí)行速度

MySQL不支持gpu 加速SQL 的執(zhí)行速度

19. 數(shù)據(jù)類型

PGSQL數(shù)據(jù)類型豐富，如 ltree，hstore，數(shù)組類型，ip類型，text類型，有了text類型不再需要varchar，text類型字段最大存儲1GB

MySQL數(shù)據(jù)類型不夠豐富

20. 跨庫查詢

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

MySQL可以跨庫查詢

21. 備份還原

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

MySQL備份還原相對不太簡單，完整備份+binlog備份（增量）完整備份需要percona的XtraBackup工具做物理備份，MySQL本身不支持物理備份時點(diǎn)還原操作步驟繁瑣復(fù)雜

22. 性能視圖

PGSQL需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統(tǒng)統(tǒng)計(jì)信息等不好的地方是，安裝插件需要重啟數(shù)據(jù)庫，并且需要收集性能信息的數(shù)據(jù)庫需要執(zhí)行一個命令：create extension pg_stat_statements命令否則不會收集任何性能信息，比較麻煩

MySQL自帶PS庫，默認(rèn)很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：內(nèi)存占用導(dǎo)致OOM bug）

23. 安裝方式

PGSQL有各個平臺的包rpm包，deb包等等，相比MySQL缺少了二進(jìn)制包，一般用源碼編譯安裝，安裝時間會長一些，執(zhí)行命令多一些

MySQL有各個平臺的包rpm包，deb包等等，源碼編譯安裝、二進(jìn)制包安裝，一般用二進(jìn)制包安裝，方便快捷

24. DDL操作

PGSQL加字段、可變長字段類型長度改大不會鎖表，所有的DDL操作都不需要借助第三方工具，并且跟商業(yè)數(shù)據(jù)庫一樣，DDL操作可以回滾，保證事務(wù)一致性

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

25. 大版本發(fā)布速度

PGSQLPGSQL每年一個大版本發(fā)布，大版本發(fā)布的第二年就可以上生產(chǎn)環(huán)境，版本迭代速度很快PGSQL 9.6正式版推出時間：2016年P(guān)GSQL 10 正式版推出時間：2017年P(guān)GSQL 11 正式版推出時間：2018年P(guān)GSQL 12 正式版推出時間：2019年

MySQLMySQL的大版本發(fā)布一般是2年~3年，一般大版本發(fā)布后的第二年才可以上生產(chǎn)環(huán)境，避免有坑，版本發(fā)布速度比較慢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. 內(nèi)部架構(gòu)

PGSQL多進(jìn)程架構(gòu)，并發(fā)連接數(shù)不能太多，跟Oracle一樣，既然跟Oracle一樣，那么很多優(yōu)化方法也是相通的，例如：開啟大頁內(nèi)存

MySQL多線程架構(gòu)，雖然多線程架構(gòu)，但是官方有限制連接數(shù)，原因是系統(tǒng)的并發(fā)度是有限的，線程數(shù)太多，反而系統(tǒng)的處理能力下降，隨著連接數(shù)上升，反而性能下降一般同時只能處理200 ~300個數(shù)據(jù)庫連接

28. 聚集索引

PGSQL不支持聚集索引，PGSQL本身的MVCC的實(shí)現(xiàn)機(jī)制所導(dǎo)致

MySQL支持聚集索引

29. 空閑事務(wù)終結(jié)功能

PGSQL通過設(shè)置 idle_in_transaction_session_timeout 參數(shù)來終止空閑事務(wù)，比如：應(yīng)用代碼中忘記關(guān)閉已開啟的事務(wù)，PGSQL會自動查殺這種類型的會話事務(wù)

MySQL不支持終止空閑事務(wù)功能

30. 應(yīng)付超大數(shù)據(jù)量

PGSQL不能應(yīng)付超大數(shù)據(jù)量，由于PGSQL本身的MVCC設(shè)計(jì)問題，需要垃圾回收，只能期待后面的大版本做優(yōu)化

MySQL不能應(yīng)付超大數(shù)據(jù)量，MySQL自身架構(gòu)的問題

31. 分布式演進(jìn)

PGSQLHTAP數(shù)據(jù)庫：cockroachDB、騰訊Tbase分片集群：Postgres-XC、Postgres-XLMySQLHTAP數(shù)據(jù)庫：TiDB分片集群：各種各樣的中間件，不一一列舉

32. 數(shù)據(jù)庫的文件名和命名規(guī)律

PGSQLPGSQL在這方面做的比較不好，DBA不能在操作系統(tǒng)層面（停庫狀態(tài)下）看清楚數(shù)據(jù)庫的文件名和命名規(guī)律，文件的數(shù)量，文件的大小一旦操作系統(tǒng)發(fā)生文件丟失或硬盤損壞，非常不利于恢復(fù)，因?yàn)檫B名字都不知道PGSQL表數(shù)據(jù)物理文件的命名/存放規(guī)律是：在一個表空間下面，如果沒有建表空間默認(rèn)在默認(rèn)表空間也就是base文件夾下，例如：/data/base/16454/3599base：默認(rèn)表空間pg_default所在的物理文件夾16454：表所在數(shù)據(jù)庫的oid3599：就是表對象的oid，當(dāng)然，一個表的大小超出1GB之后會再生成多個物理文件，還有表的fsm文件和vm文件，所以一個大表實(shí)際會有多個物理文件由于PGSQL的數(shù)據(jù)文件布局內(nèi)容太多，大家可以查閱相關(guān)資料當(dāng)然這也不能全怪PGSQL，作為一個DBA，時刻做好數(shù)據(jù)庫備份和容災(zāi)才是正道，做介質(zhì)恢復(fù)一般是萬不得已的情況下才會做

MySQL數(shù)據(jù)庫名就是文件夾名，數(shù)據(jù)庫文件夾下就是表數(shù)據(jù)文件，每個表都有對應(yīng)的frm文件和ibd文件，存儲元數(shù)據(jù)和表/索引數(shù)據(jù)，清晰明了，做介質(zhì)恢復(fù)或者表空間傳輸都很方便

33. 權(quán)限設(shè)計(jì)

PGSQLPGSQL在權(quán)限設(shè)計(jì)這塊是比較坑爹，拋開實(shí)例權(quán)限和表空間權(quán)限，PGSQL的權(quán)限層次有點(diǎn)像SQL Server，db=》schema=》object要說權(quán)限，這里要說一下Oracle，用Oracle來類比在ORACLE 12C之前，實(shí)例與數(shù)據(jù)庫是一對一，也就是說一個實(shí)例只能有一個數(shù)據(jù)庫，不像MySQL和SQL Server一個實(shí)例可以有多個數(shù)據(jù)庫，并且可以隨意跨庫查詢而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個數(shù)據(jù)庫，跟ORACLE類比就是有多個實(shí)例（之前說的實(shí)例與數(shù)據(jù)庫是一對一）一個數(shù)據(jù)庫相當(dāng)于一個實(shí)例，因?yàn)镻GSQL允許有多個實(shí)例，所以PGSQL單實(shí)例不叫一個實(shí)例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關(guān)資料PGSQL里面一個實(shí)例/數(shù)據(jù)庫下面的schema相當(dāng)于數(shù)據(jù)庫，所以這個schema的概念對應(yīng)MySQL的database注意點(diǎn)：正因?yàn)槭且粋€數(shù)據(jù)庫相當(dāng)于一個實(shí)例，PGSQL允許有多個實(shí)例/數(shù)據(jù)庫，所以數(shù)據(jù)庫之間是互相邏輯隔離的，導(dǎo)致的問題是，不能一次對一個PGSQL集簇下面的所有數(shù)據(jù)庫做操作必須要逐個逐個數(shù)據(jù)庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有數(shù)據(jù)庫都做性能收集的話，需要逐個數(shù)據(jù)庫去執(zhí)行加載命令又例如跨庫查詢需要dblink插件或fdw插件，兩個數(shù)據(jù)庫之間做查詢相當(dāng)于兩個實(shí)例之間做查詢，已經(jīng)跨越了實(shí)例了，所以需要dblink插件或fdw插件，所以道理非常簡單權(quán)限操作也是一樣逐個數(shù)據(jù)庫去操作，還有一個就是PGSQL雖然像SQL Server的權(quán)限層次結(jié)構(gòu)db=》schema=》object，但是實(shí)際會比SQL Server要復(fù)雜一些，還有就是新建的表還要另外授權(quán)在PGSQL里面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎么去用角色，所以PGSQL在權(quán)限設(shè)計(jì)這一塊確實(shí)比較坑爹

MySQL使用mysql庫下面的5個權(quán)限表去做權(quán)限映射，簡單清晰，唯一問題是缺少權(quán)限角色user表db表host表tables_priv表columns_priv表

34. 發(fā)展歷史

PGSQL在1995年，開發(fā)人員Andrew Yu和Jolly Chen在Postgres中添加了一個SQL（Structured Query Language，結(jié)構(gòu)化查詢語言）翻譯程序，該版本叫做Postgres95，在開放源代碼社區(qū)發(fā)放。在1996年，再次對Postgres95做了較大的改動，并將其命名為PostgresSQL 6.0版發(fā)布，PostgresSQL 的名字就此定型，從1995年算起，大概有24年歷史

MySQL在1996年，MySQL 1.0發(fā)布，它只面向一小撥人，相當(dāng)于內(nèi)部發(fā)布。到了1996年10月，MySQL 3.11.1發(fā)布(MySQL沒有2.x版本)，最開始只提供Solaris操作系統(tǒng)下的二進(jìn)制版本，一個月后，Linux版本出現(xiàn)從1996年算起，大概有23年歷史

以上是“postgresql和mysql的區(qū)別有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！