資料行存放區索引 - 資料倉儲

發行項
05/04/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體 Analytics Platform System (PDW)

資料行存放區索引與資料分割結合使用，對於建置 SQL Server 資料倉儲非常重要。

資料倉儲的主要功能

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

AlwaysOn 支援在可讀取之次要複本上的資料行存放區索引進行查詢。
Multiple Active Result Sets (MARS) 支援資料行存放區索引。
全新的 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) (部分機器翻譯) 動態管理檢視，可提供資料列群組層級的效能疑難排解資訊。
資料行存放區索引上的單一執行緒查詢可以批次模式執行。以前只有多執行緒查詢可以批次模式執行。
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  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--Add a nonclustered index.  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

範例：使用非叢集索引在資料行存放區資料表上強制執行主索引鍵條件約束。

根據設計，資料行存放區資料表不允許叢集主索引鍵條件約束。現在您可以在資料行存放區資料表上使用非叢集索引強制執行主索引鍵條件約束。在非 NULL 的資料行上主索引鍵等同 UNIQUE 條件約束，而且 SQL Server 會實作 UNIQUE 條件約束來當作非叢集索引。結合這些事實，以下範例在非 NULL 資料行 accountkey 上定義 UNIQUE 條件約束。結果是一個將主索引鍵條件約束強制做為非 NULL 資料行上之 UNIQUE 條件約束的非叢集索引。

接下來，該資料表會轉換為叢集資料行存放區索引。轉換期間會保留非叢集索引。結果是一個包含強制執行主索引鍵條件約束之非叢集索引的叢集資料行存放區索引。因為資料行存放區資料表上的任何更新或插入都會影響非叢集索引，所以任何違反唯一條件約束和非 NULL 的作業都會造成整個作業失敗。

結果是一個包含強制在兩個索引上執行主索引鍵條件約束之非叢集索引的資料行存放區索引。

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

藉由啟用資料列層級和資料列群組層級鎖定來改善效能

為了補充資料行存放區索引功能上的非叢集索引，SQL Server 2016 (13.x) 提供選取、更新與刪除作業的詳細鎖定功能。查詢能夠針對非叢集索引在索引搜尋上以資料列層級鎖定執行，或針對資料行存放區索引在完整資料表掃描上以資料行群組層級鎖定執行。藉由適當地使用資料列層級和資料列群組層級鎖定，來以此方法達到更高的獨取/寫入並行。

--Granular locking example  
--Store table t_account as a columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
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) 來確保資料行存放區索引上查詢陳述式層級的一致性。這可讓查詢執行而不會封鎖資料寫入器。此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。如需詳細資訊，請參閱 SQL Server 中的快照隔離 (部分機器翻譯)。