資料行存放區索引：概觀

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse Analytics分析平台系統（PDW）Microsoft Fabric 中的 SQL 資料庫

「資料行存放區索引」是儲存和查詢大型資料倉儲事實資料表的標準。 此索引使用資料行基礎的資料儲存和查詢處理，相較於傳統的資料列導向儲存，最高可在您的資料倉儲中達到 10 倍的查詢效能提升。 相較於未壓縮的資料大小，您也可以將資料壓縮提升高達 10 倍。 從 SQL Server 2016 (13.x) SP1 開始，資料行存放區索引可啟用作業分析：在交易式工作負載上執行高效能即時分析的能力。

深入了解相關案例：

• 數據倉儲中的數據行存放區索引
• 開始使用列存儲進行即時運行分析

何謂資料行存放區索引？

資料行存放區索引是使用單欄式資料格式 (稱為資料行存放區) 儲存、擷取及管理資料的一項技術。

主要詞彙和概念

以下是與資料行存放區索引相關聯的主要詞彙和概念。

列式存储

列存儲是一種將資料在邏輯上組織成具有列和欄的資料表，並實際以欄式資料格式儲存的技術。

資料列存放區

列存儲是將資料以包含列和欄的資料表的形式邏輯組織起來，然後實際以列格式儲存。 此格式是傳統儲存關聯式資料表資料的方式。 在 SQL Server 中，資料列存放區是指其基礎資料格式為堆積、叢集索引或記憶體最佳化資料表的資料表。

注意

在資料行存放區索引的相關討論中，資料列存放區和資料行存放區等詞用於強調資料儲存的格式。

列群組

資料列群組是指同時壓縮成資料行存放區格式的一組資料列。 資料列群組通常包含了每個資料列群組的資料列數目上限，即 1,048,576 個資料列。

為達到高效能和高壓縮率，資料行存放區索引會將資料表切割為資料列群組，然後以資料行取向的方式壓縮每個資料列群組。 列群組中的資料列數量必須足夠大，以改善壓縮率，但也需足夠小，以便從記憶體操作中獲益。

從列群組中刪除所有資料後，該列群組從 COMPRESSED 狀態轉換為 TOMBSTONE 狀態，然後由名為 Tuple Mover 的背景程序移除。 如需有關資料列群組狀態的詳細資訊，請參閱 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)。

提示

太多小型資料列群組會降低資料行存放區索引的品質。 在 SQL Server 2017 (14.x) 之前，需要遵循內部閾值原則來進行重新組織作業，以合併較小的 COMPRESSED 資料列群組，該內部閾值原則會決定如何移除已刪除的資料列以及合併已壓縮的資料列群組。
從 SQL Server 2019 (15.x) 開始，背景合併任務也負責合併那些因刪除大量資料列而壓縮的資料列群組。
合併較小的資料列群組之後，索引品質應該會改善。

注意

從 SQL Server 2019 (15.x)、Azure SQL Database、Azure SQL 受控執行個體和 Azure Synapse Analytics 中的專用 SQL 集區開始，背景合併工作將會協助元組移動器，自動壓縮存在了一段時間的較小 OPEN 異動資料列群組（由內部門檻決定），或合併已刪除大量資料列的 COMPRESSED 資料列群組。 這可以隨著時間的推移改善資料行存儲索引的品質。

欄位區段

列片段是指列組內部的資料列。

• 每個資料列群組中都包含該資料表裡每個資料欄的一個資料欄區段。
• 每個資料行區段會各自壓縮成一體並且儲存到實體媒體上。
• 每個區段都有中繼資料，可讓您快速刪除區段，而不需讀取它們。

群集列存儲索引

叢集資料行存放區索引是整個資料表的實體儲存體。

為了減少資料行區段的碎片化並提升效能，資料行存放區索引可能會暫時將部分資料儲存到稱為「deltastore」的叢集索引中，並使用 B 樹清單來儲存已刪除資料列的識別碼。 增量庫操作在後台處理。 為了傳回正確的查詢結果，叢集資料行存放區索引會整合來自資料行存放區和差異存放區的查詢結果。

注意

文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。 這不適用於資料行存放區索引或記憶體最佳化資料表上的索引。 如需詳細資訊，請參閱《SQL Server 和 Azure SQL 索引架構和設計指南》。

Delta 資料列群組

