360°全方位比較PostgreSQL和MySQL
一、原文
https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison
二、摘要
本文對(duì)MySQL和PostgreSQL進(jìn)行詳細(xì)的比較，方便選擇。
1、為什么使用PostgreSQL
2、為什么使用MySQL
3、易用性
4、語法
5、數(shù)據(jù)類型
6、復(fù)制與集群
7、視圖
8、觸發(fā)器
9、存儲(chǔ)過程
10、查詢
11、分區(qū)
12、表的可伸縮性
13、NOSQL能力
14、安全
15、分析函數(shù)
16、GUI工具
17、性能
18、Adoption
19、最佳環(huán)境
三、PG vs MySQL：選擇哪個(gè)？
PostgreSQL和MySQL都是最流行的開源數(shù)據(jù)庫。MySQL被認(rèn)為是世界上最流行的數(shù)據(jù)庫，而PostgreSQL被認(rèn)為是世界上最先進(jìn)的數(shù)據(jù)庫。MySQL并不完全符合SQL標(biāo)準(zhǔn)，并且很多PG上的特性并不支持。這就是為什么PG受到大量開發(fā)者喜歡的原因，并且現(xiàn)在PG越來越流行。
前幾年，Oracle收購了MySQL，導(dǎo)致MySQL的出現(xiàn)兩個(gè)版本：商業(yè)版和社區(qū)版。對(duì)于后者，由于Oracle控制了MySQL的開發(fā)，受到了廣大使用者的批評(píng)。
PostgreSQL是世界上最受歡迎的數(shù)據(jù)庫：他支持大量企業(yè)級(jí)特性和功能。PG由postgresql全球社區(qū)開發(fā)，該社區(qū)由一批優(yōu)秀的開發(fā)人員組成，幾十年來一直努力確保PG具有豐富的功能，并與其他開源、商業(yè)數(shù)據(jù)庫競爭。社區(qū)也從世界各地的公司得到巨大貢獻(xiàn)。
1、為什么使用PG
PG作為開源、功能豐富的數(shù)據(jù)庫，可與Oracle展開競爭。開發(fā)者也會(huì)將PG當(dāng)做NoSql數(shù)據(jù)庫來使用。在云中和本地部署使用PG非常簡單，也可以在docker容器等各個(gè)平臺(tái)使用。
PG完全支持ACID，對(duì)開發(fā)人員和DBA非常友好，是跨任何域的高并發(fā)事務(wù)、復(fù)雜應(yīng)用程序最佳選擇，可以滿足基于WEB和移動(dòng)的各種應(yīng)用程序服務(wù)。PG也是一個(gè)非常好的數(shù)據(jù)倉庫，用于大數(shù)據(jù)上運(yùn)行復(fù)雜的報(bào)告查詢。
2、為什么使用MySQL
MySQL具有社區(qū)版和商業(yè)版。商業(yè)版由Oracle管理。作為關(guān)系型數(shù)據(jù)庫，部署和使用非常簡單。但是對(duì)于SQL標(biāo)準(zhǔn)要求很高的應(yīng)用不太合適。MySQL的集成能力也有限，很難成為異構(gòu)數(shù)據(jù)庫環(huán)境的一部分。
MySQL適用于簡單web應(yīng)用程序或者需要簡單schema、SQL執(zhí)行數(shù)據(jù)庫操作的應(yīng)用。對(duì)于處理大量數(shù)據(jù)的復(fù)雜應(yīng)用來說，MySQL并不是一個(gè)很好的選擇。
3、易用性
PG能夠處理結(jié)構(gòu)化和非結(jié)構(gòu)化的數(shù)據(jù)、具備關(guān)系型數(shù)據(jù)庫所有的特性。MySQL在SQL和特性方面的局限性可能會(huì)為其構(gòu)建高效的RDBMS應(yīng)用程序帶來挑戰(zhàn)。
4、語法
大部分?jǐn)?shù)據(jù)庫的SQL語法都比較相似。然而，MySQL并不支持所有的SQL。對(duì)于支持的SQL和其他數(shù)據(jù)庫都比較相似。例如查詢，PG和MySQL都是：
SELECT * FROM employees;
5、數(shù)據(jù)類型
MySQL和PG都支持許多數(shù)據(jù)類型，從傳統(tǒng)的數(shù)據(jù)類型（integer、date、timestamp）到復(fù)雜類型（json、xml、text）。然而，在復(fù)雜實(shí)時(shí)數(shù)據(jù)查詢下又有所不同。
PG不止支持傳統(tǒng)數(shù)據(jù)類型：numeric、strings、date、decimal等，還支持非結(jié)構(gòu)的數(shù)據(jù)類型：json、xml、hstore等以及網(wǎng)絡(luò)數(shù)據(jù)類型、bit字符串，還有ARRAYS，地理數(shù)據(jù)類型。
MySQL不支持地理數(shù)據(jù)類型。
從9.2開始，PG支持json數(shù)據(jù)類型。相對(duì)于MySQL來說，PG對(duì)json的支持比較先進(jìn)。他有一些json指定的操作符和函數(shù)，是的搜索json文本非常高效。9.4開始，可以以二進(jìn)制的格式存儲(chǔ)json數(shù)據(jù)，支持在該列上進(jìn)行全文索引（GIN索引），從而在json文檔中進(jìn)行快速搜索。
從5.7開始，MySQL支持json數(shù)據(jù)類型，比PG晚。也可以在json列上建立索引。然而對(duì)json相關(guān)的函數(shù)的支持比較有限。不支持在json列上全文索引。由于MySQL對(duì)SQL支持的限制，在存儲(chǔ)和處理json數(shù)據(jù)方面，MySQL不是一個(gè)很好的選擇。
6、復(fù)制和集群
MySQL和PG都具有復(fù)制和集群的能力，能夠確保數(shù)據(jù)操作水平分布。
MySQL支持主-備、一主多備的復(fù)制機(jī)制，通過SQLs即binlog保證將所有的數(shù)據(jù)傳輸?shù)絺錂C(jī)上。這也是復(fù)制只能是異步、半同步的原因。
優(yōu)點(diǎn)：備機(jī)可以寫。這就意味著一旦master崩潰了，slave可以馬上接管，確保應(yīng)用正常工作。DBAs需要確保slave變成主了，并且新的binlog復(fù)制到原主。當(dāng)有很多長SQL時(shí)，復(fù)制會(huì)變得慢。
MySQL也支持NDB集群，即多主的復(fù)制機(jī)制。這種類型的復(fù)制對(duì)要求水平擴(kuò)展的事務(wù)有利。
PG的復(fù)制和MySQL不同，他是基于WAL文件，使復(fù)制更加可靠、更快、更有利于管理。他也支持主備和一主多從的模式，包括級(jí)聯(lián)復(fù)制形式。PG的復(fù)制成為流復(fù)制或物理復(fù)制，可以異步也可以同步。
默認(rèn)情況下，復(fù)制時(shí)異步，Slave能夠滿足讀請(qǐng)求。如果要求在備機(jī)上讀到的數(shù)據(jù)和主機(jī)上一樣，就需要設(shè)置同步復(fù)制。但是缺點(diǎn)是一旦備機(jī)上事務(wù)沒有提交，主機(jī)就會(huì)hang住。
可以使用第三方工具Slony、Bucardo、Londiste、RubyRep等對(duì)表級(jí)別的復(fù)制進(jìn)行歸檔。這些工具都是基于觸發(fā)器的復(fù)制。PG也支持邏輯復(fù)制。最初通過pglogical擴(kuò)展支持邏輯復(fù)制，從10開始內(nèi)核支持邏輯復(fù)制。
7、視圖
MySQL支持視圖，視圖下面通過SQL使用的表的個(gè)數(shù)限制為61。視圖不存儲(chǔ)物理數(shù)據(jù)，也不支持物化視圖。簡單SQL語句創(chuàng)建的視圖可以更新，復(fù)雜SQL創(chuàng)建的視圖不可以更新。
PG和MySQL類似。簡單SQL創(chuàng)建的視圖可更新，復(fù)雜的不行。但是可以通過RULES更新復(fù)雜的視圖。PG支持物化視圖和REFRESHED。
8、觸發(fā)器
MySQL支持INSERT、UPDATE、DELETE上AFTER和BEFORE事件的觸發(fā)器。觸發(fā)器不同執(zhí)行動(dòng)態(tài)SQL語句和存儲(chǔ)過程。
PG的觸發(fā)器比較先進(jìn)。支持AFTER、BEFORE、INSTEAD OF事件的觸發(fā)器。如果在觸發(fā)器喚醒時(shí)執(zhí)行一個(gè)復(fù)雜的SQL，可以通過函數(shù)來完成。PG中的觸發(fā)器可以動(dòng)態(tài)執(zhí)行函數(shù)：
CREATE TRIGGER audit
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW EXECUTE FUNCTION employee_audit_func();
9、存儲(chǔ)過程
MySQL和PG都支持存儲(chǔ)過程，但MySQL僅支持標(biāo)準(zhǔn)的SQL語法，而PG支持非常先進(jìn)的存儲(chǔ)過程。PG以帶RETURN VOID子句的函數(shù)形式完成存儲(chǔ)過程。PG支持的語言有很多：Ruby、Perl、Python、TCL、PL/pgSQL、SQL和JavaScript。而MySQL則沒有這么多。
10、查詢
使用MySQL時(shí)需要考慮的限制：
?某些UPDATE SQL的返回值不符合SQL標(biāo)準(zhǔn)
mysql> select * from test;
+------+------+
| c | c1 |
+------+------+
| 10 | 100 |
+------+------+
1 row in set (0.01 sec)

