MySQL同PostgreSQL + PostGIS對比

標籤：

MySQL同PostgreSQL對比

1. MySQL

   相對來說比較年輕，首度出現在1994年。它聲稱自己是最流行的開來源資料庫。MySQL就是LAMP（用於Web開發的軟體包，包括Linux、Apache及Perl/PHP/Python）中的M。構建在LAMP棧之上的大多數應用都會使用MySQL，包括那些知名的應用，如 WordPress、Drupal、Zend及phpBB等。

   開始，MySQL的設計目標是成為一個快速的Web伺服器後端，使用快速的索引序列存取方法（ISAM），不支援ACID。經過早期快速的發展之 後，MySQL開始支援更多的儲存引擎，並通過InnoDB引擎實現了ACID。MySQL還支援其他儲存引擎，提供了暫存資料表的功能（使用MEMORY存 儲引擎），通過MyISAM引擎實現了高速讀的資料庫，此外還有其他的核心儲存引擎與第三方引擎。

   現在，基於最初的MySQL代碼還有更多的資料庫可供選擇，因為幾個核心的MySQL開發人員已經發布了MySQL分支。最初的MySQL建立者之一 Michael “Monty” Widenius貌似後悔將MySQL賣給了Sun公司，於是又開發了他自己的MySQL分支MariaDB，它是免費的，基於GPL許可。知名的 MySQL開發人員Brian Aker所建立的分支Drizzle對其進行了大量的改寫，特別針對多CPU、雲、網路應用與高並發進行了最佳化。

   當前只有MyISAM引擎的資料表支援地理空間資料的儲存。MySQL對於空間資料庫本身是支援的,只是支援的不太全面,實際上專業空間資料庫非postgis莫屬,而且對於地圖方面的功能並不是要求很高，如果對分析能力沒有很高的要求，選擇Mysql進行空間資料的儲存是可以達到在讀取上的要求的。同時MyISAM引擎擁有較高的插入，查詢速度，但不支援事務，這也是其一大缺點。

2. PostgreSQL

   PostgreSQL標榜自己是世界上最先進的開來源資料庫。PostgreSQL的一些粉絲說它能與Oracle相媲美，而且沒有那麼昂貴的價格和傲慢的客服。它擁有很長的曆史，最初是1985年在加利福尼亞大學伯克利分校開發的，作為Ingres資料庫的後繼。

   PostgreSQL是完全由社區驅動的開源項目，由全世界超過1000名貢獻者所維護。它提供了單個完整功能的版本，而不像MySQL那樣提供了 多個不同的社區版、商業版與企業版。PostgreSQL基於自由的BSD/MIT許可，組織可以使用、複製、修改和重新分發代碼，只需要提供一個著作權聲 明即可。

   可靠性是PostgreSQL的最高優先順序。它以堅如磐石的品質和良好的工程化而聞名，支援高事務、任務關鍵型應用。PostgreSQL的文檔非 常精良，提供了大量免費的線上手冊，還針對舊版本提供了歸檔的參考手冊。PostgreSQL的社區支援是非常棒的，還有來自於獨立廠商的商業支援。

   資料一致性與完整性也是PostgreSQL的高優先順序特性。PostgreSQL是完全支援ACID特性的，它對於資料庫訪問提供了強大的安全性 保證，充分利用了企業安全工具，如Kerberos與OpenSSL等。你可以定義自己的檢查，根據自己的商務規則確保資料品質。在眾多的管理特性 中，point-in-time recovery（PITR）是非常棒的特性，這是個靈活的高可用特性，提供了諸如針對失敗恢複建立熱備份以及快照與恢複的能力。但這並不是 PostgreSQL的全部，項目還提供了幾個方法來管理PostgreSQL以實現高可用、負載平衡與複製等，這樣你就可以使用適合自己特定需求的功能 了。

