`
suiliangxiang
  • 浏览: 69365 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

SQL Server 索引使用分析(2)- 改善SQL语句,防止索引失效

阅读更多
原文出处
 
改善SQL语句
 
  很多人不知道SQL语句在sql server中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解。比如:
select * from table1 where name=''zhangsan'' and tID > 10000
和执行:
select * from table1 where tID > 10000 and name=''zhangsan''
  一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name=''zhangsan''的,而后再根据限制条件条件tID>10000来提出查询结果。
  事实上,这样的担心是不必要的。sql server中有一个“查询分析优化器”,它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间,也就是说,它能实现自动优化。
  虽然查询优化器可以根据where子句自动的进行查询优化,但大家仍然有必要了解一下“查询优化器”的工作原理,如非这样,有时查询优化器就会不按照您的本意进行快速查询。
  在查询分析阶段,查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG),那么就称之为可优化的,并且可以利用索引快速获得所需数据。
  SARG的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的AND连接。形式如下:
列名 操作符 <常数 或 变量>
<常数 或 变量> 操作符列名
列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:
Name=’张三’
价格>5000
5000<价格
Name=’张三’ and 价格>5000
  如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是sql server必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。
  介绍完SARG后,我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验:
1、Like语句是否属于SARG取决于所使用的通配符的类型
如:name like ‘张%’ ,这就属于SARG
而:name like ‘%张’ ,就不属于SARG。
原因是通配符%在字符串的开通使得索引无法使用。
2、or 会引起全表扫描
  Name=’张三’ and 价格>5000 符号SARG,而:Name=’张三’ or 价格>5000 则不符合SARG。使用or会引起全表扫描。
3、非操作符、函数引起的不满足SARG形式的语句
  不满足SARG形式的语句最典型的情况就是包括非操作符的语句,如:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等,另外还有函数。下面就是几个不满足SARG形式的例子:
ABS(价格)<5000
Name like ‘%三’
有些表达式,如:
WHERE 价格*2>5000
sql server也会认为是SARG,SQL SERVER会将此式转化为:
WHERE 价格>2500/2
但我们不推荐这样使用,因为有时sql server不能保证这种转化与原始表达式是完全等价的。
4、IN 的作用相当与OR
语句:
Select * from table1 where tid in (2,3)
Select * from table1 where tid=2 or tid=3
是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。
5、尽量少用NOT
6、exists 和 in 的执行效率是一样的
  很多资料上都显示说,exists要比in的执行效率要高,同时应尽可能的用not exists来代替not in。但事实上,我试验了一下,发现二者无论是前面带不带not,二者之间的执行效率都是一样的。因为涉及子查询,我们试验这次用sql server自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开:
(1)select title,price from titles where title_id in (select title_id from sales where qty>30)
该句的执行结果为:
表 ''sales''。扫描计数 18,逻辑读 56 次,物理读 0 次,预读 0 次。
表 ''titles''。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。
(2)select title,price from titles
       where exists (select * from sales
       where sales.title_id=titles.title_id and qty>30)
第二句的执行结果为:
表 ''sales''。扫描计数 18,逻辑读 56 次,物理读 0 次,预读 0 次。
表 ''titles''。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。
我们从此可以看到用exists和用in的执行效率是一样的。
7、用函数charindex()和前面加通配符%的LIKE执行效率一样
  前面,我们谈到,如果在LIKE前面加上通配符%,那么将会引起全表扫描,所以其执行效率是低下的。但有的资料介绍说,用函数charindex()来代替LIKE速度会有大的提升,经我试验,发现这种说明也是错误的:
 
select gid,title,fariqi,reader from tgongwen
         where charindex(''刑侦支队'',reader)>0 and fariqi>''2004-5-5''
用时:7秒,另外:扫描计数 4,逻辑读 7155 次,物理读 0 次,预读 0 次。
select gid,title,fariqi,reader from tgongwen
         where reader like ''%'' + ''刑侦支队'' + ''%'' and fariqi>''2004-5-5''
用时:7秒,另外:扫描计数 4,逻辑读 7155 次,物理读 0 次,预读 0 次。
8、union并不绝对比or的执行效率高
  我们前面已经谈到了在where子句中使用or会引起全表扫描,一般的,我所见过的资料都是推荐这里用union来代替or。事实证明,这种说法对于大部分都是适用的。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
          where fariqi=''2004-9-16'' or gid>9990000
用时:68秒。扫描计数 1,逻辑读 404008 次,物理读 283 次,预读 392163 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
用时:9秒。扫描计数 8,逻辑读 67489 次,物理读 216 次,预读 7499 次。
看来,用union在通常情况下比用or的效率要高的多。
  但经过试验,笔者发现如果or两边的查询列是一样的话,那么用union则反倒和用or的执行速度差很多,虽然这里union扫描的是索引,而or扫描的是全表。
 
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
          where fariqi=''2004-9-16'' or fariqi=''2004-2-5''
用时:6423毫秒。扫描计数 2,逻辑读 14726 次,物理读 1 次,预读 7176 次。
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''
用时:11640毫秒。扫描计数 8,逻辑读 14806 次,物理读 108 次,预读 1144 次。
9、字段提取要按照“需多少、提多少”的原则,避免“select *”
  我们来做一个试验:
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用时:4673毫秒
select top 10000 gid,fariqi,title from tgongwen order by gid desc
用时:1376毫秒
select top 10000 gid,fariqi from tgongwen order by gid desc
用时:80毫秒
  由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。
10、count(*)不比count(字段)慢
  某些资料上说:用*会统计所有列,显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看:
select count(*) from Tgongwen
用时:1500毫秒
select count(gid) from Tgongwen
用时:1483毫秒
select count(fariqi) from Tgongwen
用时:3140毫秒
select count(title) from Tgongwen
用时:52050毫秒
  从以上可以看出,如果用count(*)和用count(主键)的速度是相当的,而count(*)却比其他任何除主键以外的字段汇总速度要快,而且字段越长,汇总的速度就越慢。我想,如果用count(*), sql server可能会自动查找最小字段来汇总的。当然,如果您直接写count(主键)将会来的更直接些。
11、order by按聚集索引列排序效率最高
  我们来看:(gid是主键,fariqi是聚合索引列):
select top 10000 gid,fariqi,reader,title from tgongwen
用时:196 毫秒。 扫描计数 1,逻辑读 289 次,物理读 1 次,预读 1527 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
用时:4720毫秒。 扫描计数 1,逻辑读 41956 次,物理读 0 次,预读 1287 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用时:4736毫秒。 扫描计数 1,逻辑读 55350 次,物理读 10 次,预读 775 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
用时:173毫秒。 扫描计数 1,逻辑读 290 次,物理读 0 次,预读 0 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
用时:156毫秒。 扫描计数 1,逻辑读 289 次,物理读 0 次,预读 0 次。
  从以上我们可以看出,不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的,但这些都比“order by 非聚集索引列”的查询速度是快得多的。
  同时,按照某个字段进行排序的时候,无论是正序还是倒序,速度是基本相当的。
12、高效的TOP
  事实上,在查询和提取超大容量的数据集时,影响数据库响应时间的最大因素不是数据查找,而是物理的I/0操作。如:
select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu=''办公室''
order by gid desc) as a
order by gid asc
  这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是10000条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是sql server中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用,发现TOP确实很好用,效率也很高。但这个词在另外一个大型数据库Oracle中却没有,这不能说不是一个遗憾,虽然在ORACLE中可以用其他方法(如:rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中,我们就将用到TOP这个关键词。
  到此为止,我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然,我们介绍的这些方法都是“软”方法,在实践中,我们还要考虑各种“硬”因素,如:网络性能、服务器的性能、操作系统的性能,甚至网卡、交换机等。
分享到:
评论

相关推荐

    SQLServer索引基础知识----聚集索引,非聚集索引[归纳].pdf

    SQL Server 索引基础知识----聚集索引,非聚集索引 SQL Server 索引是关系型数据库中的一种重要组件,它可以大大提高查询的效率。索引可以分为聚集索引和非聚集索引两种,本文将详细介绍这两种索引的概念、特点和...

    Inside Microsoft SQL Server 2008 T-SQL Programming.pdf

    本书主要聚焦于T-SQL(Transact-SQL)编程语言的使用,T-SQL是SQL Server使用的SQL方言,它不仅包括标准SQL的功能,还扩展了许多专为SQL Server设计的独特特性。T-SQL编程基础部分涵盖了以下内容: - **SQL语句语法...

    Microsoft SQL SERVER 2008技术内幕 T-SQL查询

    《Microsoft SQL SERVER 2008技术内幕 T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL查询技术的专业书籍。T-SQL(Transact-SQL)是微软公司为SQL Server定制的SQL方言,它扩展了标准SQL,提供了更强大的数据库...

    sql server 索引的使用

    sql server 索引的使用 --创建索引create index(索引关键字) indexName(索引名) on userInfo(userName,userAge) create index indexName on userInfo(userName,userAge) --查看索引 sp_helpindex userInfo --创建...

    Microsoft SQL Server 2008技术内幕:T-SQL查询.pdf

    《Microsoft SQL Server 2008技术内幕:T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL(Transact-SQL)查询的权威指南。T-SQL是微软SQL Server数据库管理系统的核心语言,用于数据操作、查询、存储过程编写以及...

    Microsoft SQL Server 2008技术内幕:T-SQL查询_源代码及附录.zip

    T-SQL允许创建存储过程,这是一种预编译的SQL语句集合,可提高性能并简化复杂的操作。触发器则是一种特殊类型的存储过程,当数据库中的特定事件(如INSERT、UPDATE或DELETE)发生时自动执行。 6. **游标**: 游标...

    V1.0-sqlServer索引使用总结.docx

    SQL Server 索引使用总结 本文档总结了 SQL Server 中索引的使用方法、分类和注意事项,并提供了实践测试的示例代码。 索引的使用目的 索引的使用目的主要是为了提高查询速度和效率。通过创建索引,可以快速地...

    SQL Server 索引中include的魅力(具有包含性列的索引)

    SQL Server 索引中 include 的魅力(具有包含性列的索引) SQL Server 索引中 include 的魅力(具有包含性列的索引)是指在非聚集索引中添加非键列,以扩展索引的功能,提高查询性能。通过将非键列添加到非聚集索引...

    SQLserver索引创建语句.txt

    SQLserver索引创建语句

    SqlServer重建索引

    用于SqlServer的索引重建,全语句实现,可根据实际情况进行部分关键表的索引重建。

    sqlserver自动生成sql语句工具sqlserver转oracle

    首先,标题中的"sqlserver自动生成sql语句工具"指的是可以分析SQL Server数据库结构和数据,自动生成对应的SQL创建语句的软件。这种工具通常能帮助数据库管理员快速获取数据库的定义,便于备份、迁移或在其他环境中...

    sqlserver管理索引优化SQL语句

    sqlserver管理索引优化SQL语句

    SQL-Serve实验5-索引和视图-.doc

    SQL Server索引和视图是数据库管理系统中两个重要的概念,本实验旨在通过实践操作,掌握使用SQL Server管理平台和Transact-SQL语句创建、查看和删除索引、视图的方法。 一、索引的概念和类型 索引是数据库管理...

    SQLServer索引设计经验谈

    ### SQL Server索引设计经验谈 #### 摘要 本文旨在探讨Microsoft SQL Server中的索引设计技巧,尤其是如何通过合理的索引策略来优化查询性能。通过对聚集索引与非聚集索引的选择、索引列的排序方式、填充因子的...

    Microsoft SQL Server 2008技术内幕T-SQL语言基础

    《Microsoft SQL Server 2008技术内幕T-SQL语言基础》是一本深入解析SQL Server 2008中T-SQL(Transact-SQL)语言的权威指南。T-SQL是微软SQL Server数据库管理系统的核心组件,用于数据查询、操作、编程以及数据库...

    SQLSERVER技术内幕系列2005-2008

    最后,我们看到一个名为《非常好用的SQL语句优化34条.txt》的文本文件,这很可能是作者或专家总结的SQL查询优化技巧。这些技巧可能包括避免全表扫描,合理使用索引,优化联接操作,以及利用查询提示和执行计划来提升...

    microsoft sql server 2008技术内幕 t-sql语言基础

    《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》这本书是针对SQL Server 2008数据库管理系统,特别关注其Transact-SQL(T-SQL)语言的全面指南,非常适合初学者深入理解T-SQL的基础知识。T-SQL是SQL Server的...

    SQLServer 索引查询优化指南

    - SQLServer的查询优化器是负责生成执行计划的组件,它根据查询语句、表统计信息和索引来决定最佳的执行路径。 - 优化器可能会选择全表扫描、索引扫描或索引查找等操作。 4. **索引的选择与设计** - 索引不是越...

    sql server学习手册以及t-sql优化方案.zip

    SQL Server 2008作为Microsoft公司的一款企业级数据库管理系统,广泛应用于数据存储、处理和分析。对于任何希望深入理解和掌握SQL Server的人来说,全面了解其基础知识及T-SQL的优化策略至关重要。本资料包《sql ...

    SQL Server 2000 看图教程2-2

    - **表的创建**:在SQL Server 2000中,我们可以通过SQL语句或企业管理器创建数据库表,定义字段、数据类型、主键等。 - **索引**:理解索引的作用,如何创建唯一索引、非唯一索引、聚簇和非聚簇索引以提高查询...

Global site tag (gtag.js) - Google Analytics