SQL Server 性能调优2 之索引（Index）的建立

3121321321

前言索引是关系数据库中最重要的对象之一，他能显著减少磁盘I/O及逻辑读取的消耗，并以此来提升 SELECT 语句的查找性能。但它是一把双刃剑，使用不当反而会影响性能：他需要额外的空间来存放这些索引信息，并且当数据更新时需要一些额外开销来保持索引的同步。
形象的来说索引就像字典里的目录，你要查找某一个字的时候可以根据它的比划/拼音先在目录中找到对应的页码范围，然后在该范围中找到这个字。如果没有这个目录（索引），你可能需要翻遍整本字典来找到要找的字。
SQL Server 中的索引以 B-Tree 的形式存储，如下图：


建立聚集索引（clustered index）来改进性能RDBMS 随着数据的增长都会面临查询性能的下降，索引就是专门设计来解决这个问题的。聚集索引是所有索引的基础，没有它数据表就是一个堆（heap）。聚集索引决定了数据的物理存储形态，所以一张表上只能有一个聚集索引。SQL Server 的 sys.partitions 系统视图中记录着所有聚集索引的信息（它们的 Index_ID为1）。
聚集索引可以包含多个字段（列），通常应挑选绝大多数查询语句中经常涉及到的筛选字段，并且事先了解以下几点：

• 字段应当包含大量的非重复的值。例如：身份证号
• 默认情况下主键字段将自动建立聚集索引，但这不是必须的，你可以手工修改为非聚集索引（non-clustered index）
• 字段经常参与筛选，即：经常在 WHERE, JOIN, ORDER BY, GROUP BY 语句中使用
• 字段经常参与比较，即：经常参与 >, <, >=, <=, BETWEEN, IN 运算
• 字段长度越短越好
  

另外在可能的情况下建议对聚集索引实施以下规则：

• 包含的字段都设为唯一(unique)且非空(NOT NULL)
• 包含字段的长度越短越好，包含的字段越少越好
• 每张表都有聚集索引，并且把 WHERE 中经常使用到的字段作为该聚集索引的字段
• 尽量避免在 varchar 列上建立聚集索引
  

我们来做一次10w条数据的性能比较（测试数据的生成SQL请参照附录）：
SELECT OrderDate,Amount,Refno FROM ordDemo WHERE Refno<3
索引建立前的执行计划：

• CREATE CLUSTERED INDEX idx_refno ON ordDemo(refno)  
• GO  
• --再次执行相同的查询语句  
• SELECT OrderDate,Amount,Refno FROM ordDemo WHERE Refno<3  
• GO  
  

建立索引后的执行计划：

通过对比我们可发现I/O 消耗从 0.379421 降低为 0.0571991，并且从 Table Scan 处理转变为 Index Seek。


建立非聚集索引（non-clustered index）来改善性能上面提到了索引能有效改善查询性能，但由于一张表只能有一个聚集索引，而一个聚集索引通常无法包含所有必要的列，所以 SQL Server 允许我们建立非聚集索引来实现这个需求。
【 SQL Server 2005 及之前的版本允许建立249 个非聚集索引；SQL Server 2008 及 SQL Server 2012 允许999个非聚集索引】
通常当你在某一个字段上建立一个唯一键（unique key）的时候，SQL Server 会自动在该列上建立一个非聚集索引。sys.partitions 系统表中存放着非聚集索引的相关信息（Index_ID>1）。
在为某张表建立非聚集索引之前请先确认两点：该表是否真的需要非聚集索引？该表是否有合适的字段来建立非聚集索引？

这是因为索引建得不好不但不能带来性能的提高，还会花费额外的空间来存放索引并产生额外的 I/O 操作！
建立非聚集索引选择字段时应遵循以下规则：

• 字段应当包含大量的非重复的值。
• 字段经常参与等值（=）运算
• 字段经常参与筛选，即：经常在 JOIN, ORDER BY, GROUP BY 语句中使用
  

我们继续之前的测试，来看看非聚集索引带来的速度提升：

• SELECT OrderDate FROM ordDemo  
• WHERE OrderDate='2011-11-28 20:29:00.000'  
• GO  
  

执行计划如下图：

建立非聚集索引，并再次执行查询：

