MySQL和Postgres的比較

我使用哪個數據庫：PostgreSQL 或 MySQL?這個老生常談的問題已經困惑了開發者至少兩年了。我全面地接觸了這兩個數據庫系統（MYSQL有一年，而Postgres有二年了），而且對MySQL網站提出的關于兩者之間的差異非常好奇。

1999年9月，當我們正開始對SoureForge架設基礎的時候，我對這兩個數據庫作了性能測試。當時，盡管我已經習慣于使用Postgres完成所有工作，但兩者的性能差異是如此之明顯以至與我們不得不采用MySQL。其他的慣用MySQL的開發者也擁護這個決定。

在當時，這個性能測試要勝于平時任何人為的測試。我想看看這兩個數據庫在網站某一具體的網頁上應用的情況。所提及的這個網頁是SourForge討論區。它包括3個表的簡單相關結合。每個表有20-30,000行數據。以及遞歸的實現和嵌套的信息，所以數據庫在此頁上的應用確實是一個瓶頸。

開始實行，我從庫中卸下了真實的數據。編輯了SQL表，并且將其導入MySQL 3.22.30和PostgreSQL7.0.2，運行在擁有1G RAM 的VA Linux quad-xeon 4100 server上的Red Hat Linux 6.2上。

使我陷入的第一個問題是，在Postgres中，有一個不可思議的問題：每行數據最多只能有8K。在信息公告板上，你時常會超過每行8K的限制。所以Postgres在導入數據時阻塞。為了能繼續使用，我只得將數據的"body"拋棄，并且重新導入數據。Postgres 開發小組已經意識到了這個限制，并且在7.1的版本中修改了，而且他們告訴你，你可通過重新編譯Postgres使得它支持32K/每行，盡管這樣做會使整個系統的性能受到影響。

在這一點上，我又陷入了Postgres另一個小問題上，它的"serial"數據類型（等價于MYSQL的auto_increment)會造成一個"sequence"：當它的雙親表被拋棄時，它本身不被拋棄。因此，當你想重新創建這個表時，會引起一個名字沖突。有許多新的用戶被這個問題所困惑，所以這些問題使Postgres在測試中失去一些優勢。相反，MySQL聰明到它可以在你導入數據的時候對auto_increment進行自動增量，然而Postgres的sequence 不可以在你導入數據時重置，從而引起新插入操作的失敗。

方法

為了盡可能地真實，我從網站上選擇了實際的網頁，并且使它輕巧地交叉于MySQL和Postgres之間。這基本上意味著所有mysql_query()調用將被pg_exec()替換。這個頁包括許多選取與連接操作，與典型的網站做的差不多。

一旦這個測試頁運行和調試，我便使用"AB"，就是"Apache Benchmarking"應用集，從我的工作站通過我的100M局域網到達quad-xeon服務器。為了在負載的數量上得到一個概念，我使用AB進行各種并發連接的測試，從10-120,當離開了這些頁后，這個數穩定在了1000上。

為了更加地接近實際應用，我在腳本中安置了一個隨機數生成器，它插入頁面的10%的數據到數據庫中。在PHPBuilder中所有討論區頁面有10%是作為發送新信息的。

進一步地，就以上所說，我都使用從現成的數據中提取的數據，你得不出比此更真實的情況了。

數值

實際測試結果：

并發連接 w/p:

10 客戶 - 10.27 頁/秒333.69 kb/s

20 客戶 - 10.24 頁/秒332.86 kb/s

30 客戶 - 10.25 頁/秒333.01 kb/s

40 客戶 - 10.0 頁/秒324.78 kb/s

50 客戶 - 10.0 頁/秒324.84 kb/s

75 客戶 - 9.58 頁/秒311.43 kb/s

90 客戶 - 9.48 頁/秒307.95 kb/s

100 客戶 - 9.23 頁/秒300.00 kb/s

110 客戶 - 9.09 頁/秒295.20 kb/s

120 客戶 - 9.28 頁/秒295.02 kb/s (2.2% 失敗)

 

并發連接w/10% 插入:

30 客戶 - 9.97 頁/秒324.11 kb/s

40 客戶 - 10.08 頁/秒327.40 kb/s

75 客戶 - 9.51 頁/秒309.13 kb/s

 

MySQL

 

并發連接測試 w/p:

30 客戶 - 16.03 頁/秒 521.01 kb/s

40 客戶 - 15.64 頁/秒 507.18 kb/s

50 客戶 - 15.43 頁/秒 497.88 kb/s

75 客戶 - 14.70 頁/秒 468.64 kb/s

90 - mysql 崩潰

110 - mysql 崩潰

120 - mysql 崩潰

 

 

并發連接測試w/op:

10 客戶 - 16.55 頁/秒537.63 kb/s

20 客戶 - 15.99 頁/秒519/51 kb/s

30 客戶 - 15.55 頁/秒505.19 kb/s

40 客戶 - 15.46 頁/秒490.01 kb/s 47 失敗

50 客戶 - 15.59 頁/秒482.24 kb/s 82 失敗

75 客戶 - 17.65 頁/秒452.08 kb/s 363 失敗

