用时:6453毫秒

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 非聚集索引列”的查询速度是快得多的。

表 ”sales”。扫描计数
18,逻辑读 56 次,物理读 0 次,预读 0 次。

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

但要既使聚集索引列既符合查询列的需要,又符合排序列的需要,这通常是一个矛盾。笔者前面“索引”的讨论中,将fariqi,即用户发文日期作为了聚集索引的起始列,日期的精确度为“日”。这种作法的优点,前面已经提到了,在进行划时间段的快速查询中,比用ID主键列有很大的优势。

但经过试验,笔者发现如果or两边的查询列是一样的话,那么用union则反倒和用or的执行速度差很多,虽然这里union扫描的是索引,而or扫描的是全表。 

您可能感兴趣的文章:

  • SQL Server
    分页查询存储过程代码
  • 防SQL注入
    生成参数化的通用分页查询语句
  • php下巧用select语句实现mysql分页查询
  • SQL行号排序和分页(SQL查询中插入行号
    自定义分页的另类实现)
  • oracle,mysql,SqlServer三种数据库的分页查询的实例
  • 高效的SQLSERVER分页查询(推荐)
  • Mysql中分页查询的两个解决方法比较
  • mysql分页原理和高效率的mysql分页查询语句
  • Oracle实现分页查询的SQL语法汇总
  • sql分页查询几种写法

Name=’张三’ and 价格>5000 符号SARG,而:Name=’张三’ or 价格>5000
则不符合SARG。使用or会引起全表扫描。

介绍完SARG后,我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验:

欧博国际平台 1欧博国际平台 2

1.(1)select title,price from titles where title_id in (select
title_id from sales where qty>30)

2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

表 ”titles”。扫描计数
1,逻辑读 2 次,物理读 0 次,预读 0 次。

五、其他注意事项

1、Like语句是否属于SARG取决于所使用的通配符的类型

5.order by gid asc

5000<价格

1.select top 10000 gid,fariqi,reader,title from tgongwen order by gid
asc

1.select gid,title,fariqi,reader from tgongwen where
charindex(”刑侦支队”,reader)>0 and fariqi>”2004-5-5”

我们来做一个试验:

用时:1500毫秒

看来,用union在通常情况下比用or的效率要高的多。

Select * from table1 where tid in (2,3)和Select * from table1 where
tid=2 or tid=3

列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:

用时:68秒。扫描计数
1,逻辑读 404008 次,物理读 283 次,预读 392163 次。

1.select top 10000 gid,fariqi,reader,title from tgongwen

表 ”sales”。扫描计数
18,逻辑读 56 次,物理读 0 次,预读 0 次。

1.select gid,title,fariqi,reader from tgongwen where reader
like ”%” + ”刑侦支队” + ”%” and fariqi>”2004-5-5”

6、exists 和 in 的执行效率是一样的

下面的表总结了何时使用聚集索引或非聚集索引(很重要):

很多人不知道SQL语句在SQL
SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL
SERVER误解。比如:

用时:1376毫秒

我们来做一个试验:

通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL
SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。

用时:6423毫秒。扫描计数
2,逻辑读 14726 次,物理读 1 次,预读 7176 次。

但我们不推荐这样使用,因为有时SQL
SERVER不能保证这种转化与原始表达式是完全等价的。

1.select gid,title,fariqi,reader from tgongwen where reader
like ”%” + ”刑侦支队” + ”%” and fariqi>”2004-5-5”

1.select gid,fariqi,neibuyonghu,title from Tgongwen

8、union并不绝对比or的执行效率高

下面是实例语句:(都是提取25万条数据)

1.select top 10000 gid,fariqi,reader,title from tgongwen order by gid
desc

用时:3280毫秒

用时:156毫秒。 扫描计数
1,逻辑读 289 次,物理读 0 次,预读 0 次。

)聚集索引的重要性和如何选择聚集索引

11、order by按聚集索引列排序效率最高

但在分页时,由于这个聚集索引列存在着重复记录,所以无法使用max或min来最为分页的参照物,进而无法实现更为高效的排序。而如果将ID主键列作为聚集索引,那么聚集索引除了用以排序之外,没有任何用处,实际上是浪费了聚集索引这个宝贵的资源。

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
fariqi=”2004-9-16”

01.CREATE procedure pagination1

02.(@pagesize int, --页面大小,如每页存储20条记录

03.@pageindex int --当前页码

04.)

05.as

06. 

07.set nocount on

08. 

09.begin

10.declare @indextable table(id int identity(1,1),nid int) --定义表变量

11.declare @PageLowerBound int --定义此页的底码

12.declare @PageUpperBound int --定义此页的顶码

13.set @PageLowerBound=(@pageindex-1)*@pagesize

14.set @PageUpperBound=@PageLowerBound+@pagesize

15.set rowcount @PageUpperBound

16.insert into @indextable(nid) select gid from TGongwen

17.      where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

18.select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t

19.where O.gid=t.nid and t.id>@PageLowerBound

20.and t.id<=@PageUpperBound order by t.id

21.end

22. 

23.set nocount off

我们从此可以看到用exists和用in的执行效率是一样的。

4、IN 的作用相当与OR

Name=’张三’

12、高效的TOP

该句的执行结果为:

页码

方案1

方案2

方案3

1

60

30

76

10

46

16

63

100

1076

720

130

500

540

12943

83

1000

17110

470

250

10000

24796

4500

140

100000

38326

42283

1553

250000

28140

128720

2330

500000

121686

127846

7168

1.select top 10000 gid,fariqi,reader,title from tgongwen

表 ”sales”。扫描计数 18,逻辑读 56 次,物理读 0 次,预读 0 次。

