分享:指定索引优化SQL

dotnba 2009-09-29 07:24:14
这两天遇到一个问题,比较奇怪,记录下来。问题描述如下:
在Sql Server 2005中,有表ITWORKITEMS,数据有100万行,WIREFID 字段上建有索引(非联合索引)。此表共有9个索引,无聚集索引。执行如下步骤:

1.

SQL code
    SET STATISTICS TIME ON


2.

SQL code(方式一)
    DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC



结果:
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 315 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
(102 行受影响)
SQL Server 执行时间:
CPU 时间 = 1485 毫秒,占用时间 = 22802 毫秒。

3.

SQL code
    DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
CHECKPOINT



4.

SQL code(方式二)
    DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1 FROM ITWORKITEMS I
WHERE I.WIREFID LIKE 'nvevrqt6e25wzpvhpa95htn4h2.%'
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC




SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 283 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
(102 行受影响)
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 436 毫秒。

全文参看:http://blog.sina.com.cn/s/blog_3f2ef1180100cf4p.html
...全文
305 23 打赏 收藏 举报
写回复
23 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
boy9742 2011-06-02
[Quote=引用 9 楼 ldy 的回复:]
引用 7 楼 liangck 的回复:
SQL SERVER查询优化器去优化查询的时候.会执行几个步骤.
第一.查询分析
分析查询子句(WHERE)的表达式,是否符合SARG.
由于你的WHERE LIKE时,通配符出现在后面,而不是开头,所以符合SARG.
第二.索引选择
分析你的符合SARG的表达式中,属性列是否存在索引,以及索引的有效性,
以及索引是否包含查询中……
[/Quote]
看完了,这里已经回答了各种可能。
  • 打赏
  • 举报
回复
--小F-- 2009-10-11
学习
  • 打赏
  • 举报
回复
sgtzzc 2009-10-11
学习!
  • 打赏
  • 举报
回复
dotnba 2009-10-11
自己顶一下...
  • 打赏
  • 举报
回复
dotnba 2009-09-30
[Quote=引用 17 楼 liangck 的回复:]
感谢lz分享...但也要BS一下,是在09-03-10发表在sina的..但现在才分享给csdner..BS一下.
[/Quote]
呵呵,好久不混论坛玩了阿,并且离编程比较远了。这个纯属爱好了
  • 打赏
  • 举报
回复
「已注销」 2009-09-29
谢谢lz分享
  • 打赏
  • 举报
回复
liangCK 2009-09-29
感谢lz分享...但也要BS一下,是在09-03-10发表在sina的..但现在才分享给csdner..BS一下.
  • 打赏
  • 举报
回复
dotnba 2009-09-29
[Quote=引用 4 楼 garnett_kg 的回复:]
怪怪的,SP2之前的版本是会有这种问题。你打了SP几的?


这样呢?
SQL codeDECLARE@Process_Instance_IdnVarChar(48)SET@Process_Instance_Id='nvevrqt6e25wzpvhpa95htn4h2.%'SELECT1FROM ITWORKITEMS IWHERE I.WIREFIDLIKE@Process_Instance_IdORDERBY I.WISTARTDESC, I.PRINSTUIDDESC, I.WIIDDESCOPTION (RECOMPILE)---
[/Quote]

这个选项好使...此外,升级到SP3似乎不能改善这个问题。
  • 打赏
  • 举报
回复
dotnba 2009-09-29
[Quote=引用 13 楼 liangck 的回复:]
SQL codeCREATEPROC p@Process_Instance_IdASSELECT1FROM ITWORKITEMS IWHERE I.WIREFIDLIKE@Process_Instance_IdGOEXEC p'nvevrqt6e25wzpvhpa95htn4h2.%'如果优化程序认为WIREFID的选择性足够高,这样会生成的执行计划会跟你的第一个查询的计划一样的.
[/Quote]

