菜单

利用索引改善sql语句,大数据查询优化

2019年8月3日 - 数据网络

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

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

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

最早较好地实现这种根据页面大小和页码来提取数据的方法大概就是“俄罗斯存储过程”。这个存储过程用了游标,由于游标的局限性,所以这个方法并没有得到大家的普遍认可。

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

而聚集索引在每个表内又只能建立一个,这使得聚集索引显得更加的重要。聚集索引的挑选可以说是实现“查询优化”和“高效分页”的最关键因素。

3、非操作符、函数引起的不满足SARG形式的语句

在选择即不重复值,又容易分辨大小的列时,我们通常会选择主键。下表列出了笔者用有着1000万数据的办公自动化系统中的表,在以GID(GID是主键,但并不是聚集索引。)为排序列、提取gid,fariqi,title字段,分别以第1、10、100、500、1000、1万、10万、25万、50万页为例,测试以上三种分页方案的执行速度:(单位:毫秒)

用时:1483毫秒

用时:6390毫秒

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

用时:6453毫秒

order by gid asc

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

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

  1. 你不大可能一该不停地进行insert, SQL
    Server能把你传来的命令缓存起来,依次执行,不会漏掉任何一个insert。
  2. 你也可以建立一个相同结构但不做索引的表,insert数据先插入到这个表里,当这个表中行数达到一定行数再用insert table1 select * from
    table2这样的命令整批插入到有索引的那个表里。

这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是10000条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL
SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用,发现TOP确实很好用,效率也很高。但这个词在另外一个大型数据库ORACLE中却没有,这不能说不是一个遗憾,虽然在ORACLE中可以用其他方法(如:rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中,我们就将用到TOP这个关键词。

这里,用聚合索引比用一般的主键作order
by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

2、or 会引起全表扫描

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

用时:4673毫秒

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

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

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

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

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

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

(二)改善SQL语句

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

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

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

本篇文章的题目是:“查询优化及分页算法方案”。笔者只所以把“查询优化”和“分页算法”这两个联系不是很大的论题放在一起,就是因为二者都需要一个非常重要的东西――聚集索引。

1.select count(fariqi) from Tgongwen

笔者曾在网上看到了一篇小短文《从数据表中取出第n条到第m条的记录的方法》,全文如下:

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

有索引情况下,insert速度一定有影响,不过:

是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。

由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。

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

在上一节的标题中,笔者写的是:实现小数据量和海量数据的通用分页显示存储过程。这是因为在将本存储过程应用于“办公自动化”系统的实践中时,笔者发现这第三种存储过程在小数据量的情况下,有如下现象:

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

该句的执行结果为:

该句的执行结果为:

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

WHERE 价格*2>5000

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

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

并在select语句后加:

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

1.select count(gid) from Tgongwen

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

有些表达式,如:

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

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

5000<价格

更重要的是,对于非常大的数据模型而言,分页检索时,如果按照传统的每次都加载整个数据源的方法是非常浪费资源的。现在流行的分页方法一般是检索页面大小的块区的数据,而非检索所有的数据,然后单步执行当前行。

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

从上表中,我们可以看出,三种存储过程在执行100页以下的分页命令时,都是可以信任的,速度都很好。但第一种方案在执行分页1000页以上后,速度就降了下来。第二种方案大约是在执行分页1万页以上后速度开始降了下来。而第三种方案却始终没有大的降势,后劲仍然很足。

ABS(价格)<5000

2、您最频繁使用的、需要排序的字段上。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
gid>9990000

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

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

获取指定页的数据

order by gid desc) as a

而:name like ‘%张’ ,就不属于SARG。

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

3.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
gid>9990000

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

2、or 会引起全表扫描

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

1.select count(title) from Tgongwen

原因是通配符%在字符串的开通使得索引无法使用。

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

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

1、主键就是聚集索引

我们来做一个试验:

1.select * from table1 where name=”zhangsan” and tID >
10000和执行select * from table1 where tID > 10000 and
name=”zhangsan”

1.select top 10 * from (

四、其他书上没有的索引使用经验总结

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

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

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

01.CREATE PROCEDURE pagination2

02.(

03.@SQL nVARCHAR(4000), --不带排序语句的SQL语句

04.@Page int, --页码

05.@RecsPerPage int, --每页容纳的记录数

06.@ID VARCHAR(255), --需要排序的不重复的ID号

07.@Sort VARCHAR(255) --排序字段及规则

08.)

09.AS

10. 

11.DECLARE @Str nVARCHAR(4000)

12. 

13.SET @Str=''SELECT TOP ''+CAST(@RecsPerPage AS VARCHAR(20))+'' * FROM

14.(''+@SQL+'') T WHERE T.''+@ID+''NOT IN (SELECT TOP''+CAST((@RecsPerPage*(@Page-1))

15.AS VARCHAR(20))+'' ''+@ID+'' FROM (''+@SQL+'') T9 ORDER BY''+@Sort+'') ORDER BY ''+@Sort

16. 

17.PRINT @Str

18. 

19.EXEC sp_ExecuteSql @Str

20.GO

其实,以上语句可以简化为:

1.SELECT TOP 页大小 *

2.FROM Table1 WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id))

