MySQL與PostgreSQL數(shù)據(jù)庫新版本競爭分析

1、 MySQL與PostgreSQL數(shù)據(jù)庫新版本競爭分析MySQL 8和PostgreSQL 10已發(fā)布，本文回顧這兩大開源關(guān)系型數(shù)據(jù)庫是如何彼此競爭的。在這些版本之前，人們普遍認(rèn)為，Postgres在功能集表現(xiàn)更出色，也因其“學(xué)院派”風(fēng)格而備受稱贊，MySQL則更善長大規(guī)模并發(fā)讀/寫。但是隨著它們最新版本的發(fā)布，兩者之間的差距明顯變小了。特性比較首先來看看我們都喜歡談?wù)摰摹皶r(shí)髦”功能。特性MySQL 8PostgreSQL 10查詢 & 分析公用表表達(dá)式 (CTEs) New窗口函數(shù) New數(shù)據(jù)類型JSON支持 ImprovedGIS / SRS Improved全文檢索可擴(kuò)展性邏輯復(fù)制 Ne

2、w半同步復(fù)制 New聲明式分區(qū) New過去經(jīng)常會說MySQL最適合在線事務(wù)，PostgreSQL最適合分析流程，但現(xiàn)在不是了。公共表表達(dá)式（CTEs）和窗口函數(shù)是選擇PostgreSQL的主要原因。但是現(xiàn)在，通過引用同一個(gè)表中的boss_id來遞歸地遍歷一張雇員表，或者在一個(gè)排序的結(jié)果中找到一個(gè)中值（或50%），這在MySQL上不再是問題。在PostgreSQL中進(jìn)行復(fù)制缺乏配置靈活性，這就是Uber轉(zhuǎn)向MySQL的原因。但是現(xiàn)在，有了邏輯復(fù)制特性，就可以通過創(chuàng)建一個(gè)新版本的Postgres并切換到它來實(shí)現(xiàn)零停機(jī)升級。在一個(gè)巨大的時(shí)間序列事件表中截?cái)嘁粋€(gè)陳舊的分區(qū)也要容易得多。就特性而言，這兩

3、個(gè)數(shù)據(jù)庫現(xiàn)在都是一致的。不同之處現(xiàn)在，我們只剩下一個(gè)問題選擇這一個(gè)而不選另一個(gè)的原因是什么呢？生態(tài)系統(tǒng)是其中一個(gè)因素。MySQL有一個(gè)充滿活力的生態(tài)系統(tǒng)，包括MariaDB、Percona、Galera等等，以及除了InnoDB以外的存儲引擎，但這也可能會令人困惑。Postgres的高端選擇有限，但隨著最新版本引入的新功能，這個(gè)情況會有所改變。治理是另一個(gè)因素。當(dāng)Oracle（或最初的SUN）收購MySQL時(shí)，每個(gè)人都擔(dān)心會毀掉這個(gè)產(chǎn)品，但在過去的十年里，這并不是事實(shí)。事實(shí)上，在收購之后，MySQL的發(fā)展反而加速了。而Postgres在工作管理和協(xié)作社區(qū)方面有著豐富的經(jīng)驗(yàn)。基礎(chǔ)架構(gòu)不會經(jīng)常改變

4、，雖然最近沒有對這方面的詳細(xì)討論，但這也是值得再次考慮的。來復(fù)習(xí)一下：特性MySQL 8PostgreSQL 10架構(gòu)單進(jìn)程多進(jìn)程并發(fā)多線程fork(2)表結(jié)構(gòu)聚簇索引堆頁壓縮TransparentTOAST更新In-Place / Rollback SegmentsAppend Only / HOT垃圾回收清除線程自動清空進(jìn)程事務(wù)日志REDO Log (WAL)WAL復(fù)制日志Separate (Binlog)WAL進(jìn)程 vs 線程當(dāng)Postgres派生出一個(gè)子進(jìn)程來建立連接時(shí)，每個(gè)連接最多可以占用10MB。與MySQL的線程連接模型相比，它的內(nèi)存壓力更大，在64位平臺上，線程的默認(rèn)堆棧大小為

