個人其它平臺技術(shù)文章：

• 知乎ID：磚一塊一塊搬
• 小紅書ID：碼農(nóng)有道

在知乎上有同學(xué)提問關(guān)于 Postgres 和 MySQL 的區(qū)別，原本只是簡單的回答，結(jié)果越講越多，干脆整理成一篇文章分享出來。

簡單來說，這兩種數(shù)據(jù)庫的核心差異，主要體現(xiàn)在主鍵索引和二級索引的實現(xiàn)方式，以及底層的數(shù)據(jù)存儲與更新機制上。

接下來，我們就來詳細看看這兩者的不同之處。

索引

索引是一種用于加速查詢的數(shù)據(jù)結(jié)構(gòu)，通常采用 B+ 樹實現(xiàn)。這種結(jié)構(gòu)通過多層節(jié)點進行鍵值查找，數(shù)據(jù)庫內(nèi)部通常以“頁（page）”的形式組織這些節(jié)點。查找過程中，系統(tǒng)會從根節(jié)點出發(fā)，逐層遍歷樹結(jié)構(gòu)，逐步排除不包含目標數(shù)據(jù)的頁面，直到最終定位到包含目標鍵的葉子頁面。

葉子節(jié)點中存儲的是有序的鍵（key）以及對應(yīng)的值（value）。一旦定位到目標鍵，便可直接獲取相應(yīng)的值。與此同時，該頁面會被緩存在數(shù)據(jù)庫的共享緩沖區(qū)中，以便后續(xù)查詢可以重用，從而提升查詢效率。

在 B+ 樹索引中，“鍵”指的是創(chuàng)建索引時指定的列，而“值”是什么，不同的數(shù)據(jù)庫有不同的實現(xiàn)方式。接下來，我們就來看看 Postgres 和 MySQL 在這方面的具體差異。

MySQL

在 MySQL 中，主鍵索引的“值”其實就是整行數(shù)據(jù)本身，即該行的所有字段內(nèi)容。這也是為什么主鍵索引通常被稱為“聚簇索引”。

需要說明的是，這里的描述是基于行存儲的數(shù)據(jù)庫系統(tǒng)。對于采用列存儲、圖數(shù)據(jù)庫或文檔型數(shù)據(jù)庫的系統(tǒng)來說，由于存儲模型不同，“值”的定義也會有所區(qū)別。

當通過主鍵索引查找某個鍵對應(yīng)的數(shù)據(jù)時，只需定位到該鍵所在的頁面，就可以直接獲取整行數(shù)據(jù)，無需額外的 I/O 操作去讀取其他列，因為其值包含了整行數(shù)據(jù)本身。

而在二級索引中，鍵是你創(chuàng)建索引時指定的列，但“值”不再是整行數(shù)據(jù)，而是一個指針，用于定位這條完整數(shù)據(jù)所在的位置。通常來說，二級索引葉子節(jié)點中的值就是對應(yīng)行的主鍵值。

也正因如此，MySQL 要求每張表必須有一個主鍵索引，所有二級索引最終都要通過主鍵來定位數(shù)據(jù)。如果沒有顯式定義主鍵，MySQL 會自動為你生成一個隱藏主鍵。

Postgres

在 Postgres 中，從技術(shù)上講并沒有“主鍵索引”的概念，所有索引本質(zhì)上都是二級索引。它們都指向系統(tǒng)管理的 tuple ID（元組 ID），根據(jù)這些 ID 再定位到實際存儲在堆中的數(shù)據(jù)頁。需要注意的是，堆中的表數(shù)據(jù)是無序的，并不像聚簇索引的葉子節(jié)點那樣按鍵值順序排列。

舉個例子，如果你依次插入第 1 到第 100 行數(shù)據(jù)，這些行可能會被存儲在同一個頁面中；但如果你隨后更新了第 1 到第 20 行，這些更新后的數(shù)據(jù)很可能會被寫入到其他頁面，從而導(dǎo)致數(shù)據(jù)的物理分布變得無序。

而在 MySQL 的聚簇索引中，插入操作需要保證數(shù)據(jù)按鍵值（也就是索引字段值）順序?qū)懭耄@就限制了數(shù)據(jù)的物理排列方式。因此，Postgres 中的表通常被稱為“堆組織表（heap organized tables）”，而不是“索引組織表（index organized tables）”。

此外需要特別注意的是，在 Postgres 中，更新和刪除操作本質(zhì)上其實都是插入操作。每次執(zhí)行更新或刪除時，系統(tǒng)都會生成一個新的元組 ID（tuple ID），而原有的元組 ID 會被保留下來，用于支持 MVCC（多版本并發(fā)控制）機制。這個細節(jié)我們稍后會在文章中進一步探討。

實際上，僅有元組 ID 并不足以定位具體數(shù)據(jù)，還需要知道該元組所在的數(shù)據(jù)頁編號。這兩個信息組合在一起，稱為 c_tid。想一想，如果只有元組 ID 而不知道它在哪一頁，是無法直接定位數(shù)據(jù)的。

由于在 Postgres 中，索引只保存元組的位置信息，因此必須多執(zhí)行一次 I/O 操作來加載對應(yīng)的數(shù)據(jù)頁，才能獲取完整的行數(shù)據(jù)。

查詢代價

我們來看下面這張示例表：

# 表 T；
# 主鍵列 PK 上有主索引，C2 列上有二級索引，C1 沒有索引；
# C1 和 C2 是文本類型，PK 是整數(shù)。

| PK | C1 | C2 |
|----|----|----|
| 1  | x1 | x2 |
| 2  | y1 | y2 |
| 3  | z1 | z1 |

現(xiàn)在，我們對比在 MySQL 和 Postgres 中執(zhí)行以下 SQL 查詢時的差異：

SELECT * FROM T WHERE C2 = 'x2';

在 MySQL 中，這條查詢會涉及兩次 B+ 樹查找操作。首先，通過 C2 列的二級索引查找字段 C2 值為 'x2' 的記錄，獲取其對應(yīng)的主鍵值 1；接著再通過主鍵索引查找主鍵為 1 的那一行數(shù)據(jù)，從而獲取整行記錄（ * 表示所有的字段）。

有些人可能會認為，這只是兩次 I/O 操作，其實并非如此。B+ 樹查找的時間復(fù)雜度是 O(logN)，當索引規(guī)模較大時，一次查找可能涉及多個節(jié)點，每個節(jié)點對應(yīng)一個頁面，因此可能觸發(fā)多次 I/O 操作。

而在 Postgres 中，執(zhí)行這條查詢時，首先通過 C2 列的二級索引查找匹配的元組 ID，然后進行一次堆訪問，從堆中加載包含完整行數(shù)據(jù)的頁面。

從訪問路徑來看，大多數(shù)情況下，一次索引查找加一次堆訪問要比兩次 B+ 樹查找更高效，也就是說，Postgres 在這種場景下的查詢性能往往優(yōu)于 MySQL。

為了讓這個例子更貼近實際，我們進一步假設(shè)：C2 列的值并不唯一，也就是說，可能有多行記錄的 C2 值都是 'x2'。在這種情況下，查詢過程中會返回多個匹配的元組 ID（在 MySQL 中則是多個主鍵值）。

問題在于，這些匹配的行可能分布在多個不同的數(shù)據(jù)頁上，導(dǎo)致大量隨機讀操作。在 MySQL 中，這意味著要重復(fù)進行多次主鍵索引查找（當然，查詢優(yōu)化器也可能根據(jù)匹配記錄數(shù)量，選擇走索引掃描而非逐條查找），但無論是在 MySQL 還是 Postgres 中，這種情況最終都不可避免地帶來頻繁的隨機 I/O。

為盡量減少這種隨機訪問，Postgres 會采用 位圖索引掃描（Bitmap Index Scan） 的方式來優(yōu)化查詢流程：首先將所有匹配結(jié)果按照頁面而非單條元組進行聚合，然后一次性批量加載這些數(shù)據(jù)頁，盡可能降低 I/O 次數(shù)。接下來再在內(nèi)存中進行過濾，最終返回滿足條件的記錄。

