列存储索引的新增功能

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

了解可用于每个版本的 SQL Server 的列存储功能，以及 SQL 数据库、Azure Synapse Analytics 和 Analytics Platform System (PDW) 的最新版本。

产品发布的功能摘要

此表概述了列存储索引的主要功能以及提供这些功能的产品。

列存储索引功能 SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) SQL 数据库1 Azure Synapse Analytics 专用 SQL 池
多线程查询的批模式执行2 是 是 是 是 是 是 是 是
单线程查询的批模式执行 是 是 是 是 是 是
存档压缩选项 是 是 是 是 是 是 是
快照隔离和读提交快照隔离 是 是 是 是 是 是
创建表时，请指定列存储索引 是 是 是 是 是 是
Always On 支持列存储索引 是 是 是 是 是 是 是 是
Always On 可读次要副本支持只读非聚集列存储索引 是 是 是 是 是 是 是 是
Always On 可读次要副本支持可更新列存储索引 是 是 是 是
堆或 B 树上的只读非聚集列存储索引 是 是 是 3 是 3 是 3 是 3 是 3 是 3
堆或 B 树上的可更新非聚集列存储索引 是 是 是 是 是 是
允许在使用非聚集列存储索引的堆或 B 树上实施额外的 B 树索引 是 是 是 是 是 是 是 是
可更新的聚集列存储索引 是 是 是 是 是 是 是
基于聚集列存储索引的 B 树索引 是 是 是 是 是 是
基于内存优化表的列存储索引 是 是 是 是 是 是
非聚集列存储索引定义支持使用筛选的条件 是 是 是 是 是 是
CREATE TABLE 和 ALTER TABLE 中的列存储索引的压缩延迟选项 是 是 是 是 是 是
支持 nvarchar(max) 类型 是 是 是 是 否 4
列存储索引具有一个非持久化计算列 是 是 是
元组移动器背景合并支持 是 是 是 是
有序聚集列存储索引 是 是 是

1 对于 SQL 数据库，列存储索引可用于 Azure SQL 数据库 DTU 高级层、DTU 标准层（S3 及更高）以及所有 vCore 层。 对于 SQL Server 2016 (13.x) SP1 及更高版本，列存储索引可用于所有版本。 对于 SQL Server 2016 (13.x)（早于 SP1）及更早版本，列存储索引仅可用于企业版。

2批处理模式操作的并行度 (DOP) 限制为 2（针对 SQL Server Standard Edition）和 1（针对 SQL Server Web Edition 和 Express Edition）。 该限制是指在基于磁盘的表和内存优化表上创建的列存储索引。

3 要创建只读非聚集列存储索引，请将索引存储在只读文件组内。

4 虽然专用 SQL 池中不支持，但在无服务器 SQL 池中支持。

SQL Server 2022 (16.x)

SQL Server 2022 (16.x) 添加了这些新功能。 有关详细信息，请参阅 SQL Server 2022 (16.x) 中的新增功能。

  • 有序聚集列存储索引可提高基于有序列谓词的查询性能。 有序列存储索引可以通过完全跳过数据段来提高性能。 这可以大幅减少完成列存储数据查询所需的 IO。 有关详细信息，请参阅区段消除。 SQL Server 2022 (16.x) 中引入了有序聚集列存储索引。 有关详细信息，请参阅 CREATE COLUMNSTORE INDEX 和使用有序聚集列存储索引调整性能。

  • 使用聚集列存储行组消除字符串的谓词下推使用边界值来优化字符串搜索。 所有列存储索引都受益于按数据类型增强的段消除。 从 SQL Server 2022 (16.x) 开始，段消除功能扩展到字符串、二进制文件、guid 数据类型以及用于小数位数大于 2 的 datetimeoffset 数据类型。 以前，列存储段消除仅适用于数值、日期和时间数据类型，以及小数位数小于或等于 2 的 datetimeoffset 数据类型。 升级到支持字符串 min/max 段消除的 SQL Server 版本（SQL Server 2022 (16.x) 及更高版本）之后，列存储索引在使用 REBUILD 或 DROP/CREATE 重新生成之前不会受益于此功能。

