MySQL與PostgreSQL哪個更好？

今天看了一下PostgreSQL和MySQL的對比，發現一篇文章描述的很好，就轉發了出來。大家可以一起瞻讀下。

原位址 https://www.biaodianfu.com/mysql-vs-postgresql.html

網上已經有很多拿PostgreSQL與MySQL比較的文章了，這篇文章隻是對一些重要的資訊進行下梳理。

MySQL

MySQL聲稱自己是最流行的開源資料庫。LAMP中的M指的就是MySQL。建構在LAMP上的應用都會使用MySQL，如WordPress、Drupal等大多數php開源程式。MySQL最初是由MySQL AB開發的，然後在2008年以10億美金的價格賣給了Sun公司，Sun公司又在2010年被Oracle收購。Oracle支援MySQL的多個版本：Standard、Enterprise、Classic、Cluster、Embedded與Community。其中有一些是免費下載下傳的，另外一些則是收費的。其核心代碼基于GPL許可，由于MySQL被控制在Oracle，社群擔心會對MySQL的開源會有影響，是以開發了一些分支，比如： MariaDB和Percona。

PostgreSQL

PostgreSQL标榜自己是世界上最先進的開源資料庫。PostgreSQL的一些粉絲說它能與Oracle相媲美，而且沒有那麼昂貴的價格和傲慢的客服。最初是1985年在加利福尼亞大學伯克利分校開發的，作為Ingres資料庫的後繼。PostgreSQL是完全由社群驅動的開源項目。它提供了單個完整功能的版本，而不像MySQL那樣提供了多個不同的社群版、商業版與企業版。PostgreSQL基于自由的BSD/MIT許可，組織可以使用、複制、修改和重新分發代碼，隻需要提供一個版權聲明即可。

MySQL與PostgreSQL的對比

MySQL的背後是一個成熟的商業公司，而PostgreSQL的背後是一個龐大的志願開發組。這使得MySQL的開發過程更為慎重，而PostgreSQL的反應更為迅速。這樣的兩種背景直接導緻了各自固有的優點和缺點。

PostgreSQL相對于MySQL的優勢

1）不僅僅是關系型資料庫

除了存儲正常的資料類型外，還支援存儲：

• array，不管是一位數組還是多為數組均支援

• json（hStore）和jsonb，相比使用text存儲接送要高效很多

  json和jsonb之間的差別

  jsonb和json在更高的層面上看起來幾乎是一樣的，但在存儲實作上是不同的。

  • json存儲完的文本，json列會每次都解析存儲的值，它不支援索引，但你可以為查詢建立表達式索引。

  • jsonb存儲的二進制格式，避免了重新解析資料結構。它支援索引，這意味着你可以不使用指定的索引就能查詢任何路徑。

    當我們比較寫入資料速度時，由于資料存儲的方式的原因，jsonb會比json稍微的慢一點。json列會每次都解析存儲的值，這意味着鍵的順序要和輸入的時候一樣。但jsonb不同，以二進制格式存儲且不保證鍵的順序。是以，如果你有軟體需要依賴鍵的順序，jsonb可能不是你的應用的最佳選擇。使用jsonb的優勢還在于你可以輕易的整合關系型資料和非關系型資料， PostgreSQL對于mongodb這類的基于文檔的資料庫是個不小的威脅，畢竟如果一個表中隻有一列資料的類型是半結構化的，沒有必要為了遷就它而整個表的設計采用schemaless的結構。

2）支援地理資訊處理擴充

PostGIS 為PostgreSQL提供了存儲空間地理資料的支援，使PostgreSQL成為了一個空間資料庫，能夠進行空間資料管理、數量測量與幾何拓撲分析。在功能上，和MYSQL對比，PostGIS具有下列優勢：

O2O業務場景中的LBS業務使用PostgreSQL + PostGIS有無法比拟的優勢。

3）可以快速建構REST API

PostgREST 可以友善的為任何 PostgreSQL 資料庫提供完全的 RESTful API 服務。

4）支援樹狀結構

支援R-trees這樣可擴充的索引類型，可以更友善地處理一些特殊資料。MySQL 處理樹狀的設計會很複雜, 而且需要寫很多代碼, 而 PostgreSQL 可以高效處理樹結構。