3. 平台對比
   MySQL與PostgreSQL都出現在一些高流量的Web網站
   MySQL：Slashdot、Twitter、Facebook
   PostgreSQL：Yahoo使用了一個修改的PostgreSQL資料庫來處理每天數以億計的事件，還有Reddit和Disqus

   MySQL與PostgreSQL都能運行在多個作業系統上，如Linux、Unix、Mac OS X與Windows。他們都是開源、免費的，因此測試他們時的唯一代價就是你的時間與硬體。他們都很靈活且具有延展性，可用在小型系統和大型分布式系統 上。MySQL在一個領域上要比PostgreSQL更進一步，那就是它的觸角延伸到了嵌入式領域，這是通過libmysqld實現的。 PostgreSQL不支援嵌入式應用，依然堅守在傳統的用戶端/伺服器架構上。

   MySQL通常被認為是針對網站與應用的快速資料庫後端，能夠進行快速的讀取和大量的查詢操作，不過在複雜特性與資料完整性檢查方面不太盡如人意。 PostgreSQL是針對事務型公司專屬應用程式的嚴肅、功能完善的資料庫，支援強ACID特性和很多資料完整性檢查。他們二者都在某些任務上具有很快的速 度，MySQL不同儲存引擎的行為有較大差別。MyISAM引擎是最快的，因為它只執行很少的資料完整性檢查，適合於後端讀操作較多的網站，不過對於包含 敏感性資料的讀/寫資料庫來說就是個災難了，因為MyISAM表最終可能會損壞。MySQL提供了修複MySQL表的工具，不過對于敏感資料來說，支援 ACID特性的InnoDB則是個更好的選擇。

   與之相反，PostgreSQL則是個只有單一儲存引擎的完全整合的資料庫。你可以通過調整postgresql.conf檔案的參數來改進效能，也可以調整查詢與事務。PostgreSQL文檔對於效能調優提供了非常詳盡的介紹。

   MySQL與PostgreSQL都是高可配置的，並且可以針對不同的任務進行相應的最佳化。他們都支援通過擴充來添加額外的功能。

4. 小結
   PostgreSQL， 它的可靠性好，在保護資料方面很擅長，而且是個社區項目，不會陷入廠商的牢籠之中。MySQL更加靈活，提供了更多選項來針對不同的任務進行裁剪，上手比較快。

空間資料儲存管理現狀

由於空間資料具有空間位置、非結構化、空間關係、分類編碼、海量資料等特徵，一般的商用資料庫管理系統難以滿足要求。

為了提高資料庫管理系統（DBMS）對空間資料的管理能力，國內外先後出現過:檔案與關聯式資料庫混合管理系統、全關係型空間資料庫管理系統、關係型資料庫+空間資料引擎、擴充項物件關係型資料庫管理系統，以及物件導向空間資料庫管理系統等多種解決方案。目前，國內外較為流行的主要集中在“關係型資料庫+空間資料引擎”、“擴充項物件關係型資料庫”兩方面。

因此，關係型資料庫僅僅是存放空間資料的容器，而空間資料引擎則是空間資料進出該容器的轉換通道。這類系統的典型代表有ESRI的ArcSDE和MapInfo的SpatialWare。其優點是，訪問速度快，支援通用的關聯式資料庫管理系統，空間資料按BLOB存取，可跨資料庫平台，與特定GIS平台結合緊密，應用靈活。其缺點主要表現為，空間操作和處理無法在資料庫核心中實現，資料模型較為複雜，擴充SQL比較困難，不易實現資料共用與互操作。

擴充項物件關係型資料庫管理系統是由資料庫廠商研發的管理空間資料一種解決方案。由於關係型資料庫難以管理非結構化資料（也包括空間資料），資料庫廠商借鑒物件導向技術，發展了對象關係型資料庫管理系統。

此系統支援抽象的資料類型（ADT）及其相關操作的定義;使用者利用這種能力可以增加空間資料類型及相關函數，從而將空間資料類型與函數就從中介軟體（空間資料引擎）轉移到了資料庫管理系統中，客戶也不必採用空間資料引擎的專用介面進行編程，而是使用增加了的空間資料類型和函數的標準擴充型SQL語言來操作空間資料。

這類支援空間擴充的產品有Oracle的Oracle Spatial, IBM的DB2 Spatial Extender, Informix 的Spatial DataBlade。其優點是，空間資料的管理與通用資料庫系統融為一體，空間資料按對象存取，可在資料庫核心中實現空間操作和處理，擴充SQL比較方便，較易實現資料共用與互操作。其缺點主要表現為，實現難度大，壓縮資料比較困難，目前的功能和效能與第一類系統尚存在差距。

目前開源空間資訊軟體領域最效能優秀的資料庫軟體當屬PostgreSQL資料庫，而構建在其上的空間對象擴充模組PostGIS則使得其成為一個真正的大型空間資料庫。PostGIS在對象關係型資料庫PostgreSQL上增加了儲存管理空間資料的能力，相當於Oracle的spatial部分。PostGIS最大的特點是符合并且實現了OpenGIS的一些規範，是最著名的開源GIS資料庫。

PostgreSQL + PostGIS ： 擴充項物件關係型資料庫管理系統

在PostgreSQL中已經定義了一些基本的集合實體類型，這些類型包括:點（POINT）、線（LINE）、線段（LSEG）、方形（BOX）、多邊形（POLYGON）和圓（CIRCLE）等;另外，PostgreSQL定義了一系列的函數和操作符來實現幾何類型的操作和運算;同時，PostgreSQL引入空間資料索引R-tree。

