前言

高效的數據檢索是數據庫管理的基石， PostgreSQL和SQL Server都能提供強大的數據訪問方法以支持各種工作負載方面表現出色。然而，它們的實現方式存在顯著差異，反映了各自獨特的設計理念和使用場景。 在這篇文章中將介紹PostgreSQL提供的各種數據訪問方法，其中包括一個非常獨特的特點：PostgreSQL不支持聚集索引。這一根本性的差異對于理解PostgreSQL與SQL Server在數據存儲和檢索上的不同方式至關重要。

順序掃描

任何數據庫系統(tǒng)的核心都離不開最簡單的數據訪問方法，就是掃描表中的所有行。 PostgreSQL 通過順序掃描（Sequential Scan）來實現這一點，它逐行讀取表中的每一行。 雖然對于大型數據集而言，這看起來可能效率不高，但在特定場景下，它往往是最實際的選擇。 當處理小型表時，使用索引的開銷通常超過其帶來的好處，因此順序掃描非常有效。 此外，當查詢需要表中大量行時，例如需要查詢超過50%的數據行時候，順序掃描可以通過最小化隨機I/O來優(yōu)于索引掃描。 SQL Server采用了一種類似的技術，稱為表掃描（Table Scan），它逐行讀取整個表。無論是PostgreSQL還是SQL Server都依賴其查詢優(yōu)化器來決定何時應選擇表掃描而不是使用索引掃描。例如，在沒有合適索引的情況下，或者查詢涉及廣泛的過濾條件時，優(yōu)化器將選擇全表掃描。盡管順序掃描和表掃描有時被批評為較慢，但它們依然是數據庫處理特定工作負載時必不可少的工具。

在PostgreSQL中，所有的表默認存儲在堆結構（Heap）中，這意味著行沒有固定的順序。PostgreSQL中沒有聚集索引的概念，這意味著順序掃描通常會訪問以任意順序存儲的行。

索引掃描

在PostgreSQL中，索引掃描（Index Scan）是一個基本的查詢執(zhí)行方法，它使用索引來高效地檢索符合特定查詢條件的行。 當執(zhí)行索引掃描時，PostgreSQL會遍歷索引結構（B樹）來查找滿足查詢條件的行的位置（元組指針）。這些指針將引導PostgreSQL定位到堆表中的相應行，進而檢索完整的行數據。 在PostgreSQL中，索引掃描的關鍵之處在于，它對堆表的查找操作是作為索引掃描的一部分內部執(zhí)行的。因此，PostgreSQL 執(zhí)行計劃將索引掃描顯示為一個單獨的操作，它封裝了索引遍歷和隨后從堆表中檢索行數據這兩個步驟。

與此不同，SQL Server的執(zhí)行計劃明確區(qū)分了這兩個步驟。在SQL Server中，索引查找（Index Seek）操作負責遍歷索引以找到匹配的行。當索引不包含查詢所需的所有列時，SQL Server會引入一個單獨的操作，對于聚集索引表是鍵查找（Key Lookup），對于堆表則是RID查找（RID Lookup）。這些查找操作會直接從基礎表中獲取額外的列。通過分離這些步驟，SQL Server的執(zhí)行計劃提供了一個更加清晰詳細的視圖，展示查詢如何訪問數據，包括索引遍歷和數據行檢索的成本和行為。 這種執(zhí)行計劃表示的差異凸顯了不同的設計理念。 PostgreSQL將堆查找集成到索引掃描操作中，呈現一個簡化的執(zhí)行計劃。然而，這也可能掩蓋索引掃描中堆訪問部分的具體成本。另一方面，SQL Server明確分離提供了對查詢執(zhí)行過程更為詳細的洞察。例如，當SQL Server的執(zhí)行計劃中包含鍵查找時，立刻可以看出索引缺少一些必需的列，這可以幫助數據庫管理員通過創(chuàng)建覆蓋索引來消除查找操作。這種透明度對于識別和解決復雜查詢中的性能瓶頸特別有幫助。

位圖索引掃描與位圖堆掃描

對于具有多個條件或過濾器的查詢，PostgreSQL經常使用位圖堆掃描（Bitmap Heap Scan），這是一種將索引訪問的精確性與批量讀取的高效性相結合的混合方法。 在執(zhí)行此類查詢時，PostgreSQL首先使用相關的索引構建一個位圖，即匹配查詢條件的行的壓縮表示。與逐行訪問不同，位圖使PostgreSQL能夠批量獲取這些行從而減少隨機磁盤 I/O。這種方法對于必須同時評估多個條件的大型表特別有用，例如按客戶年齡和地點進行過濾。位圖掃描Bitmap Scan也分為兩個階段，第一個階段是Bitmap Index Scan，第二個階段是Bitmap Heap Scan。Bitmap Heap Scan采用Bitmap Index Scan生成的bitmap(或者經過 BitmapAnd 和 BitmapOr 節(jié)點通過一系列位圖集操作后，生成的bitmap)來查找相關數據。位圖的每個page可以是精確的（直接指向tuple的）或有損的（指向包含至少一行與查詢匹配的page）。

