引言:
關係型資料庫的使用場景中,mysql這麼好用,為什麼要考慮pgsql?
看完這篇文章也許你會有不同的想法。
概述:
mysql出現在1994年,現在所有權歸屬oracle,創始人現在又發布了新的免費開源資料庫MariaDB,現在開源關係型資料庫領域,mysql使用確實是最廣泛的,官方說許多世界上最大、發展最快的組織都在使用mysql。
pgsql又稱PostgresSQL,出現在1986年,官方標榜自己是世界上最先進最高級的開源關係型資料庫。
pgsql和mysql簡介:
pgsql的一些粉絲說可以跟oracle相媲美,可靠性是pgsql的最高優先級,數據一致性與完整性也是pgsql的高優先級特性;
mysql和pgsql都出現在一些高流量的web站點上,都能用在大型分布式系統上,都支持事務,都支持全文索引;
mysql現在已經支持嵌入式應用,而pgsql依然堅守在傳統B/S架構上;
mysql能夠進行快速的讀取和大量的查詢操作,不過在複雜特性與數據完整性檢查方面不太盡如人意;
pgsql是針對事務性企業級應用嚴肅、功能完善的資料庫,支持強ACID特性,會做很多數據完整性檢查;
mysql上myISAM存儲引擎因為執行很少的數據完整性檢查,所以速度快,對於敏感數據,對讀寫要求高的數據,支持 ACID特性的InnoDB則是個更好的選擇,相反pgsql是個只有單一存儲引擎完全集成的資料庫;
mysql與pgsql都是高可配置的,通過參數調整性能,也可以調整查詢與事務特性,他們都支持通過擴展添加額外的功能;
pgsql可靠性更好,穩定性很強,在保護數據安全方面更擅長,並且是社區項目,不會陷入商業廠商牢籠之中;
mysql的普及更廣一些,學習資料更多,熟練使用的人更多,所以選擇mysql的企業很多。
pgsql相比mysql的優勢:
pgsql存儲過程的功能支持要比mysql好,具備本地緩存執行計劃的能力;
pgsql對表連接支持更完整,優化器的功能更完整,支持的索引類型很多,複雜查詢能力較強;
pgsql主備複製屬於物理複製,支持異步、同步、半同步複製,mysql基於binlog的邏輯複製,是異步複製,pgsql數據的一致性更加可靠,複製性能更高,對主機性能的影響也更小;
pgsql支持json數據類型,而mysql不支持,mysql字符型有長度限制,而pgsql的text類型無限長,可以使用xml xpath,用pgsql的話,mongodb這樣文檔資料庫就省了;
pgsql是完全免費開源的,而mysql歸屬oracle後,開源程度大不如以前;
pgsql在GIS領域多年來處於優勢地位,有豐富的幾何類型,支持極其豐富的空間函數,可以建立R樹、GIST樹等空間索引,instagram就是因為pgsql的空間資料庫擴展postgis遠遠強於mysql的my spatial而採用的pgsql;
pgsql有極其強悍的sql編程能力,有非常豐富的統計函數和統計語法支持,可用多種語言寫存儲過程,對R支持也非常好,這一點mysql就差的很遠,騰訊內部數據存儲主要用mysql,但是數據分析主要是hadoop+pgsql;
pgsql有多種集群架構可選擇,plproxy可以支持語句級的鏡像或分片,slony可以進行欄位級的同步設置,standby可以構建WAL文件級或流式的讀寫分離集群,同步頻率和集群策略調整方便,操作非常簡單;
pgsql支持topology、pgrouting等功能強大、方便使用的GIS擴展,例如可以用pgrouting計算路徑規劃;
pgsql資料庫方便QGIS等GIS工具連接和圖層展示等操作;
下圖是用QGIS從pgsql讀取數據並展示圖層:
mysql相比pgsql的優勢:
mysql採用索引組織表,這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作,但是對表結構設計存在約束;
mysql的優化器較簡單,系統表、運算符、數據類型的實現都很精簡,非常適合簡單的查詢操作;
mysql分區表的實現要優於pgsql的基於繼承表的分區實現,主要體現在分區個數達到上千上萬後的處理性能差異較大;
mysql的存儲引擎插件化機制,使得它的應用場景更加廣泛,比如除了Innodb適合事務處理場景外,Myisam適合靜態數據的查詢場景;
mysql也支持空間索引,是用R樹實現的空間索引,但空間函數等功能不如pgsql豐富,空間查詢比pgsql慢;
pgsql更適合嚴格的企業應用場景,比如金融、電信、ERP、CRM等,mysql更適用業務邏輯相對簡單、數據可靠性要求更低的網際網路場景,比如google、facebook、淘寶等。
一個簡單的性能對比:
下圖是redis、mongo、pgsql、mysql在一個空間查詢場景下的對比:
pgsql支持的幾種高級插件:
postgis提供豐富的空間數據類型和空間函數。
pgrouting 用來進行路徑規劃。
postgis_topology 用於管理點、線、面等拓撲對象。
postgis_sfcgal 用於實現3D相關算法。
fuzzystrmatch 字符串相似度計算。
address_standardizer 用於地址標準化。
pg_trgm 用於分詞索引。
結論:
如果空間操作比較多,比較複雜,那就得選擇pgsql;
如果對數據安全性和穩定性要求很高,pgsql是更好的選擇;
如果業務場景沒那麼複雜,mysql完全可以勝任。
希望這篇文章對你有所幫忙,歡迎留言討論。