360°全方位比較PostgreSQL和MySQL
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時需要考慮的限制:
l 某些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 |
+------+------+
l 不能執行的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):
l 子查詢中不能使用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在使用時可能不太易用。
l MySQL8.0,只有innodb和NDB儲存引擎支援表分割槽,其他儲存引擎不支援。
l 如果分割槽key的列不是主鍵或者唯一鍵的一部分,那麼就不可能對錶進行分割槽。
l 從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。限制:
l 和MySQL類似,宣告表分割槽只能在主鍵和唯一鍵上
l 繼承表分割槽,子表不能繼承主鍵和唯一鍵。
l 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所有的視窗函式,除了以下限制:
l 視窗函式不能出現在UPDATE和DELETE中
l 視窗函式不支援DISTINCT
l 視窗函式不支援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。
18 、 Adoption
PG是世界上最先進的開源資料庫。 EnterpriseDB 和2ndQuadrant公司能夠保證PG在世界範圍上被更多使用者使用。
MySQL表示RDBMS和ORDBMS應用的最佳選擇。因為自從Oracle收購MySQL依賴,MySQL的採用率明顯下降,開源領域的開發進度也受到衝擊,招致MySQL使用者的批評。
19 、 最佳環境
MySQL流行於LAMP棧,PG流行於LAPP棧。LAPP棧代表Linux、Apache、Postgres和Php/Python,並且越來越流行。LAMP棧代表 Linux Apache MySQL/MongoDB and Php/Python。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31493717/viewspace-2673939/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL與MySQL比較MySql
- PostgreSQL與MySQL的比較 - hackrMySql
- OpenShift與Docker全方位比較Docker
- MongoDB和MySQL比較MongoDBMySql
- 翻譯-Salt與Ansible全方位比較
- 關聯式資料庫比較:SQLite vs MySQL vs PostgreSQL資料庫SQLiteMySql
- MySQL中的NULL和空串比較MySqlNull
- 從全方位為你比較3種資料科學工具的比較:Python、R和SAS(附連結)資料科學Python
- MYSQL和ORACLE時區設定比較MySqlOracle
- 資料庫比較 PostgreSQL vs MongoDB資料庫SQLMongoDB
- js 深比較和淺比較JS
- Mysql中的Datetime和Timestamp比較MySql
- MySQL 中的 distinct 和 group by 的效能比較MySql
- PostgreSQL與Rust的聚合實現比較SQLRust
- PostgreSQL、Redis與Memcached的效能比較 - CYBERTECSQLRedis
- MySQL:MySQL層比較函式呼叫MySql函式
- MySQL 的 timestamp 和 datetime 型別比較MySql型別
- MySQL和MSSQL下,text、ntext、image、blob的比較MySql
- ORACLE11.2.0.3和MYSQL5.6 DDL比較OracleMySql
- Oracle與PostgreSQL比較:PostgreSQL至少在安裝和大小方面擊敗了Oracle - foersterOracleSQL
- Oracle date 型別比較和String比較Oracle型別
- 【原創】MySQL和PostgreSQL 匯入資料對比 薦MySql
- 資料庫系列:MySQL引擎MyISAM和InnoDB的比較資料庫MySql
- TCP和UDP比較TCPUDP
- Java和JavaSciprt比較Java
- Redis 和 Memcached 比較Redis
- MongoDB和Redis比較。MongoDBRedis
- ETL和EAI比較AI
- 使用perl比較mysql的版本MySql
- mysql timestamp比較查詢MySql
- mysql字元型別varchar()比較MySql字元型別
- Zope的優點和Apache+PHP+MySQL的比較(轉)ApachePHPMySql
- 用PHP連mysql和oracle資料庫效能比較(轉)PHPMySqlOracle資料庫
- Go和Python比較的話,哪個比較好?GoPython
- PostgreSQL 原始碼解讀(196)- 浮點數比較SQL原始碼
- mysql中count(1)與count(*)比較MySql
- mysql比較兩個日期間隔MySql
- etcd和redis比較Redis