四、分析执行计划创建索引

 

根据语句的执行计划来判断应该对什么表创建什么索引，是常用优化技巧。其实文章前面的例子已经告诉读者如何结合statistics profile 和statistics IO语句的输出来创建索引。这里分析一个稍微复杂一些的例子。

 

SQL语句如下：

SELECT CurrentseNo FROM v_ptdata_edss WHERE MRN = @P1 

 

Statistics IO的输出如下：

 

Table 'ptseoutpat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.

Table 'ptdata'. Scan count 1, logical reads 3218, physical reads 0, read-ahead reads 0.

 

部分执行计划如下：

 

Rows    Executes StmtText                                                                                      

------  -------- -----------------------------------------------------------------------------------------------

0       1        SELECT CurrentseNo FROM v_ptdata_edss WHERE MRN = @P1                                         

0       1          |--Nested Loops(Inner Join, OUTER REFERENCES:([ptdata].[CurrentseNo]))                      

1       1               |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([TTSH_Neon_ADT].[dbo].[ptdata]))      

1       1               |    |--Filter(WHERE:(Convert([ptdata].[PatExtID])=[@P1]))                             

571955  1               |         |--Index Scan(OBJECT:([TTSH_Neon_ADT].[dbo].[ptdata].[PK_ptdata]))           

0       1               |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009], [Expr1010], [Expr1011]))     

2       1                    |--Merge Interval                                                                 