CREATE PROC p
@Process_Instance_Id nVarChar(40)
AS SELECT 1 FROM ITWORKITEMS I WHERE I.WIREFID LIKE @Process_Instance_Id
GO
EXEC p 'nvevrqt6e25wzpvhpa95htn4h2.%'


SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 9 毫秒,占用时间 = 9 毫秒。

(103 行受影响)

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
CPU 时间 = 15 毫秒,占用时间 = 10 毫秒。

事实证明你正确,哈哈。估计我当时是有复杂的其它连接,后来采用了指定索引的办法。
  • 打赏
  • 举报
回复
dotnba 2009-09-29
[Quote=引用 8 楼 liangck 的回复:]
SQL codeSET@Process_Instance_Id='nvevrqt6e25wzpvhpa95htn4h2.%'SELECT1FROM ITWORKITEMS IWITH(INDEX(IX9_WORKITEMS))WHERE I.WIREFIDLIKE@Process_Instance_Id-------
如果I.WIREFIDLIKE'nvevrqt6e25wzpvhpa95htn4h2.%'
不能高选择性的过滤掉大部分记录的话,也就是符合条件的记录很多时,强制使用非聚焦索引,
会比直接表扫描成本还要高很多.
[/Quote]

这个返回的记录只有几十条,表有几百万条。
  • 打赏
  • 举报
回复
liangCK 2009-09-29
CREATE PROC p @Process_Instance_Id varchar(50)
AS
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
GO

EXEC p 'nvevrqt6e25wzpvhpa95htn4h2.%'
如果优化程序认为WIREFID的选择性足够高,
这样会生成的执行计划会跟你的强制使用查询的计划一样的.

修改一下,漏了个类型.
  • 打赏
  • 举报
回复
7761098 2009-09-29
干吗不写在CSDN呢,sina好慢
  • 打赏
  • 举报
回复
fanzhouqi 2009-09-29
小弟也碰到过,按理来说 sql server 2005 like%% 是会用到索引的。就只能是表扫描。
jj哥的博文也提到过 like使用索引的问题
http://blog.csdn.net/jinjazz/archive/2009/09/14/4551008.aspx
  • 打赏
  • 举报
回复
dotnba 2009-09-29
[Quote=引用 6 楼 soft_wsx 的回复:]
历害!
[/Quote]
什么厉害?
  • 打赏
  • 举报
回复
dotnba 2009-09-29
[Quote=引用 7 楼 liangck 的回复:]
SQL SERVER查询优化器去优化查询的时候.会执行几个步骤.
第一.查询分析
  分析查询子句(WHERE)的表达式,是否符合SARG.
  由于你的WHERE LIKE时,通配符出现在后面,而不是开头,所以符合SARG.
第二.索引选择
  分析你的符合SARG的表达式中,属性列是否存在索引,以及索引的有效性,
  以及索引是否包含查询中涉及到的列等
  而此时,你的WHERE中的列存在索引.
第三.检查统计值.
  优化器需要统计值去分析你的选择语句中的选择性.
  也就是说,如果优化器分析你的统计值时,认为.如果顺序IO比随机IO更有效.
  那就不会去使用非聚集索引去随机IO的.

  也因为在计划编译的时候,变量的值是未知道的.所以你的第一个语句中.使用了变量.
  优化器不能根据具体的值去分析统计值中,该列数据的选择性,数据分布情况.
  所以第一个语句的这一步.不过去.所以.优化器没有选择该索引.而是使用了扫描的计划.
第四.索引消耗.
  如果优化器认为.使用索引会使成本更高,那优化器是不会去使用这个索引的.


--对于第一个使用了变量的查询.
可采用的方法,可以使用存储过程.因为存储过程在编译的时候,已经知道了参数的值.
也可采用强制使用索引.如果你知道该WHERE返回的记录很少的时候,可以采用强制索引的方法.
但如果WHERE返回的记录很多.那强制使用索引的成本会异常的高.
也可使用OPTION(OPTIMIZE FOR)让优化器根据指定的一个值去生成一个执行计划.
[/Quote]

