談談 PostgreSQL 與 MySQL 的區別

PostgreSQL 與 MySQL 對比

都屬於開放源碼的一員，性能和功能都在高速地提高和增強。MySQL AB 的人們和 PostgreSQL 的開發者們都在儘可能地把各自的數據庫改得越來越好，所以對於任何商業數據庫使用其中的任何一個都不能算是錯誤的選擇。

PostgreSQL : 免費

原則: 對於一個數據庫，穩定性和速度並不能代表一切。對於一個成熟的數據庫，穩定性肯定會日益提供。而隨着硬件性能的飛速提高，速度也不再是什麼太大的問題。

1 架構對比

• MySQL：多線程

• PostgreSQL：多進程

多線程架構和多進程架構之間沒有絕對的好壞，例如 oracle 在 unix 上是多進程架構，在 windows 上是多線程架構。

PG 的有多種集羣架構可以選擇，plproxy 可以支持語句級的鏡像或分片，slony 可以進行字段級的同步設置，standby 可以構建 WAL 文件級或流式的讀寫分離集羣，同步頻率和集羣策略調整方便，操作非常簡單。

pgsql 對於 numa 架構的支持比 mysql 強一些，比 MYSQL 對於讀的性能更好一些，pgsql 提交可以完全異步，而 mysql 的內存表不夠實用（因爲表鎖的原因）

2 對存儲過程及事務的支持能力

1） MySQL 對於無事務的 MyISAM 表，採用表鎖定，一個長時間運行的查詢很可能會長時間地阻礙對錶的更新，而 PostgreSQL 不存在這樣的問題。

2） PostgreSQL 支持存儲過程，要比 MySQL 好，具備本地緩存執行計劃的能力；

3） MySQL 4.0.2-alpha 開始支持事務的概念，保留無事務的表類型， 爲用戶提供了更多的選擇。

3 穩定性及性能

1）高併發讀寫，負載逼近極限下，PG 的性能指標仍可以維持雙曲線甚至對數曲線，到頂峯之後不再下降，而 MySQL 明顯出現一個波峯後下滑（5.5 版本之後，在企業級版本中有個插件可以改善很多，不過需要付費）

2） PostgreSQL 的穩定性極強， Innodb 等引擎在崩潰、斷電之類的災難場景下抗打擊能力有了長足進步，然而很多 MySQL 用戶都遇到過 Server 級的數據庫丟失的場景——mysql 系統庫是 MyISAM 的，相比之下，PG 數據庫這方面要好一些。

3） mysql 的 innodb 引擎，可以充分優化利用系統所有內存，超大內存下 PG 對內存使用的不那麼充分（需要根據內存情況合理配置）。從測試結果上看，mysql 5.5 的性能提升很大，單機性能強於 pgsql，5.6 應該會強更多。

4 高可用性

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

innodb 的基於回滾段實現的 MVCC 機制，相對 PG 新老數據一起存放的基於 XID 的 MVCC 機制，是佔優的。新老數據一起存放，需要定時觸 發 VACUUM，會帶來多餘的 IO 和數據庫對象加鎖開銷，引起數據庫整體的併發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹；

5 數據同步方式

1）mysql 到現在也是異步複製，pgsql 可以做到同步，異步，半同步複製。

2） mysql 的同步是基於 binlog 複製，類似 oracle golden gate, 是基於 stream 的複製，做到同步很困難，這種方式更加適合異地複製；pgsql 的複製基於 wal，可以做到同步複製。同時，pgsql 還提供 stream 複製。

3） MySQL 的複製可以用多級從庫，但是在 9.2 之前，PGSQL 不能用從庫帶從庫。

4） PG 的主備複製屬於物理複製，相對於 MySQL 基於 binlog 的邏輯複製，數據的一致性更加可靠，複製性能更高，對主機性能的影響也更小。

7 權限控制對比

MySQL 允許你定義一整套的不同的數據級、表級和列級的權限，允許你指定基於主機的權限；

MySQL 的 MERGE 表提供了一個獨特管理多個表的方法。myisampack 可以對只讀表進行壓縮，此後仍然可以直接訪問該表中的行。

7 SQL 語句支持能力

1） PG 有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，比如分析函數（ORACLE 的叫法，PG 裏叫 window 函數）；

2） 支持用多種語言來寫存儲過程，對於 R 的支持也很好。這一點上 MYSQL 就差的很遠，很多分析功能都不支持。騰訊內部數據存儲主要是 MYSQL，但是數據分析主要是 HADOOP+PGSQL（聽李元佳說過，但是沒有驗證過）。

3） pgsql 對錶名大小的處理，只有在 SQL 語句中，表名加雙引號，才區分大小寫。

4）在 SQL 的標準實現上要比 MySQL 完善，而且功能實現比較嚴謹；

5）對錶連接支持較完整，優化器的功能較完整，支持的索引類型很多，複雜查詢能力較強；