接下來，我們來看一個查詢的例子。

SELECT * FROM T WHERE PK BETWEEN 1 AND 3;

在主鍵索引上的范圍查詢方面，MySQL 的表現(xiàn)更為出色。它只需進行一次查找，定位到第一個匹配的鍵，然后沿著 B+ 樹葉子節(jié)點的鏈表依次向后遍歷，獲取相鄰的鍵，并在遍歷過程中直接讀取對應(yīng)的整行數(shù)據(jù)。

相比之下，Postgres 在這方面就顯得吃力一些。雖然它的二級索引同樣會在 B+ 樹的葉子節(jié)點上進行遍歷，找到所有匹配的鍵，但它只會收集對應(yīng)的元組 ID 和頁面信息，但此時工作并未就此完成。Postgres 還需要執(zhí)行額外的隨機讀操作，從堆中加載這些元組對應(yīng)的完整行數(shù)據(jù)。而這些數(shù)據(jù)行很可能分布在堆的不同位置，尤其是在頻繁更新的情況下，數(shù)據(jù)往往不會連續(xù)、緊湊地存放在一起。

對于更新頻繁的場景來說，這正是 Postgres 的一項劣勢。因此，為表設(shè)置合適的 FillFactor（填充因子）非常重要，它可以在一定程度上可以緩解數(shù)據(jù)分散帶來的性能問題。

接著，我們來看一個更新操作的例子：

UPDATE T SET C1 = 'XX1' WHERE PK = 1;

在 MySQL 中，如果更新的是一個未被索引的列，那么只需在主鍵索引的葉子節(jié)點中，直接修改該行對應(yīng)的字段值即可。由于所有的二級索引都通過主鍵進行定位，而主鍵值本身沒有發(fā)生變化，因此不需要更新任何索引結(jié)構(gòu)。

而在 Postgres 中，即使更新的是一個沒有索引的列，系統(tǒng)也會生成一個新的元組并賦予新的元組 ID，這意味著，所有原本指向舊元組的二級索引項都需要更新為指向新元組的位置。換句話說，雖然索引列本身并未發(fā)生變化，但由于底層的元組位置發(fā)生了改變，相關(guān)索引也“可能”需要更新，從而引發(fā)大量的寫入 I/O。

早在 2016 年，Uber 就曾明確表達過對這一機制的不滿，這也是他們將數(shù)據(jù)庫從 Postgres 遷移到 MySQL 的主要原因之一。

這里之所以說“可能”需要更新所有二級索引，是因為 Postgres 中存在一個名為 HOT（Heap Only Tuple）的優(yōu)化機制。

這個優(yōu)化的原理是：在滿足一定條件的情況下，允許二級索引保留原有的元組 ID，而不立即更新為新生成的元組 ID。此時，Postgres 會在堆頁面內(nèi)，通過在舊元組和新元組之間建立一個鏈表，從舊元組跳轉(zhuǎn)到新元組，實現(xiàn)不同版本之間的關(guān)聯(lián)。這樣就可以避免更新索引，從而減少寫入 I/O 的開銷。

數(shù)據(jù)類型很重要

在 MySQL 中，主鍵的數(shù)據(jù)類型至關(guān)重要，因為所有二級索引都需要存儲對應(yīng)的主鍵信息。舉例來說，如果主鍵使用的是 UUID，那么每條二級索引記錄中都要包含這個較長的主鍵值，導(dǎo)致二級索引變得臃腫，不僅占用更多的存儲空間，也會帶來更高的讀 I/O 開銷。

而在 Postgres 中，二級索引并不存儲主鍵的實際值，而是統(tǒng)一使用固定大小為 4 字節(jié)的元組 ID（tid）來指向堆中的數(shù)據(jù)。正因如此，即使主鍵是 UUID，二級索引的大小也不會受到影響。

撤銷日志（Undo Logs）

