应用索引技术优化SQL 语句二

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

 

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

应用索引技术优化SQL 语句二

上一篇:HahaMil数据库(数据库操作组件)


下一篇:【转】System.Data.OracleClient requires Oracle client software version 8.1.7 or greater