分享：指定索引优化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
...全文
364 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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系统的性能，加快数据的查询速度与减少系统的响应时间  下面举两个简单的例子：         图书馆的
MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）
目录0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合建索引3.6 哪些情况不适合建索引4 性能分析4.1 性能分析前提知识4.2 Explain使用简介4.3 执行计划包含的信息字段解释（重中之重）id（表的读取顺序）select_type（ 数据读取操作的操作类型）table（显示执行的表名）type（访问类型排列）possible_k
应用实例

27,582

社区成员

68,544

社区内容

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

加载中

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

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

+ 用AI写文章