【美菜網】PostgreSQL與MySQL比較

標籤：

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中可以不必建專門的降序索引了。

【美菜網】PostgreSQL與MySQL比較