增量資料列群組是僅能搭配資料行存放區索引使用的叢集的 B 樹索引。 藉由儲存資料列直到數量達到閾值（1,048,576 個資料列），然後將其移入列存儲來改善壓縮和效能。

差異資料列群組一旦達到資料列數目上限，就會從 OPEN 轉換為 CLOSED 狀態。 名為「元組移動器」的背景處理程序會檢查已關閉的資料列群組。 如果程序發現封閉的資料列群組，便會壓縮該資料列群組，並將其儲存至資料行存放區中作為 COMPRESSED 資料列群組。

當差異資料列群組被壓縮時，現有的差異資料列群組會轉換成 TOMBSTONE 狀態。當沒有對其的參考時，Tuple Mover 會稍後將其移除。

如需有關資料列群組狀態的詳細資訊，請參閱 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)。

注意

從 SQL Server 2019 (15.x) 開始，有一項輔助 Tuple Mover 的背景合併任務，可以依據內部閾值，自動壓縮已存在一段時間的小型 OPEN 差異資料列群組，或合併由於刪除大量資料列而形成的 COMPRESSED 資料列群組。 這可以隨著時間的推移改善資料行存儲索引的品質。

Deltastore

列存放索引可以包含多個 Delta 資料列群組。 所有差異資料列群組統稱為 Delta 存放區。

在大規模的大量載入過程中，大多數資料列會直接進入資料行存放區，而不經過差異存放區。 在大量載入的結束階段，某些資料列的數量可能太少，未能達到資料列群組的最低大小標準，即 102,400 個資料列。 因此，最後這些資料列就會進入 Delta 儲存區，而不是資料列儲存區。 若是小於 102,400 筆資料列的小批量載入，則所有資料列都將直接進入增量存儲區。

非叢集列存儲索引

非叢集資料行存放區索引和叢集資料行存放區索引的功能相同。 差別在於非叢集索引是在資料列存放區資料表上建立的次要索引，但是叢集資料行存放區索引則是整個資料表的主要儲存體。

非叢集索引包含基礎資料表中部分或所有資料列和資料行的複本。 此索引會定義為資料表的一個或多個資料行，並具有篩選資料列的選用條件。

非叢集資料行存放區索引可使用即時作業分析，其中 OLTP 工作負載會使用基礎叢集索引，並同時對資料行存放區索引執行分析。 如需更多資訊，請參閱開始使用 Columnstore 進行即時作業分析。

批次模式執行

批次模式執行是用來同時處理多個資料列的查詢處理方法。 執行批次模式時，會與欄存儲格式緊密整合，並對其進行最佳化。 批次模式執行有時又稱為向量式或向量化執行。 資料行存放區索引的查詢使用批次模式執行，通常可改善查詢效能 2 至 4 倍。 如需詳細資訊，請參閱查詢處理架構指南。

為什麼應該使用列存儲索引？

資料列存放區索引可提供非常高的資料壓縮，通常最多可達 10 倍，因此可大幅降低數據倉儲儲存成本。 資料行存放區索引在分析時所提供的效能遠比 B 型樹狀結構索引還高。 資料行存放區索引是資料倉儲和分析工作負載的慣用資料儲存格式。 從 SQL Server 2016 (13.x) 開始，您可以使用資料行存放區索引，對您的作業工作負載進行即時分析。

欄位存放索引如此快速的原因：

• 資料行會儲存來自相同網域的值，而且通常會有類似的值，因此壓縮率會很高。 您系統中的 I/O 瓶頸會減至最少或消失，而且會大幅減少記憶體耗用量。

• 高壓縮率會透過使用較小的記憶體中耗用量改善查詢效能。 而查詢效能可獲得改善是因為 SQL Server 能夠執行更多記憶體中查詢及資料作業。

• 批次執行藉由同時處理多個資料列來改善查詢效能，通常是 2 至 4 倍。

• 查詢通常只會選取資料表中的少數資料行，如此可降低讀取實體媒體的總 I/O 量。

何時應該使用資料行存放區索引？

建議使用案例：

• 使用叢集資料行儲存索引來儲存用於資料倉儲工作負載的事實資料表和大型維度資料表。 此方法可以提升查詢效能和資料壓縮最多 10 倍。 如需詳細資訊，請參閱資料倉儲的資料行存放區索引。

• 使用非叢集資料行存放區索引，對 OLTP 工作負載執行即時分析。 如需更多資訊，請參閱開始使用 Columnstore 進行即時作業分析。