SQL Server并沒有直接等同于位圖堆掃描的操作，但它在并行查詢執(zhí)行計劃中使用位圖過濾（Bitmap Filtering）。位圖堆掃描在處理需要多個索引掃描的查詢時尤其具有優(yōu)勢，因為它將這些操作合并為一個更高效的過程。這種方法突顯了 PostgreSQL 在動態(tài)優(yōu)化復雜查詢方面的獨特能力。通過平衡順序訪問和索引訪問的優(yōu)點，位圖堆掃描架起了精確性與高效性之間的橋梁，使其在分析和報告工作負載中變得不可或缺。

僅索引掃描

在PostgreSQL中，僅索引掃描（Index-Only Scans）是一種查詢執(zhí)行特性，允許數據完全從索引中檢索，跳過對堆表的訪問。 當查詢只涉及索引中的列時，這種方法是可行的。在進行僅索引掃描時，PostgreSQL直接從索引的葉節(jié)點中獲取數據，從而顯著減少了I/O操作并提高了查詢性能，特別適用于讀密集型工作負載。例如，如果一個查詢僅檢索客戶的姓名和電子郵件，并且這些列是索引的一部分，那么數據庫完全避免了訪問堆表的開銷。在SQL Server中，類似的概念是通過覆蓋索引（Covering Indexes）來實現的，在索引定義中包含了額外的列（超出索引鍵列的部分）。這些額外的列被稱為包含列（Included Columns），它們允許 SQL Server 直接從索引中檢索所有所需的數據，而無需執(zhí)行鍵查找（Key Lookup）或 RID 查找（RID Lookup）。

并行查詢執(zhí)行

隨著數據集的增大和查詢變得更加復雜，采用并行處理對于保持性能至關重要。 PostgreSQL支持并行查詢執(zhí)行，允許多個工作進程分擔和處理大規(guī)模的工作負載。例如，并行掃描將一個大型表分成多個分段，每個工作進程同時掃描其中的一部分。這種方法能夠顯著減少資源密集型操作的查詢時間。SQL Server也支持執(zhí)行計劃中的并行處理，使用并行掃描（Parallel Scan）和合并流（Gather Streams）等操作符，將工作負載分配并合并到多個工作線程中。SQL Server的并行查詢引擎與其優(yōu)化器緊密集成，通常能為事務型OLTP和分析型OLAP工作負載生成高效的執(zhí)行計劃。

聚集索引的作用

PostgreSQL和SQL Server之間最顯著的區(qū)別之一是PostgreSQL不支持聚集索引。 在SQL Server中，聚集索引定義了表中行的物理順序。這可以顯著提高范圍查詢或返回按排序順序排列行的查詢的性能，因為數據已經根據索引鍵物理排序。 在PostgreSQL中，所有表都以堆（heap）形式存儲，這意味著行沒有特定的存儲順序。雖然PostgreSQL提供了一個名為CLUSTER的命令，可以基于索引物理重新排序表，但這個操作不是動態(tài)的，必須手動執(zhí)行。此外，CLUSTER創(chuàng)建的排序不會隨著行的插入、更新或刪除而保持。 PostgreSQL的這種設計選擇優(yōu)先考慮靈活性，而不是聚集索引可能帶來的性能提升。 通過保持表的無序，PostgreSQL 允許多個索引并存，這對于大量數據寫入的場景或者說寫多讀少的場景非常有利。

總結

PostgreSQL 和 SQL Server 中的數據訪問方法展示了各自系統(tǒng)的優(yōu)勢和優(yōu)先事項。PostgreSQL 的靈活性，例如位圖堆掃描、僅索引掃描，使其成為開發(fā)者在查詢執(zhí)行上尋求精確控制的強大選擇。 然而，PostgreSQL 不支持聚集索引是其與 SQL Server 的一個關鍵區(qū)別。另一方面，SQL Server 使用聚集索引來提供表行的物理排序，這可以顯著有利于范圍查詢和排序操作。 這種結構性差異體現了兩個系統(tǒng)的不同哲學：PostgreSQL 傾向于適應性，而 SQL Server 強調緊密集成的優(yōu)化。理解這些差異能夠幫助數據庫專業(yè)人員做出明智的決策，并針對每個平臺的獨特優(yōu)勢優(yōu)化查詢。

?轉自https://www.cnblogs.com/lyhabc/p/18692856/