數據倉儲中的欄儲存索引

2025-04-18

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Analytics Platform System （PDW）Microsoft Fabric 中的 SQL 資料庫

列存儲索引結合資料分區，對於構建 SQL Server 資料倉儲非常重要。本文著重於使用 SQL 資料庫引擎進行數據倉儲設計的主要使用案例和範例。

資料倉儲的主要功能

SQL Server 2016 (13.x) 導入了這些增強資料行存放區效能的功能：

AlwaysOn 可用性群組支持查詢可讀取次要複本上的數據行存放區索引。
Multiple Active Result Sets (MARS) 支援列存儲索引。
全新動態管理檢視 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) 提供資料列群組層級的效能疑難排解資訊。
數據行存放區索引上的所有查詢都可以在批次模式中執行。先前，只有平行查詢可以在批次模式中執行。
Sort、Distinct Sort 和 Distinct 運算符會以批次模式執行。
窗口匯總現在會以批次模式執行，以達到資料庫相容性層級 130 和更高層級。
匯總下推技術，用於更有效地處理匯總。支援所有資料庫相容性層級。
字串謂詞下推技術可有效處理字串謂詞。支援所有資料庫相容性層級。
資料庫相容性等級 130 以上的快照隔離。
SQL Server 2022 （16.x）引進已排序的叢集數據行存放區索引。如需詳細資訊，請參閱 CREATE COLUMNSTORE INDEX 和使用已排序的數據行存放區索引進行效能微調。如需已排序的數據行存放區索引可用性，請參閱已排序的數據行索引可用性。

如需了解 SQL Server 和 Azure SQL 各版本與平台的新功能，請參閱資料行存放區索引的最新功能。

透過結合非叢集和資料行存放區索引來改善效能

從 SQL Server 2016 （13.x）開始，您可以在叢集數據行存放區索引上建立數據列存放區非叢集索引。

例如：以非叢集索引改善資料表搜尋的效率

若要改善在資料倉儲中搜尋資料表的效率，您可以建立專用的非叢集索引來執行對資料表搜尋有最佳效能的查詢。例如，進行符合值或傳回小範圍值的查詢時，相較於列存儲索引，B 樹索引的效能更佳。它們不需要完整掃描數據行存放區索引，並透過 B 型樹狀結構索引執行二進位搜尋，更快速地傳回正確的結果。

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.

--Create the table
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--Add a nonclustered index.
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

範例：使用非叢集索引在資料列存放區資料表上設置主索引鍵條件約束。

因為數據表最多可以有一個叢集索引，因此具有叢集數據行存放區索引的數據表不能有叢集主鍵條件約束。若要在數據行存放區數據表上建立主鍵條件約束，您必須將其宣告為非叢集。

下列範例會建立具有非叢集主鍵條件約束的數據表，然後在數據表上建立叢集數據行存放區索引。由於數據行存放區數據表上的任何插入或更新也會修改非叢集索引，因此違反主鍵條件約束的所有作業都會導致整個作業失敗。

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

啟用數據列層級和數據列群組層級鎖定來改善效能

為了補充行列存儲索引特性上的非叢集索引，SQL Server 2016 （13.x）提供對SELECT、UPDATE和DELETE作業的細粒度鎖定功能。查詢能夠針對非叢集索引在索引搜尋上以資料列層級鎖定執行，或針對資料行存放區索引在完整資料表掃描上以資料行群組層級鎖定執行。藉由適當地使用資料列層級和資料列群組層級鎖定，達到更高的讀寫並行性。

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRAN
    -- The query plan chooses a seek operation on the nonclustered index
    -- and takes the row lock
    SELECT * 
    FROM t_account 
    WHERE AccountKey = 100;
COMMIT TRAN;

快照隔離和讀取提交快照隔離

使用快照隔離（SI）來保證交易一致性，並使用讀提交快照隔離（RCSI）來保證列存索引查詢的語句層級一致性。這可讓查詢執行而不會封鎖資料寫入器。此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。如需詳細資訊，請參閱 Database Engine 中的數據列版本設定隔離等級。

共用方式為