• CREATE NONCLUSTERED INDEX idx_orderdate  
• on ordDemo(orderdate)  
• GO  
•   
• SELECT OrderDate FROM ordDemo  
• WHERE OrderDate='2011-11-28 20:29:00.000'  
• GO  
  

比较结果非常明显，非聚集索引建立之后 I/O Cost, CPU Cost, Operator Cost 等消耗大幅下降。在我们的例子中由于OrderDate 字段并不在聚集索引中，所以前一次的查询被解释成一个index scan。当我们在OrderDate 上建立一个非聚集索引后，查询将利用起该索引并解释成 index seek。
随着表的数据越来越多，用来存放非聚集索引的空间也会越来越大，并逐渐对性能造成影响。遇到这种情况可以把非聚集索引建立在独立的数据库文件或文件组(filegroup)中，从而减少对同一个文件的 I/O 操作压力。
合理的索引覆盖来改善性能执行下面的测试 SQL

• SELECT OrderDate,OrderID FROM ordDemo  
• WHERE OrderDate='2011-11-28 20:29:00.000'  
• GO  
  

观察执行计划后你会发现查询被解析为 index scan，而不是先前的 index seek？这是因为我们已建立的两个索引都没有包含 OrderId 字段。把 non-clustered Index 删掉了，重新建一下（把OrderId 字段也作为索引的字段）

• CREATE NONCLUSTERED INDEX idx_orderdate_orderId  
• on ordDemo(orderdate DESC,OrderId ASC)  
• GO  
  

再次执行查询，执行计划如下图

查询不出意料的再次被解析为 index seek。
注意：
一个索引中最多包含16个字段，并且这些字段的长度必须小于 900 byte。
以下类型不能作为索引的关键字段（text, ntext, image, nvarchar(max), varchar(max), varbinary(max)）
调整索引的包含字段（including columns）来提高性能索引的包含字段的概念起源自 SQL Server 2005，SQL Server 2008 及 2012 也具备该功能。它允许你在非聚集索引中包含非键值(non-key)字段，这些字段不会记入索引的大小（这样我们也就不太会促发上文提到的索引字段上限）。另外这些字段的类型可以是除 text, ntext, image 之外的任何类型。
在前文的测试案例中 OrderId 并不是一个关键字段，因为他并没有在 WHERE 子句中进行筛选，所以把他作为索引的关键字段并不合适，现在我们用 INCLUDE 来把它建立为包含字段：

• --删除前文的索引  
• DROP INDEX idx_orderdate_orderId ON ordDemo  
• GO  
•   
• --重建索引  
• CREATE NONCLUSTERED INDEX idx_orderdate_Included  
• on ordDemo(orderdate DESC)  
• INCLUDE(OrderID)  
• GO  
•   
• --再次查询  
• SELECT OrderDate,OrderID FROM ordDemo  
• WHERE OrderDate='2011-11-28 20:29:00.000'  
• GO  
  

执行计划如下图：

从性能上来说本节的优化结果与上一节的几乎一致，但采用了包含字段索引（include column index） 后，你受到的限制更小，并伴随着索引关键字段的减少，索引的占用也变小查询起来更高效。
总结下区分索引关键字段及包含字段的基本原则：

• WHERE, ORDER BY, GROUP BY, JOIN-ON 中的使用到的字段适用于关键字段
• SELECT, HAVING 中的使用到的字段适用于包含字段
  

使用过滤索引（filtered index）来提高性能过滤索引起源自 SQL Server 2008 ，SQL Server 2012 也具备该功能，你可以把它看成一个带着 WHERE 子句的非聚集索引。适当地使用能减少索引的存储尺寸及维护消耗，同时提高查询性能。

常规的索引都是对整张表的每条数据进行索引，而过滤索引仅仅对满足特定条件的记录进行索引，这个特定条件在建立过滤索引时通过 WHERE 子句来定义。
类似以下的场景你可以考虑采用过滤索引：
一张包含多年数据的巨型表，实际使用中仅查询当年数据。
一张记录产品类别的表，包含许多过期不再使用的类别。
一个订单表，包含OrderStartDate 及 OrderEndDate 字段。当订单完成时更新OrderEndDate，其他情况为 null。你可以在 OrderEndDate 上建立过滤索引，这样当你需要查询哪些订单未完成时可以利用。
在建立过滤索引时需要进行一些设定：

