快捷搜索:

SQL 扫描参数(SARG)

改良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

50005000

假如一个表达式不能满意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

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 wherefariqi='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这个关键词。

到此为止,我们上面评论争论了若何实现从大年夜容量的数据库中快速地查询出您所必要的数据措施。当然,我们先容的这些措施都是“软”措施,在实践中,我们还要斟酌各类“硬”身分,如:收集机能、办事器的机能、操作系统的机能,以致网卡、互换机等。

您可能还会对下面的文章感兴趣: