PostgreSQL vs. MySQL

Dodd發表於2013-06-19
MySql

     談到PostgreSQL和MySQL的比較，總會讓雙方支援陣營騷動和興奮，它們都是技術過硬，安裝基礎很好的開源資料庫，在現實中工作中，要選擇一個佔絕對優勢的開源資料庫基本上是不靠譜的，不管是PostgreSQL還是MySQL，都不能聲稱自己比對方更優秀，對使用者來說，只有合適的，沒有最優秀的。

      PostgreSQL被譽為市場上最先進的開源資料庫。資料一致性和完整性等性質都是PostgreSQL的高度優先事項。

      MySQL被譽為是最流行的開源資料庫。最初MySQL就被設計為快速的Web伺服器後臺，是Web系統的理想資料庫之選，且文件資源豐富。 

     PostgreSQL許可是仿照BSD許可模式的，它允許修改程式碼，並根據修改者自願是否以開源形式再發布，這種開放式許可對想使用PostgreSQL作為他們解決方案一部分的軟體廠商來說是最理想的，因為PostgreSQL許可不強制衍生解決方案也開源，軟體廠商可以選擇開放他們的原始碼，也可以不開放。MySQL是通過GNU GPL共享，並由Oracle控制，GNU GPL更加開放，它鼓勵免費共享程式碼，它防止了採用GNU GPL共享的程式碼後，也被迫採用源作者GNU GPL許可共享的缺陷。

    以下是使用MySQL或者PostgreSQL具有代表性的網站（MySQL的確更流行更出名）：

　　MySQL:

  • Slashdot(資訊科技網站)
  • Twitter
  • Facebook
  • Wikipedia(維基百科)
  • google的一些應用
  • 阿里巴巴的很多應用
  • 很多很多網站的很多很多應用

　　PostgreSQL:

  • Yahoo執行一個多拍位元組修改後的PostgreSQL資料庫處理每天數十億事件
  • Reddit(新聞網站)
  • Disqus(社會性媒體)
二者比較如下：

