分享：指定索引优化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
...全文
383 23 打赏 收藏 转发到动态 举报
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
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）
高效SQL学习笔记
高效SQL学习笔记
sql面试题大全
sql 面试题 大全 JSP 开发
SQL语句大全(经典珍藏版)
经典的SQL语句大全 初学进阶数据库必备资源
SQL优化：索引优化
SQL索引  　　SQL索引在数据库优化中占有一个非常大的比例， 一个好的索引的设计，可以让你的效率提高几十甚至几百倍，在这里将带你一步步揭开他的神秘面纱。 　　1.1 什么是索引？ 　　SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间  下面举两个简单的例子：         图书馆的
SQL学习之索引(优化)
当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图:内联视图是 SQL 查询中的一个概念，它允许您在查询中嵌套另一个查询作为一个临时的虚拟表），然后扫描整个视图。聚簇索引：按照数据的物理顺序进行排序的索引，在一个表中只能有一个聚簇索引，因此他 决定了数据在磁盘上的存储方式。不适合的索引，或者过多的索引，都会降低插入、修改和删除数据的效率。高维数据和复杂查询的限制：对于高维数据和复杂查询，传统的索引可能无法提供良好的性能。
应用实例

27,580

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章