資料行存放區索引 - 資料倉儲Columnstore indexes - Data Warehouse

適用於: 是SQL Server是Azure SQL Database是Azure Synapse Analytics (SQL DW)是平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel Data Warehouse

結合資料分割的資料行存放區索引對於建置 SQL ServerSQL Server 資料倉儲非常重要。Columnstore indexes, in conjunction with partitioning, are essential for building a SQL ServerSQL Server data warehouse.

新功能What's new

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 導入了這些增強資料行存放區效能的功能:introduces these features for columnstore performance enhancements:

  • AlwaysOn 支援在可讀取之次要複本上的資料行存放區索引進行查詢。Always On supports querying a columnstore index on a readable secondary replica.
  • Multiple Active Result Sets (MARS) 支援資料行存放區索引。Multiple Active Result Sets (MARS) supports columnstore indexes.
  • 全新的 sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) 動態管理檢視,可提供資料列群組層級的效能疑難排解資訊。A new dynamic management view sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) provides performance troubleshooting information at the row group level.
  • 資料行存放區索引上的單一執行緒查詢可以批次模式執行。Single-threaded queries on columnstore indexes can run in batch mode. 以前只有多執行緒查詢可以批次模式執行。Previously, only multi-threaded queries could run in batch mode.
  • SORT 運算子可在批次模式中執行。The SORT operator runs in batch mode.
  • 多個 DISTINCT 運算子可在批次模式中執行。Multiple DISTINCT operation runs in batch mode.
  • 視窗彙總現可在資料庫相容性層級 130 及更高層級的批次模式下執行。Window Aggregates now runs in batch mode for database compatibility level 130 and higher.
  • 彙總下推可有效處理彙總。Aggregate Pushdown for efficient processing of aggregates. 支援所有資料庫相容性層級。This is supported on all database compatibility levels.
  • 字串述詞下推可有效處理字串述詞。String predicate pushdown for efficient processing of string predicates. 支援所有資料庫相容性層級。This is supported on all database compatibility levels.
  • 資料庫相容性等級 130 及更高層級的快照集隔離。Snapshot isolation for database compatibility level 130 and higher.

透過結合非叢集和資料行存放區索引來改善效能Improve performance by combining nonclustered and columnstore indexes

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,您可以在叢集資料行存放區索引上定義非叢集索引。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can define nonclustered indexes on a clustered columnstore index.

例如:以非叢集索引改善資料表搜尋的效率Example: Improve efficiency of table seeks with a nonclustered index

若要改善在資料倉儲中搜尋資料表的效率,您可以建立專用的非叢集索引來執行對資料表搜尋有最佳效能的查詢。To improve efficiency of table seeks in a data warehouse, you can create a nonclustered index designed to run queries that perform best with table seeks. 例如,針對 B 型樹狀結構索引執行比對值或傳回小範圍值的查詢,其執行效能會比針對資料行存放區索引為佳。For example, queries that look for matching values or return a small range of values will perform better against a B-tree index rather than a columnstore index. 它們不需要透過資料行存放區索引的完整資料表掃描,且透過 B 型樹狀結構索引執行二進位搜尋會較快傳回正確的結果。They don't require a full table scan through the columnstore index and will return the correct result faster by doing a binary search through a B-tree index.

--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);  

範例:使用非叢集索引在資料行存放區資料表上強制執行主索引鍵條件約束。Example: Use a nonclustered index to enforce a primary key constraint on a columnstore table

根據設計,資料行存放區資料表不允許叢集主索引鍵條件約束。By design, a columnstore table does not allow a clustered primary key constraint. 現在您可以在資料行存放區資料表上使用非叢集索引強制執行主索引鍵條件約束。Now you can use a nonclustered index on a columnstore table to enforce a primary key constraint. 在非 NULL 的資料行上主索引鍵等同 UNIQUE 條件約束,而且 SQL ServerSQL Server 會實作 UNIQUE 條件約束來當作非叢集索引。A primary key is equivalent to a UNIQUE constraint on a non-NULL column, and SQL ServerSQL Server implements a UNIQUE constraint as a nonclustered index. 結合這些事實,以下範例在非 NULL 資料行 accountkey 上定義 UNIQUE 條件約束。Combining these facts, the following example defines a UNIQUE constraint on the non-NULL column accountkey. 結果是一個將主索引鍵條件約束強制做為非 NULL 資料行上之 UNIQUE 條件約束的非叢集索引。The result is a nonclustered index that enforces a primary key constraint as a UNIQUE constraint on a non-NULL column.

接下來,該資料表會轉換為叢集資料行存放區索引。Next, the table is converted to a clustered columnstore index. 轉換期間會保留非叢集索引。During the conversion, the nonclustered index persists. 結果是一個包含強制執行主索引鍵條件約束之非叢集索引的叢集資料行存放區索引。The result is a clustered columnstore index with a nonclustered index that enforces a primary key constraint. 因為資料行存放區資料表上的任何更新或插入都會影響非叢集索引,所以任何違反唯一條件約束和非 NULL 的作業都會造成整個作業失敗。Since any update or insert on the columnstore table will also affect the nonclustered index, all operations that violate the unique constraint and the non-NULL will cause the entire operation to fail.

結果是一個包含強制在兩個索引上執行主索引鍵條件約束之非叢集索引的資料行存放區索引。The result is a columnstore index with a nonclustered index that enforces a primary key constraint on both indexes.

--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); 

藉由啟用資料列層級和資料列群組層級鎖定來改善效能Improve performance by enabling row-level and row-group-level locking

為了補充資料行存放區索引功能上的非叢集索引, SQL Server 2016 (13.x)SQL Server 2016 (13.x) 提供選取、更新和刪除作業的詳細鎖定功能。To complement the nonclustered index on a columnstore index feature, SQL Server 2016 (13.x)SQL Server 2016 (13.x) offers granular locking capability for select, update, and delete operations. 查詢能夠針對非叢集索引在索引搜尋上以資料列層級鎖定執行,或針對資料行存放區索引在完整資料表掃描上以資料行群組層級鎖定執行。Queries can run with row-level locking on index seeks against a nonclustered index and rowgroup-level locking on full table scans against the columnstore index. 藉由適當地使用資料列層級和資料列群組層級鎖定,來以此方法達到更高的獨取/寫入並行。Use this to achieve higher read/write concurrency by using row-level and rowgroup-level locking appropriately.

--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;  
END TRAN  

快照集隔離和讀取認可快照集隔離Snapshot isolation and read-committed snapshot isolations

使用「快照集隔離」(SI) 來保證交易的一致性,以及「讀取認可快照集隔離」(RCSI) 來確保資料行存放區索引上查詢陳述式層級的一致性。Use snapshot isolation (SI) to guarantee transactional consistency, and read-committed snapshot isolations (RCSI) to guarantee statement level consistency for queries on columnstore indexes. 這可讓查詢執行而不會封鎖資料寫入器。This allows the queries to run without blocking data writers. 此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions. 如需詳細資訊,請參閱 MSDN 上的 SQL Server 中的快照集隔離For more information, see Snapshot Isolation in SQL Server on MSDN.

另請參閱See Also

資料行存放區索引設計指導 Columnstore Indexes Design Guidance
資料行存放區索引資料載入指導 Columnstore Indexes Data Loading Guidance
資料行存放區索引效能 Columnstore Indexes Query Performance
開始使用資料行存放區進行即時作業分析 Get started with Columnstore for real-time operational analytics
重新組織與重建索引 Reorganize and Rebuild Indexes
資料行存放區索引架構Columnstore Index Architecture