90 客戶 - mysql 崩潰

 

并發連接 w/10% 插入運算:

20 客戶 - 16.37 頁/秒531.79 kb/s

30 客戶 - 16.15 頁/秒524.64 kb/s

40 客戶 - 22.04 頁/秒453.82 kb/sec 378 失敗

我覺得測試結果中最有意思的事情是觀察沒有出錯情況下已經裝入運行的Postgres能負載多少。實際上，Postgres在沒有錯誤的情況下負載好像要比MySQL高3倍。MySQL在負有40-50個并發連接時開始失常，而在沒有其它影響的情況下，Postgres能使120個并發連接運行自如。我的猜測是：如果有足夠的內存與夠快的CPU，Postgres能夠正常負載的遠遠不止120個并發連接。

表面上看，這顯然是Postgres的一個巨大的勝利，但是如果你仔細觀察一下更多細節的測試結果，可以發現Postgres生成每頁的時間要長2-3倍，所以它需要擴展負載至少2-3倍則剛好能與MySQL打成平手。所以按照不出錯誤的情況下，兩者并發生成盡可能多的頁，則他們之間幾乎沒有什么差別。按照某一時刻生成一頁的情況看，MySQL確實快2-3倍。

另外一個有趣的事情是：在以上敘述的"10%插入運算"的測試中，MySQL在分解運算方面快一些。研究表明，當插入運算操作發生時，MySQL鎖定整個表，而Postgres有一個美妙的"better than row-level locking"的機制。這個不同很快引起了MySQL并發連接的堆積，從而導致崩潰。同樣的，如果你正在從數據庫中進行大量的選中操作，而另外的進程正在對表進行插入操作。Postgres可以完全地不受干擾，而MySQL會堆積連接直到像紙牌做的房子一樣崩潰。

你會發現在PHP中的穩定的連接并不有助于MySQL多少，相反卻明顯地有助于Postgres。實際上，其于穩定的連接的Postgres測試要快30%。這就告訴我們，Postgres在開放性連接與確認進程上需要占用大量的系統開銷。其中有一些是Linux的錯誤和它的和僵硬的進程安排。盡管如此，MySQL并不再乎你如何看待它。

MySQL

MySQL的優缺點已經為許多人所知道：它是一個快速的、輕量級的數據庫，但是基本上可以為大多數的網站服務得很好。

無論如何，如果你計劃在一個高流量的站點（說明，每天生成多于500,000頁）上，那么忘了MySQL吧。因為它往往會崩潰或者在裝入運行后死機。任何曾經訪問過Slashdot的人都可以證明它的弱點。（mod_perl and MySQL)

話說回來，MySQL證明了可使極大多數網站的頁生成速度最高在每秒15頁。如果你經常地運行在每秒15頁之上，那么你將會非常高興地支付更大的服務器或者Oracle license的費用。

優勢

明顯地，MySQL勝過Postgres的優勢就在于性能。而且在它的發行版中，還具有一些強有力的管理工具。（其中一些工具允許你察看進程并且可以在程度運行時進行動態調試。）比如熱備份，文件毀壞恢復工具等。