1.你说的很全面,关于你的第三点,我在我的文章的后面也提到了:
后记:
2009.3.17,灵光乍现,大概觉得MSSQL为什么要将带参数以全表扫描作为执行计划了。因为Like后面的值不确定(参数),假设值是类似'%value%'这样的模式,那必然还是利用不上索引。这样想来,其以最坏情况来处理,也就是在情理之中了

2.你说在存储过程中,“可以使用存储过程.因为存储过程在编译的时候,已经知道了参数的值. ”,我记得我就是从存储过程中发现性能的问题,把存储过程中的代码简化拿出来的。所以对这个话质疑。

  • 打赏
  • 举报
回复
liangCK 2009-09-29
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%' 

SELECT 1

FROM ITWORKITEMS I WITH(INDEX(IX9_WORKITEMS))
WHERE I.WIREFID LIKE @Process_Instance_Id
-------

如果I.WIREFID LIKE'nvevrqt6e25wzpvhpa95htn4h2.%'
不能高选择性的过滤掉大部分记录的话,也就是符合条件的记录很多时,强制使用非聚焦索引,
会比直接表扫描成本还要高很多.
  • 打赏
  • 举报
回复
liangCK 2009-09-29
SQL SERVER查询优化器去优化查询的时候.会执行几个步骤.
第一.查询分析
分析查询子句(WHERE)的表达式,是否符合SARG.
由于你的WHERE LIKE时,通配符出现在后面,而不是开头,所以符合SARG.
第二.索引选择
分析你的符合SARG的表达式中,属性列是否存在索引,以及索引的有效性,
以及索引是否包含查询中涉及到的列等
而此时,你的WHERE中的列存在索引.
第三.检查统计值.
优化器需要统计值去分析你的选择语句中的选择性.
也就是说,如果优化器分析你的统计值时,认为.如果顺序IO比随机IO更有效.
那就不会去使用非聚集索引去随机IO的.

也因为在计划编译的时候,变量的值是未知道的.所以你的第一个语句中.使用了变量.
优化器不能根据具体的值去分析统计值中,该列数据的选择性,数据分布情况.
所以第一个语句的这一步.不过去.所以.优化器没有选择该索引.而是使用了扫描的计划.
第四.索引消耗.
如果优化器认为.使用索引会使成本更高,那优化器是不会去使用这个索引的.


--对于第一个使用了变量的查询.
可采用的方法,可以使用存储过程.因为存储过程在编译的时候,已经知道了参数的值.
也可采用强制使用索引.如果你知道该WHERE返回的记录很少的时候,可以采用强制索引的方法.
但如果WHERE返回的记录很多.那强制使用索引的成本会异常的高.
也可使用OPTION(OPTIMIZE FOR)让优化器根据指定的一个值去生成一个执行计划.
  • 打赏
  • 举报
回复
soft_wsx 2009-09-29
历害!
  • 打赏
  • 举报
回复
dotnba 2009-09-29
SELECT @@VERSION AS 'SQL Server Version'


Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) Feb 13 2007 23:02:48 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


估计是版本的原因,我一会升级到SP2试一下。
  • 打赏
  • 举报
回复
Garnett_KG 2009-09-29
怪怪的,SP2之前的版本是会有这种问题。你打了SP几的?


这样呢?

DECLARE @Process_Instance_Id nVarChar(48)
SET @Process_Instance_Id = 'nvevrqt6e25wzpvhpa95htn4h2.%'
SELECT 1
FROM ITWORKITEMS I
WHERE I.WIREFID LIKE @Process_Instance_Id
ORDER BY I.WISTART DESC, I.PRINSTUID DESC, I.WIID DESC
OPTION (RECOMPILE) ---

  • 打赏
  • 举报
回复
加载更多回复(3)
发帖
应用实例

2.7w+

社区成员

MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
帖子事件
创建了帖子
2009-09-29 07:24
社区公告
暂无公告