5）有極其強悍的 SQL 程式設計能力

支援遞歸，有非常豐富的統計函數和統計文法支援。

• MySQL：支援 CREATE PROCEDURE 和 CREATE FUNCTION 語句。存儲過程可以用 SQL 和 C++ 編寫。使用者定義函數可以用 SQL、C 和 C++ 編寫。
• PostgreSQL：沒有單獨的存儲過程，都是通過函數實作的。使用者定義函數可以用 PL/pgSQL（專用的過程語言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 編寫。

6）外部資料源支援

可以把 70 種外部資料源 (包括 Mysql, Oracle, CSV, hadoop …) 當成自己資料庫中的表來查詢。Postgres有一個針對這一難題的解決方案：一個名為“外部資料封裝器（Foreign Data Wrapper，FDW）”的特性。該特性最初由PostgreSQL社群領袖Dave Page四年前根據SQL标準SQL/MED（SQL Management of External Data）開發。FDW提供了一個SQL接口，用于通路遠端資料存儲中的遠端大資料對象，使DBA可以整合來自不相關資料源的資料，将它們存入Postgres資料庫中的一個公共模型。這樣，DBA就可以通路和操作其它系統管理的資料，就像在本地Postgres表中一樣。例如，使用FDW for MongoDB，資料庫管理者可以查詢來自文檔資料庫的資料，并使用SQL将它與來自本地Postgres表的資料相關聯。借助這種方法，使用者可以将資料作為行、列或JSON文檔進行檢視、排序和分組。他們甚至可以直接從Postgres向源文檔資料庫寫入（插入、更細或删除）資料，就像一個一體的無縫部署。也可以對Hadoop叢集或MySQL部署做同樣的事。FDW使Postgres可以充當企業的中央聯合資料庫或“Hub”。

7）沒有字元串長度限制

一般關系型資料庫的字元串有限定長度8k左右，無限長 TEXT 類型的功能受限，隻能作為外部大資料通路。而PostgreSQL的 TEXT 類型可以直接通路，SQL文法内置正規表達式，可以索引，還可以全文檢索，或使用xml xpath。MySQL 的各種text字段有不同的限制，要手動區分 small text, middle text, large text… PostgreSQL 沒有這個限制，text 能支援各種大小。

8）支援圖結構資料存儲

沒有具體使用過，具體可以自己搜尋下。參考連結：https://mp.weixin.qq.com/s/cjor82wgDu5gzDvTYpLDWw

9）支援視窗函數

視窗函數提供跨行相關的目前查詢行集執行計算的能力。僅當調用跟着OVER子句的聚集函數，作為視窗函數；否則它們作為正常的聚合函數。視窗也是一種分組，但和 group by 的分組不同。視窗，可以提供分組之外，還可以執行對每個視窗進行計算。可以相像成是group by 後，然後對每個分組進行計算，而不像Group by ，隻是單純地分組。MySQL 不支援 OVER 子句, 而PostgreSQL支援。OVER 子句能簡單的解決 “每組取 top 5” 的這類問題。MySQL支援的SQL文法(ANSI SQL标準)的很小一部分。不支援遞歸查詢、通用表表達式（Oracle的with 語句）或者視窗函數（分析函數）。

10）對索引的支援更強

PostgreSQL 的可以使用函數和條件索引，這使得PostgreSQL資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。對于索引類型：

• MySQL：取決于存儲引擎。MyISAM：BTREE，InnoDB：BTREE。

• PostgreSQL：支援 B-樹、哈希、R-樹和 Gist 索引。

  InnoDB的表和索引都是按相同的方式存儲。也就是說表都是索引組織表。這一般要求主鍵不能太長而且插入時的主鍵最好是按順序遞增，否則對性能有很大影響。PostgreSQL不存在這個問題。

索引類型方面，MySQL取決于存儲引擎。

• MyISAM：BTREE，InnoDB：BTREE。
• PostgreSQL支援 B-樹、哈希、R-樹和 Gist 索引。

11）叢集支援更好

Mysql Cluster可能與你的想象有較大差異。開源的cluster軟體較少。複制(Replication)功能是異步的并且有很大的局限性。例如，它是單線程的(single-threaded)，是以一個處理能力更強的Slave的恢複速度也很難跟上處理能力相對較慢的Master。