特性MySQLPostgreSQL
例項通過執行 MySQL 命令（mysqld）啟動例項。一個例項可以管理一個或多個資料庫。一臺伺服器可以執行多個 mysqld 例項。一個例項管理器可以監視 mysqld 的各個例項。
通過執行 Postmaster 程式（pg_ctl）啟動例項。一個例項可以管理一個或多個資料庫，這些資料庫組成一個叢集。叢集是磁碟上的一個區域，這個區域在安裝時初始化並由一個目錄組成，所有資料都儲存在這個目錄中。使用 initdb 建立第一個資料庫。一臺機器上可以啟動多個例項。
資料庫資料庫是命名的物件集合，是與例項中的其他資料庫分離的實體。一個 MySQL 例項中的所有資料庫共享同一個系統編目。資料庫是命名的物件集合，每個資料庫是與其他資料庫分離的實體。每個資料庫有自己的系統編目，但是所有資料庫共享 pg_databases。
資料緩衝區通過 innodb_buffer_pool_size 配置引數設定資料緩衝區。這個引數是記憶體緩衝區的位元組數，InnoDB 使用這個緩衝區來快取表的資料和索引。在專用的資料庫伺服器上，這個引數最高可以設定為機器實體記憶體量的 80%。Shared_buffers 快取。在預設情況下分配 64 個緩衝區。預設的塊大小是 8K。可以通過設定 postgresql.conf 檔案中的 shared_buffers 引數來更新緩衝區快取。
資料庫連線客戶機使用 CONNECT 或 USE 語句連線資料庫，這時要指定資料庫名，還可以指定使用者 id 和密碼。使用角色管理資料庫中的使用者和使用者組。客戶機使用 connect 語句連線資料庫，這時要指定資料庫名，還可以指定使用者 id 和密碼。使用角色管理資料庫中的使用者和使用者組。
身份驗證MySQL 在資料庫級管理身份驗證。 基本只支援密碼認證。PostgreSQL 支援豐富的認證方法：信任認證、口令認證、Kerberos 認證、基於 Ident 的認證、LDAP 認證、PAM 認證
加密可以在表級指定密碼來對資料進行加密。還可以使用 AES_ENCRYPT 和 AES_DECRYPT 函式對列資料進行加密和解密。可以通過 SSL 連線實現網路加密。可以使用 pgcrypto 庫中的函式對列進行加密/解密。可以通過 SSL 連線實現網路加密。
審計可以對 querylog 執行 grep。可以在表上使用 PL/pgSQL 觸發器來進行審計。
查詢解釋使用 EXPLAIN 命令檢視查詢的解釋計劃。使用 EXPLAIN 命令檢視查詢的解釋計劃。
備份、恢復和日誌InnoDB 使用寫前（write-ahead）日誌記錄。支援線上和離線完全備份以及崩潰和事務恢復。需要第三方軟體才能支援熱備份。在資料目錄的一個子目錄中維護寫前日誌。支援線上和離線完全備份以及崩潰、時間點和事務恢復。 可以支援熱備份。
JDBC 驅動程式可以從 參考資料 下載 JDBC 驅動程式。可以從 參考資料 下載 JDBC 驅動程式。
表型別取決於儲存引擎。例如，NDB 儲存引擎支援分割槽表，記憶體引擎支援記憶體表。支援臨時表、常規表以及範圍和列表型別的分割槽表。不支援雜湊分割槽表。 由於PostgreSQL的表分割槽是通過表繼承和規則系統完成了，所以可以實現更復雜的分割槽方式。
索引型別取決於儲存引擎。MyISAM：BTREE，InnoDB：BTREE。支援 B-樹、雜湊、R-樹和 Gist 索引。
約束支援主鍵、外來鍵、惟一和非空約束。對檢查約束進行解析，但是不強制實施。支援主鍵、外來鍵、惟一、非空和檢查約束。
儲存過程和使用者定義函式支援 CREATE PROCEDURE 和 CREATE FUNCTION 語句。儲存過程可以用 SQL 和 C++ 編寫。使用者定義函式可以用 SQL、C 和 C++ 編寫。沒有單獨的儲存過程，都是通過函式實現的。使用者定義函式可以用 PL/pgSQL（專用的過程語言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 編寫。
觸發器支援行前觸發器、行後觸發器和語句觸發器，觸發器語句用過程語言複合語句編寫。支援行前觸發器、行後觸發器和語句觸發器，觸發器過程用 C 編寫。
系統配置檔案my.confPostgresql.conf
資料庫配置my.confPostgresql.conf
客戶機連線檔案my.confpg_hba.conf
XML 支援有限的 XML 支援。有限的 XML 支援。
資料訪問和管理伺服器OPTIMIZE TABLE —— 回收未使用的空間並消除資料檔案的碎片
myisamchk -analyze —— 更新查詢優化器所使用的統計資料（MyISAM 儲存引擎）
mysql —— 命令列工具
MySQL Administrator —— 客戶機 GUI 工具		Vacuum —— 回收未使用的空間
Analyze —— 更新查詢優化器所使用的統計資料
psql —— 命令列工具
pgAdmin —— 客戶機 GUI 工具
併發控制支援表級和行級鎖。InnoDB 儲存引擎支援 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 語句在事務級設定隔離級別。支援表級和行級鎖。支援的 ANSI 隔離級別是 Read Committed（預設 —— 能看到查詢啟動時資料庫的快照）和 Serialization（與 Repeatable Read 相似 —— 只能看到在事務啟動之前提交的結果）。使用 SET TRANSACTION 語句在事務級設定隔離級別。使用 SET SESSION 在會話級進行設定。 



MySQL相對於PostgreSQL的劣勢：
MySQL
PostgreSQL
最重要的引擎InnoDB很早就由Oracle公司控制。目前整個MySQL資料庫都由Oracle控制。
BSD協議，沒有被大公司壟斷。
對複雜查詢的處理較弱，查詢優化器不夠成熟
很強大的查詢優化器，支援很複雜的查詢處理。
只有一種表連線型別:巢狀迴圈連線(nested-loop),不支援排序-合併連線(sort-merge join)與雜湊連線(hash join)。
都支援
效能優化工具與度量資訊不足

提供了一些效能檢視，可以方便的看到發生在一個表和索引上的select、delete、update、insert統計資訊，也可以看到cache命中率。網上有一個開源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式儲存。也就是說表都是索引組織表。這一般要求主鍵不能太長而且插入時的主鍵最好是按順序遞增，否則對效能有很大影響。

不存在這個問題。

大部分查詢只能使用表上的單一索引;在某些情況下，會存在使用多個索引的查詢,但是查詢優化器通常會低估其成本,它們常常比表掃描還要慢。

不存在這個問題

表增加列，基本上是重建表和索引，會花很長時間。

表增加列，只是在資料字典中增加表定義，不會重建表