5、256KB（當(dāng)然，線程本地排序緩沖區(qū)等使這種開銷變得不那么重要，即使在不可以忽略的情況下，仍然如此）。盡管“寫時(shí)復(fù)制”保存了一些與父進(jìn)程共享的、不可變的內(nèi)存狀態(tài)，但是當(dāng)你有1000多個(gè)并發(fā)連接時(shí)，基于流程的架構(gòu)的基本開銷是很繁重的，而且它可能是容量規(guī)劃的最重要因素之一。也就是說，如果你在30臺服務(wù)器上運(yùn)行一個(gè)Rails應(yīng)用，每個(gè)服務(wù)器都有16個(gè)CPU核心32線程，那么你有960個(gè)連接?？赡苤挥胁坏?.1%的應(yīng)用會超出這個(gè)范圍，但這是需要記住的。聚簇索引 vs 堆表聚簇索引是一種表結(jié)構(gòu)，其中的行直接嵌入其主鍵的b樹結(jié)構(gòu)中。一個(gè)（非聚集）堆是一個(gè)常規(guī)的表結(jié)構(gòu)，它與索引分別填充數(shù)據(jù)行。有了聚簇索引，

6、當(dāng)你通過主鍵查找記錄時(shí)，單次I/O就可以檢索到整行，而非集群則總是需要查找引用，至少需要兩次I/O。由于外鍵引用和JOIN將觸發(fā)主鍵查找，所以影響可能非常大，這將導(dǎo)致大量查詢。聚簇索引的一個(gè)理論上的缺點(diǎn)是，當(dāng)你使用二級索引進(jìn)行查詢時(shí)，它需要遍歷兩倍的樹節(jié)點(diǎn)，第一次掃描二級索引，然后遍歷聚集索引，這也是一棵樹。但是，如果按照現(xiàn)代表設(shè)計(jì)的約定，將一個(gè)自動增量整數(shù)作為主鍵1它被稱為代理鍵那么擁有一個(gè)聚集索引幾乎總是可取的。更重要的是，如果你做了大量的ORDER BY id來檢索最近的（或最老的）N個(gè)記錄的操作，我認(rèn)為這是很適用的。Postgres不支持聚集索引，而MySQL(InnoDB)不支持堆。

7、但不管怎樣，如果你有大量的內(nèi)存，差別應(yīng)該是很小的。頁結(jié)構(gòu)與壓縮Postgres和MySQL都有基于頁面的物理存儲。(8KB vs 16KB)PostgreSQL物理存儲的介紹頁結(jié)構(gòu)看起來就像上圖所示。它包含一些我們不打算在這里討論的條目，但是它們包含關(guān)于頁的元數(shù)據(jù)。條目后面的項(xiàng)是一個(gè)數(shù)組標(biāo)識符，由指向元組或數(shù)據(jù)行的（偏移、長度）對組成。在Postgres中，相同記錄的多個(gè)版本可以以這種方式存儲在同一頁面中。MySQL的表空間結(jié)構(gòu)與Oracle相似，它有多個(gè)層次，包括層、區(qū)段、頁面和行層。此外，它還有一個(gè)用于撤銷的單獨(dú)段，稱為“回滾段”。與Postgres不同的是，MySQL將在一個(gè)單獨(dú)的區(qū)域中

8、保存同一記錄的多個(gè)版本。如果存在一行必須適合兩個(gè)數(shù)據(jù)庫的單個(gè)頁面，這意味著一行必須小于8KB（至少有2行必須適合MySQL的頁面，恰巧是16KB/2 = 8KB）。那么，當(dāng)你在一個(gè)列中有一個(gè)大型JSON對象時(shí)會發(fā)生什么呢？Postgres使用TOAST，這是一個(gè)專用的影子表（shadow table）存儲。當(dāng)行和列被選中時(shí)，大型對象就會被拉出。換句話說，大量的黑盒不會污染你寶貴的緩存。它還支持對TOAST對象的壓縮。MySQL有一個(gè)更復(fù)雜的特性，叫做透明頁壓縮，這要?dú)w功于高端SSD存儲供應(yīng)商Fusio-io的貢獻(xiàn)。它設(shè)計(jì)目的是為了更好地使用SSD，在SSD中，寫入量與設(shè)備的壽命直接相關(guān)。對My

9、SQL的壓縮不僅適用于頁面外的大型對象，而且適用于所有頁面。它通過在稀疏文件中使用打孔來實(shí)現(xiàn)這一點(diǎn)，這是被ext4或btrfs等現(xiàn)代文件系統(tǒng)支持的。有關(guān)更多細(xì)節(jié)，請參見：在FusionIO上使用新MariaDB頁壓縮獲得顯著的性能提升。（/significant-performance-boost-with-new-mariadbcompression-on-fusionio/）更新的開銷另一個(gè)經(jīng)常被忽略的特性，但是對性能有很大的影響，并且可能是最具爭議的話題，是更新。這也是Uber放棄Postgres的另一個(gè)原因，這激起了許多Postgres支持者的反駁。MySQL對Uber可能是合適的，但