儘管在PostgreSQL提供了上述幾項支援空間資料的特性，但其提供的空間特性很難達到GIS的要求，主要表現在:缺乏複雜的空間類型;沒有提供空間分析;沒有提供投影變換功能。為了使得PostgreSQL更好的提供空間資訊服務，PostGIS應運而生。

1. PostGIS 的 作用
   PostGIS是對象關係型資料庫系統PostgreSQL的一個擴充，PostGIS提供如下空間資訊服務功能:空間對象、空間索引、空間操作函數和空間操作符。同時，PostGIS遵循OpenGIS的規範。

2. PostGIS 的特性
   PostGIS支援所有的空間資料類型，這些類型包括:點（POINT）、線（LINESTRING）、多邊形（POLYGON）、多點（MULTIPOINT）、多線（MULTILINESTRING）、多多邊形（MULTIPOLYGON）和集合對象集（GEOMETRYCOLLECTION）等。PostGIS支援所有的對象表達方法，比如WKT和WKB。

   PostGIS支援所有的資料存取和構造方法，如GeomFromText()、AsBinary()，以及GeometryN()等。

   PostGIS提供簡單的空間分析函數（如Area和Length）同時也提供其他一些具有複雜分析功能的函數，比如Distance。

   PostGIS提供了對於中繼資料的支援，如GEOMETRY_COLUMNS和SPATIAL_REF_SYS，同時，PostGIS也提供了相應的支援函數，如AddGeometryColumn和DropGeometryColumn。

   PostGIS提供了一系列的二元謂詞（如Contains、Within、Overlaps和Touches）用於檢測空間對象之間的空間關係，同時返回布爾值來表徵對象之間符合這個關係。

   PostGIS提供了空間操作符（如Union和Difference）用於空間資料操作。比如，Union操作符融合多邊形之間的邊界。兩個交迭的多邊形通過Union運算就會形成一個新的多邊形，這個新的多邊形的邊界為兩個多邊形中最大邊界。

3. PostGIS的額外功能
   資料庫座標變換：
   資料庫中的幾何類型可以通過Transform函數從一種投影系變換到另一種投影系中。

   球體長度運算：
   儲存在普通地理座標系中的集合類型如果不進行座標變換是無法進行程度運算的，OpenGIS所提供的座標變換使得積累類型的程度計算變成可能。

   三維的幾何類型：
   SFSQL規範只是針對二維集合類型。OpenGIS提供了對三維集合類型的支援，具體是利用輸入的集合類型維數來決定輸出的表現方式。例如，即便所有幾何對象內部都以三維形式儲存，純粹的二維交叉點通常還是以二維的形式返回。此外，還提供幾何對象在不同維度間轉換的功能。

   空間聚集合函式：
   在資料庫中，聚集合函式是一個執行某一屬性列所有資料操作的函數。比如Sum和Average，Sum是求某一關係屬性列的資料總和，Average則是求取某一關係屬性列的資料平均值。與此對應，空間聚集合函式也是執行相同的操作，不過操作的對象是空間資料。例如聚集合函式Extent返回一系列要素中的最大的包裹矩形框，如“SELECT EXTENT(GEOM) FROM ROADS”這條SQL語句的執行結果是返回ROADS這個資料表中所有的包裹矩形框。

   柵格資料類型：
   PostGIS通過一種新的資料類型片，提供對於大的柵格資料對象的儲存。片由以下幾個部分組成:包裹矩形框、SRID、類型和一個位元組序列。通過將片的大小控制在資料庫頁值（32×32）以下，使得快速的隨即訪問變成可能。一般大的圖片也是通過將其切成32×32像素的片然後再儲存在資料庫中的。

總結：

MySQL和PostgreSQL同屬開源關係型資料庫，同時都支援標準的SQL，在學習成本上都差不多。 在靈活性上MySQL略佔上風，在作為單純地屬性資料的查詢讀取上MySQL 速度很快，對空間資料的讀取支援也很友好， 但如果對空間資料進行其他動作，MySQL在這一點上是很難做到的。

PostgreSQL是完全免費，同時有很多社區在對其進行維護，PostgreSQL在很多方面都比MySQL強，如複雜SQL的執行、預存程序、觸發器、索引。同時PostgreSQL是多進程的， 而MySQL是線程的，雖然並發不高時，MySQL處理速度快，但當並發高的時候，對於現在多核的單台機器上， MySQL的線程無法充分利用CPU的能力，導致MySQL的總體處理效能不如PostgreSQL， 所以綜合來看PostgreSQL在穩定性和效能上是優於MySQL的。同時有PostGIS作為 PostgreSQL單獨的擴充項物件，對空間資料有很強大的支援能力，同時對空間資料的分析能力也十分出色，也十分符合當前 採用‘擴充項物件關係型資料庫管理系統’的方式來進行空間資料管理的趨勢。

MySQL同PostgreSQL + PostGIS對比