数据库性能优化第一部分（ 索引策略）
4.91/5 (123投票s)
在本系列关于数据库性能优化的第一篇文章中，我将讨论索引策略和索引维护。
引言
几周前，我的一位朋友告诉我，他的数据库性能很差。他问我如何找出导致性能差的原因以及如何解决这个问题。我对数据库调优有一些了解，并向他提出了一些建议。过了一段时间，我也遇到了同样的性能差问题，并且必须尽快解决。在那之后，我意识到有一个指导指南会很有帮助，于是我决定写一些关于这个主题的文章。
在这篇关于数据库性能调优的第一篇文章中，我将介绍一些索引调优的技术。
首先，了解索引很重要。SQL Server 2005 和 2008 支持大多数数据类型的两种索引：聚集索引和非聚集索引。它还支持全文索引和 XML 索引，但这些类型的索引仅适用于特定数据类型。
基本术语
在我们开始讨论索引策略之前，我认为简要描述一些基本术语很重要。
非聚集索引
数据以随机顺序存储，但逻辑顺序由索引指定。数据行可能随机分布在整个表中。非聚集索引树包含按排序顺序排列的索引键，索引的叶级别包含指向数据页和数据页中行号的指针。在非聚集索引中
- 行的物理顺序与索引顺序不同。
- 通常在 JOIN、WHERE 和 ORDER BY 子句中使用的列上创建。
- 适用于值可能频繁修改的表。
Microsoft SQL Server 在给出 CREATE INDEX 命令时默认创建非聚集索引。一个数据库表可以有多个非聚集索引。在 SQL Server 2005 中，每个表最多可以有 249 个非聚集索引；在 SQL Server 2008 中，每个表最多可以有 999 个非聚集索引。创建非聚集索引适用于以下列：
- 经常用于搜索条件。
- 用于连接其他表。
- 用作外键字段。
- 具有高选择性（对于特定值，返回行数占总行数的百分比较低（0-5%）的列）
- 在 ORDER BY 子句中使用。
- XML 类型（需要创建主索引和辅助索引；更多信息将在后续文章中介绍）。
聚集索引
聚集索引会改变数据块的顺序以匹配索引，从而导致行数据按顺序存储。因此，给定的数据库表只能创建一个聚集索引。聚集索引可以极大地提高整体检索速度，但通常仅在数据按聚集索引的相同或相反顺序顺序访问，或者选择项目范围时才有效。
由于物理记录在磁盘上按此排序顺序排列，因此序列中的下一行项紧挨着最后一行，从而减少了所需的数据块读取次数。因此，聚集索引的主要特征是物理数据行的顺序与指向它们的索引块相匹配。一些数据库将数据块和索引块分开存储在不同的文件中，而另一些数据库则将两种完全不同的数据块存储在同一个物理文件（或多个物理文件）中。创建这样一个对象，使其行的物理顺序与行的索引顺序相同，并且聚集索引的底部（叶）级别包含实际的数据行。
许多开发人员倾向于认为 SQL Server 按照记录插入表的顺序存储数据。这是不正确的。SQL Server 根据您创建聚集索引的方式存储数据。请记住，SQL Server 通过放置唯一索引来强制执行 PRIMARY KEY 约束。如果表上没有其他聚集索引，SQL Server 将使 PRIMARY KEY 索引成为聚集索引。在创建 PRIMARY KEY 或 UNIQUE KEY 索引以外的索引时，SQL Server 默认将其创建为非聚集索引。
在每个表上拥有聚集索引非常重要。如果表没有聚集索引，则所有新记录都必须添加到表占用的最后一个数据页。如果表有聚集索引，则新记录可以添加到最后一个页面或表中间。根据聚集索引，它们可以添加到更合适的位置。
覆盖索引
通过将非键列包含在非聚集索引的叶级别，可以扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这些索引称为覆盖索引或包含列的索引。覆盖索引的理念是 SQL Server 不需要使用非聚集索引和表之间的查找来返回查询结果。由于聚集索引就是实际的表，因此聚集索引始终覆盖查询。包含的非键列具有以下优点：
- 它们可以是数据库引擎不允许作为索引键列的数据类型。
- 数据库引擎在计算索引键列的数量或索引键大小时不会考虑它们。
- 覆盖索引的表现始终优于非覆盖索引。
创建覆盖索引时应牢记一些准则：
- 非键列在 CREATE INDEX 语句的 INCLUDE 子句中定义。
- 非键列只能在表或索引视图的非聚集索引上定义。
- 除了 text、ntext 和 image 外，所有数据类型都允许。
- 计算列（确定性的，无论是精确还是不精确）都可以作为包含列。
- 与键列一样，源自 image、ntext 和 text 数据类型的计算列可以作为非键（包含）列，只要计算列的数据类型允许作为非键索引列。
- 列名不能同时出现在 INCLUDE 列表和键列列表中。
- INCLUDE 列表中的列名不能重复。
- 最多可以有 1023 个附加列用作非键列（表最多有 1024 列）。
使用覆盖索引带来的性能优势通常对于返回大量行的查询（顺便说一句，这类查询称为非选择性查询）非常显著。对于只返回少量行的查询，性能提升不大。但在这里您可以提问，什么是少量行？少量行可能是指一个包含数百行的表中的 10 行，或者一个包含 1,000,000 行的表中的 1000 行。
筛选索引
SQL Server 2008 引入了一种新的索引类型，称为筛选索引。筛选索引是一种非聚集索引，特别适合覆盖由简单 WHERE 子句确定的数据子集。包含筛选索引行的 B 树将只包含在创建索引时使用的筛选条件满足的行，因此设计良好的筛选索引可以快速提高查询性能，降低索引维护成本，并快速降低索引存储成本。筛选索引相比标准的全非聚集索引具有一些优势：
- 如上所述，筛选索引只包含满足定义的筛选条件的行。因此，它减少了索引的存储空间需求。我将在下面的示例中详细解释这一点。
- 筛选统计信息或筛选索引的统计信息更紧凑、更准确，因为它们只考虑筛选索引中的行，并且筛选索引较小的尺寸降低了更新统计信息的成本/开销。
- 数据修改的影响在使用筛选索引时较小，因为它仅在索引数据受影响或插入匹配筛选条件的新记录时才更新。
- 维护成本也将降低，因为在重新组织或重建索引时，只有一部分行需要被考虑。
- 最重要的是，作为一个优化的非聚集索引，如果只需要筛选索引条件所覆盖的数据子集，查询性能将得到提升。
在早期版本的 SQL Server 中，要获得类似的优势，您可以选择使用索引视图。使用索引视图在外观上与筛选索引相似，但在这两种方法之间可以找到一些差异。这里有一个表格列出了一些差异：
| 表达式 | 筛选索引 | 索引视图 |
| 单列 | 筛选索引是在特定表的列上创建的。 | 索引视图可以基于来自多个基表的列创建。 |
| 简单 WHERE 条件 | 筛选索引不能在其 WHERE 子句中使用复杂逻辑，例如不允许使用 LIKE 子句，只允许使用简单的比较运算符。 | 此限制不适用于索引视图，您可以设计任意复杂的条件。 |
| 可以进行在线重建 | 筛选索引可以在线重建。 | 索引视图无法在线重建。 |
| 非唯一或唯一 | 您可以将筛选索引创建为非唯一索引。 | 索引视图只能创建为唯一索引。 |
| 计算列 | 筛选索引不支持计算列。 | 索引视图可以使用计算列。 |
| 连接 | 不支持。 | 支持。 |
索引选择性和密度
我认为索引选择性和密度是您应该了解的基本术语之一。索引选择性表示表中不同键值的数量。因此，唯一键和主键将具有完美的选择性。通常，索引的选择性越高，对 SQL Server 查询优化器的作用越好。如果索引的选择性不高，查询优化器可能会决定使用表扫描比索引查找更有效。索引密度表示表中重复键值的数量。因此，选择性越高的索引密度越低。通常，最好的索引是选择性最高的索引。
填充因子
填充因子选项用于调整索引数据存储和性能。填充因子值决定了叶级别页面中要填充数据的空间百分比，从而在每个页面上保留剩余空间以供将来增长。例如，指定 80 的填充因子意味着每个叶级别页面的 20% 将留空，为基础表添加数据时提供索引扩展空间。空空间保留在索引行之间，而不是在索引的末尾。填充因子值为 1 到 100 的百分比，服务器范围的默认值为 0，表示叶级别页面已满。填充因子值 0 和 100 在所有方面都相同。
填充因子设置仅在创建索引或重建索引时生效。SQL Server 数据库引擎不会动态地在页面中保留指定百分比的空空间。试图在数据页上维护额外空间会事与愿违，因为当数据输入时，数据库引擎将不得不执行页面拆分来维护填充因子指定的每页空闲空间百分比。以下是一些修改填充因子时应考虑的信息：
- 数据在表中如何插入、更新和删除，决定了表中叶级别页面的填充程度。微调此设置通常需要一些测试和分析。这对于数据库中的大型活动表至关重要。
- 如果数据始终在表末尾插入，则填充因子可以为 90% 到 100%，因为数据永远不会插入到页面中间。UPDATE 和 DELETE 语句可能会扩展（UPDATE）或减小（DELETE）每个叶级别页面所需的空间。这应根据测试进行微调。
- 如果数据可以插入到表的任何位置，那么根据 INSERT、UPDATE 和 DELETE 活动，60% 到 80% 的填充因子可能是合适的。但是，有必要进行一些测试和分析，以确定适合您环境的设置。
- 在其他条件相同的情况下（即表大小、SQL Server 版本、选项等），较低的填充因子百分比可能需要更多的存储空间，而较高的填充因子百分比页面更紧凑。
- 另一个需要考虑的方面是您的索引重建计划。如果您无法按计划定期重建索引，并且表中的 INSERT、UPDATE 和 DELETE 活动很高，那么一个考虑因素可能是使用较低的填充因子来限制碎片。权衡是可能需要更多的存储空间。
创建索引的最佳实践
创建索引对于系统性能和 SQL Server 数据库的未来维护（我们将在本文后面讨论）至关重要。选择合适的索引可以显著提高应用程序性能，通常可以提高几个数量级。这并非听起来那么简单。在选择索引时，有几个要点必须考虑。您定义的每个索引都可以提高 SELECT 查询的性能，但另一方面，可能会降低 INSERT 和 UPDATE 查询的性能。原因是 SQL Server 会自动维护索引键。因此，每次发出数据修改语句时，SQL Server 不仅提供数据修改，还会更新受影响表上定义的所有索引。在具有许多索引的大型表，或具有长键的少数索引的情况下，性能下降会很明显。在某些情况下，可以在更新或插入新记录之前删除索引，然后再重新创建索引。这种情况适用于需要更新或插入大量数据的超大型表。您会发现，向没有索引的表插入比向有索引的表插入要快得多。
在实现索引时，有几个技巧需要牢记：
- 保持索引精简。尽量最多在一到两列上构建索引。宽索引比窄索引扫描所需时间更长。
- 为每个表创建聚集索引。但是，要明智地选择聚集索引的列。尽量为最常用于检索数据的列创建聚集索引。
- 尽量为具有高选择性（即重复值不多的列）的列创建聚集索引。
- 尽量为永远不会更新或很少更新的列创建聚集索引。每次更新聚集索引键时，SQL Server 都必须维护不仅聚集索引，还包括非聚集索引，因为非聚集索引包含指向聚集索引的指针。这是不应在多列上创建聚集索引的另一个原因。
- 默认情况下，SQL Server 会在 PRIMARY KEY 列上创建聚集索引（除非在创建主键之前创建了聚集索引）。通常，将聚集索引放在主键上是有益的，但有时将聚集索引用于其他列更好。如果您的测试表明在非键列上使用聚集索引可以提高查询性能，请随时覆盖默认行为。
- SQL Server 在更新索引键时必须执行一些维护操作，因此每个额外的索引都可能对 INSERT、UPDATE 和 DELETE 语句的性能产生轻微的性能损失。尽管如此，与 SELECT 语句的性能提升相比，索引维护的成本可能要小得多。因此，您可以相对宽松地创建非聚集索引的数量。
- 确保消除重复索引，即针对同一组列创建的多个索引。此类索引不提供额外的好处，但会占用额外的磁盘空间，并可能降低 INSERT、UPDATE 和 DELETE 语句的性能。
- 检查服务器级别配置的默认填充因子级别。如果在 CREATE INDEX 语句中未指定 FILLFACTOR 选项，则每个新索引都将使用默认填充因子创建。这可能符合您的意图，也可能不符合。
- 非聚集索引可以在不同的文件组中创建，这些文件组可以位于单独的磁盘驱动器上，以改善数据访问 I/O 操作。
在一些文章中，我发现身份列是创建聚集索引的最佳选择，但您必须记住，这种方式可能不是最佳方式，因为它迫使用户将新数据输入到表的最后一个数据页。这种情况有时被称为“热点”，因为多个用户可能在争夺一个页面上的最后一个可用位置，从而导致 INSERT 语句变慢。
在某些情况下，您可能会发现有些表从未基于某个列进行查询。在这种情况下，一些开发人员更喜欢在最常用于数据检索并唯一标识每个记录的列集上创建聚集索引。这类索引称为复合聚集索引。您可能认为这是一个好主意，因为身份列在大多数情况下没有业务含义。然而，从性能的角度来看，您应该避免复合聚集索引。总的来说，索引越精简，SQL Server 扫描或查找它的速度就越快。对于小型表（或数据集），复合索引的表现相对较好，但随着记录数量的增加，性能会下降。
创建索引有一些限制。并非所有列都可以添加到索引中。特别是 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列不能指定为索引键列。但是，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型可以作为非键索引列参与非聚集索引。
如何选择每个索引中字段的合适顺序
选择每个索引中字段的合适顺序非常重要。错误的顺序会导致索引可能无法使用。第一个规则是选择性最高的列应该放在前面。这个规则有时会引起误解，认为每个索引都应该包含选择性最高的列作为前导列。让我们考虑一个有 3 列的表：ID、fname 和 lname。ID 是具有聚集索引的最具选择性的列。现在您想为 fname 和 lname 创建一个非聚集索引。lname 的选择性高于 fname。如果您想为其余两列创建非聚集索引，请将 lname 放在第一列，fname 放在第二列。不要将 ID 列放在索引中。要非常小心您将哪一列放在第一位。这是因为 SQL Server 只为索引的第一列维护直方图。这意味着 SQL Server 只知道第一列值的实际分布。如果第一列的选择性不高，索引可能不会被使用。
示例
在这里，我将向您展示一些基本示例，演示索引如何影响查询性能。为进行测试，我在 AdwentureWorks2008R2 数据库中创建了表 [Person].[Person_Test]。此表与 [Person].[Person] 具有相同的结构。我创建此表是因为我不想更改现有表。
没有索引的表
如果您在没有索引的表上运行查询，SQL Server 会对表进行表扫描，逐行查找以确定是否有姓氏为“Brown”的记录。您可以看到下面的图片，此查询的估计子树成本为 2.84525。此值表示查询优化器执行此查询及其在同一子树中的所有先前操作的总成本。数字越低，SQL Server 执行查询的资源消耗越少。
在姓氏列上具有非聚集索引的表
在此示例中，我们将创建一个在 LastName 列上的非聚集索引。
CREATE NONCLUSTERED INDEX [IX_Person_Test_LastName] ON [Person].[person_test]
(
[LastName] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
当您运行此查询时，SQL Server 使用索引进行索引查找（而不是表扫描），在此操作之后，它需要执行 RID 查找来获取实际数据。您可以在下面的图片中看到估计执行成本为 0.299353，这表明有索引（在本例中为 LastName 列上的非聚集索引）的查询性能要好得多。
在姓氏列上具有聚集索引的表
在这里，我将向您展示聚集索引如何影响性能。执行查询之前，您必须在 LastName 列上创建聚集索引。
CREATE CLUSTERED INDEX [IX_Person_Test_LastName_Clustered] ON [Person].[person_test]
(
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
当此查询运行时，SQL Server 会进行索引查找。最棒的是，索引直接指向实际数据页。您可以看到，估计子树成本仅为 0.0155815。对于此类查询，使用聚集索引是最快的访问方法。
在姓氏列上具有非聚集索引的表（选择姓氏）
在此示例中，我们仅请求 LastName 列。由于此查询仅由非聚集索引处理，因此 SQL Server 无需访问实际数据页。根据此查询，估计子树成本仅为 0.0033832。您可以看到这比前面的示例还要好。
在姓氏列上具有聚集索引的表（选择姓氏）
更进一步来说，下面的输出基于LastName上的聚集索引且无非聚集索引。您可以看到子树成本与返回所有列的成本相同，即使我们只选择一列。因此，非聚集索引的表现更好。
在姓氏列上具有非聚集索引的表（选择姓氏和名字）
当您运行此查询时，SQL Server 使用索引进行索引查找，在此操作之后，它需要执行 RID 查找来获取实际数据。您可以在下面的图片中看到估计执行成本为 0.29934。
在姓氏列上包含名字的非聚集索引表（选择姓氏和名字）
在此示例中，您将看到覆盖索引如何提高查询性能。以下脚本允许您创建覆盖索引。
CREATE NONCLUSTERED INDEX [IX_Person_Test_LastName_Include_FirstName] ON [Person].[person_test]
(
[LastName] ASC
)INCLUDE (FirstName) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
在此查询中，我们请求 2 列（LastName 和 FirstName）。此查询仅由非聚集索引处理，因为此索引包含两个列的数据页信息。估计子树成本为 0.0033832，远低于上一个示例。
带有筛选索引的表
在此示例中，我将向您展示如何创建筛选索引以及这种类型的索引如何提高性能。让我们考虑以下查询：
SELECT LastName from person.person_test where modifieddate<'2005-01-01'
如果没有在表上创建非聚集筛选索引，SQL Server 会执行完整的表扫描。如果表中有数百万条记录，这可能需要很长时间。
为了提高查询性能，您可以创建非聚集筛选索引。
CREATE NONCLUSTERED INDEX [IX_Person_Test_LastName_Filtered_ModifiedDate] ON [Person].[person_test]
(
[LastName] ASC
)WHERE ModifiedDate <'2005-01-01' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, önLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
正如您所见，在有非聚集筛选索引的情况下，估计子树成本远低于没有索引的情况。
以升序与降序构建索引
创建索引时，通常会使用默认选项。这些选项以升序创建索引。这通常是创建索引的最逻辑方法，但在某些情况下，这种方法可能不是最佳的。例如，当您使用默认选项在 TableA 的 ColumnA 上创建索引时，最新的数据位于末尾。当您想要以升序获取数据，从最近的到最早的排序时，这效果很好。但如果您需要将最新的数据排在前面呢？在这种情况下，您可以按降序创建索引。在接下来的几个示例中，我将向您展示如何按不同顺序创建索引以及它们如何影响查询性能。对于所有以下示例，我将使用 AdventureWorks2008R2 数据库的 PurchasingOrderHeader。
在此第一个示例中，我们仅请求 PurchasingOrderHeader 的 OrderDate 列。
您可以看到估计子树成本为 0.0380656。但是，如果我们使用 Order By 子句会发生什么？让我们试着调查一下。
当我们使用 Order By 子句时，SQL Server 将对请求的数据进行排序。您可以看到，在这种情况下，排序操作是资源消耗最多的操作，并且估计的总体子树成本有所增加。为了提高此查询的性能，我们可以创建一个在 OrderDate 列上的非聚集索引。
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] ASC )
如果我们再次运行查询，我们将看到估计子树成本比之前要好，因为 SQL Server 查询优化器不执行排序操作。它只是进行索引查找。
但是这里您可以问，如果我需要降序排列的数据怎么办？您可以使用降序索引应用相同的技术。我将向您展示它们可以像升序索引一样影响性能。
当您运行使用降序选项对记录进行排序的查询时，您可以看到估计子树成本与没有索引的升序情况相同。为了提高查询性能，您可以创建一个使用降序选项的非聚集索引。
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderHeader_OrderDate]
ON [Purchasing].[PurchaseOrderHeader]
( [OrderDate] DESC )
历史
- 2011 年 8 月 7 日 - 发布了原始版本。
- 2011 年 8 月 9 日 - 更新了“筛选索引”章节。