MS SQL Server 中的索引
4.84/5 (71投票s)
本文重点介绍 MS SQL Server 如何使用索引来读写数据。
引言
我从一个旧货店买了一本书；卖家打包好书，还送了两个书签。我心里想，为什么要书签呢？我明明可以轻松记住页码，下次接着上次停下的地方继续读，或者从头读一遍找到上次的阅读点。但不是所有人都记忆力超群；而且，还有更重要的事情要记。我爷爷宁愿用书签来帮助他找回阅读进度。这不就像一个简单的索引吗？
本文重点介绍 MS SQL Server 如何使用索引来读写数据。SQL Server 以区(extents)和页(pages)的形式组织数据。每个区的大小为 64 KB，包含 8 个 8KB 大小的页。一个区可能包含来自多个或同一张表的数据，但每个页只包含来自单个表的数据。从逻辑上讲，数据以记录集的形式存储在表中。我们有字段（列）来标识每条记录集包含的数据类型。表只不过是记录集的集合；默认情况下，除非在表上定义了聚集索引，否则行以堆(heaps)的形式存储，在这种情况下，记录集会根据聚集索引进行排序和存储。堆结构是一种简单的排列方式，插入的记录存储在表页的下一个可用空间中。
如果目的仅仅是存储数据，堆似乎是个不错的选择，但当数据检索介入时，这个选择就会适得其反。在这种情况下，索引就像一个消防员。索引以 B-Tree 的形式排列，叶节点包含数据或指向数据的指针。由于存储的数据是按排序顺序的，索引可以精确地知道每个记录在哪里。因此，索引极大地优化和增强了数据检索。
但万事皆有代价；我们为表中的索引付出的代价是，每次进行插入/更新/删除时，SQL Server 都会更新受 DML 操作影响的表上的活动索引。因此，仅仅为了更好的数据检索而疯狂地创建索引是无法达到目的的。如果表上有 20 个索引，每次对表进行 DML 操作时，这 20 个索引都将被更新，以便它们能唯一地确定记录的位置。让我们深入了解索引。
环境设置：所有代码均在 MS SQL Server 2008 R2 上进行测试。
聚集索引 (CI)
聚集索引会重新组织表中记录的物理存储方式。因此，一张表只能有一个聚集索引。聚集索引的叶节点包含数据页，我指的是聚集索引中的键值对包含索引键和实际数据值。另外请记住，当表上创建主键时，默认会创建一个聚集索引。聚集索引就像您的火车票 B4/24，您知道要登上 B4 车厢并坐在 24 号座位。所以这个索引会物理上引导您找到您的实际座位。
我们将通过一个例子来详细说明。
USE TestDB
GO
CREATE TABLE Sales(
ID INT IDENTITY(1,1)
,ProductCode VARCHAR(20)
,Price FLOAT(53)
,DateTransaction DATETIME);
我创建了一个 Sales 表，然后创建了一个存储过程向 Sales 表插入 200,000 条记录。这么大的数据量有助于我们清晰地注意到差异。
CREATE PROCEDURE InsertIntoSales
AS
SET NOCOUNT ON
BEGIN
DECLARE @PC VARCHAR(20)='A12CB'
DECLARE @Price INT = 50
DECLARE @COUNT INT = 0
WHILE @COUNT<200000
BEGIN
SET @PC=@PC+CAST(@COUNT AS VARCHAR(20))
SET @Price=@Price+@COUNT
INSERT INTO Sales VALUES (@PC,@Price,GETDATE())
SET @PC='A12CB'
SET @Price=50
SET @COUNT+=1
END
END
EXEC InsertIntoSales
现在我们已经创建了表并插入了 200,000 条记录，但没有任何列定义了索引。
按 Control+M。这将在结果中“包含实际执行计划”。让我们运行以下查询。
SET STATISTICS IO ON
SELECT * FROM Sales WHERE ID=189923
ID ProductCode Price DateTransaction
----------- ---------------- ---------- -----------------------
189923 A12CB189922 189972 2011-03-21 12:07:48.310
(1 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 1129, physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
结果中的“执行计划”选项卡显示记录是通过表扫描检索的，逻辑读取次数为 1129。
现在，让我们在 Sales 表的 ID 列上构建一个聚集索引。
CREATE CLUSTERED INDEX CL_ID ON SALES(ID);
让我们按 CTRL+M 并重新运行相同的查询。
SET STATISTICS IO ON
SELECT * FROM Sales WHERE ID=189923
ID ProductCode Price DateTransaction
----------- ---------------- ------------ -----------------------
189923 A12CB189922 189972 2011-03-21 12:07:48.310
(1 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 3, physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
结果中的“执行计划”选项卡显示记录是通过索引查找(Index seek)检索的，逻辑读取次数为 3。在创建聚集索引后，SQL Server 能够显著减少逻辑读取次数，查询得到了优化。显然，索引知道在哪里查找记录。
非聚集索引 (NCI)
非聚集索引是一种特殊的索引类型，其逻辑顺序与磁盘上行的物理存储顺序不匹配。非聚集索引的叶节点不包含数据页，而是包含指向数据页的指针。这意味着非聚集索引不能独立存在，它需要一个基础来生存。非聚集索引使用聚集索引（如果已定义）或堆来构建自身。
当非聚集索引使用堆时，叶节点（或指针）是数据的物理位置。当它使用聚集索引时，叶节点（或指针）是聚集索引的键值，这个键值又指向实际数据。
第一部分：当 NCI 使用 CI 时
回到 Sales 表，我们已经在 ID 列上有了 CI (CL_ID)，现在如果我们有一个类似以下的查询：
SET STATISTICS IO ON
SELECT * FROM Sales WHERE ProductCode like 'A12CB908%' order by Price
Press Control+M and execute the query
There are arround 111 records retrived
-----------------------------------------------------
(111 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 1130, physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
我们发现查询首先使用聚集索引获取 111 条记录，然后进行排序操作；逻辑读取次数高达 1130。还有一个缺失索引的建议。
让我们采纳 SQL Server 的建议，在 ProductCode 列上创建一个非聚集索引 (NONCI_PC)。由于我们已经有了 CI，这个 NCI 将基于 CI 构建。
CREATE NONCLUSTERED INDEX NONCI_PC ON SALES(ProductCode);
按 Control+M 并重新运行相同的查询，这次我们可以看到数据获取计划发生了变化。
SET STATISTICS IO ON
SELECT * FROM Sales WHERE ProductCode like 'A12CB908%' order by Price
-------------------------------
(111 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 351, physical reads 0,
read-ahead reads 7, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
逻辑读取次数已最小化，修改后的执行计划如图所示。这是非聚集索引使用聚集索引的示例。
第二部分：当 NCI 使用堆时
当表上没有构建聚集索引而创建了非聚集索引时，它将使用堆进行数据检索。索引的列会与指向数据物理位置的指针一起排序。
关键问题是，我如何知道应该在 CI 上还是在堆上创建 NCI？
答案在于查询。如果数据通常通过一个特定列进行查询，那么在 CI 上构建 NCI 会更有益。但当出现像我们目前 Sales 示例中的情况时，我们将基于堆构建一个单列 NC 索引，NCI 将仅仅是一个包含键值对（索引键和物理位置，即值）的二维表。在这种情况下，这将是最佳优化。为了说明这一点，让我们仔细回顾一下示例。
对于 Sales 示例，让我们删除在 ID 列上创建的聚集索引 CL_ID 并重新评估。
DROP INDEX Sales.CL_ID;
SET STATISTICS IO ON
SELECT * FROM Sales WHERE ProductCode like 'A12CB908%' order by Price
------------------------------------
(111 row(s) affected)
Table 'Sales'. Scan count 1, logical reads 114, physical reads 0,
read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
逻辑读取次数得到了进一步优化，执行计划也得到了修订。在这种情况下，查询使用了在堆上运行的非聚集索引。
我们已经能够创建索引，使我们的查询以最小的性能开销工作。那么现在下一个大问题是，如果我们有一个工具能根据我们的查询提示我们应该构建哪些索引，那岂不是太棒了？
是的，有这样的工具，DTA 报告可以帮助我们。
使用 DTA
现在我们将看到如何从 DTA 获取建议并优化我们的查询。
为了做到这一点，让我们删除我们在 Sales 表上迄今为止创建的所有索引。
DROP INDEX CL_ID ON Sales;
DROP INDEX NONCI_PC ON Sales;
太好了！接下来我们将为 DTA 创建两个工作负载。转到“开始”>“所有程序”>“MS SQL Server 2008”>“性能工具”>“SQL Server Profiler”。
加载后，在左上角单击“新建跟踪”，连接到服务器，然后会出现以下屏幕。在“常规”选项卡中，填写跟踪名称（我将其命名为“Trace1”），勾选“保存到文件”并提供保存您的Trace1.trc文件的路径。
接下来是“事件选择”选项卡。在这里，我们需要提供要记录条目的事件。我只激活了“T-SQL”，取消了其他所有选项。
在“列筛选器”按钮中，我按NTUserName like ‘Keshav SINGH’进行了筛选，这是我的登录名……然后按 OK。
看起来我们准备好了。按“运行”并开始跟踪。
接下来，我们登录 SSMS 并执行查询。
SELECT * FROM Sales WHERE ID=189923
回到 Profiler，我们会发现 SQL 查询已被捕获。停止跟踪并保存。
好的，我们已经准备好了工作负载。现在是时候寻求 DTA 的帮助了……
转到“开始”>“所有程序”>“MS SQL Server 2008”>“性能工具”>“数据库引擎优化顾问”。
DTA 界面如下。连接到托管包含 Sales 表的 TestDB 数据库的同一服务器。在“工作负载选择”的“常规”选项卡中，勾选“文件”单选按钮，然后浏览到我们从 Profiler 捕获的Trace1.trc。工作负载分析的数据库是 TestDB，在选择要调整的数据库和表下，选择 TestDB 并勾选 Sales 表。
接下来，在“调整选项”选项卡中，保留默认选择，即索引调整。
按“开始分析”（左上角的绿色三角形）按钮，DTA 开始工作。
作业完成后，您会发现三个附加选项卡：进度、建议和报告。让我们看一下建议。DTA 建议针对该查询，我们应该在 Sales 表的 ID 列上创建一个聚集索引，并提供索引的估计大小/成本。如果您单击定义，它还会提供您需要执行的 T-SQL 查询。复制该查询，连接到 SSMS 并执行，您将获得约 99% 的估计性能提升。
好的，现在我们按照 DTA 的建议在 Sales (ID) 上创建了 CI。接下来，我们将对以下查询遵循相同的指南。在 Profiler 中创建一个工作负载，并使用 DTA 来优化查询。
SELECT * FROM Sales WHERE ProductCode like 'A12CB908%' order by Price
完成后，我们会收到 DTA 的两条建议，并且正如我们所见，估计性能提升了 98%。
第一个建议是创建统计信息。
第二个建议是在 ProductCode 和 Price 上创建非聚集索引。
摘要
构建索引完全基于查询的标准。一张表上可以创建的非聚集索引的数量没有硬性规定。经常执行 DML 操作的列适合建立索引。SQL Server 在任何版本中最多允许一个聚集索引。至于非聚集索引，2005 版本允许创建 249 个，而 2008 版本允许创建 999 个非聚集索引。
此外，并非总是可以通过简单地在列上创建索引来进一步优化查询。而且因为没有免费服务，索引也要付出相当大的代价。每次在索引表上执行 DML（插入/更新/删除）时，SQL Server 都会更新索引以识别记录。因此，如果索引越多，DML 执行所需的时间就越长。所以简单地创建大量索引并不能达到目的。这就是为什么建议在执行 BCP 或 BULK INSERT 之前删除所有索引，并在活动完成后重新构建它们。
此外，索引会导致表碎片化，并耗费 DBA 大量的宝贵时间进行维护。如果明智地使用，它们可以使查询性能提升很多倍。
常见问题解答
在一次面试中，我被问到：一旦我们声明了主键，默认情况下就会在列上创建聚集索引；如果我想在两个不同的列上创建聚集索引和主键，可以吗？
在主键和聚集索引上使用两个不同的列是完全可能的。但请记住，如果我首先在表上创建主键，它也会创建一个 CI。现在，如果我需要它们在两个不同的列上，请删除主键约束，CI 将自动消失。现在在 A 列上创建一个 CI，并将 B 列声明为主键，B 列将默认创建 NCI 而不是 CI。这样，我们就可以让两个列分别作为主键和 CI 声明。