2       1                    |    |--Sort(TOP 2, ORDER BY:([Expr1012] DESC, [Expr1013] ASC, [Expr1009] ASC, [Exp

2       1                    |         |--Compute Scalar(DEFINE:([Expr1012]=4&[Expr1011]=4 AND NULL=[Expr1009],

2       1                    |              |--Concatenation                                                   

1       1                    |                   |--Compute Scalar(DEFINE:([Expr1006]=NULL, [Expr1007]=NULL, [Ex

1       1                    |                   |    |--Constant Scan                                         

1       1                    |                   |--Compute Scalar(DEFINE:([Expr1009]='Jan  1 1900 12:00AM', [Ex

1       1                    |                        |--Constant Scan                                         

0       2                    |--Index Seek(OBJECT:([TTSH_Neon_ADT].[dbo].[ptseoutpat].[ptseoutpat1]), SEEK:([pts

                                                                                                                

分析的关键是：

 

步骤1）找出最昂贵的表（也就是logical reads最多的表），是'ptdata' 表。

 

步骤2）从执行计划中找出对ptdata表的相应的操作，通常是左边行数最多的那一行如上图中的标志行。对表的操作是index scan操作。

 

步骤3）根据操作判断如何创建index或如何改写语句。从执行计划中我们看到index scan之后的操作也就是下面的filter操作把数据大大减少了：

 

Filter(WHERE:(Convert([ptdata].[PatExtID])=[@P1])) 

 

一般情况下，对这个字段建立索引问题就解决了。但对我们的例子语句而言还不够。实际上PatExtID字段已经有索引了。那么为什么用index scan而不用index seek呢？ 后来发现原因是传递的参数@P1和表字段PatExtID的类型是不一致的。@P1是nvarchar类型，而PatExtID是varchar类型。这导致了SQL Server 产生了对索引字段进行index scan的Convert操作。解决方法很简单，把传递的参数改成varchar或把表字段类型改成nvarchar，使得它们类型一致就可以了。

 

五．语句的写法影响SQL Server 能否利用索引

 

仅仅有索引是不够的。语句的写法会影响SQL Server 对索引的选择。比如下面的语句：

 

select  学生姓名, 入学时间 from tbl1 where DATEDIFF(mm,'20050301',入学时间)=1

 

理所当然，需要在入学时间字段上建立索引：

 

create nonclustered index idx_入学时间 on tbl1(入学时间)

 

然后运行如下script 5看看该索引是否有用：

/******Script 5***********************************/

set statistics profile on

set statistics io on

go

select  学生姓名, 入学时间 from tbl1 where DATEDIFF(mm,'20050301',入学时间)=1

go

set statistics profile off

set statistics io off

/*************************************************/

 

语句的部分输出如下：

 

Table 'tbl1'. Scan count 1, logical reads 385, physical reads 0, read-ahead reads 0.         

Rows  Executes    StmtText                                                             

----------- ----------- ----------------------------------------------------------------------

56    1          select  学生姓名, 入学时间 from tbl1 where DATEDIFF(mm,'20050301',入学

56    1             |--Table Scan(OBJECT:([tempdb].[dbo].[tbl1]), WHERE:(datediff(month,

 

不幸的是，是Table Scan，刚建立的索引并没有被使用。这是因为WHERE语句中的DATEDIFF函数引起的。因为函数作用在索引字段上， SQL Server 无法直接利用索引定位数据，必须对该字段所有的值运算该函数才能得知函数结果是否满足where条件。在这种情况下，Table Scan是最好的选择。为了使用索引，可以把语句改成如下的样子：

 

select  学生姓名, 入学时间  from tbl1 

  where 入学时间>='20050401' and 入学时间<'20050501'

 

把该语句替换script 5中select语句然后运行该script，结果如下：

 

Table 'tbl1'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0.     

Rows Executes StmtText                                                                  

-----------------------------------------------------------------------------------------

56   1  SELECT [学生姓名]=[学生姓名],[入学时间]=[入学时间] FROM [tbl1] WHERE [入学时间]>=

56   1    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[tbl1]) WITH PR

56   1         |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_入学时间]), SEEK:([tbl1].

 

可以看到Table Scan变成了Index seek， Logical Reads 也减少到58。从上面的例子可以知道，为了利用索引，不要对where语句中的字段直接使用各种函数或表达式。要尽量把函数或表达式放在操作符的右边。

 

再多举一些例子，下面的where语句写法是不好的：

 

Where substring(colum1,1,4)>'ddd'

Where convert(varchar(200),column1)>'aaa'

 

如果你实在无法避免上面的情况，而相关的语句又是数据库系统的关键语句，那么建议你从系统设计的高度来考虑问题。比方说，改变表的结构等，使得不再需要在where子句中的字段上直接使用函数或表达式等。

 

使用前置百分号或不等号也是不好的Where写法：

 

Where column1 like ‘%abc%’

Where column1 <> 'bb'

 

第一个where语句中因为第一个百分号会导致SQL Server 进行索引扫描（index scan）或Table Scan。要尽量不使用前置百分号。比方说改成如下的语句就会好得多：

 

Where column1 like ‘abc%’

 

再多看一个例子：

 

Where column1 ＝2 OR column2＝30

 

这个where语句中如果column1 和column2中任何一个字段没有索引，那么整条语句就会导致全表扫描。（想一想为什么？）所以在有OR的where语句要特别注意OR两边的字段都要有必要的索引。

六、有关索引的几个问题

 

问题1，是否值得在identity字段上建立聚集索引。答案取决于identity 字段如何在语句中使用。如果你经常根据该字段搜索返回很少的行，那么在其上建立索引是值得的。反之如果identity字段根本很少在语句中使用，那么就不应该对其建立任何索引。

 

问题2，一个表应该建立多少索引合适。如果表的80％以上的语句都是读操作，那么索引可以多些。但是不要太多。特别是不要对那些更新频繁的表其建立很多的索引。很少表有超过5个以上的索引。过多的索引不但增加其占用的磁盘空间，也增加了SQL Server 维护索引的开销。

 

问题4：为什么SQL Server 在执行计划中没有使用你认为应该使用的索引？原因是多样的。一种原因是该语句返回的结果超过了表的20％数据，使得SQL Server 认为scan比seek更有效。

 

另一种原因可能是表字段的statistics过期了，不能准确反映数据的分布情况。你可以使用命令UPDATE STATISTICS tablename with FULLSCAN来更新它。只有同步的准确的statistics才能保证SQL Server 产生正确的执行计划。过时的老的statistics常会导致SQL Server生成不够优化的甚至愚蠢的执行计划。所以如果你的表频繁更新，而你又觉得和之相关的SQL语句运行缓慢，不妨试试UPDATE STATISTIC with FULLSCAN 语句。

 

你甚至可以使用Index hint比较不同索引的性能差异。比如对上面script 4提到的两个索引，可以这样比较，

 

select 学生姓名, 入学时间 from tbl1 with (index= idx_年龄入学时间)

where  ……

或者：

select 学生姓名, 入学时间 from tbl1 with (index= idx_入学时间年龄)

where  ……

 

如果强制使用你的索引后logical reads大大减少，那么就需要进一步研究为什么SQL Server 不使用正确的索引。注意，不要总是将使用索引等同于好的性能，反之亦然。SQL Server只在索引能提高性能时才使用索引检索。有时候使用Table scan的性能比使用某个索引反而要好。

 

问题5、什么使用聚集索引，什么时候使用非聚集索引

 

在SQL Server 中索引有聚集索引和非聚集索引两种。它们的主要差别是前者的索引叶子就是数据本身，而后者的叶子节点包含的是指向数据的书签（即数据行号或聚集索引的key）。

 

在上面的例子中我全部使用非聚集索引，原因是对一个表而言聚集索引只能有一个，而非聚集索引可以有多个。如果你把上面例子中的非聚集索引换成聚集索引，效果也是类似的，只是聚集索引没有Bookmark Lookup操作。什么时候应该使用聚集索引，什么时候使用非聚集索引取决于应用程序的访问模式。我的建议是在那些关键的字段上使用聚集索引。一个表一般都需要建立一个聚集索引。对于什么时候使用聚集索引，SQL Server 2000联机手册中有如下描述：

 

在创建聚集索引之前，应先了解您的数据是如何被访问的。可考虑将聚集索引用于：

  • 包含大量非重复值的列。
  • 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。
  • 被连续访问的列。
  • 返回大型结果集的查询。
  • 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
  • OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。

 

聚集索引不适用于：

  • 频繁更改的列

这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

  • 宽键

来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

 

七、结束语

 

如何使一个性能缓慢的系统运行更快更高效，不但需要整体分析数据库系统，找出系统的性能瓶颈，更需要优化数据库系统发出的SQL 语句。一旦找出关键的SQL 语句并加与优化，性能问题就会迎刃而解。读完本文，你应该知道创建索引的关键是什么，以及如何分析SQL语句的执行计划来创建索引。在优化了索引后大部分数据库系统的性能都能够得到不同程度的改善，有的系统甚至能够获得好几倍以上的性能提升。本文并不能解决你在优化SQL语句中碰到的所有问题，但其中讨论的内容或技巧对许多性能问题都有一定的参