SQL Server 2019 (15.x)

SQL Server 2019 (15.x) 添加了这些新功能：

功能性

从 SQL Server 2019 (15.x) 开始，元组移动器通过后台合并任务获得帮助，该任务会自动压缩较小的已存在一段时间（由内部阈值确定）的 OPEN 增量行组，或者合并已从中删除大量行的 COMPRESSED 行组。 以前，需要索引重新组织操作才能将行组与部分删除的数据合并。 随着时间的推移，这会提高列存储索引的质量。

SQL Server 2017 (14.x)

SQL Server 2017 (14.x) 添加了这些新功能。

功能性

  • SQL Server 2017 (14.x) 支持聚集列存储索引中的非持久化计算列。 聚集列存储索引不支持持久化计算列。 无法在计算列上创建非聚集列存储索引。

SQL Server 2016 (13.x)

SQL Server 2016 (13.x) 添加了重要的增强功能，目的是改进列存储索引的性能和灵活性。 这些改进功能可以增强数据仓库方案的效果，并实现实时运营分析。

功能性

  • 一个行存储表可以有一个可更新的非聚集列存储索引。 以前，非聚集列存储索引是只读的。

  • 非聚集列存储索引定义支持使用筛选的条件。 若要尽量减少在 OLTP 表中添加列存储索引的性能影响，请使用筛选条件，以便创建仅关于运行工作负荷冷数据的非聚集列存储索引。

  • 一个内存中表可以有一个列存储索引。 你可以在创建表时创建它，也可以稍后使用 ALTER TABLE (Transact-SQL) 来添加。 以前，仅基于磁盘的表可以有列存储索引。

  • 聚集列存储索引可以有一个或多个非聚集行存储索引。 以前，列存储索引不支持非聚集索引。 SQL Server 自动维护 DML 操作的非聚集索引。

  • 支持主键和外键，即可通过使用 B 树索引在聚集列存储索引上强制实施这些约束。

  • 列存储索引有一个压缩延迟选项，该选项可以最大限度地减少事务工作负荷对实时运营分析的影响。 此选项允许通过频繁地更改行来保持稳定，然后再将这些行压缩到列存储中。 有关详细信息，请参阅 CREATE COLUMNSTORE INDEX (Transact-SQL) 和开始使用列存储进行实时运营分析。

数据库兼容级别 120 或 130 的性能

  • 列存储索引支持读提交快照隔离级别 (RCSI) 和快照隔离 (SI)。 这样可以在无锁的情况下进行事务一致性分析查询。

  • 列存储支持索引碎片整理，即可以移除已删除的行而无需显式重新生成索引。 ALTER INDEX ... REORGANIZE 语句将根据内部定义的策略，以联机操作的方式从列存储移除已删除的行

  • 可在 Always On 可读次要副本上访问列存储索引。 可将分析查询分流到 Always On 次要副本，从而改进运营分析的性能。

  • 聚合下推会在表扫描期间计算聚合函数 MIN、MAX、SUM、COUNT 和 AVG，前提是数据类型不超过 8 个字节的长度，且不是字符串数据类型。 无论有没有 GROUP BY 子句，聚合列存储索引和非聚合列存储索引都支持聚合下推。 在 SQL Server 上，此增强功能专用于企业版。

  • 通过字符串谓词下推，可使比较 VARCHAR/CHAR 或 NVARCHAR/NCHAR 类型的字符串的查询速度更快。 这适用于常用的比较运算符，包括 LIKE 等使用位图筛选器的运算符。 这适用于所有支持的排序规则。 在 SQL Server 上，此增强功能专用于企业版。

  • 利用基于矢量的硬件功能增强批处理模式操作。 数据库引擎会检测 AVX 2（高级矢量扩展）和 SSE 4（流式处理 SIMD 扩展 4）硬件扩展的 CPU 支持级别，并使用它们（若支持）。 在 SQL Server 上，此增强功能专用于企业版。

