360°全方位比較PostgreSQL和MySQL

一、原文

https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison

二、摘要

本文對MySQL和PostgreSQL進行詳細的比較，友善選擇。

1、為什麼使用PostgreSQL

2、為什麼使用MySQL

3、易用性

4、文法

5、資料類型

6、複制與叢集

7、視圖

8、觸發器

9、存儲過程

10、查詢

11、分區

12、表的可伸縮性

13、NoSQL能力

14、安全

15、分析函數

16、GUI工具

17、性能

18、Adoption

19、最佳環境

三、PG vs MySQL：選擇哪個？

PostgreSQL和MySQL都是最流行的開源資料庫。MySQL被認為是世界上最流行的資料庫，而PostgreSQL被認為是世界上最先進的資料庫。MySQL并不完全符合SQL标準，并且很多PG上的特性并不支援。這就是為什麼PG受到大量開發者喜歡的原因，并且現在PG越來越流行。

前幾年，Oracle收購了MySQL，導緻MySQL的出現兩個版本：商業版和社群版。對于後者，由于Oracle控制了MySQL的開發，受到了廣大使用者的批評。

PostgreSQL是世界上最受歡迎的資料庫：他支援大量企業級特性和功能。PG由postgresql全球社群開發，該社群由一批優秀的開發人員組成，幾十年來一直努力確定PG具有豐富的功能，并與其他開源、商業資料庫競争。社群也從世界各地的公司得到巨大貢獻。

1、為什麼使用PG

PG作為開源、功能豐富的資料庫，可與Oracle展開競争。開發者也會将PG當做NoSQL資料庫來使用。在雲中和本地部署使用PG非常簡單，也可以在docker容器等各個平台使用。

PG完全支援ACID，對開發人員和DBA非常友好，是跨任何域的高并發事務、複雜應用程式最佳選擇，可以滿足基于WEB和移動的各種應用程式服務。PG也是一個非常好的資料倉庫，用于大資料上運作複雜的報告查詢。

2、為什麼使用MySQL

MySQL具有社群版和商業版。商業版由Oracle管理。作為關系型資料庫，部署和使用非常簡單。但是對于SQL标準要求很高的應用不太合适。MySQL的內建能力也有限，很難成為異構資料庫環境的一部分。

MySQL适用于簡單web應用程式或者需要簡單schema、SQL執行資料庫操作的應用。對于處理大量資料的複雜應用來說，MySQL并不是一個很好的選擇。

3、易用性

PG能夠處理結構化和非結構化的資料、具備關系型資料庫所有的特性。MySQL在SQL和特性方面的局限性可能會為其建構高效的RDBMS應用程式帶來挑戰。

4、文法

大部分資料庫的SQL文法都比較相似。然而，MySQL并不支援所有的SQL。對于支援的SQL和其他資料庫都比較相似。例如查詢，PG和MySQL都是：

SELECT * FROM employees;

5、資料類型

MySQL和PG都支援許多資料類型，從傳統的資料類型（integer、date、timestamp）到複雜類型（json、xml、text）。然而，在複雜實時資料查詢下又有所不同。

PG不止支援傳統資料類型：numeric、strings、date、decimal等，還支援非結構的資料類型：json、xml、hstore等以及網絡資料類型、bit字元串，還有ARRAYS，地理資料類型。

MySQL不支援地理資料類型。

從9.2開始，PG支援json資料類型。相對于MySQL來說，PG對json的支援比較先進。他有一些json指定的操作符和函數，是的搜尋json文本非常高效。9.4開始，可以以二進制的格式存儲json資料，支援在該列上進行全文索引（GIN索引），進而在json文檔中進行快速搜尋。

從5.7開始，MySQL支援json資料類型，比PG晚。也可以在json列上建立索引。然而對json相關的函數的支援比較有限。不支援在json列上全文索引。由于MySQL對SQL支援的限制，在存儲和處理json資料方面，MySQL不是一個很好的選擇。

6、複制和叢集

MySQL和PG都具有複制和叢集的能力，能夠確定資料操作水準分布。

MySQL支援主-備、一主多備的複制機制，通過SQLs即binlog保證将所有的資料傳輸到備機上。這也是複制隻能是異步、半同步的原因。

優點：備機可以寫。這就意味着一旦master崩潰了，slave可以馬上接管，確定應用正常工作。DBAs需要確定slave變成主了，并且新的binlog複制到原主。當有很多長SQL時，複制會變得慢。

MySQL也支援NDB叢集，即多主的複制機制。這種類型的複制對要求水準擴充的事務有利。

PG的複制和MySQL不同，他是基于WAL檔案，使複制更加可靠、更快、更有利于管理。他也支援主備和一主多從的模式，包括級聯複制形式。PG的複制成為流複制或實體複制，可以異步也可以同步。

