[轉帖] “王者對戰”之 MySQL 8 vs PostgreSQL 10

標籤：引入   join   狀態   速度   緩衝   增量   重做日誌   增量備份   org   

原貼地址:https://www.oschina.net/translate/showdown-mysql-8-vs-postgresql-10?lang=chs&page=2#

英文原版地址：http://rachbelaid.com/introduction-to-postgres-physical-storage/

 

既然 MySQL 8 和 PostgreSQL 10 已經發布了，現在是時候回顧一下這兩大開源關係型資料庫是如何彼此競爭的。

在這些版本之前，人們普遍認為，Postgres 在功能集表現更出色，也因其“學院派”風格而備受稱讚，MySQL 則更善長大規模並發讀/寫。

但是隨著它們最新版本的發布，兩者之間的差距明顯變小了。

特性比較

讓我們來看看我們都喜歡談論的“時髦”功能。

 

特性	MySQL 8	PostgreSQL 10
查詢 & 分析
通用資料表運算式 (CTEs)	? New	?
視窗函數	? New	?
資料類型
JSON 支援	? Improved	?
GIS / SRS	? Improved	?
全文檢索索引	?	?
可擴充性
邏輯複製	?	? New
半同步複製	?	? New
聲明式分區	?	? New

 

過去經常會說 MySQL 最適合線上事務，PostgreSQL 最適合分析流程。但現在不是了。

公用表運算式(CTEs) 和視窗函數是選擇 PostgreSQL 的主要原因。但是現在，通過引用同一個表中的 boss_id 來遞迴地遍曆一張僱員表，或者在一個排序的結果中找到一個中值（或 50%），這在 MySQL 上不再是問題。

在 PostgreSQL 中進行複製缺乏配置靈活性，這就是 Uber 轉向 MySQL 的原因。但是現在，有了邏輯複製特性，就可以通過建立一個新版本的 Postgres 並切換到它來實現零停機升級。在一個巨大的時間序列事件表中截斷一個陳舊的分區也要容易得多。

就特性而言，這兩個資料庫現在都是一致的。

 有哪些不同之處呢？

現在，我們只剩下一個問題 —— 那麼，選擇一個而不選另一個的原因是什麼呢？

生態系統是其中一個因素。MySQL 有一個充滿活力的生態系統，包括 MariaDB、Percona、Galera 等等，以及除 InnoDB 以外的儲存引擎，但這也可能是和令人困惑的。Postgres 的高端選擇有限，但隨著最新版本引入的新功能，這會有所改變。

治理是另一個因素。當 Oracle（或最初的 SUN）收購 MySQL時，每個人都擔心他們會毀掉這個產品，但在過去的十年裡，這並不是事實。事實上，在收購之後，發展反倒加速了。而 Postgres 在工作管理和協作社區方面有著豐富的經驗。

基礎架構不會經常改變，雖然近來沒有對這方面的詳細討論，這也是值得再次考慮的。

來複習下：

特性	MySQL 8	PostgreSQL 10
架構	單進程	多進程
並發	多線程	fork(2)
表結構	聚簇索引	堆
頁壓縮	Transparent	TOAST
更新	In-Place / Rollback Segments	Append Only / HOT
記憶體回收	清除線程	自動清空進程
交易記錄	REDO Log (WAL)	WAL
複製日誌	Separate (Binlog)	WAL

  進程vs線程

當 Postgres 派生出一個子進程來建立串連時，每個串連最多可以佔用 10MB。與 MySQL 的線程串連模型相比，它的記憶體壓力更大，在 64 位元平台上，線程的預設堆棧大小為 256KB。（當然，執行緒區域排序緩衝區等使這種開銷變得不那麼重要，即使在不可以忽略的情況下，仍然如此。）

儘管“寫時複製”儲存了一些與父進程共用的、不可變的記憶體狀態，但是當您有 1000 多個並發串連時，基於流程的架構的基本開銷是很繁重的，而且它可能是容量規劃的最重要的因素之一。

也就是說，如果你在 30 台伺服器上運行一個 Rails 應用，每個伺服器都有 16 個 CPU 核心 32 線程，那麼你有 960 個串連。可能只有不到 0.1% 的應用會超出這個範圍，但這是需要記住的。

 聚簇索引 vs 堆表