• 如需資料行存放區索引的更多使用案例，請參閱視需要選擇最適合的資料行存放區索引。

如何在資料列存放區索引與資料行存放區索引之間進行選擇？

行存儲索引在查詢深入資料時、搜尋特定值，或只查詢小範圍數值時，提供最佳效能。 在有交易型工作負載的情況下使用行存儲索引，因為這樣的工作負載通常需要以資料表查詢為主，而非資料表掃描。

資料行索引可顯著提升分析查詢的效能，尤其是在大型資料表上掃描大量資料時。 請在資料倉儲與分析工作負載中（尤其是事實資料表）使用資料行儲存索引，因為它們通常需要進行完整的資料表掃描，而不是資料表搜尋。

已排序的叢集資料行存放區索引會根據已排序的資料行述詞改善查詢的效能。 已排序的資料行存放區索引能夠增強資料列群組排除，透過完全跳過資料列群組來提升效能。 如需詳細資訊，請參閱使用已排序的數據行存放區索引 效能微調。 如需已排序的數據行存放區索引可用性，請參閱 已排序的數據行索引可用性。

我可以將資料列存放區和資料行存放區合併到同一個資料表嗎？

是。 從 SQL Server 2016 (13.x) 開始，您可以在資料列存放區資料表上建立可更新的非叢集資料行存放區索引。 資料行存放區索引會儲存所選資料行的複本，因此您需要額外的空間來存放此資料，但所選資料平均會壓縮 10 倍。 您就可以同時在資料行存放區索引上執行分析，並在資料列存放區索引上執行交易。 當行存儲表中的資料發生變更時，列存儲也會更新，以確保兩個索引對相同的資料進行操作。

從 SQL Server 2016 (13.x) 開始，您在資料行存放區索引上可以有一個或多個非叢集資料列存放區索引，而且可以針對基礎資料行存放區執行有效率的資料表搜尋。 其他選項也變得可用。 例如，您可以在列存儲資料表上使用唯一約束，強制執行主索引鍵約束。 由於非唯一值將無法插入至資料列存放區資料表，因此 SQL Server 無法將值插入至資料行存放區。

已排序的列存儲索引

藉由啟用有效率的區段消除，已排序的數據行存放區索引會略過大量不符合查詢述詞的已排序數據，以提供更快的效能。 由於數據排序作業，將數據載入已排序的數據行存放區索引可能需要比非排序索引中更長的時間，不過使用已排序的數據行存放區索引查詢之後可以更快執行。

• 如需有關使用已排序資料行存放區索引在 SQL Database Engine 中進行數據倉儲工作負載效能微調的更多資訊，請參閱 使用已排序的資料行存放區索引進行效能微調。
• 如需何時使用哪一種數據行存放區索引的詳細資訊，請參閱 為您的需求選擇最佳的數據行存放區索引。

已排序的列存儲索引可用性

自 SQL Server 2022（16.x）首次引入以來，已排序的列存儲索引可在以下平台中使用：

平台	已排序的叢集列存儲索引	已排序的非叢集列儲存索引
Azure SQL Database	是	是
Azure SQL 受控實例AUTD	是	是
Azure SQL 受控實例2022	是	不
Microsoft Fabric SQL 資料庫	是1	是
SQL Server 2022 (16.x)	是	不
Azure Synapse Analytics 中的專用 SQL 集區	是	不

^AUTD 適用於使用 Always-up-to-date 更新原則設定的 Azure SQL 受控實例。
²⁰²² 適用於使用 SQL Server 2022 更新原則設定的 Azure SQL 受控實例。
¹In Fabric SQL 資料庫中，具有叢集資料行存放區索引的數據表不會 鏡像至 Fabric OneLake。

中繼資料

欄存索引中的所有資料行都會在中繼資料中儲存為內含欄位。 列存儲索引並不包含鍵欄位。

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

相關工作

所有關聯式資料表都會使用列存儲作為基礎資料格式，除非您將其指定為叢集列存儲索引。 CREATE TABLE 會建立列存放資料表，除非您指定 WITH CLUSTERED COLUMNSTORE INDEX 選項。