3.ORDER BY ID

但这个存储过程有一个致命的缺点,就是它含有NOT IN字样。虽然我可以把它改造为:

1.SELECT TOP 页大小 *

2.FROM Table1 WHERE not exists

3.(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )

4.order by id

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

2、只要建立索引就能显著提高查询速度

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

2.where fariqi> dateadd(day,-90,getdate())

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

用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

5、尽量少用NOT

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

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

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

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
fariqi=”2004-2-5”

是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。

用时:9秒。扫描计数
8,逻辑读 67489 次,物理读 216 次,预读 7499 次。

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

where neibuyonghu=”办公室”

这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是10000条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL
SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用,发现TOP确实很好用,效率也很高。但这个词在另外一个大型数据库ORACLE中却没有,这不能说不是一个遗憾,虽然在ORACLE中可以用其他方法(如:rownumber)来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中,我们就将用到TOP这个关键词。

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

使用时间:4470毫秒

4、IN 的作用相当与OR

经过这样的优化,笔者发现,无论是大数据量的情况下还是小数据量的情况下,分页速度一般都是几十毫秒,甚至0毫秒。而用日期段缩小范围的查询速度比原来也没有任何迟钝。聚集索引是如此的重要和珍贵,所以笔者总结了一下,一定要将聚集索引建立在:

如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是SQL
SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。

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

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

在前面的讨论中我们已经提到了,聚集索引有两个最大的优势:

Name=’张三’

不满足SARG形式的语句最典型的情况就是包括非操作符的语句,如:NOT、!=、<>、!<、!>、NOT
EXISTS、NOT IN、NOT
LIKE等,另外还有函数。下面就是几个不满足SARG形式的例子:

10、count(*)不比count(字段)慢

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

7、用函数charindex()和前面加通配符%的LIKE执行效率一样

3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度

1.select count(*) from Tgongwen

如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是SQL
SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。

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

我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,那么就可以利用这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以用操作符“>”或“<”号来完成这个使命,使查询语句符合SARG形式。如:

我们前面已经谈到了在where子句中使用or会引起全表扫描,一般的,我所见过的资料都是推荐这里用union来代替or。事实证明,这种说法对于大部分都是适用的。

3、非操作符、函数引起的不满足SARG形式的语句

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

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

用时:3140毫秒

1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where
gid<=250000

WHERE 价格>2500/2

事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

虽然查询优化器可以根据where子句自动的进行查询优化,但大家仍然有必要了解一下“查询优化器”的工作原理,如非这样,有时查询优化器就会不按照您的本意进行快速查询。

图片 1图片 2

有些表达式,如:

5.order by gid asc

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

在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

由此看来,我们每少提取一个字段,数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。

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

union

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

语句:

 

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

(2)在主键上建立聚集索引,在fariq上建立非聚集索引:

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

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

如:name like ‘张%’
,这就属于SARG

1.select count(*) from Tgongwen

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

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

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

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

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

原因是通配符%在字符串的开通使得索引无法使用。

而:name like ‘%张’
,就不属于SARG。

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

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

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

1.select count(title) from Tgongwen

1.从publish 表中取出第 n 条到第 m 条的记录:

2.SELECT TOP m-n+1 *

3.FROM publish

4.WHERE (id NOT IN

5.    (SELECT TOP n-1 id

6.     FROM publish))

7. 

8.id 为publish 表的关键字

用时:80毫秒

虽然在超大容量情况下,这个分页的实现过程是很快的,但在分前几页时,这个1-3秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢,借用户的话说就是“还没有ACCESS数据库速度快”,这个认识足以导致用户放弃使用您开发的系统。

一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name=”zhangsan”的,而后再根据限制条件条件tID>10000来提出查询结果。

图片 3图片 4

用时:1500毫秒

本篇文章汇集了笔者近段在使用数据库方面的心得,是在做“办公自动化”系统时实践经验的积累。希望这篇文章不仅能够给大家的工作带来一定的帮助,也希望能让大家能够体会到分析问题的方法;最重要的是,希望这篇文章能够抛砖引玉,掀起大家的学习和讨论的兴趣,以共同促进,共同为公安科技强警事业和金盾工程做出自己最大的努力。

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

自动化实例写的存储过程

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

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

select top 10000 gid,fariqi,title from tgongwen

查询速度:2513毫秒

SARG的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的AND连接。形式如下:

相关文章

发表评论

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

网站地图xml地图