聚簇索引是一種表結構，其中的行直接嵌入其主鍵的 b 樹結構中。一個（非聚集）堆是一個常規的表結構，它與索引分別填充資料行。

有了聚簇索引，當您通過主鍵尋找記錄時，單次 I/O 就可以檢索到整行，而非叢集則總是需要尋找引用，至少需要兩次 I/O。由於外鍵引用和 JOIN 將觸發主鍵尋找，所以影響可能非常大，這將導致大量查詢。

聚簇索引的一個理論上的缺點是，當您使用二級索引進行查詢時，它需要遍曆兩倍的樹節點，第一次掃描二級索引，然後遍曆叢集索引，這也是一棵樹。

但是，如果按照現代表設計的約定，將一個自動增量整數作為主鍵[1]——它被稱為代理鍵——那麼擁有一個叢集索引幾乎總是可取的。更重要的是，如果您做了大量的 ORDER BY id 來檢索最近的（或最老的）N 個記錄的操作，我認為這是很適用的。

Postgres 不支援叢集索引，而 MySQL(InnoDB)不支援堆。但不管怎樣，如果你有大量的記憶體，差別應該是很小的。

 頁結構和壓縮

Postgres 和 MySQL 都有基於頁面的實體儲存體。(8KB vs 16KB)

PostgreSQL 實體儲存體的介紹

頁結構看起來就像右邊的圖。它包含一些我們不打算在這裡討論的條目，但是它們包含關於頁的中繼資料。條目後面的項是一個數群組識別碼，由指向元組或資料行的（位移、長度）對組成。在 Postgres 中，相同記錄的多個版本可以以這種方式儲存在同一頁面中。

MySQL 的資料表空間結構與 Oracle 相似，它有多個層次，包括層、區段、頁面和行層。

此外，它還有一個用於撤銷的單獨段，稱為“復原段”。與 Postgres 不同的是，MySQL 將在一個單獨的地區中儲存同一記錄的多個版本。

  

如果存在一行必須適合兩個資料庫的單個頁面，，這意味著一行必須小於 8KB。（至少有 2 行必須適合 MySQL 的頁面，恰巧是 16KB/2 = 8KB）

 

那麼當你在一個列中有一個大型 JSON 對象時會發生什麼呢？

Postgres 使用 TOAST，這是一個專用的影子表(shadow table)儲存。當行和列被選中時，大型物件就會被拉出。換句話說，大量的黑盒不會汙染你寶貴的緩衝。它還支援對 TOAST 對象的壓縮。

MySQL 有一個更複雜的特性，叫做透明頁壓縮，這要歸功於高端 SSD 儲存供應商 Fusio-io 的貢獻。它設計目的是為了更好地使用 SSD，在 SSD 中，寫入量與裝置的壽命直接相關。

對 MySQL 的壓縮不僅適用於頁面外的大型物件，而且適用於所有頁面。它通過在疏鬆檔案中使用打孔來實現這一點，這是被 ext4 或 btrfs 等現代檔案系統支援的。

有關更多細節，請參見：在 FusionIO 上使用新 MariaDB 頁壓縮獲得顯著的效能提升。

 更新的開銷

另一個經常被忽略的特性，但是對效能有很大的影響，並且可能是最具爭議的話題，是更新。

這也是Uber放棄Postgres的另一個原因，這激起了許多Postgres的支援者來反駁它。

• MySQL 對Uber可能是合適的, 但是未必對你合適

• 一篇PostgreSQL對Uber的回應 (PDF)

兩者都是MVCC資料庫，它們可以隔離多個版本的資料。

為了做到這一點，Postgres將舊資料儲存在堆中，直到被清空，而MySQL將舊資料移動到一個名為復原段的單獨地區。

在Postgres中，當您嘗試更新時，整個行必須被複製，以及指向它的索引條目也被複製。這在一定程度上是因為Postgres不支援叢集索引，所以從索引中引用的一行的物理位置不是由邏輯鍵抽象出來的。