• ARITHABORT = ON
• CONCAT_NULL_YIELDS_NULL = ON
• QUOTED_IDENTIFIER = ON
• ANSI_WARNINGS = ON
• ANSI_NULLS = ON
• ANSI_PADDING = ON
• NUMERIC_ROUNDABORT = OFF
  

来看一下示例：

• SET ANSI_NULLS ON  
• SET ANSI_PADDING ON  
• SET ANSI_WARNINGS ON  
• SET ARITHABORT ON  
• SET CONCAT_NULL_YIELDS_NULL ON  
• SET QUOTED_IDENTIFIER ON  
• SET NUMERIC_ROUNDABORT OFF  
• GO  
•   
• CREATE NONCLUSTERED INDEX idx_orderdate_Filtered  
• on ordDemo(orderdate DESC)  
• INCLUDE(OrderId)  
• WHERE OrderDate = '2011-11-28 20:29:00.000'  
• GO  
•   
• SELECT OrderDate,OrderID FROM ordDemo WHERE OrderDate='2011-11-28 20:29:00.000'  
• GO  
  

I/O 消耗从上一节的0.0078751 减少为 0.003125，优化效果非常显著。
使用列存储索引（columnstore index）来提高性能目前为止我们讨论的都是行存储索引（rowstore index），SQL Server 2012 开始支持列存储索引。
行存储索引在数据页（data page）中保存数据行，列存储索引在数据页中保存数据列。假设我们有一张表（tblEmployee），包括 empId, FirstName, LastName 三列。行存储索引/列存储索引表现为以下存储形式：



显然当你需要对某几列值进行查找筛选的时候，列存储索引需要访问的数据页更少，从而降低了I/O开销，并因此提高了执行效率。在你决定采用列存储索引之前建议你确认一下3点：

• 你的数据表是否可以设定为只读(read-only)
• 你的数据表是否非常巨大（百万级以上）
• 如果你的数据库是个OLTP，是否能允许你切换（开/关）列存储索引
  

如果以上3点的答案都是OK的，那么你可以开始使用列存储索引了，不过你还会受到以下限制：

• 你不能包含1024个以上字段
• 字段类型只能是以下几种：
  

int
big int
small int
tiny int
money
smallmoney
bit
float
real
char(n)
varchar(n)
nchar(n)
nvarchar(n)
date
datetime
datetime2
small datetime
time
datetimeoffset (precision <=2)
decimal 或 numeric (precision <=18)

好，我们来试验一下列存储索引：
执行以下的代码，根据输出的执行计划可以发现它已经利用了我们先前建立的聚集索引（行存储索引）。

• SELECT  
•   Refno  
•   ,sum(Amount) as SumAmt  
•   ,avg(Amount) as AvgAmt  
• FROM  
•   ordDemo  
• WHERE  
•   Refno>3  
• Group By  
•   Refno  
• Order By  
•   Refno  
• GO  
  

接着我们把已经存在的行存储索引删除，建立列存储索引：

• DROP INDEX idx_refno ON ordDemo  
•   
• CREATE NONCLUSTERED COLUMNSTORE INDEX  
• idx_columnstore_refno  
• ON ordDemo (Amount,refno)  
  

再次执行相同的查询语句，执行计划如下图：


通过比较，我们可以发现I/O消耗显著下降:)
注意：由于建立了列存储索引，此时该表是只读的，如果你要恢复成可写的状态必须删除这个列存储索引！
附录生成测试数据的SQL代码：

• --建表  
• CREATE TABLE ordDemo (OrderID INT IDENTITY, OrderDate DATETIME,Amount MONEY, Refno INT)  
• GO  
•   
• --插入 100000 条测试数据  
• INSERT INTO ordDemo (OrderDate, Amount, Refno)  
•   SELECT TOP 100000  
•     DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2011-11-04' AS DATETIME)), ABS(a.object_id % 10), CAST(ABS(a.object_id % 13) AS VARCHAR)  
•   FROM sys.all_objects a  
• CROSS JOIN sys.all_objects b  
• GO