10、是未必對你合適/articles/on-ubers-choice-of-databases一篇PostgreSQL對Uber的回應(yīng)（PDF）/presentations/uber-perconalive-2017.pdf兩者都是MVCC數(shù)據(jù)庫，它們可以隔離多個(gè)版本的數(shù)據(jù)。為了做到這一點(diǎn)，Postgres將舊數(shù)據(jù)保存在堆中，直到被清空，而MySQL將舊數(shù)據(jù)移動到一個(gè)名為回滾段的單獨(dú)區(qū)域。在Postgres中，當(dāng)你嘗試更新時(shí)，整個(gè)行必須被復(fù)制，以及指向它的索引條目也被復(fù)制。這在一定程度上是因?yàn)镻ostgres不支持聚集索引，所以從索引中引用的一行的物理位置不是由邏輯鍵抽象出來的。為了解決這個(gè)問題，

11、Postgres使用了堆上元組（HOT），在可能的情況下不更新索引。但是，如果更新足夠頻繁（或者如果一個(gè)元組比較大），元組的歷史可以很容易地超過8KB的頁面大小，跨越多個(gè)頁面并限制該特性的有效性。修剪和/或碎片整理的時(shí)間取決于啟發(fā)式解決方案。另外，設(shè)置不超過100的填充參數(shù)會降低空間效率這是一種很難在創(chuàng)建表時(shí)考慮的折衷方案。這種限制更深入，因?yàn)樗饕M沒有關(guān)于事務(wù)的任何信息，所以直到9.2之前一直不能支持僅索引掃描。 它是所有主要數(shù)據(jù)庫（包括MySQL、Oracle、DB2和SQL Server）支持的最古老，最重要的優(yōu)化方法之一。 但即使使用最新版本，當(dāng)有許多UPDATE在可見性映射中設(shè)置臟

12、位時(shí)，Postgres也不能完全支持僅索引掃描，并且在我們不需要時(shí)經(jīng)常選擇Seq掃描。在MySQL上，更新發(fā)生在原地，舊的行數(shù)據(jù)被封存在一個(gè)稱為回滾段的獨(dú)立區(qū)域中。 結(jié)果是你不需要VACUUM，并且提交非?？?，而回滾相對較慢，這對于大多數(shù)用例來說是一個(gè)可取的折衷。它也足夠聰明，盡快清除歷史。 如果事務(wù)的隔離級別設(shè)置為READ-COMMITTED或更低，則在語句完成時(shí)清除歷史記錄。事務(wù)記錄的大小不會影響主頁面。碎片化是一個(gè)偽命題。因此，在MySQL上能更好、更可預(yù)測整體性能。Garbage Collection垃圾回收在Postgres中VACUUM上開銷很高，因?yàn)樗饕ぷ髟诙褏^(qū)，造成了直接的

13、資源競爭。它感覺就像是編程語言中的垃圾回收它會擋在路上，并隨時(shí)讓你停下來。為具有數(shù)十億記錄的表配置autovacuum仍然是一項(xiàng)挑戰(zhàn)。在MySQL上清除（Purge）也可能相當(dāng)繁重，但由于它是在單獨(dú)的回滾段中使用專用線程運(yùn)行的，因此它不會以任何方式影響讀取的并發(fā)性。即使使用默認(rèn)配置，變膨脹的回滾段使你執(zhí)行速度減慢的可能性也是很低的。擁有數(shù)十億記錄的繁忙表不會導(dǎo)致MySQL上的歷史數(shù)據(jù)膨脹，諸如存儲上的文件大小和查詢性能等事情上幾乎是可以預(yù)測的并且很穩(wěn)定。日志與副本Postgres擁有被稱作預(yù)寫日志（WAL）的單信源事務(wù)歷史。它一直被用于副本，并且稱為邏輯復(fù)制的新功能可將二進(jìn)制內(nèi)容快速解碼為更易消化的邏輯語句，從而可對數(shù)據(jù)進(jìn)行細(xì)粒度控制。MySQL維護(hù)兩個(gè)單獨(dú)的日志：1、用于崩潰恢復(fù)的InnoDB特定的重做日志；2、用于復(fù)制和增量備份的二進(jìn)制日志。InnoDB上的重做日志與Oracle一致，它是一個(gè)免維護(hù)的循環(huán)緩沖區(qū)，不會隨著時(shí)間的推移而增長，只在啟動時(shí)以固定大小創(chuàng)建。 這種設(shè)計(jì)保證在物理設(shè)備上保留一個(gè)連續(xù)的連續(xù)區(qū)域，從而提高性能。 更大的重做日志產(chǎn)生更高的性能，但要以崩潰恢復(fù)時(shí)間為代價(jià)。隨著新的復(fù)制功能添加到Postgres，我覺得他們不分伯仲?？偨Y(jié)令人驚訝