儲存過程與觸發器的功能有限。可用來編寫儲存過程、觸發器、計劃事件以及儲存函式的語言功能較弱

除支援pl/pgsql寫儲存過程，還支援perl、python、Tcl型別的儲存過程：pl/perl，pl/python，pl/tcl。

也支援用C語言寫儲存過程。

不支援Sequence。

支援

不支援函式索引，只能在建立基於具體列的索引。

不支援物化檢視。


支援函式索引，同時還支援部分資料索引，通過規則系統可以實現物化檢視的功能。

執行計劃並不是全域性共享的, 僅僅在連線內部是共享的。

執行計劃共享

MySQL支援的SQL語法(ANSI SQL標準)的很小一部分。不支援遞迴查詢、通用表表示式（Oracle的with 語句）或者視窗函式（分析函式）。



都 支援

不支援使用者自定義型別或域(domain)

支援。

對於時間、日期、間隔等時間型別沒有秒以下級別的儲存型別

可以精確到秒以下。

身份驗證功能是完全內建的，不支援作業系統認證、PAM認證，不支援LDAP以及其它類似的外部身份驗證功能。

支援OS認證、Kerberos 認證 、Ident 的認證、LDAP 認證、PAM 認證

不支援database link。有一種叫做Federated的儲存引擎可以作為一箇中轉將查詢語句傳遞到遠端伺服器的一個表上,不過,它功能很粗糙並且漏洞很多

有dblink，同時還有一個dbi-link的東西，可以連線到oracle和mysql上。

Mysql Cluster可能與你的想象有較大差異。開源的cluster軟體較少。

複製(Replication)功能是非同步的,並且有很大的侷限性.例如,它是單執行緒的(single-threaded),因此一個處理能力更強的Slave的恢復速度也很難跟上處理能力相對較慢的Master.

有豐富的開源cluster軟體支援。

explain看執行計劃的結果簡單。

explain返回豐富的資訊。

類似於ALTER TABLE或CREATE TABLE一類的操作都是非事務性的.它們會提交未提交的事務，並且不能回滾也不能做災難恢復

DDL也是有事務的。



PostgreSQL主要優勢：
　　1. PostgreSQL完全免費，而且是BSD協議，如果你把PostgreSQL改一改，然後再拿去賣錢，也沒有人管你，這一點很重要，這表明了PostgreSQL資料庫不會被其它公司控制。oracle資料庫不用說了，是商業資料庫，不開放。而MySQL資料庫雖然是開源的，但現在隨著SUN被oracle公司收購，現在基本上被oracle公司控制，其實在SUN被收購之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的資料都是放在InnoDB引擎中的，反正我們公司都是這樣的。所以如果MySQL的市場範圍與oracle資料庫的市場範圍衝突時，oracle公司必定會犧牲MySQL，這是毫無疑問的。 
　　2. 與PostgreSQl配合的開源軟體很多，有很多分散式叢集軟體，如pgpool、pgcluster、slony、plploxy等等，很容易做讀寫分離、負載均衡、資料水平拆分等方案，而這在MySQL下則比較困難。
      3. PostgreSQL原始碼寫的很清晰，易讀性比MySQL強太多了，懷疑MySQL的原始碼被混淆過。所以很多公司都是基本PostgreSQL做二次開發的。
      4. PostgreSQL在很多方面都比MySQL強，如複雜SQL的執行、儲存過程、觸發器、索引。同時PostgreSQL是多程式的，而MySQL是執行緒的，雖然併發不高時，MySQL處理速度快，但當併發高的時候，對於現在多核的單臺機器上，MySQL的總體處理效能不如PostgreSQL，原因是MySQL的執行緒無法充分利用CPU的能力。
     目前只想到這些，以後想到再新增，歡迎大家拍磚。


PostgreSQL與oracle或InnoDB的多版本實現的差別