預設情況下，複制時異步，Slave能夠滿足讀請求。如果要求在備機上讀到的資料和主機上一樣，就需要設定同步複制。但是缺點是一旦備機上事務沒有送出，主機就會hang住。

可以使用第三方工具Slony、Bucardo、Londiste、RubyRep等對表級别的複制進行歸檔。這些工具都是基于觸發器的複制。PG也支援邏輯複制。最初通過pglogical擴充支援邏輯複制，從10開始核心支援邏輯複制。

7、視圖

MySQL支援視圖，視圖下面通過SQL使用的表的個數限制為61。視圖不存儲實體資料，也不支援物化視圖。簡單SQL語句建立的視圖可以更新，複雜SQL建立的視圖不可以更新。

PG和MySQL類似。簡單SQL建立的視圖可更新，複雜的不行。但是可以通過RULES更新複雜的視圖。PG支援物化視圖和REFRESHED。

8、觸發器

MySQL支援INSERT、UPDATE、DELETE上AFTER和BEFORE事件的觸發器。觸發器不同執行動态SQL語句和存儲過程。

PG的觸發器比較先進。支援AFTER、BEFORE、INSTEAD OF事件的觸發器。如果在觸發器喚醒時執行一個複雜的SQL，可以通過函數來完成。PG中的觸發器可以動态執行函數：

CREATE TRIGGER audit

AFTER INSERT OR UPDATE OR DELETE ON employee

FOR EACH ROW EXECUTE FUNCTION employee_audit_func();

9、存儲過程

MySQL和PG都支援存儲過程，但MySQL僅支援标準的SQL文法，而PG支援非常先進的存儲過程。PG以帶RETURN VOID子句的函數形式完成存儲過程。PG支援的語言有很多：Ruby、Perl、Python、TCL、PL/pgSQL、SQL和JavaScript。而MySQL則沒有這麼多。

10、查詢

使用MySQL時需要考慮的限制：

某些UPDATE SQL的傳回值不符合SQL标準

mysql> select * from test;

+------+------+

| c | c1 |

+------+------+

| 10 | 100 |

+------+------+

1 row in set (0.01 sec)

mysql> update test set c=c+1, c1=c;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

+------+------+

| c | c1 |

+------+------+

| 11 | 11 |

+------+------+

1 row in set (0.00 sec)

預期的标準形式：

mysql> select * from test;

+------+------+

| c | c1 |

+------+------+

| 11 | 10 |

+------+------+

不能執行的UPDATE或DELETE語句：

mysql> delete from test where c in (select t1.c from test t1, test t2 where t1.c=t2.c);

ERROR 1093 (HY000):

子查詢中不能使用LIMIT子句

mysql> select * from test where c in (select c from test2 where c<3 limit 1);

ERROR 1235 (42000):

MySQL也不支援“LIMIT & IN/ALL/ANY/SOME子句”。同樣也不支援FULL OUTER JOINS、INTERSECT、EXCEPT等。也不支援Partial索引、bitmap索引、表達式索引等。PG支援所有SQL标準的特性。對于需要寫複雜SQL的開發者來說，PG是一個很好的選擇。

11、分區

MySQL和PG都支援表分區，然而雙方都有一些限制。

MySQL支援的分區類型有RANGE、LIST、HASH、KEY和COLUMNS（RANGE和LIST），也支援SUBPARTITIONING。然而DBA在使用時可能不太易用。

MySQL8.0，隻有innodb和NDB存儲引擎支援表分區，其他存儲引擎不支援。
如果分區key的列不是主鍵或者唯一鍵的一部分，那麼就不可能對表進行分區。
從5.7.24開始，逐漸取消支援将表分區放在表空間上，這意味着DBA無法平衡表分區和磁盤IO。

mysql> create table emp (id int not null, fname varchar (30), lname varchar(30), store_id int not null ) partition by range (store_id) ( partition p0 values less than (6) tablespace tbs, partition p1 values less than(20) tablespace tbs1, partition p2 values less than (40) tablespace tbs2);

ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace.

mysql>

PG支援表分區繼承和聲明表分區。聲明表分區在10引入，和MySQL類似，而表分區繼承通過使用觸發器和規則來完成。分區類型支援RANGE、LIST、HASH。限制：

和MySQL類似，聲明表分區隻能在主鍵和唯一鍵上
繼承表分區，子表不能繼承主鍵和唯一鍵。
INSERT和UPDATE不能自動恒信到字表。

12、表的擴充性

表段變得越來越大時會造成性能問題，在這個表上的查詢會占用更多資源，花費更多時間。MySQL和PG需考慮不同因素。

MySQL支援B+tree索引和分區，這些可以對大表提升性能。然而，由于不支援bitmap、partial和函數索引，DBA不能更好的進行調優。而且分區表不能放到不同表空間上，這也造成IO不能更好平衡。

