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

前言

索引是关系数据库中最重要的对象之中的一个，他能显著降低磁盘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。

下面类型不能作为索引的keyword段（text, ntext, image, nvarchar(max), varchar(max), varbinary(max)）

调整索引的包括字段（including columns）来提高性能

索引的包括字段的概念起源自 SQL Server 2005，SQL Server 2008 及 2012 也具备该功能。它同意你在非聚集索引中包括非键值(non-key)字段，这些字段不会记入索引的大小（这样我们也就不太会促发上文提到的索引字段上限）。另外这些字段的类型能够是除 text, ntext, image 之外的不论什么类型。

在前文的測试案例中 OrderId 并非一个keyword段，由于他并没有在 WHERE 子句中进行筛选，所以把他作为索引的keyword段并不合适。如今我们用 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） 后，你受到的限制更小，并伴随着索引keyword段的降低，索引的占用也变小查询起来更高效。

总结下区分索引keyword段及包括字段的基本原则：

  • WHERE, ORDER BY, GROUP BY, JOIN-ON 中的使用到的字段适用于keyword段
  • 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


posted @ 2017-06-02 09:32  gccbuaa  阅读(393)  评论(0)  编辑  收藏  举报