PostgreSQL與oracle或InnoDB的多版本實現最大的區別在於最新版本和歷史版本是否分離儲存，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分離了資料,索引本身沒有分開。
   PostgreSQL的主要優勢在於：
   1. PostgreSQL沒有回滾段，而oracle與innodb有回滾段，oracle與Innodb都有回滾段。對於oracle與Innodb來說，回滾段是非常重要的，回滾段損壞，會導致資料丟失，甚至資料庫無法啟動的嚴重問題。另由於PostgreSQL沒有回滾段，舊資料都是記錄在原先的檔案中，所以當資料庫異常crash後，恢復時，不會象oracle與Innodb資料庫那樣進行那麼複雜的恢復，因為oracle與Innodb恢復時同步需要redo和undo。所以PostgreSQL資料庫在出現異常crash後，資料庫起不來的機率要比oracle和mysql小一些。
   2. 由於舊的資料是直接記錄在資料檔案中，而不是回滾段中，所以不會象oracle那樣經常報ora-01555錯誤。
   3. 回滾可以很快完成，因為回滾並不刪除資料，而oracle與Innodb，回滾時很複雜，在事務回滾時必須清理該事務所進行的修改，插入的記錄要刪除，更新的記錄要更新回來(見row_undo函式)，同時回滾的過程也會再次產生大量的redo日誌。
   4. WAL日誌要比oracle和Innodb簡單，對於oracle不僅需要記錄資料檔案的變化，還要記錄回滾段的變化。
   PostgreSQL的多版本的主要劣勢在於：
   1、最新版本和歷史版本不分離儲存，導致清理老舊版本需要作更多的掃描，代價比較大，但一般的資料庫都有高峰期，如果我們合理安排VACUUM，這也不是很大的問題，而且在PostgreSQL9.0中VACUUM進一步被加強了。
　　2、由於索引中完全沒有版本資訊，不能實現Coverage index scan，即查詢只掃描索引，直接從索引中返回所需的屬性，還需要訪問表。而oracle與Innodb則可以;


程式模式與執行緒模式的對比
PostgreSQL和oracle是程式模式，MySQL是執行緒模式。
程式模式對多CPU利用率比較高。
程式模式共享資料需要用到共享記憶體，而執行緒模式資料本身就是在程式空間內都是共享的，不同執行緒訪問只需要控制好執行緒之間的同步。
執行緒模式對資源消耗比較少。
所以MySQL能支援遠比oracle多的更多的連線。
對於PostgreSQL的來說，如果不使用連線池軟體，也存在這個問題，但PostgreSQL中有優秀的連線池軟體軟體，如pgbouncer和pgpool，所以通過連線池也可以支援很多的連線。

堆表與索引組織表的的對比

Oracle支援堆表，也支援索引組織表
PostgreSQL只支援堆表，不支援索引組織表
Innodb只支援索引組織表
索引組織表的優勢：
表內的資料就是按索引的方式組織，資料是有序的，如果資料都是按主鍵來訪問，那麼訪問資料比較快。而堆表，按主鍵訪問資料時，是需要先按主鍵索引找到資料的物理位置。
索引組織表的劣勢：
索引組織表中上再加其它的索引時，其它的索引記錄的資料位置不再是物理位置，而是主鍵值，所以對於索引組織表來說，主鍵的值不能太大，否則佔用的空間比較大。
對於索引組織表來說，如果每次在中間插入資料，可能會導致索引分裂，索引分裂會大大降低插入的效能。所以對於使用innodb來說，我們一般最好讓主鍵是一個無意義的序列，這樣插入每次都發生在最後，以避免這個問題。
由於索引組織表是按一個索引樹，一般它訪問資料塊必須按資料塊之間的關係進行訪問，而不是按物理塊的訪問資料的，所以當做全表掃描時要比堆錶慢很多，這可能在OLTP中不明顯，但在資料倉儲的應用中可能是一個問題。




　 PostgreSQL9.0中的特色功能：   
    PostgreSQL中的Hot Standby功能
    也就是standby在應用日誌同步時，還可以提供只讀服務，這對做讀寫分離很有用。這個功能是oracle11g才有的功能。

    PostgreSQL非同步提交（Asynchronous Commit）的功能：
　 這個功能oracle中也是到oracle11g R2才有的功能。因為在很多應用場景中，當當機時是允許丟失少量資料的，這個功能在這樣的場景中就特別合適。在PostgreSQL9.0中把synchronous_commit設定為false就開啟了這個功能。需要注意的是，雖然設定為了非同步提交，當主機當機時，PostgreSQL只會丟失少量資料，非同步提交併不會導致資料損壞而資料庫起不來的情況。MySQL中沒有聽說過有這個功能。

     PostgreSQL中索引的特色功能：
     PostgreSQL中可以有部分索引，也就是隻能表中的部分資料做索引，create index 可以帶where 條件。同時PostgreSQL中的索引可以反向掃描，所以在PostgreSQL中可以不必建專門的降序索引了。


--轉自ChinaUnix
http://bbs.chinaunix.net/thread-1688208-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7220098/viewspace-764356/，如需轉載，請註明出處，否則將追究法律責任。

相關文章