【乾貨總結】:可能是史上最全的MySQL和PGSQL對比材料

来源:https://www.cnblogs.com/lyhabc/archive/2019/10/07/11628042.html
-Advertisement-
Play Games

關鍵詞：PostgreSQL 11、MySQL5.7 比較版本：PostgreSQL 11 VS MySQL5.7（innodb引擎） Oracle官方社區版 版權情況：PostgreSQL 11（免費開源）、MySQL5.7 Oracle官方社區版（免費開源） ...


【乾貨總結】:可能是史上最全的MySQL和PGSQL的對比材料

運維了MySQL和PGSQL已經有一段時間了，最近接到一個資料庫選型需求，於是便開始收集資料整理了一下，然後就有了下麵的對比表

關鍵詞：PostgreSQL 11、MySQL5.7

 

比較版本：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特性支持

PGSQL SQL特性支持情況支持94種，SQL語法支持最完善，例如：支持公用表表達式（WITH查詢）     MySQL SQL特性支持情況支持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，部分索引，表達式索引）     MySQL btree 索引，全文索引（低效），表達式索引(需要建虛擬列)，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. 大版本發佈速度

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

MySQL的大版本發佈一般是2年~3年，一般大版本發佈後的第二年才可以上生產環境，避免有坑，版本發佈速度比較慢

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. 分散式演進

PGSQL HTAP資料庫：cockroachDB、騰訊Tbase 分片集群：  Postgres-XC、Postgres-XL

  MySQL
HTAP資料庫：TiDB
分片集群： 各種各樣的中間件，不一一列舉

32. 資料庫的文件名和命名規律

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

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

33. 許可權設計

PGSQL 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

使用mysql庫下麵的5個許可權表去做許可權映射，簡單清晰，唯一問題是缺少許可權角色

user表
db表
host表
tables_priv表
columns_priv表

 

 

小結

上面的對比表還不是很完善，只有一些本人認為比較關鍵的特性拿出來對比

 

總的來說，兩種資料庫都有優缺點，大家在選型的時候需要謹慎選擇，MySQL需要多折騰，PGSQL讓你少折騰，因為PGSQL本身已經做的比較完善，不太需要依賴一些第三方工具

當然，如果在MySQL上選擇Percona 分支，MariaDB分支，或者Oracle官方的MySQL企業版就另當別論

MySQL因為需要支持更換存儲引擎，所以某些功能都要受制於存儲引擎層，例如：物理複製

而PGSQL不支持更換存儲引擎（在PGSQL V12開始也支持可插撥的表存取介面），而且一直由官方統一開發和維護，所以相對比較穩定，功能也比較完善，對得上它的稱號：《世界上功能最為強大的開源資料庫》

PGSQL V12 支持可插撥的表存取介面之後，有可能由第三方存儲引擎來改進PGSQL本身的MVCC實現機制，而不需要等待官方去解決，聚集索引、undo表空間這些都不再是問題

 

 

如有不對的地方，歡迎大家拍磚o(∩_∩)o 

本文版權歸作者所有，未經作者同意不得轉載。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux三劍客及使用介紹
    Linux 三劍客是（grep，sed，awk）三者的簡稱，熟練使用這三個工具可以提升運維效率。Linux 三劍客以正則表達式作為基礎，而在Linux系統中，支持兩種正則表達式，分別為“標準正則表達式”和“擴展正則表達式”。在掌握好正則表達式後，將具體講解三劍客的用法。 一、正則表達式 可以看到標準 ...
  • 痞子衡嵌入式：史上最強i.MX RT學習資源彙總(持續更新中...)
    大家好，我是痞子衡，是正經搞技術的痞子。今天痞子衡給大家介紹的是i.MX RT學習資源。 ...
  • Linux-3.14.12記憶體管理筆記【伙伴管理演算法（2）】
    前面已經分析了linux記憶體管理演算法（伙伴管理演算法）的準備工作。 具體的演算法初始化則回到start_kernel()函數接著往下走，下一個函數是mm_init()： 乍看僅僅是幾個函數的調用，實際上這裡的事情遠遠沒這麼簡單。其中page_cgroup_init_flatmem()與cgroup相關， ...
  • Linux-3.14.12記憶體管理筆記【伙伴管理演算法（1）】
    前面分析了memblock演算法、內核頁表的建立、記憶體管理框架的構建，這些都是x86處理的setup_arch()函數裡面初始化的，因地制宜，具有明顯處理器的特征。而start_kernel()接下來的初始化則是linux通用的記憶體管理演算法框架了。 build_all_zonelists()用來初始化 ...
  • SUSE CaaS Platform 4 - 使用 Ceph RBD 作為持久存儲（動態）
    存儲類 存儲類（storage class）是kubernetes資源類型，它是由管理員為管理PV之便而按需創建的類別 存儲類好處是支持 PV 的動態創建，系統按PVC的需求標準動態創建適配的PV會為存儲管理帶來極大的靈活性。 PV的動態供給，其重點是在存儲類的定義，其分類大概是對存儲的性能進行分類 ...
  • 資料庫系統（二）--關係型資料庫之關係代數
    關係型資料庫-關係操作集合 1、 基本的關係操作 關係模型中常用的關係操作包括查詢(Query)操作和插入(Insert)、刪除 (Delete)、修改(Update)操作兩大部分。 查詢操作分為：選擇、投影、連接、除、並、差、交、笛卡爾積等； 五種基本操作：選擇、投影、並、差、笛卡爾積； 關係操作 ...
  • ES 32 - Elasticsearch 數據建模的探索與實踐
    本篇博文通過對ES中不同類型的欄位的建模方案進行說明, 並結合實際案例, 演示了index、stored、dynamic等參數的使用, 並歸納了ES處理關聯關係、避免太多的欄位、避免正則查詢、避免空值引起聚合結果失真等最佳實踐. 如有疑問, 留言區見
  • Mysql 單表查詢where初識
    Mysql 單表查詢where初識 準備數據 數據基本測試 where 條件過濾 比較運算符 , 邏輯運算符, 範圍判斷, 空判斷, 模糊查詢 邏輯運算符: and, or, not Null 判斷 is null; is not null 範圍查詢 in; between...and in 用於離 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類