SQLServer性能调优2之索引（Index）的建立 - 数据库编程

查询 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 = ONCONCAT_NULL_YIELDS_NULL = ONQUOTED_IDENTIFIER = ONANSI_WARNINGS = ONANSI_NULLS = ONANSI_PADDING = ONNUMERIC_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

SQLServer性能调优2之索引（Index）的建立(二)

使用过滤索引（filtered index）来提高性能

使用列存储索引（columnstore index）来提高性能

附录