PG的表達式索引、partial索引、bitmap索引和全文索引都可以提升大表的性能。PG的表分區和索引可以放到不同的磁盤上，能夠更好提升表的擴充性。為實作水準表級别的擴充，可以使用citusdb、Greenplum、Netezza等。開源的PG不支援水準表分區，PostgresXC支援，但是他的性能不好。

13、存儲

資料存儲是資料庫的一個關鍵能力。PG和MySQL都提供多種選項存儲資料。

PG有一個通用的存儲特性：表空間能夠容納表、索引、物化視圖等實體對象。通過表空間，可以将對象進行分組并存儲到不同實體位置，可以提升IO能力。PG12之前版本，不支援可拔插存儲，12隻支援可拔插架構。

MySQL和PG類似，未來具有表空間特性。他支援可拔插存儲引擎。這是MySQL的一個優點。

14、支援的資料模型

關系型資料庫的NoSQL能力能夠幫助處理非結構化的資料，例如json、xml、text等。

MySQL的NoSQL能力比較有限。5.7引入了json資料類型，需要很長時間才能變得更加成熟。

PG具有豐富的json能力，未來3年内是需要NoSQL能力的開發者的一個很好的選擇。Json和jsonb資料類型，使得PG對json操作更快更有效。同樣可以在json資料列上建立B-tree索引和GIN索引。XML和HSTORE資料類型可以處理XML格式以及其他複雜text格式的資料。對空間資料類型的支援，使得PG是一個完整的多模型資料庫。

15、安全性

資料庫安全在未認證即可通路的資料庫中扮演者很重要的角色。安全包括對象級别和連接配接級别。

MySQL通過ROLES和PRIVILEGES将通路權限付給資料庫、對象和連接配接。每個使用者都需要賦予連接配接權限。

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser@'192.168.1.1’ IDENTIFIED BY 'newpassword';

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser@'192.168.1.*’ IDENTIFIED BY 'newpassword';

每次賦權時都需要指定密碼，否則使用者将不能連接配接。

MySQL同樣支援SSL連接配接。可以和外部認證系統LDAP和PAM內建。是其企業版一部分。

PG使用GRANT指令通過ROLES和PRIVILEGES提供通路權限。連接配接認證比較簡單，通過pg_hba.conf認證檔案設定：

host database user address auth-method [md5 or trust or reject]

PG開源版本同樣支援SSL連接配接，可以和外部認證系統內建。

解析函數對一組行資料進行聚合。有兩種類型的解析函數：視窗函數和聚合函數。聚合函數執行聚合并傳回記錄集合的一個聚合值（sum,avg,min,max等）；而解析函數傳回每個記錄的聚合值。MySQL和PG都支援多種聚合函數。MySQL8.0才支援視窗函數，PG很早就已經支援了。

PG支援的視窗函數：

函數名	描述
CUME_DIST	Return the relative rank of the current row.
DENSE_RANK	Rank the current row within its partition without gaps.
FIRST_VALUE	Return a value evaluated against the first row within its partition.
LAG	Return a value evaluated at the row that is at a specified physical offset row before the current row within the partition.
LAST_VALUE	Return a value evaluated against the last row within its partition.
LEAD	Return a value evaluated at the row that is offset rows after the current row within the partition.
NTILE	Divide rows in a partition as equally as possible and assign each row an integer starting from 1 to the argument value.
NTH_VALUE	Return a value evaluated against the nth row in an ordered partition.
PERCENT_RANK	Return the relative rank of the current row (rank-1) / (total rows-1)
RANK	Rank the current row within its partition with gaps.
ROW_NUMBER	Number the current row within its partition starting from 1.

MySQL支援PG所有的視窗函數，除了以下限制：

視窗函數不能出現在UPDATE和DELETE中
視窗函數不支援DISTINCT
視窗函數不支援NESTED

16、圖形界面工具

MySQL有Oracle的SQL Developer、MySQL workbench、dbeaver、omnidb等，監控工具有nagios、cacti、zabbix等。PG也可以使用Oracle的SQL Developer、pgAdmin、omnidb、dbeaver。監控工具有Nagios, Zabbix, and Cacti。

17、性能

MySQL資料庫性能調優選項比較有限，很多索引類型都不支援。寫一個高效的SQL語句具有挑戰性。對于大規模資料，MySQL也不是個很好的選擇。表空間僅支援innodb，并且無法容納表分區。

PG非常适合任何類型的負載：OLTP，OLAP，資料倉庫等。由于支援的索引類型比較多，可以更好的提升性能。PG也有選項采集資料庫記憶體使用，分區表可以放到不同表空間平衡IO。

360°全方位比較PostgreSQL和MySQL