Freigeben über

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

  • Artikel

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

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

Comments

  • Anonymous
    October 19, 2013
    好文章