SQL Server 索引构造及其利用（二）[MSSQL防范]

SQL Server索引构造及其利用（一）
SQL Server索引构造及其利用（二）
SQL Server索引构造及其利用（三）
SQL Server索引构造及其利用（四）

改进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这个关键词.
　　到此为止,我们上面谈论了若何实现从大容量的数据库中快速地查询出您所需求的数据办法.当然,我们介绍的这些办法都是"软"办法,在实践中,我们还要考虑各种"硬"因素,如:网络性能、服务器的性能、操作系统的性能,乃至网卡、交换机等.

（待续...）

  　　以上是“SQL Server 索引构造及其利用（二）[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：