我已經閱讀了 MySQL 和 PostgreSQL 之間相同架構/查詢的性能差異。以下是對文章的簡要復述：

PostgreSQL 表是堆表（意味著沒有聚集索引）…（ Postgres ）表的主鍵查找需要點擊索引，查找文件中的位置，然後點擊堆表並提取記錄。這意味著許多隨機磁碟 I/O… InnoDB 使用不同的方法。使用 InnoDB，表是一個 b-tree 索引（聚集，物理排序）…… PK 查找所需的隨機磁碟 I/O 更少……同時，索引掃描需要遍歷兩個索引而不是一個（index -> PK index -> table row），這意味著使用除主鍵之外的任何索引最終都會變慢，並且順序掃描仍然更慢。

哪種查詢使用 Postgres 比使用 MySQL InnoDB 快得多？

我理解為什麼 PK 查找對 MySQL 來說要好得多。我不明白：

1. 為什麼通過兩個索引查找（InnoDB，通過非 PK 索引查找）要慢得多？它是否需要兩倍的 I/O 或 CPU？它能否彌補 PK 查找提升帶來的巨大好處？
2. 為什麼 InnoDB 順序掃描較慢？

PS Internet說Postgres更適合複雜查詢和子查詢，但我仍然不明白為什麼它更好？

為了避免激戰，我只看一下每個儲存在查詢方面的工作方式，而不是真正的基準。我將使用此表作為參考（程式碼應稍作修改以在兩個 RDBMS 上執行）：

CREATE TABLE employees (
   emp_id int,
   name varchar,
   depto_no int,
   salary decimal,
   CONSTRAINT emp_pk PRIMARY KEY (emp_id);
);
CREATE INDEX emp_depto_idx ON employees (depto_no);

在 PostgreSQL 上會有 3 個結構：

1. employees堆，基本上是按順序儲存的表（就像你想像的表一樣）
2. emp_pk索引（也是主鍵），儲存為 B 樹索引，其中每個元素都有一個指向堆的指針employee，具有磁碟中的確切頁/偏移量
3. emp_depto_idx索引，也就是一個帶有指向堆的指針的emp_pkB 樹，只是它不強制唯一性

在 MySQL InnoDB 上，只有兩個：

1. emp_pk並將employees儲存為一個結構，按emp_id列排序的 B 樹，並將其他列上的值作為有效負載保留在葉節點中。
2. emp_depto_idxindex 是一個 B 樹，它在每個元素上都有emp_id引用該行的值（不是物理位置指針）。

主鍵查找

為什麼 PK 查找對 MySQL 來說要好得多

我知道你知道，但讓我們說清楚。

當你像這樣查詢它時：

SELECT * FROM employees WHERE emp_id = 10;

在 PostgreSQL 上，它可以通過索引導航emp_pk（對 B 樹索引進行一次掃描），然後獲取頁面/偏移量以從employees堆表中獲取引用行（直接獲取頁面/行，而不是真正的掃描）。因此，對索引進行一次掃描，對堆進行一次直接提取。

在 MySQL 上，它只會瀏覽主鍵索引（對 B 樹索引進行一次掃描），因為所有資訊都已經存在，不需要其他查找。因此，只需對 index 進行一次掃描。

因此，雖然 PostgreSQL 需要進行一次掃描和一次提取，但 MySQL 只需進行一次掃描。

二級索引查找

為什麼通過兩個索引查找（InnoDB，通過非 PK 索引查找）要慢得多？它是否需要兩倍的 I/O 或 CPU？它能否彌補 PK 查找提升帶來的巨大好處？

現在，假設另一個查詢：

SELECT * FROM employees WHERE depto_no = 14;

在 PostgreSQL 上，它與另一個沒有太大區別。它將掃描emp_depto_idx，然後對於返回的每一行，直接從堆中獲取值。因此，對索引進行一次掃描，並在堆上直接獲取匹配的每一行。

在 MySQL 上，它將掃描emp_depto_idx（一次掃描索引），然後，對於返回的每一行，它將獲取引用emp_id並掃描主鍵索引。因此，對二級索引進行一次掃描，並對匹配的每一行的主索引進行一次掃描。

看到不同？PostgreSQL 將進行一次掃描，然後使用直接指針獲取每個匹配的行，而 InnoDB 將首先進行類似的掃描，然後對每個匹配的行進行另一次掃描。現在，如果部門 14 的員工很少，它可能已經足夠快了，但隨著員工數量的增加，它會變得非常慢（當然，在兩個 RDBMS 上它會變得更慢，但 InnoDB 的曲線可能更高）。

全掃描

為什麼 InnoDB 順序掃描較慢？

簡單的答案，因為它不是真正的“順序”……

好吧，讓我們看看最簡單的（當然也很慢）查詢：

SELECT * FROM employees;

在 PostgreSQL 上，它可以簡單employees地按物理順序逐行掃描整個堆（不管這裡的插入順序如何，重要的是元組和頁面現在是如何物理排列的）。

在 InnoDB 上，它需要遍歷索引，這意味著更多的隨機掃描（因為索引頁不一定在物理和邏輯上以相同的方式排序）。

如果您考慮磁碟，則差異非常明顯，眾所周知，順序訪問比隨機訪問要快得多。對於 SSD，這不一定是真的，儘管順序訪問仍然有優勢，例如預讀。所以在大多數情況下，PostgreSQL 的全掃描可能會比 InnoDB 快，至少對於相當大的表來說（注意我沒有定義什麼是“大”，你必須嘗試一下，看看差異在哪裡真正重要，它可能不是在許多情況下很重要）。對於 RDBMS 來說，最好的辦法是設計模型和查詢以避免完全掃描，如果可能的話。

複雜查詢

Internet 說 Postgres 更適合複雜查詢和子查詢，但我仍然不明白為什麼它更好？

這是一個巨大的話題，可能會引發更多的火焰戰爭，所以我只是給你一些例子。通常說 PostgreSQL 更適合複雜的查詢，如果您簡單地考慮一下 MySQL 中不存在的查詢功能（尚未考慮性能），這可能是正確的，例如：

• 公用表表達式 (CTE)
• 視窗函式
• 橫向連接
• 數組
• JSON 類型、函式和運算符
• 等等。

除此之外，兩者的計劃者和執行者也有很多不同。例如，PostgreSQL 可以使用嵌套循環、雜湊連接和合併連接進行連接，而 MySQL 只能使用嵌套循環進行連接。儘管如此，MySQL 在其嵌套循環算法中有許多優化，而 PostgreSQL 在其規劃器上的選擇更加困難，有時它會出錯（但 MySQL 也是如此）。

最後的話

這個答案只是對該主題的快速瀏覽，對於這兩個 RDBMS 來說，在性能方面還有很多事情需要考慮，比如僅索引掃描、vacuum vs undo、並行性等。事實是你可以不能簡單地說一個比另一個快，我很清楚（對你來說是嗎？），一個在某些環境中可能更快，而另一個在其他環境中可能更快。

引用自：https://dba.stackexchange.com/questions/149193