PostgreSQL有豐富的開源cluster軟體支援。plproxy 可以支援語句級的鏡像或分片，slony 可以進行字段級的同步設定，standby 可以建構WAL檔案級或流式的讀寫分離叢集，同步頻率和叢集政策調整友善，操作非常簡單。

另外，PostgreSQL的主備複制屬于實體複制，相對于MySQL基于binlog的邏輯複制，資料的一緻性更加可靠，複制性能更高，對主機性能的影響也更小。對于WEB應用來說，複制的特性很重要，mysql到現在也是異步複制，pgsql可以做到同步，異步，半同步複制。還有mysql的同步是基于binlog複制，類似oracle golden gate,是基于stream的複制，做到同步很困難，這種方式更加适合異地複制，pgsql的複制基于wal，可以做到同步複制。同時，pgsql還提供stream複制。

12）事務隔離做的更好

MySQL 的事務隔離級别 repeatable read 并不能阻止常見的并發更新, 得加鎖才可以, 但悲觀鎖會影響性能, 手動實作樂觀鎖又複雜. 而 PostgreSQL 的列裡有隐藏的樂觀鎖 version 字段, 預設的 repeatable read 級别就能保證并發更新的正确性, 并且又有樂觀鎖的性能。

13）對于字元支援更好一些

MySQL 裡需要 utf8mb4 才能顯示 emoji 的坑, PostgreSQL 沒這個坑。

14）對表連接配接支援較完整

對表連接配接支援較完整，MySQL隻有一種表連接配接類型:嵌套循環連接配接(nested-loop),不支援排序-合并連接配接(sort-merge join)與散列連接配接(hash join)。PostgreSQL都支援。

15）存儲方式支援更大的資料量

PostgreSQL主表采用堆表存放，MySQL采用索引組織表，能夠支援比MySQL更大的資料量。

16）時間精度更高

MySQL對于時間、日期、間隔等時間類型沒有秒以下級别的存儲類型，而PostgreSQL可以精确到秒以下。

17）優化器的功能較完整

MySQL對複雜查詢的處理較弱，查詢優化器不夠成熟，explain看執行計劃的結果簡單。性能優化工具與度量資訊不足。

PostgreSQL很強大的查詢優化器，支援很複雜的查詢處理。explain傳回豐富的資訊。提供了一些性能視圖，可以友善的看到發生在一個表和索引上的select、delete、update、insert統計資訊，也可以看到cache命中率。網上有一個開源的pgstatspack工具。

18)序列支援更好

MySQL 不支援多個表從同一個序列中取 id, 而 PostgreSQL 可以。

19）對子查詢支援更好

對子查詢的支援。雖然在很多情況下在SQL語句中使用子查詢效率低下，而且絕大多數情況下可以使用帶條件的多表連接配接來替代子查詢，但是子查詢的存在在很多時候仍然不可避免。而且使用子查詢的SQL語句與使用帶條件的多表連接配接相比具有更高的程式可讀性。幾乎任何資料庫的子查詢 (subquery) 性能都比 MySQL 好。

20）增加列更加簡單

MySQL表增加列，基本上是重建表和索引，會花很長時間。PostgreSQL表增加列，隻是在資料字典中增加表定義，不會重建表.

MySQL相對于PostgreSQL的優勢

1）MySQL比PostgreSQL更流行

流行對于一個商業軟體來說，也是一個很重要的名額，流行意味着更多的使用者，意味着經受了更多的考驗，意味着更好的商業支援、意味着更多、更完善的文檔資料。易用，很容易安裝。第三方工具，包括可視化工具，讓使用者能夠很容易入門。

2）復原實作更優

innodb的基于復原段實作的MVCC機制，相對PG新老資料一起存放的基于XID的MVCC機制，是占優的。新老資料一起存放，需要定時觸發VACUUM，會帶來多餘的IO和資料庫對象加鎖開銷，引起資料庫整體的并發能力下降。而且VACUUM清理不及時，還可能會引發資料膨脹。

3）在Windows上運作更可靠

與PostgreSQL相比，MySQL更适宜在Windows環境下運作。MySQL作為一個本地的Windows應用程式運作(在 NT/Win2000/WinXP下，是一個服務)，而PostgreSQL是運作在Cygwin模拟環境下。PostgreSQL在Windows下運作沒有MySQL穩定，應該是可以想象的。