幾乎所有現(xiàn)代數(shù)據(jù)庫都支持多版本并發(fā)控制（MVCC）。以最常見的“讀已提交”（Read Committed）隔離級別為例，如果一個事務(wù) tx1 對某一行數(shù)據(jù)進行了更新但尚未提交，而此時另一個并發(fā)事務(wù) tx2 試圖讀取這行數(shù)據(jù)，那么它應(yīng)當讀取更新前的舊值，而不是尚未提交的新值。

大多數(shù)數(shù)據(jù)庫（包括 MySQL）通過撤銷日志（Undo Logs）來實現(xiàn)這一機制。

當某個事務(wù)修改了一行數(shù)據(jù)時，變更會直接寫入共享緩沖池中的頁面，也就是說，該頁面始終保存的是最新的數(shù)據(jù)。隨后，事務(wù)會將“如何撤銷這次變更”的信息寫入撤銷日志（Undo Log），以便在需要時可以還原該行數(shù)據(jù)的舊狀態(tài)。這樣，當并發(fā)事務(wù)基于自身的隔離級別需要讀取舊版本數(shù)據(jù)時，系統(tǒng)就可以通過解析 Undo Log 構(gòu)造出對應(yīng)的舊數(shù)據(jù)行。

你可能會疑惑：將未提交的修改直接寫入頁面，真的安全嗎？如果后臺進程把這個頁面刷入磁盤，而事務(wù)還沒提交，此時數(shù)據(jù)庫突然崩潰了怎么辦？

這正是Undo Log存在的意義。在數(shù)據(jù)庫啟動時，如果檢測到之前發(fā)生了崩潰，會在恢復(fù)階段利用 Undo Log 回滾那些未提交的變更，從而確保數(shù)據(jù)一致性不被破壞。

當然，Undo Log 會對并發(fā)事務(wù)帶來額外的開銷，尤其是在存在長事務(wù)的情況下。為了構(gòu)造舊版本數(shù)據(jù)，系統(tǒng)需要執(zhí)行額外的 I/O 操作，而且Undo Log 也有可能被寫滿，從而導(dǎo)致事務(wù)失敗。

我曾遇到過一個真實案例：某數(shù)據(jù)庫系統(tǒng)在崩潰恢復(fù)時，光是回滾一個運行了 3 小時但未提交的長事務(wù)，，就耗費了一個多小時。由此可見，盡量避免長事務(wù)，是非常有必要的。

而 Postgres 的處理方式則完全不同。每一次更新、插入或刪除操作，都會生成該行數(shù)據(jù)的一個新副本，并分配一個新的元組 ID（tuple ID），同時記錄創(chuàng)建和刪除該元組的事務(wù) ID。借助這些信息，Postgres 可以安全地將變更寫入數(shù)據(jù)頁，并允許并發(fā)事務(wù)根據(jù)自身的事務(wù) ID 判斷應(yīng)讀取舊版本還是新版本的數(shù)據(jù)。這個設(shè)計非常巧妙。

當然，再巧妙的方案也并非沒有代價。我們之前就提到過，為每次變更生成新的元組 ID，會對二級索引帶來額外開銷。此外，Postgres 還需要在適當時機清理那些不再需要的舊元組，這項清理工作則由 Vacuum 機制負責執(zhí)行。

Processes vs Threads

MySQL 使用的是線程模型，而 Postgres 則采用的是進程模型。兩者各有優(yōu)劣，我在另一篇文章中已做過詳細分析。就個人而言，在數(shù)據(jù)庫系統(tǒng)中，我更傾向于使用線程。原因很簡單，線程更輕量，而且能共享父進程的虛擬內(nèi)存地址空間。而進程則需要獨立的虛擬內(nèi)存，其進程控制塊（PCB）也比線程控制塊（TCB）更大，資源開銷更高。

轉(zhuǎn)自

MySQL在Uber的華麗轉(zhuǎn)身：Postgres輸在哪？
https://mp.weixin.qq.com/s/BEoOsLXQtwKchB_Wk45Bvw?clicktime=1753496836&enterid=1753496836&exptype=unsubscribed_card_recommend_article_u2i_mainprocess_coarse_sort_pcfeeds&ranksessionid=1753496829_1&scene=169&subscene=200