當您使用 CREATE TABLE 陳述式建立資料表時，可以指定 WITH CLUSTERED COLUMNSTORE INDEX 選項，將資料表建立為資料行存放區。 如果您已經有一個資料列存放區資料表，並想要將它轉換成資料行存放區，則可以使用 CREATE COLUMNSTORE INDEX 陳述式。

任務	參考文章	備註
建立資料表作為列存儲。	CREATE TABLE (Transact-SQL)	從 SQL Server 2016 (13.x) 開始，您可以建立資料表作為叢集資料行存放區索引。 您不需要先建立列存儲表，然後再將其轉換為行存儲。
建立記憶體最佳化且具有資料行存放區索引的資料表。	CREATE TABLE (Transact-SQL)	從 SQL Server 2016 (13.x) 開始，您可以建立具有資料行存放區索引的記憶體最佳化資料表。 建立資料表之後，也可以使用 ALTER TABLE ADD INDEX 語法來加入資料行存放區索引。
將列存取表轉換成行存取。	CREATE COLUMNSTORE INDEX (Transact-SQL)	將現有的堆狀結構或 B 樹轉換成列存儲。 範例示範如何在執行這項轉換時處理現有的索引及索引名稱。
將列存儲資料表轉換成行存儲。	建立叢集索引 (Transact-SQL) 或將資料行存儲表轉換回資料列存儲堆	此轉換通常並非必要，但有時您仍舊需要轉換。 範例示範如何將資料行存放區轉換成堆積或叢集索引。
在列存放區表上建立欄存放區索引。	CREATE COLUMNSTORE INDEX (Transact-SQL)	列式儲存資料表可以有一個欄式儲存索引。 從 SQL Server 2016 (13.x) 開始，資料行存放區索引可以有一個篩選條件。 範例示範基本語法。
為作業分析建立高效能的索引。	開始使用列存儲進行即時運行分析	描述如何建立互補資料行存放區和 B 型樹狀結構索引，讓 OLTP 查詢使用 B 型樹狀結構索引，而分析查詢使用資料行存放區索引。
為資料倉儲建立高效能的資料行存放區索引。	資料倉儲的資料行存放區索引	描述如何在資料行存放區資料表上使用 B 型樹狀結構索引，建立高效能的資料倉儲查詢。
使用 B-tree 索引對欄存索引施加主鍵約束。	資料倉儲的資料行存放區索引	示範如何合併 B-樹與資料行存放區索引，來在資料行存放區索引上強制執行主索引鍵限制。
刪除列存放區索引。	DROP INDEX (Transact-SQL)	使用 B 樹索引的標準 DROP INDEX 語法來卸除資料行存放區索引。 刪除叢集資料行存放區索引後，資料行存放區資料表會轉換為堆表。
從資料行存放區索引中刪除一列。	DELETE (Transact-SQL)	使用 DELETE (Transact-SQL) 刪除資料列。 資料行存放區資料列：SQL Server 會將該資料列標示為邏輯刪除，但在重建索引之前，不會回收該資料列的實體儲存體。 差異存放區資料列：SQL Server 會以邏輯方式和實際方式刪除該資料列。
更新資料行存放區索引中的列。	UPDATE (Transact-SQL)	使用 UPDATE (Transact-SQL) 更新資料列。 列存儲資料列：SQL Server 會將該資料列標示為邏輯刪除，然後將更新後的資料列插入到增量存儲區中。 差異存放區資料列：SQL Server 會更新差異存放區中的該資料列。
將資料載入資料行存放區索引。	列存儲索引資料載入
強制將所有列從差異存放區移入行存放區。	ALTER INDEX (Transact-SQL) ... REBUILD 將索引維護最佳化以改善查詢效能並降低資源耗用量	ALTER INDEX 搭配 REBUILD 選項會強制將所有資料列移入資料行儲存區。
重組資料行存放區索引。	ALTER INDEX (Transact-SQL)	ALTER INDEX ... REORGANIZE 會線上重組資料行存放區索引。
合併具有資料行存放區索引的資料表。	MERGE (Transact-SQL)

相關內容

• 列存儲索引的最新功能
• 資料行存放區索引 - 資料載入指導
• 資料行存放區索引 - 查詢效能
• 開始使用資料行存放區進行即時作業分析
• 數據倉儲中的數據行存放區索引
• 資料行存放區索引重組
• SQL Server 及 Azure SQL 索引架構與設計指南
• 列存儲索引架構
• CREATE COLUMNSTORE INDEX (Transact-SQL)