SQL Server - 索引：关键要求、性能影响和注意事项

SQL Server索引用于帮助更快地检索数据,是数据库表性能优化技术。那么索引如何提高查询性能？索引有没有好坏之分？假设有一个包含50列的表，在每个列上创建索引是否可行？如果创建多个索引，是否有助于SQL查询运行得更快？

但在我们深入研究之前，必须首先了解为什么需要索引。

想象一下，您参观了一个收藏了数千本书的图书馆。您正在寻找一本特定的书，但您将如何找到它？如果浏览在每个书架上每本书，可能需要几天时间才能找到它。当您从存储在数据库中的数百万行中查找记录时，如何才能快速找到哪?

SQL Server索引以B-Tree格式存储，由顶部的根节点和底部的叶节点组成。对于我们的图书馆图书示例，用户发出查询以搜索ID为391的图书。在这种情况下，查询引擎开始从根节点遍历并移动到叶节点。

根节点->中间节点->叶子节点。

查询引擎在中间节点查找，参考页面。在此示例中，第一个中间节点由1-500的书ID组成，第二个中间节点由501-1000组成。

查询引擎基于中间节点，遍历B-Tree，寻找对应的中间节点和叶子节点。这个叶子节点可以由实际数据组成，也可以根据索引类型指向实际数据页。在下图中，我们看到了如何使用SQL Server索引遍历索引来查找数据。在这种情况下，SQLServer不必遍历每一页，从而查找特定的图书。

节点

接下来让我们看一下有无索引的查询性能。

假设我们需要来自[SalesOrderDetail_Demo]表的[SalesOrderID] 56958的数据。

select *

FROM [AdventureWorks]. [Sales].[SalesOrderDetail_Demo]

where SalesOrderID=56958

此表上没有任何索引。在SQLServer中，没有任何索引的表称为堆表 heap table。

查看是否有索引，使用命令sp_helpindex [Table name]

sp_helpindex [Sales].[SalesOrderDetail_Demo]

如果我看查看执行计划，我们会看到一下内容

当将光标悬停在 Table

Scan 图标上时，它显示实际结果集包含2 行，但为此目的，它会读取该表中的所有行。

读取的行数：121317

实际执行行数：2

执行计划

现在，想象一下，如果有一个数百万或数十亿行的表。遍历表中的所有记录而找到几条复合要求的记录，不是一个好的方法。在一个广泛的在线事务处理（online transaction processing）数据库系统中，它没有有效地使用服务器资源（CPU、IO、内存），因此，可能会面临性能问题。

现在，让我们使用具有索引的表运行上面的 select 语句。创建一个主键聚集索引和在[ProductID]和 [rowguid]两列的非聚集索引。

所有index

创建索引后，查看上面查询语句的执行计划。

执行计划

在聚集索引查找详细信息中，显示查询优化器精确地读取了它在输出中给出的行。

经过比较两个执行计划，我们可以十分清晰地看到使用索引后，数据库性能大大提升。下图显示了表扫描和索引查找之间的区别。

表扫描和索引查找之间的区别

顾名思义，一个好的索引可以提高查询性能并最大限度地减少资源利用率。

索引会降低 SQL Server中查询的性能吗？

有时我们在特定列上创建索引，但它从未被使用过。假设在某列上有一个索引，然而对该列执行了大量插入和更新。对于每次更新，还需要相应的索引更新。如果您的工作负载有更多的写入活动，并且列上有许多索引，则会降低查询的整体性能。未使用的索引也可能导致 select 语句的性能下降。

查询优化器使用统计信息来构建执行计划。它读取所有索引及其数据采样，并在此基础上构建优化的查询执行计划。我们可以使用动态管理视图 sys.dm_db_index_usage_stats 跟踪索引使用情况并监控资源，例如用户扫描、用户搜索和用户查找。

SQL Server 有两个主要索引——聚集索引(clustered)和非聚集索引(non-clustered)。

聚集索引将实际数据存储在索引的叶节点中。它根据聚集索引键对数据页中的数据进行物理排序。 SQL Server 允许每个表有一个聚集索引。您可以连接多个列来构建聚集索引键。

非聚集索引是一个逻辑索引，它具有指向聚集索引键的索引键列。

我们还可以在 SQL Server 中拥有其他索引，例如 XML 索引、列存储索引(column store index)、空间索引(spatial index)、全文索引(full-text index)、哈希索引(hash index)等。

在 SQL Server 中构建索引之前，您应该考虑以下几点：

工作量Workload

需要索引的列

表大小

列数据的升序或降序

列顺序

索引类型

填充因子(Fill facto),填充索引和 TempDB 排序顺序