数据库兼容级别 130 的性能

  • 对于使用任何下述操作的查询，新提供了批处理模式执行支持：

    • SORT
    • 使用多个不同函数的聚合函数。 一些示例：COUNT/COUNT、AVG/SUM、CHECKSUM_AGG、STDEV/STDEVP
    • 窗口聚合函数：COUNT、COUNT_BIG、SUM、AVG、MIN、MAX 和 CLR
    • 窗口用户定义聚合：CHECKSUM_AGG、STDEV、STDEVP、VAR、VARP 和 GROUPING
    • 窗口聚合分析函数：LAG、LEAD、FIRST_VALUE、LAST_VALUE、PERCENTILE_CONT、PERCENTILE_DISC、CUME_DIST 和 PERCENT_RANK

  • 在 MAXDOP 1 下运行或使用串行查询计划以批处理模式执行的单线程查询。 以前，仅多线程查询以批处理执行的方式运行。

  • 无论是按行存储还是按列存储索引方式访问数据，内存优化表查询都可具有 SQL 互操作模式的并行计划。

可支持性

以下系统视图是针对列存储的新视图：

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (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_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

以下内存中 OLTP 式 DMV 包含列存储的更新：

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

限制

  • 对于内存中表，列存储索引必须包括所有列；列存储索引不能有经过筛选的条件。
  • 对于内存中表，基于列存储索引的查询仅在互操作模式下运行，不在内存中本机模式下运行。 支持并行执行。

已知问题

适用范围：SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Azure Synapse Analytics 专用 SQL 池

  • 目前，压缩列存储区段中的 LOB 列（varbinary(max)、varchar(max) 和 nvarchar(max)）不受 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 影响。

SQL Server 2014 (12.x)

SQL Server 2014 (12.x) 引入了聚集列存储索引作为主存储格式。 这样就可以进行常规加载以及更新、删除和插入操作。

  • 表可以使用聚集列存储索引作为主表存储。 不允许在表上使用其他索引，但可对聚集列存储索引进行更新，因此可执行常规加载并对各个行进行更改。
  • 非聚集列存储索引的功能仍与 SQL Server 2012 (11.x) 中的一样，区别是增加了能够以批处理模式执行的运算符。 该索引仍然不能进行更新，只能重新生成和使用分区切换。 非聚集列存储索引只能用于基于磁盘的表，不能用于内存中表。
  • 聚集和非聚集列存储索引有一个存档压缩选项，允许进一步压缩数据。 存档选项用于减少内存中数据和磁盘上数据的大小，但会降低查询执行速度。 它适用于访问不频繁的数据。
  • 聚集列存储索引和非聚集列存储索引的作用方式非常类似：使用相同的列存储格式、相同的查询处理引擎，以及相同的动态管理视图集。 不同之处在于，一个是主要索引类型，一个是次要索引类型，而非聚集列存储索引为只读。
  • 对于多线程查询来说，以下运算符以批处理模式运行：scan、filter、project、join、group by 和 union all。

SQL Server 2012 (11.x)

SQL Server 2012 (11.x) 引入了非聚集列存储索引作为另一基于行存储表的索引类型，并为基于列存储数据的查询引入了批处理。

  • 一个行存储表可以有一个非聚集列存储索引。
  • 列存储索引是只读的。 创建列存储索引以后，不能通过INSERT、DELETE和UPDATE操作来更新表；要执行这些操作，必须在删除索引后更新表，然后重新生成列存储索引。 可以使用分区切换将其他数据加载到表中。 分区切换的优点是，你可以在不删除和重新生成列存储索引的情况下加载数据。
  • 列存储索引始终需要额外的存储空间，通常需要在行存储的基础上再多出 10%，因为它会存储数据的副本。
  • 批处理的查询性能会翻倍，或者说批处理会改善查询性能，但这仅适用于并行执行查询的情况。

相关内容