应用索引技术优化SQL 语句(Part 2)

根据语句的执行计划来判断应该对什么表创建什么索引，是常用优化技巧。其实文章前面的例子已经告诉读者如何结合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两边的字段都要有必要的索引。