设计索引

SQL Server 提供多种索引类型来支持不同的工作负载。 在较高的层次上，可以将索引视为与表或视图相关联的磁盘结构，与扫描整个表相比，它使 SQL Server 能够更轻松地查找与索引键（由表或视图中的一列或多列组成）关联的一行或多行。

聚集索引

常见的 DBA 工作面试问题是询问应聘者聚集索引和非聚集索引之间的差异，因为索引是 SQL Server 中的基础数据存储技术。 聚集索引是基础表，根据键值按排序顺序存储。 给定表上只能有一个聚集索引，因为行只能按一个顺序存储。 没有聚集索引的表称为堆，而堆通常仅用作临时表。 一个重要的性能设计原则是使聚集索引键尽可能地窄。 在考虑将一个或多个关键列用于聚集索引时，应选择包含唯一值或包含多个非重复值的列。 良好的聚集索引键的另一个属性是用于按顺序访问的记录，这些记录经常用于对从表中检索到的数据进行排序。 在用于排序的列上使用聚集索引可以避免在每次执行查询时进行排序所需的开销，因为数据已按所需顺序存储。

非聚集索引

非聚集索引是与数据行分开的结构。 非聚集索引包含为索引定义的键值，以及指向包含该键值的数据行的指针。 可以使用 SQL Server 中的包含列功能将额外的非键列添加到非聚集索引的叶级别，以便可以覆盖更多列。 可以对表创建多个非聚集索引。

以下示例显示了何时需要向现有非聚集索引添加索引或添加列。

查询计划指示，对于使用索引查找检索的每一行，需要从聚集索引（表本身）检索更多数据。 存在一个非聚集索引，但其仅包括产品列。 如果将查询中的其他列添加到非聚集索引，则可以看到执行计划发生了更改，从而消除了键查找。

上面创建的索引是涵盖索引的示例。 除键列外，还会包括用于涵盖查询的附加列，这样就不需要访问表本身。

非聚集索引和聚集索引都可以定义为唯一索引，这意味着键值不能重复。 在表上创建 PRIMARY KEY 或 UNIQUE 约束时，会自动创建唯一索引。

本部分重点介绍 SQL Server 中的 B 树索引，也称为行存储索引。 下示意图示 B 树的一般结构：

索引 B 树中的每个页面称为一个索引节点，B 树的顶部节点称为根节点。 索引中的底部节点称为叶节点，叶节点的集合为叶级别。

索引设计是艺术与科学的结合。 其键中列数较少的窄索引需要较少的更新时间，且维护开销也较低；但是，对于像包含更多列的较宽索引这样的查询而言，它可能没有作用。 可能需要根据应用程序查询所选的列尝试几种索引方法。 查询优化器通常会为查询选择它认为最好的现有索引，然而，这并不意味着没有更好的索引可以建立。

正确地为数据库编制索引是一项复杂的任务。 在为表计划索引时，应牢记以下几项基本原则：

• 了解系统的工作负载。 与用于具有高读取活动的数据仓库操作的表相比，主要用于插入操作的表从额外索引中获益较少。
• 围绕最常运行的查询优化索引。
• 为查询中的列选择适当的数据类型。 索引最适合用于整数数据类型、唯一或非 null 列。
• 在谓词和连接子句中经常使用的列上创建非聚集索引，并使其尽可能狭窄以最大程度地减少开销。
• 考虑数据大小/容量。 对小表的表扫描相对便宜，而对大表的扫描成本较高。

SQL Server 提供的另一种选择是创建筛选索引。 筛选索引非常适合大型表中的列，其中相当大比例的行在该列中共享相同的值。 以下示例是一个员工表，其中存储了所有员工的记录，包括离职或停用的员工。

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

在此表中，有一个名为 CurrentFlag 的列，该列指示当前是否雇用了某员工。 本示例使用 bit 数据类型，仅指示两个值，1 表示当前正在雇佣，0 表示当前未雇佣。 在 CurrentFlag 列上使用 WHERE CurrentFlag = 1 创建筛选索引可以高效查询当前员工。

还可以为视图创建索引，当视图包含诸如聚合和/或表联接之类的查询元素时，此操作可以显著提高性能。

列存储索引

列存储索引列存储索引为涉及到大型聚合工作负载的查询提供了增强的性能。 列存储索引最初针对数据仓库，后来被用于各种其他工作负载，以解决大表上的查询性能问题。 像 B 树索引一样，聚集列存储索引是以特殊方式存储的表本身，而非聚集列存储索引则独立于表存储。 聚集列存储索引本质上包含表中的所有列，但未排序。

非聚集列存储索引通常用于两种方案。 第一种情况是列存储索引不支持列的数据类型（例如，XML、CLR、sql_variant、ntext、text 和 image）。 由于聚集列存储索引始终包含表的所有列，因此非聚集索引是唯一的选择。 第二种方案涉及到筛选索引，用于混合事务分析处理 (HTAP) 体系结构，其中数据被加载到表中，同时运行报告。 筛选索引（通常在日期字段上）可以实现高效的插入和报告性能。

列存储索引独立存储每一列，具有两个好处：通过仅扫描所需的列来减少 IO，以及由于列中的数据相似而实现更高的压缩率。 它们在扫描大型数据集（例如数据仓库中的事实表）的分析查询方面表现最佳。 可以使用 B 树非聚集索引来扩充列存储索引，以进行单一实例值查找。

这些索引还受益于批处理执行模式，一次处理一组行（通常约 900 条），而不是逐一处理。 此方法大大减少了 CPU 指令。

SELECT SUM(Sales) FROM SalesAmount;

批处理模式可以提高传统行处理的性能。 尽管行存储的批处理模式没有与列存储索引相同的读取性能，但是分析查询可能将性能提高多达 5 倍。

列存储索引为数据仓库工作负载提供的另一个好处是为 102,400 行或更多行的批量插入操作提供了优化的加载路径。 102,400 是直接加载到列存储中的最小值，而每个行集合（称为行组）最多可以达到约 1,024,000 行。 行组更少但更饱满，这使得 SELECT 查询更高效，因为只需扫描更少的行组就可以检索请求的记录。 这些负载发生在内存中并直接加载到索引中。 对于较小的卷，会将数据写入称为增量存储的 B 树结构中，并异步加载到索引中。

在此示例中，相同的数据将加载到两个表中， FactResellerSales_CCI_Demo 和 FactResellerSales_Page_Demo。 FactResellerSales_CCI_Demo 具有聚集列存储索引，而 FactResellerSales_Page_Demo 具有包含两列的聚集 B 树索引，并且已进行页压缩。 可以看到，每个表正在从 FactResellerSalesXL_CCI 表中加载 1,024,000 行。 当 SET STATISTICS TIME 是 ON时，SQL Server 会跟踪查询执行的运行时间。 将数据加载到列存储表中大约花费了 8 秒钟，而将数据加载到页压缩的表中则花费了大约 20 秒。 在此示例中，进入列存储索引的所有行均被加载到单个行组中。

如果在单次操作中将少于 102,400 行的数据加载到列存储索引中，则会将其加载到称为增量存储的 B 树结构中。 数据库引擎使用称为元组移动器的异步过程将此数据移动到列存储索引中。 具有开放的增量存储会影响查询的性能，因为读取这些记录的效率要比从列存储中读取的效率低。 也可以使用 COMPRESS_ALL_ROW_GROUPS 选项重新组织索引，以便强制将增量存储添加并压缩到列存储索引中。