首页 > 代码库 > 应用索引技术优化SQL 语句二
应用索引技术优化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 语句二