為瞭解決這個問題，Postgres使用了堆上元組（HOT），在可能的情況下不更新索引。但是，如果更新足夠頻繁（或者如果一個元組比較大），元組的曆史可以很容易地超過8 KB的頁面大小，跨越多個頁面並限制該特性的有效性。修剪和/或磁碟重組的時間取決於啟發學習法解決方案。另外，設定不超過100的填充參數會降低空間效率——這是一種很難在建立表時考慮的折衷方案。

 

這種限制更深入; 因為索引元組沒有關於事務的任何資訊，所以直到9.2之前一直不能支援僅索引掃描。 它是所有主要資料庫（包括MySQL，Oracle，IBM DB2和Microsoft SQL Server）支援的最古老，最重要的最佳化方法之一。 但即使使用最新版本，當有許多UPDATE在可見度映射中設定髒位時，Postgres也不能完全支援僅索引掃描，並且在我們不需要時經常選擇Seq掃描。

在MySQL上，更新發生在原地，舊的行資料被封存在一個稱為復原段的獨立地區中。 結果是你不需要VACUUM，並且提交非常快，而復原相對較慢，這對於大多數用例來說是一個可取的折衷。

它也足夠聰明，儘快清除曆史。 如果事務的隔離等級設定為READ-COMMITTED或更低，則在陳述式完成時清除記錄。

事務記錄的大小不會影響首頁面。 片段化是一個偽命題。 因此，在MySQL上能更好，更可預測整體效能。

 

 Garbage Collection 記憶體回收

在Postgres中VACUUM上開銷很高，因為它在主要工作在堆區，造成了直接的資源競爭。它感覺就像是程式設計語言中的記憶體回收 - 它會擋在路上，並隨時讓你停下來。

為具有數十億記錄的表配置autovacuum仍然是一項挑戰。

在MySQL上清除（Purge）也可能相當繁重，但由於它是在單獨的復原段中使用專用線程啟動並執行，因此它不會以任何方式影響讀取的並發性。即使使用預設配置，變膨脹的復原段使你執行速度減慢的可能性也是很低的。

擁有數十億記錄的繁忙表不會導致MySQL上的曆史資料膨脹，諸如儲存上的檔案大小和查詢效能等事情上幾乎是可以預測的並且很穩定。

 

日誌與副本

Postgres 擁有被稱作 預寫記錄檔 (WAL)的單信源事務曆史。它一直被用於副本，並且稱為邏輯複製的新功能可將二進位內容快速解碼為更易消化的邏輯語句，從而可對資料進行細粒度控制。

MySQL維護兩個單獨的日誌：1.用於崩潰恢複的InnoDB特定的重做日誌，以及 2. 用於複製和增量備份的二進位日誌。

 

InnoDB 上的重做日誌與 Oracle 一致，它是一個免維護的迴圈緩衝區，不會隨著時間的推移而增長，只在啟動時以固定大小建立。 這種設計保證在物理裝置上保留一個連續的連續地區，從而提高效能。 更大的重做日誌產生更高的效能，但要以崩潰恢復為代價。

隨著新的複製功能添加到Postgres，我覺得他們不分伯仲。

 

 前面文章太長不想讀的話，請看後面的總結

令人驚訝的是，它證明了普遍的觀點依然存在；MySQL最適合線上交易，而PostgreSQL最適合僅用於append only模式，像資料倉儲一樣分析過程。[2]

正如我們在這篇文章中看到的，Postgres的絕大多數難題都來自於append only模式，過於冗餘的堆結構。

Postgres的未來版本可能需要對其儲存引擎進行重大改進。您不必為接受我說的——實際上在官方wiki上已經有對它的討論，這表明現在是時候從InnoDB身上學回來一些好的想法了。

人們一次又一次的說MySQL正在追趕Postgres，但是這一次，潮流已經改變。

 

——————————————————————————————————————————

1. UUID作為主鍵是一個可怕的想法，順便說一句——密碼隨機性完全是為了殺死引用的局部性而設計，因此效能會損失。??

2. 當我說Postgres特別適合分析時，我是認真的：萬一你不知道TimescaleDB，它是PostgreSQL上邊的一個封裝，允許你每秒插入100萬條資料，每台伺服器又1000億行。多麼瘋狂的事情。難怪Amazon會選擇PostgreSQL作為Redshift的基礎。

[轉帖] “王者對戰”之 MySQL 8 vs PostgreSQL 10