SQL Server 2012 中的列存储索引
4.36/5 (5投票s)
本文解释了列存储索引的内部机制及其优缺点。
引言
SQL Server 2012 为我们引入了一项名为列存储索引 (CSI) 的新功能，该功能极大地提高了查询性能。本文分为以下几个部分
- 理解 CSI
- CSI 演示
- CSI 的限制
- 执行查询，忽略 CSI
背景
SQL Server 直到 2008R2 版本，都支持两种存储类型——堆 (Heap) 和 B 树 (B-Trees)。没有任何索引的表中的数据会以堆存储模式存储，而带有索引（聚集或非聚集）的表中的数据则以 B 树结构存储。但是，堆和 B 树存储模式都以行式存储数据，而列存储索引 (CSI) 则以列式格式存储数据。这将带来巨大的性能提升，我们将在本文后面介绍。为了进一步加快此类查询的速度，SQL 2012 还引入了一种新的查询处理模式，称为“批处理模式”，其中操作符处理一批行（通常是 1000 行），而不是一次处理一行。列存储索引结合批处理模式，通常可以将查询性能提高 10 倍，在某些情况下甚至提高 100 倍。
CSI 如何提高性能？
SQL Server 中的基本存储单位称为“页 (Page)”。任何数据库的 mdf 文件都分成 n 个页，理想情况下，页的数量会随着数据库中数据的增长而增长。SQL Server 中的页大小为 8 KB。一个区 (Extent) 由 8 个物理上连续的页组成，即 64 KB。一个区可以是统一区（数据仅属于 1 个表）或混合区（数据可以属于多个表）。一旦一个区（64 KB）的大小被填满，就会立即在新区中填充数据。
现在，为了演示方便，我们不考虑实际大小。我们考虑以下场景。假设有一个包含 `EmpID`、`EmpName` 和 `Designation` 列的员工表。假设表中共有 6 行数据，每页包含 3 行（仅为演示目的）。
现在，让我们看看在没有 CSI 和有 CSI 的情况下数据是如何检索的。
select empname from tblEmployees
下图显示了在没有 CSI 的情况下，数据是如何存储和查询的。
（数据以行式存储在页中。）
现在，让我们在表上创建列存储索引（稍后会显示语法），下图显示了检索过程。
（数据以列式存储在页中。）
可以看到，在第一个查询中，数据是从 2 个页中检索的，而在第二个查询中，数据仅从 1 个页中检索，因此磁盘 I/O 操作次数会减少，从而提高了查询性能。
演示
在此演示中，我们将使用 `AdventureworksDW` 数据库，该数据库可在 www.codeplex.com 上找到。
在 Management Studio 中打开一个新的查询窗口，并启用统计信息，以便可以看到查询运行所需的时间。
SET STATISTICS TIME ON
SET STATISTICS IO ON
现在运行以下查询；
select ProductKey,UnitPrice,CustomerPONumber,OrderDate from FactResellerSales
由于启用了统计信息，我们可以在输出窗口的消息选项卡中看到逻辑读取次数和 CPU 时间，如下所示。
现在，让我们使用以下查询在此表上创建列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate,SalesOrderNumber);
这需要几秒钟才能运行，因为 SQL Server 会重新组织数据存储机制。运行此查询后，再次运行 `select` 查询。现在观察相应的逻辑读取次数和 CPU 时间之间的差异。
可以看到，逻辑读取次数的改进超过 10 倍，而 CPU 时间缩短了一半。当记录数量巨大时，这个比例会更大。
CSI 的限制
- SQL 2012 中 CSI 的最大限制是，一旦对表应用了 CSI，该表将变为只读。该表不支持任何 `Insert`/`Update`/`Delete` 操作，因为 CSI 的设计理念是仅在数据量巨大或不经常更新的表上创建它们。但在大多数实际情况下，这并不能满足需求。此问题的解决方案也在下文（SQL 2014 支持可更新的 CSI）中进行了说明。
- 列存储索引（SQL 2012）不支持所有数据类型的列。受限类型包括：
- 二进制
- 文本
- 图像
- varchar(max)
- xml
- uniqueidentifier
更新 CSI 表中的数据
要在具有列存储索引的表上执行 `Insert` 或 `update` 或 `delete` 操作，有 2 种选择：
- 删除索引，执行所需的操作，然后重新创建索引。
- 禁用索引，执行所需的操作，然后重新生成索引。
但这些选项可能不适合实际场景。对于数据量巨大的表，在此处介绍了执行这些操作的最佳方法。
- 在具有大量数据且不进行更新操作的表（例如历史数据）上创建 CSI。
- 创建另一个具有相似表设计的表，只是在该表上未定义 CSI。
- 现在，此表可以包含当前记录，这些记录可能会被更新、插入或删除。
- 检索时，使用 `union` 从两个表中拉取记录，这样就可以更快地检索历史数据。
在查询中忽略 CSI 使用
假设有一个已定义 CSI 的表。现在，如果您想在不考虑 CSI 的情况下运行查询，可以使用以下查询。
select ProductKey,UnitPrice,CustomerPONumber,OrderDate from FactResellerSales
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
结论
列存储索引在适当使用时，可以减少磁盘 I/O 并更有效地利用内存。这最适合需要扫描和聚合大量数据的查询。应在只读工作负载中使用。这些限制在 SQL 2014 中得到了解决，并允许创建聚集列存储索引 (CCSI)，这意味着表也可以被更新。