6） MySQL 採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對錶結構設計存在約束；

7） MySQL 的 Join 操作的性能非常的差，只支持 Nest Join，所以一旦數據量大，性能就非常的差。PostgreSQL 除了支持 nest join 外，還支持 hash join 和 sort merge join；PostgreSQL 支持正則表達式查找，MySQL 不支持；

8 數據類型支持能力

PostgreSQL 可以更方便地使用 UDF(用戶定義函數) 進行擴展。

1）有豐富的幾何類型，實際上不止幾何類型，PG 有大量字典、數組、bitmap 等數據類型, 因此 PG 多年來在 GIS 領域處於優勢地位。相比之下 mysql 就差很多，instagram就是因爲 PG 的空間數據庫擴展POSTGIS遠遠強於 MYSQL 的 my spatial 而採用 PGSQL 的。

MYSQL 中的空間數據類型有 4 種，分別是GEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎爲 MYISAM 的表中創建，用 SPATIAL 關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明爲NOT NULL。不同的存儲引擎有差別。

MyISAM 和 InnoDB 都支持spatial extensions，但差別在於：如果使用 MyISAM，可以建立spatial index，而 InnoDB 是不支持的。

2） pgsql 對 json 支持比較好，還有很逆天的 fdw] 功能，就是把別的數據庫的表當自己的用；

3） pgsql 的字段類型支持的多，有很多 mysql 沒有的類型，但是實際中有時候用到。

4） 一般關係型數據庫的字符串有限定長度 8k 左右，無限長 TEXT 類型的功能受限，只能作爲外部大數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL 語法內置正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG 的話，文檔數據庫都可以省了。

5） postgresql 有 grouping sets 函數，也是迫使我拋棄 mysql 第一原因。做報表後臺計算，olap/oltp 之類的這個函數簡直是剛性需求。沒有 grouping sets 函數，我感覺做報表後臺計算，簡直慘不忍睹。

當然 pgsql 還有挺多很好用的窗口函數之類，用起來真心爽。mysql 做數據報表計算後臺最大缺點就是沒有 grouping sets 和一些窗口函數，替代方案很麻煩而且效率低，做很多統計數據各種表連接、外連接等等一大堆，不同數據庫之間數據的利用計算。

8） PG 支持 R-trees 這樣可擴展的索引類型，可以更方便地處理一些特殊數據。

9）PG 可以使用函數和條件索引，使得數據庫的調優非常靈活，mysql 就沒有這個功能，條件索引在 web 應用中很重要。

9 入庫過程容錯能力

大批量數據入庫，PostgresSQL 要求所有數據必須完全滿足要求，有一條錯誤，整個數據入庫過程失敗；MySQL 無此問題。比如，每天 1000 萬行數據，就因爲一條打印的不完整，PostgreSQL 會直接報錯，導致一條也導入不進去。

1000 萬里面有一行將數字類型的等級打印成了字符串的東西，那麼 pgsql 會非讓你找出這一條刪掉，然後才能將剩下的數據導入進去。mysql 就完全沒有這個問題，比如 mysql level 字段定義的 int 類型，幾千萬中有一條數據沒注意打印成字符串，mysql 會自己給你轉成 0 存儲的，不會有任何報錯。

10  表組織方式

1） pgsql 用繼承的方法實現分區表，讓分區表的使用不方便且性能差，這點比不上 mysql。

2） PG 主表採用堆表存放，MySQL 採用索引組織表，能夠支持比 MySQL 更大的數據量；

3） MySQL 分區表的實現要優於 PG 的基於繼承表的分區實現，主要體現在分區個數達到上千上萬後的處理性能差異較大。

11 開發接口

對於 web 應用來說, mysql 5.6 的內置 MC API 功能很好用，PGSQL 差一些。

PG 的 “無鎖定” 特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和 PGSQL 的 MVCC 實現有關係。

12 維護團隊

MySQL 的背後是一個成熟的商業公司，使得 MySQL 的開發過程更爲慎重；

PostgreSQL 的背後是一個龐大的志願開發組， PostgreSQL 的反應更爲迅速。這樣的兩種背景直接導致了各自固有的優點和缺點。

• 對於一個嚴肅的商業應用來說，事務的支持是不可或缺的。對於一個嚴肅的商業應用來說，作爲數據庫本身，有衆多的商業邏輯的存在，此時使用存儲過程可以在較少地增加數據庫服務器的負擔的前提下，對這樣的商業邏輯進行封裝，並可以利用數據庫服務器本身的內在機制對存儲過程的執行進行優化。此外存儲過程的存在也避免了在網絡上大量的原始的 SQL 語句的傳輸，這樣的優勢是顯而易見的。

• 用統一的 SQL，去訪問其他關係數據庫，其他 NoSQL 數據庫，HBase，甚至是各種格式的文件，操作系統信息，在線數據集。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/c-Fp97vH2qkZCnA36_U6Ig