应用索引技术优化SQL 语句(Part 2)_vc++ sql 建索引-CSDN博客

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

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

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'