創(chuàng)新互聯(lián)堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的尖山網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

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)

預(yù)期的標(biāo)準(zhǔn)形式：
mysql> select * from test;
+------+------+
| c | c1 |
+------+------+
| 11 | 10 |
+------+------+

?不能執(zhí)行的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索引、表達(dá)式索引等。PG支持所有SQL標(biāo)準(zhǔn)的特性。對(duì)于需要寫復(fù)雜SQL的開發(fā)者來說，PG是一個(gè)很好的選擇。
11、分區(qū)
MySQL和PG都支持表分區(qū)，然而雙方都有一些限制。
MySQL支持的分區(qū)類型有RANGE、LIST、HASH、KEY和COLUMNS（RANGE和LIST），也支持SUBPARTITIONING。然而DBA在使用時(shí)可能不太易用。
?MySQL8.0，只有innodb和NDB存儲(chǔ)引擎支持表分區(qū)，其他存儲(chǔ)引擎不支持。
?如果分區(qū)key的列不是主鍵或者唯一鍵的一部分，那么就不可能對(duì)表進(jìn)行分區(qū)。
?從5.7.24開始，逐步取消支持將表分區(qū)放在表空間上，這意味著DBA無法平衡表分區(qū)和磁盤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支持表分區(qū)繼承和聲明表分區(qū)。聲明表分區(qū)在10引入，和MySQL類似，而表分區(qū)繼承通過使用觸發(fā)器和規(guī)則來完成。分區(qū)類型支持RANGE、LIST、HASH。限制：
?和MySQL類似，聲明表分區(qū)只能在主鍵和唯一鍵上
?繼承表分區(qū)，子表不能繼承主鍵和唯一鍵。
?INSERT和UPDATE不能自動(dòng)恒信到字表。
12、表的擴(kuò)展性
表段變得越來越大時(shí)會(huì)造成性能問題，在這個(gè)表上的查詢會(huì)占用更多資源，花費(fèi)更多時(shí)間。MySQL和PG需考慮不同因素。
MySQL支持B+tree索引和分區(qū)，這些可以對(duì)大表提升性能。然而，由于不支持bitmap、partial和函數(shù)索引，DBA不能更好的進(jìn)行調(diào)優(yōu)。而且分區(qū)表不能放到不同表空間上，這也造成IO不能更好平衡。
PG的表達(dá)式索引、partial索引、bitmap索引和全文索引都可以提升大表的性能。PG的表分區(qū)和索引可以放到不同的磁盤上，能夠更好提升表的擴(kuò)展性。為實(shí)現(xiàn)水平表級(jí)別的擴(kuò)展，可以使用citusdb、Greenplum、Netezza等。開源的PG不支持水平表分區(qū)，PostgresXC支持，但是他的性能不好。
13、存儲(chǔ)
數(shù)據(jù)存儲(chǔ)是數(shù)據(jù)庫的一個(gè)關(guān)鍵能力。PG和MySQL都提供多種選項(xiàng)存儲(chǔ)數(shù)據(jù)。
PG有一個(gè)通用的存儲(chǔ)特性：表空間能夠容納表、索引、物化視圖等物理對(duì)象。通過表空間，可以將對(duì)象進(jìn)行分組并存儲(chǔ)到不同物理位置，可以提升IO能力。PG12之前版本，不支持可拔插存儲(chǔ)，12只支持可拔插架構(gòu)。
MySQL和PG類似，未來具有表空間特性。他支持可拔插存儲(chǔ)引擎。這是MySQL的一個(gè)優(yōu)點(diǎn)。
14、支持的數(shù)據(jù)模型
關(guān)系型數(shù)據(jù)庫的NoSQL能力能夠幫助處理非結(jié)構(gòu)化的數(shù)據(jù)，例如json、xml、text等。
MySQL的NoSQL能力比較有限。5.7引入了json數(shù)據(jù)類型，需要很長時(shí)間才能變得更加成熟。
PG具有豐富的json能力，未來3年內(nèi)是需要NoSQL能力的開發(fā)者的一個(gè)很好的選擇。Json和jsonb數(shù)據(jù)類型，使得PG對(duì)json操作更快更有效。同樣可以在json數(shù)據(jù)列上建立B-tree索引和GIN索引。XML和HSTORE數(shù)據(jù)類型可以處理XML格式以及其他復(fù)雜text格式的數(shù)據(jù)。對(duì)空間數(shù)據(jù)類型的支持，使得PG是一個(gè)完整的多模型數(shù)據(jù)庫。
15、安全性
數(shù)據(jù)庫安全在未認(rèn)證即可訪問的數(shù)據(jù)庫中扮演者很重要的角色。安全包括對(duì)象級(jí)別和連接級(jí)別。
MySQL通過ROLES和PRIVILEGES將訪問權(quán)限付給數(shù)據(jù)庫、對(duì)象和連接。每個(gè)用戶都需要賦予連接權(quán)限。
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';
每次賦權(quán)時(shí)都需要指定密碼，否則用戶將不能連接。
MySQL同樣支持SSL連接?？梢院屯獠空J(rèn)證系統(tǒng)LDAP和PAM集成。是其企業(yè)版一部分。
PG使用GRANT命令通過ROLES和PRIVILEGES提供訪問權(quán)限。連接認(rèn)證比較簡單，通過pg_hba.conf認(rèn)證文件設(shè)置：
host database user address auth-method [md5 or trust or reject]
PG開源版本同樣支持SSL連接，可以和外部認(rèn)證系統(tǒng)集成。
解析函數(shù)對(duì)一組行數(shù)據(jù)進(jìn)行聚合。有兩種類型的解析函數(shù)：窗口函數(shù)和聚合函數(shù)。聚合函數(shù)執(zhí)行聚合并返回記錄集合的一個(gè)聚合值（sum,avg,min,max等）；而解析函數(shù)返回每個(gè)記錄的聚合值。MySQL和PG都支持多種聚合函數(shù)。MySQL8.0才支持窗口函數(shù)，PG很早就已經(jīng)支持了。
PG支持的窗口函數(shù)：
函數(shù)名 ? ? ??? ? ? 描述
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所有的窗口函數(shù)，除了以下限制：
?窗口函數(shù)不能出現(xiàn)在UPDATE和DELETE中
?窗口函數(shù)不支持DISTINCT
?窗口函數(shù)不支持NESTED
16、圖形界面工具
MySQL有Oracle的SQL Developer、MySQL workbench、dbeaver、omnidb等，監(jiān)控工具有nagios、cacti、zabbix等。PG也可以使用Oracle的SQL Developer、pgAdmin、omnidb、dbeaver。監(jiān)控工具有Nagios, Zabbix, and Cacti。
17、性能
MySQL數(shù)據(jù)庫性能調(diào)優(yōu)選項(xiàng)比較有限，很多索引類型都不支持。寫一個(gè)高效的SQL語句具有挑戰(zhàn)性。對(duì)于大規(guī)模數(shù)據(jù)，MySQL也不是個(gè)很好的選擇。表空間僅支持innodb，并且無法容納表分區(qū)。
PG非常適合任何類型的負(fù)載：OLTP，OLAP，數(shù)據(jù)倉庫等。由于支持的索引類型比較多，可以更好的提升性能。PG也有選項(xiàng)采集數(shù)據(jù)庫內(nèi)存使用，分區(qū)表可以放到不同表空間平衡IO。
18、Adoption
PG是世界上最先進(jìn)的開源數(shù)據(jù)庫。 EnterpriseDB 和2ndQuadrant公司能夠保證PG在世界范圍上被更多用戶使用。
MySQL表示RDBMS和ORDBMS應(yīng)用的最佳選擇。因?yàn)樽詮腛racle收購MySQL依賴，MySQL的采用率明顯下降，開源領(lǐng)域的開發(fā)進(jìn)度也受到?jīng)_擊，招致MySQL用戶的批評(píng)。
19、最佳環(huán)境
MySQL流行于LAMP棧，PG流行于LAPP棧。LAPP棧代表Linux、Apache、Postgres和Php/Python，并且越來越流行。LAMP棧代表 Linux Apache MySQL/MongoDB and Php/Python。

網(wǎng)站名稱：360°全方位比較PostgreSQL和MySQL
轉(zhuǎn)載來于：http://sd-ha.com/article10/gcjgdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、小程序開發(fā)、移動(dòng)網(wǎng)站建設(shè)、靜態(tài)網(wǎng)站、網(wǎng)站策劃、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)