列存储索引 - 数据仓库Columnstore indexes - Data Warehouse

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse 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.
  • 多个活动的结果集 (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 primary key constraint. 现在可以在列存储表上使用非聚集索引,以强制实施主键约束。Now you can use a nonclustered index on a columnstore table to enforce a primary key constraint. 主键等同于非 NULL 列上的唯一约束,SQL ServerSQL Server 将唯一约束作为非聚集索引实施。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 列帐户密钥上的唯一约束。Combining these facts, the following example defines a UNIQUE constraint on the non-NULL column accountkey. 结果获得非聚集索引,它将主键约束强制实施为非 NULL 列上的唯一约束。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 Columnstore Indexes Query Performance
开始使用列存储进行实时运营分析 Get started with Columnstore for real-time operational analytics
重新组织和重新生成索引 Reorganize and Rebuild Indexes
列存储索引体系结构Columnstore Index Architecture