我也非常熱衷于MySQL的命令行工具。你可以使用描述和察看命令來觀察數據庫與表的結構。而Postgres的命令則明顯地少(/d可以顯示一張示范表）

局限性

從數據庫行家聽說的第一件事就是MySQL缺乏transactions,rollbacks, 和subselects的功能。如果你計劃使用MySQL寫一個關于銀行、會計的應用程序，或者計劃維護一些隨時需要線性遞增的不同類的計數器，你將缺乏transactions功能。在現有的發布版本的MySQL下，請不要有任何的這些想法。（請注意，MySQL的測試版3.23.x系列現在已經支持transactions了）。

在非常必要的情況下，MySQL的局限性可以通過一部分開發者的努力得到克服。在MySQL中你失去的主要功能是subselect語句，而這正是其它的所有數據庫都具有的。換而言之，這個失去的功能是一個痛苦，但是它可以被克服。

穩定性

MySQL在長期使用的穩定性上失了分。比如，MySQL在運行了30-60天左右的時間后，沒有理由地就放棄了隨機ghost。許多開發者使用"statically"來編譯MySQL就是為了這個原因，而且這個方法幫助了許多人。這個問題也可以通過設置一個好的調度程序讓MySQL每月被殺死并重起一次來克服。這并不是一個完全可取的方法，但至少也是一個辦法。

MySQL在守護進程的健壯性上也失了分，但這個缺點被MySQL從來不會出現不可靠的數據文件而彌補。最后一件讓人擔心的事就是你重要的數據文件會不期地出錯，而MySQL卻很好地解決了這一點。在一整年的運行MySQL之后，這從未見過數據文件或索引文件輕易地出錯。而在同樣的時間內，我已經為一些Postgres數據庫做了2-3次的恢復了。（記住，備份永遠是你最好的朋友，這點可以從PHPBUILDER數據的崩潰看出）

PostgresSQL

Postgres的測試結果可能會使許多人感到驚奇，盡管Postgres在一些網站開發者中聲譽并不怎么好（最初的Postgres的發布版本中除了對落后的性能以外，而對其他部分大肆散布謠言）。但根據我的經驗和測試，這些不良聲譽并不完全有理由。實際上，PostgresSQL可以承受3倍于MySQL的負載而且沒有任何錯誤，當然在相同的硬件和系統環境下。

Postgres一般運行在 10頁/每秒的速度之上，足夠為 400,000頁/每日的流量服務，假定一個規則的流量曲線峰值2倍于最小值。這是一個可怕的數量，它遠遠地超過了大多數人要在他們的網站上看到的數量。此外，網站的大多數頁沒有這次測試中的那么復雜。正如MySQL的情況一樣，如果你可以超過這個最高限度，你將會非常樂意地花錢添置硬件。由于Postgres的結構，如果你增加處理器和內存，那么它的性能可以繼續增長。

優勢

Postgres有一些非常高級的功能。而其中的大多數功能我并不使用，它們被真正的數據庫高手所采用。許多開發者不利用這些功能，從而無法實現他們的想法。

例如，如果你正需要做多次的更新/插入/刪除操作，你可以利用transaction。比如，你要在你的用戶列表中插入一個新的用戶，同時在另一個表中，要插入一行，而且也要更新別處的一個標志。假如這樣，如果第一次插入成功，而第二次失敗，你怎么辦？在Postgres中，你可以回退全部操作，而觀察出錯的緣由。而在MySQL中，出錯時你會非常緊張，除非你安排了一些邏輯可以控制這個局面。在實際應用中，第一個問題不會出錯，除非你是一個糟糕的程序員。而如果第二個問題出錯，那么這個結果并不非常嚴重。（除非這是一個不允許出錯的而冒險的程序，比如會計、銀行業、評論界的應用程序。）

無論如何，在Postgres7。0+中現在已經支持了外部關鍵字，那就意味著當你插入一行，數據庫會做一些公正而合理的校驗檢察。同樣，如果你要刪除當前表一行，而有另一個表依賴于當前表，數據庫則不會讓你操作。我喜歡這個想法并且能預見如何利用這個功能重寫整個網站。

Triggers 和Views是能夠在Postgres中，而不是在MySQL中使用的有趣而有用的工具。盡管我都沒有使用過，但我可以想象如果我重新設計SourceForge時將如何地頻繁利用views。

局限性
Postgres的主要局限性并不是它的性能（因為大多數網站將永遠不會陷入這個問題）。但是由于編碼硬化的限制，像每行8k的限制（日期會回到以前的時期）。當我使用Postgres設計Geocrawler.com，我不得不將大的e-mail分成每8k一塊以繞過這個僵硬的限制。在默認情況下，Postgres被編譯成只支持32個連接，這是不足以用來作高流量網站應用的，特別是考慮到Postgres生成每頁的速度要比MySQL慢。

另一個限制可警惕許多PHP的用戶：Postgres沒有與MySQL的mysql_insertid()等價的函數調用。如果在MySQL的數據庫中插入一行數據，MySQL將返回這行主關鍵字的ID。而在Postgres中完成這樣一個操作需要繞許多圈子，這是一件非常頭痛的事，而且如果用的多可能會降低效率。

穩定性

Postgres在長時間運行的情況下運行流暢而不出錯。我的Postgres 6.5.3裝在又老又累的PowerMac 8500上，無錯運行已經有90天了；而且每天生成50-100,000頁。當Postgres裝入運行，不會退出，而在壓力下不需要ghost。

Postgres有一個問題是當你確實發生問題時，一般它就真壞了。在一些舊的Postgres6.4.x中，我還發現了更加嚴重的問題：許多重復而相同的數值被插入到同一個主關鍵字中（這是不允許在任何情況下出現的）

Postgres還有另一問題就是會出現令人緊張的不完整的索引和表，你不能略過或擺脫它。我在Postgres7中沒有再發現過它，但我沒有對其全面使用，所以無法知道更多。

結論

這次測試幾乎證實了我所知道的：兩個數據庫系統都可以為大多數網站非常好地服務。它們與桌面數據庫，像FileMaker和MS Aclearcase/" target="_blank" >ccess相比，非常明顯地快。Postgres 和 MySQL都是自由而免費的，而且有積極的開發團體所支持。

如果要在兩者間選擇一個，你首先需要明白你面臨的限制并且知道你是否需要Postgres的transaction的支持或者MySQL的large-text-area支持。你可能都要，這種情況下，你只有等到將來的某一天，兩者推出新的穩定發布版。

需要提出的一件非常有趣的事是：這兩個數據庫似乎會在某處重合，就是當MySQL具有transaction的支持并且慢慢加入一些新的功能如：subselect，而Postgres在執行性能和穩定性上有所改進時。

最后，作為數據庫高手選擇，Postgres應當非常靈活。外部關鍵字，views,subselects,和transactions都非常地棒--如果你需要并且應用它們。如果你不需要或不使用這些功能，你最好使用MySQL，而利用它優越的性能。

原文轉自：http://www.kjueaiud.com