从以上我们可以看出,不排序的速度以及逻辑读次数都是和“order
by 聚集索引列” 的速度是相当的,但这些都比“order by
非聚集索引列”的查询速度是快得多的。

1.select top 10 * from (

用时:196 毫秒。 扫描计数
1,逻辑读 289 次,物理读 1 次,预读 1527 次。

用时:3170毫秒(提取50万条)

Name=’张三’ and 价格>5000

1.(1)select title,price from titles where title_id in (select
title_id from sales where qty>30)

1.select count(gid) from Tgongwen

本文的试验数据都是来自我们的HP ML
350服务器。服务器配置:双Inter Xeon 超线程 CPU 2.4G,内存1G,操作系统Windows Server 2003 Enterprise Edition,数据库SQL Server 2000 SP3

union

用时:6423毫秒。扫描计数 2,逻辑读 14726 次,物理读 1 次,预读 7176 次。

select top 10000 gid,fariqi,title from tgongwen

显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

2、or 会引起全表扫描

用时:6390毫秒

前面,我们谈到,如果在LIKE前面加上通配符%,那么将会引起全表扫描,所以其执行效率是低下的。但有的资料介绍说,用函数charindex()来代替LIKE速度会有大的提升,经我试验,发现这种说明也是错误的: 

欧博国际平台,1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
fariqi=”2004-9-16”

事实上,在查询和提取超大容量的数据集时,影响数据库响应时间的最大因素不是数据查找,而是物理的I/0操作。如:

1.(1)select gid,fariqi,neibuyonghu,title from Tgongwen where
fariqi>”2004-5-5”

列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:

1.(2)select title,price from titles where exists (select * from
sales where sales.title_id=titles.title_id and qty>30)

用时:4673毫秒

11、order by按聚集索引列排序效率最高

同时,按照某个字段进行排序的时候,无论是正序还是倒序,速度是基本相当的。

用时:52050毫秒

用时:7秒,另外:扫描计数
4,逻辑读 7155 次,物理读 0 次,预读 0 次。

1.(2)select gid,fariqi,neibuyonghu,title from Tgongwen where
fariqi>”2004-5-5” and neibuyonghu=”办公室”

ABS(价格)<5000

1.select top 10000 gid,fariqi,reader,title from tgongwen order by gid
desc

1.select top 10 * from (

1.Select top 10 * from table1 where id>200

于是就有了如下分页方案:

1.select top 页大小 *

2.from table1

3.where id>

4.(select max (id) from

5.(select top ((页码-1)*页大小) id from table1 order by id) as T

6.)

7.order by id

9、字段提取要按照“需多少、提多少”的原则,避免“select *”

1.select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi
asc

Name=’张三’ and 价格>5000 符号SARG,而:Name=’张三’ or 价格>5000
则不符合SARG。使用or会引起全表扫描。

欧博国际平台 3欧博国际平台 4

我们来看:(gid是主键,fariqi是聚合索引列):

自动化实例写的存储过程

用时:1376毫秒

从数据表中取出n条到m条记录的方法

WHERE 价格*2>5000

2.select top 10000 gid,fariqi,title from tgongwen

用时:3140毫秒

1.Select gid,fariqi,neibuyonghu,title from tgongwen

order by gid desc) as a

查询速度:2516毫秒

但我们不推荐这样使用,因为有时SQL
SERVER不能保证这种转化与原始表达式是完全等价的。

在查询分析阶段,查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG),那么就称之为可优化的,并且可以利用索引快速获得所需数据。

1.(2)select title,price from titles where exists (select * from
sales where sales.title_id=titles.title_id and qty>30)

后来,网上有人改造了此存储过程,下面的存储过程就是结合我们的办公自动化实例写的分页存储过程:

用时:52050毫秒

表 ”titles”。扫描计数 1,逻辑读 2 次,物理读 0 次,预读 0 次。

某些资料上说:用*会统计所有列,显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看:

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
fariqi>”2004-1-1”

1.select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi
asc

用时:4736毫秒。 扫描计数 1,逻辑读 55350 次,物理读 10 次,预读 775
次。

列名 操作符 <常数 或
变量>或<常数 或 变量> 操作符列名

用时:6343毫秒(提取100万条)

1.select top 10000 gid,fariqi from tgongwen order by gid desc

用时:128470毫秒(即:128秒)

从以上可以看出,如果用count(*)和用count(主键)的速度是相当的,而count(*)却比其他任何除主键以外的字段汇总速度要快,而且字段越长,汇总的速度就越慢。我想,如果用count(*),
SQL
SERVER可能会自动查找最小字段来汇总的。当然,如果您直接写count(主键)将会来的更直接些。

为解决这个矛盾,笔者后来又添加了一个日期列,其默认值为getdate()。用户在写入记录时,这个列自动写入当时的时间,时间精确到毫秒。即使这样,为了避免可能性很小的重合,还要在此列上创建UNIQUE约束。将此日期列作为聚集索引列。

用时:1483毫秒

注:文章来源与网络,仅供读者参考!

where neibuyonghu=”办公室”

(1)仅在主键上建立聚集索引,并且不划分时间段:

用时:4720毫秒。 扫描计数
1,逻辑读 41956 次,物理读 0 次,预读 1287 次。

1、您最频繁使用的、用以缩小查询范围的字段上;

有些表达式,如:

1.select count(fariqi) from Tgongwen

很多资料上都显示说,exists要比in的执行效率要高,同时应尽可能的用not
exists来代替not
in。但事实上,我试验了一下,发现二者无论是前面带不带not,二者之间的执行效率都是一样的。因为涉及子查询,我们试验这次用SQL
SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics
I/O状态打开:

语句:

5、尽量少用NOT

如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

SQL SERVER也会认为是SARG,SQL SERVER会将此式转化为:

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注