SQL Server优化笔记 – 索引部分

1. 聚集索引

通俗地来说，聚集索引是表在新增数据时的排序方式。如字典以字母排序，小说以章节排序，历史书以区域排序等。

很多人以自增的ID主键作为表的聚集索引，这种定义能够满足唯一性要求，也不会增加索引碎片。

但是除了对表进行周期性处理外，在业务上我们很少把ID作为WHERE的条件，因为自增ID很少有业务含义。

相反，我们应该思考，在查询时，我们会利用哪个WHERE条件，把要查询的数据从一个非常大的数据集筛选出来。

例如，我们有一个OrderInfo表，存储了10年的工单数据，表里有ID(自增变量)、OrderNumber(工单号)、ScheduleDate(计划时间)、CreatedOn(创建时间)等字段。

在日常业务上我们通常只查询近3个月创建的工单，那么我们可以联合CreatedOn和ID这2个字段创建聚集索引(ID字段保证唯一性)，在查询时我们总是带上WHERE CreatedOn > GETDATE() – 90这个条件，那么我们可以很快通过聚集索引定位到一个非常小的数据集。

2. 非索引列的定位

同样以OrderInfo表为例，假如我们为ScheduleDate创建非聚集索引。

如运行以下SQL，以获取计划时间为最近一周的数据:

SELECT

OrderNumber,

ScheduleDate

FROM OrderInfo

WHERE CreatedOn > GETDATE() – 90

AND (ScheduleDate BETWEEN GETDATE() AND GETDATE() + 7)

ORDER BY ScheduleDate;

我们可以看到用于筛选的2个字段CreatedOn和ScheduleDate都已经建立了索引，因此筛选速度很快。

但是在输出的2个字段中，ScheduleDate已有索引，而OrderNumber却没有索引，那么数据库是怎么定位的呢。

数据库在创建索引的时候，除了索引引用的字段外，还会包含一个隐藏的字段，即表的原始行号ROWID。

在此例中，ScheduleDate通过索引查找定位(Index Seek)，同时得到ROWID，再通过ROWID得到OrderNumber的值(Cluster Index Scan)。

如果表的数据量大，那么Cluster Index Scan要扫描的记录数非常多，也要尽可能避免，改进的方式是针对要SELECT输出的字段都加上索引。

也就是说，不仅是WHERE字段、GROUP BY字段、ORDER字段要建立索引，SELECT字段也要建立索引，而且常用的查询要尽可能建立多个字段复合索引。

3. 类型转换

类型转换会使索引失效。这是很多人会犯的错误。

例如：

1) 日期和字符串转换

错误：WHERE CONVERT(VARCHAR, OrderDate, 111) >= '2011/06/01'

正确：WHERE OrderDate >= CONVERT(DATE, '2011/06/01', 111)

2) 大小写转换

错误：WHERE UPPER(FirstName) = 'ALICE'

正确：新建一字段并建立索引，在新增数据时，将UPPER(FirstName)值写入此字段，然后将此字段用于WHERE条件。

3) 字符串LIKE操作

错误：WHERE SalesOrderNumber LIKE 'SO43%'

正确的做法是从要比较的字段中拆出一个字段，在此字段中建立索引，然后在此字段中建立WHERE比较，符号为=或<>。

4) 数值运算

错误：WHERE UnitPrice * Qty > 10000

正确：创建计算字段ALTER TABLE ADD COLUMN TotalPrice AS UnitPrice * Qty，在此字段上建立索引。

4. 视图索引

视图上也可以建立索引，但是创建视图时必须包含WITH SCHEMABINDING。