4）線程模式相比程序模式的優勢

MySQL使用了線程，而PostgreSQL使用的是程序。在不同線程之間的環境轉換和通路公用的存儲區域顯然要比在不同的程序之間要快得多。

• 程序模式對多CPU使用率比較高。程序模式共享資料需要用到共享記憶體，而線程模式資料本身就是在程序空間内都是共享的，不同線程通路隻需要控制好線程之間的同步。
• 線程模式對資源消耗比較少。是以MySQL能支援遠比PostgreSQL多的更多的連接配接。但PostgreSQL中有優秀的連接配接池軟體軟體，如pgbouncer和pgpool，是以通過連接配接池也可以支援很多的連接配接。

5）權限設定上更加完善

MySQL在權限系統上比PostgreSQL某些方面更為完善。PostgreSQL隻支援對于每一個使用者在一個資料庫上或一個資料表上的 INSERT、SELECT和UPDATE/DELETE的授權，而MySQL允許你定義一整套的不同的資料級、表級和列級的權限。對于列級的權限， PostgreSQL可以通過建立視圖，并确定視圖的權限來彌補。MySQL還允許你指定基于主機的權限，這對于目前的PostgreSQL是無法實作的，但是在很多時候，這是有用的。

6）存儲引擎插件化機制

MySQL的存儲引擎插件化機制，使得它的應用場景更加廣泛，比如除了innodb适合事務處理場景外，myisam适合靜态資料的查詢場景。

7）适應24/7運作

MySQL可以适應24/7運作。在絕大多數情況下，你不需要為MySQL運作任何清除程式。PostgreSQL目前仍不完全适應24/7運作，這是因為你必須每隔一段時間運作一次VACUUM。

8）更加試用于簡單的場景

PostgreSQL隻支援堆表，不支援索引組織表，Innodb隻支援索引組織表。

• 索引組織表的優勢：表内的資料就是按索引的方式組織，資料是有序的，如果資料都是按主鍵來通路，那麼通路資料比較快。而堆表，按主鍵通路資料時，是需要先按主鍵索引找到資料的實體位置。
• 索引組織表的劣勢：索引組織表中上再加其它的索引時，其它的索引記錄的資料位置不再是實體位置，而是主鍵值，是以對于索引組織表來說，主鍵的值不能太大，否則占用的空間比較大。

• 對于索引組織表來說，如果每次在中間插入資料，可能會導緻索引分裂，索引分裂會大大降低插入的性能。是以對于使用innodb來說，我們一般最好讓主鍵是一個無意義的序列，這樣插入每次都發生在最後，以避免這個問題。

  由于索引組織表是按一個索引樹，一般它通路資料塊必須按資料塊之間的關系進行通路，而不是按實體塊的通路資料的，是以當做全表掃描時要比堆表慢很多，這可能在OLTP中不明顯，但在資料倉庫的應用中可能是一個問題。

總結

MySQL從一開始就沒有打算做所有事情，因而它在功能方面有一定的局限性，并不能滿足一些先進應用程式的要求。MySQL對某些功能（例如引用、事務、審計等）的實作方式使得它與其他的關系型資料庫相比缺少了一些可靠性。對于簡單繁重的讀取操作，使用PostgreSQL可能有點小題大做，同時性能也比MySQL這樣的同類産品要差。除非你需要絕對的資料完整性，ACID遵從性或者設計複雜，否則PostgreSQL對于簡單的場景而言有點多餘。

如何你确定隻在MySQL和PostgreSQL中進行選擇，以下規則總是有效的：

• 如果你的作業系統是Windows，你應該使用MySQL。
• 當絕對需要可靠性和資料完整性的時候，PostgreSQL是更好的選擇。
• 如果需要資料庫執行定制程式，那麼可擴充的PostgreSQL是更好的選擇。
• 你的應用處理的是地理資料，由于R-TREES的存在，你應該使用PostgreSQL。
• 如果你對資料庫并不了十分了解，甚至不知道事務、存儲過程等究竟是什麼，你應該使用MySQL。

以上内容隻是個人觀點，如果你有不同的看法可以回複評論。