数据库结构与算法:海量数据库的查询优化及分页算法方案

海量数据库查询优化及分页算法方案
原出处不详
摘自:www.21php.com
随着“金盾工程”建设逐步深入和公安信息化高速发展公安计算机应用系统被广泛应用在各警种、各部门和此同时应用系统体系核心、系统数据存放地――数据库也随着实际应用而急剧膨胀些大规模系统如人口系统数据甚至超过了1000万条可谓海量那么如何实现快速地从这些超大容量数据库中提取数据(查询)、分析、统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决难题
在以下文章中我将以“办公自动化”系统为例探讨如何在有着1000万条数据MS SQL SERVER数据库中实现快速数据提取和数据分页以下代码介绍说明了我们例子中数据库“红头文件”表部分数据结构:
CREATE TABLE [dbo].[TGongwen] ( --TGongwen是红头文件表名
[Gid] [] IDENTITY (1, 1) NOT NULL ,
--本表id号也是主键
[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
--红头文件标题
[fariqi] [datetime] NULL ,
--发布日期
[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--发布用户
[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,
--需要浏览用户每个用户中间用分隔符“,”分开
) _disibledevent=>while @i<=250000
begin
insert o Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,办公室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队, 户政科,治安支队,外事科','这是最先25万条记录')
@i=@i+1
end
GO
declare @i
@i=1
while @i<=250000
begin
insert o Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','办公室','办公室,通信科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事科','这是中间25万条记录')
@i=@i+1
end
GO
declare @h
@h=1
while @h<=100
begin
declare @i
@i=2002
while @i<=2003
begin
declare @j
@j=0
while @j<50
begin
declare @k
@k=0
while @k<50
begin
insert o Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通信科','办公室,通信科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事科','这是最后50万条记录')
@k=@k+1
end
@j=@j+1
end
@i=@i+1
end
@h=@h+1
end
GO
declare @i
@i=1
while @i<=9000000
begin
insert o Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,办公室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队, 户政科,治安支队,外事科','这是最后添加900万条记录')
@i=@i+1000000
end
GO
通过以上语句我们创建了25万条由通信科于2004年2月5日发布记录25万条由办公室于2004年9月6日发布记录2002年和 2003年各100个2500条相同日期、区别分秒由通信科发布记录(共50万条)还有由通信科于2004年5月5日发布900万条记录合计 1000万条
、因情制宜建立“适当”索引
建立“适当”索引是实现查询优化首要前提
索引(index)是除表的外另重要、用户定义存储在物理介质上数据结构当根据索引码值搜索数据时索引提供了对数据快速访问事实上没有索引,数据库也能根据SELECT语句成功地检索到结果但随着表变得越来越大使用“适当”索引效果就越来越明显注意在这句话中我们用了“适当”这个词这是如果使用索引时不认真考虑其实现过程索引既可以提高也会破坏数据库工作性能
()深入浅出理解索引结构
实际上您可以把索引理解为种特殊目录微软SQL SERVER提供了两种索引:聚集索引(clustered index也称聚类索引、簇集索引)和非聚集索引(nonclustered index也称非聚类索引、非簇集索引)下面我们举例来介绍说明下聚集索引和非聚集索引区别:
其实我们汉语字典正文本身就是个聚集索引比如我们要查“安”字就会很自然地翻开字典前几页“安”拼音是“an”而按照拼音排序汉字字典是以英文字母“a”开头并以“z”结尾那么“安”字就自然地排在字典前部如果您翻完了所有以“a”开头部分仍然找不到这个字那么就介绍说明您字典中没有这个字；同样如果查“张”字那您也会将您字典翻到最后部分“张”拼音是“zhang”也就是说字典正文部分本身就是个目录您不需要再去查其他目录来找到您需要找内容
我们把这种正文内容本身就是种按照定规则排列目录称为“聚集索引”
如果您认识某个字您可以快速地从自动中查到这个字但您也可能会遇到您不认识字不知道它发音这时候您就不能按照刚才思路方法找到您要查字而需要去根据“偏旁部首”查到您要找字然后根据这个字后页码直接翻到某页来找到您要找字但您结合“部首目录”和“检字表”而查到字排序并不是真正正文排序思路方法比如您查“张”字我们可以看到在查部首的后检字表中“张”页码是672页检字表中“张”上面是“驰”字但页码却是63页“张”下面是“弩”字页面是390页很显然这些字并不是真正分别位于“张”字上下方现在您看到连续“驰、张、弩” 3字实际上就是他们在非聚集索引中排序是字典正文中字在非聚集索引中映射我们可以通过这种方式来找到您所需要字但它需要两个过程先找到目录中结果然后再翻到您所需要页码
我们把这种目录纯粹是目录正文纯粹是正文排序方式称为“非聚集索引”
通过以上例子我们可以理解到什么是“聚集索引”和“非聚集索引”
进步引申下我们可以很容易理解:每个表只能有个聚集索引目录只能按照种思路方法进行排序
( 2)何时使用聚集索引或非聚集索引
下面表整理总结了何时使用聚集索引或非聚集索引(很重要)
动作描述
使用聚集索引
使用非聚集索引
列经常被分组排序
应
应
返回某范围内数据
应
不应
个或极少区别值
不应
不应
小数目区别值
应
不应
大数目区别值
不应
应
频繁更新列
不应
应
外键列
应
应
主键列
应
应
频繁修改索引列
不应
应
事实上我们可以通过前面聚集索引和非聚集索引定义例子来理解上表如:返回某范围内数据项比如您某个表有个时间列恰好您把聚合索引建立在了该列这时您查询2004年1月1日至2004年10月1日的间全部数据时这个速度就将是很快您这本字典正文是按日期进行排序聚类索引只需要找到要检索所有数据中开头和结尾数据即可；而不像非聚集索引必须先查到目录中查到每项数据对应页码然后再根据页码查到具体内容
( 3)结合实际谈索引使用误区
理论目是应用虽然我们刚才列出了何时应使用聚集索引或非聚集索引但在实战中以上规则却很容易被忽视或不能根据实际情况进行综合分析下面我们将根据在实战中遇到实际问题来谈下索引使用误区以便于大家掌握索引建立思路方法
1、主键就是聚集索引
这种想法笔者认为是极端是对聚集索引种浪费虽然SQL SERVER默认是在主键上建立聚集索引
通常我们会在每个表中都建立个ID列以区分每条数据并且这个ID列是自动增大步长般为1我们这个办公自动化例子中列Gid就是如此此时如果我们将这个列设为主键SQL SERVER会将此列默认为聚集索引这样做有好处就是可以让您数据在数据库中按照ID进行物理排序但笔者认为这样做意义不大
显而易见聚集索引优势是很明显而每个表中只能有个聚集索引规则这使得聚集索引变得更加珍贵
从我们前面谈到聚集索引定义我们可以看出使用聚集索引最大好处就是能够根据查询要求迅速缩小查询范围避免全表扫描在实际应用中 ID号是自动生成我们并不知道每条记录ID号所以我们很难在实战中用ID号来进行查询这就使让ID号这个主键作为聚集索引成为种资源浪费其次让每个ID号都区别字段作为聚集索引也不符合“大数目区别值情况下不应建立聚合索引”规则；当然这种情况只是针对用户经常修改记录内容特别是索引项时候会负作用但对于查询速度并没有影响
在办公自动化系统中无论是系统首页显示需要用户签收文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段是“日期”还有用户本身“用户名”
通常办公自动化首页会显示每个用户尚未签收文件或会议虽然我们where语句可以仅仅限制当前用户尚未签收情况但如果您系统已建立了很长时间并且数据量很大那么每次每个用户打开首页时候都进行次全表扫描这样做意义是不大绝大多数用户1个月前文件都已经浏览过了这样做只能徒增数据库开销而已事实上我们完全可以让用户打开系统首页时数据库仅仅查询这个用户近3个月来未阅览文件通过“日期”这个字段来限制表扫描提高查询速度如果您办公自动化系统已经建立2年那么您首页显示速度理论上将是原来速度8倍甚至更快
在这里的所以提到“理论上” 3字是如果您聚集索引还是盲目地建在ID这个主键上时您查询速度是没有这么高即使您在“日期”这个字段上建立索引(非聚合索引)下面我们就来看下在1000万条数据量情况下各种查询速度表现(3个月内数据为25万条):
(1)仅在主键上建立聚集索引并且不划分时间段:
Select gid,fariqi,neibuyonghu,title from tgongwen
用时:128470毫秒(即:128秒)
(2)在主键上建立聚集索引在fariq上建立非聚集索引:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate)
用时:53763毫秒(54秒)
(3)将聚合索引建立在日期列(fariqi)上:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate)
用时:2423毫秒(2秒)
虽然每条语句提取出来都是25万条数据各种情况差异却是巨大特别是将聚集索引建立在日期列时差异事实上如果您数据库真有 1000万容量话把主键建立在ID列上就像以上第1、2种情况在网页上表现就是超时根本就无法显示这也是我摒弃ID列作为聚集索引个最重要原因
得出以上速度思路方法是:在各个select语句前加:declare @d datetime
@d=getdate
并在select语句后加:
select [语句执行花费时间(毫秒)]=datedf(ms,@d,getdate)
2、只要建立索引就能显著提高查询速度
事实上我们可以发现上面例子中第2、3条语句完全相同且建立索引字段也相同；区别仅是前者在fariqi字段上建立是非聚合索引后者在此字段上建立是聚合索引但查询速度却有着天壤的别所以并非是在任何字段上简单地建立索引就能提高查询速度
从建表语句中我们可以看到这个有着1000万数据表中fariqi字段有5003个区别记录在此字段上建立聚合索引是再合适不过了在现实中我们每天都会发几个文件这几个文件发文日期就相同这完全符合建立聚集索引要求:“既不能绝大多数都相同又不能只有极少数相同”规则由此看来我们建立“适当”聚合索引对于我们提高查询速度是非常重要
3、把所有需要提高查询速度字段都加进聚集索引以提高查询速度
上面已经谈到:在进行数据查询时都离不开字段是“日期”还有用户本身“用户名”既然这两个字段都是如此重要我们可以把他们合并起来建立个复合索引(compound index)
很多人认为只要把任何字段加进聚集索引就能提高查询速度也有人感到迷惑:如果把复合聚集索引字段分开查询那么查询速度会减慢吗？带着这个问题我们来看下以下查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引起始列用户名neibuyonghu排在后列)
(1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
查询速度:2513毫秒
(2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度:2516毫秒
(3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'
查询速度:60280毫秒
从以上试验中我们可以看到如果仅用聚集索引起始列作为查询条件和同时用到复合聚集索引全部列查询速度是几乎样甚至比用上全部复合索引列还要略快(在查询结果集数目样情况下)；而如果仅用复合聚集索引非起始列作为查询条件话这个索引是不起任何作用当然语句1、2查询速度样是查询条目数样如果复合索引所有列都用上而且查询结果少话这样就会形成“索引覆盖”因而性能可以达到最优同时请记住:无论您是否经常使用聚合索引其他列但其前导列定要是使用最频繁列
( 4)其他书上没有索引使用经验整理总结
1、用聚合索引比用不是聚合索引主键速度快
下面是例子语句:(都是提取25万条数据)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
使用时间:3326毫秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
使用时间:4470毫秒
这里用聚合索引比用不是聚合索引主键速度快了近1/4
2、用聚合索引比用般主键作order by时速度快特别是在小数据量情况下
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
用时:12936
select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
用时:18843
这里用聚合索引比用般主键作order by时速度快了3/10事实上如果数据量很小话用聚集索引作为排序列要比使用非聚集索引速度快得明显多；而数据量如果很大话如10万以上则 2者速度差别不明显
3、使用聚合索引内时间段搜索时间会按数据占整个数据表百分比成比例减少而无论聚合索引使用了多少个
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
用时:6343毫秒(提取100万条)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
用时:3170毫秒(提取50万条)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
用时:3326毫秒(和上句结果模样如果采集数量样那么用大于号和等于号是样)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
用时:3280毫秒
4 、日期列不会有分秒输入而减慢查询速度
下面例子中共有100万条数据2004年1月1日以后数据有50万条但只有两个区别日期日期精确到日；的前有数据50万条有5000个区别日期日期精确到秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi
用时:6390毫秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi
用时:6453毫秒
( 5)其他注意事项
“水可载舟亦可覆舟”索引也样索引有助于提高检索性能但过多或不当索引也会导致系统低效用户在表中每加进个索引数据库就要做更多工作过多索引甚至会导致索引碎片
所以说我们要建立个“适当”索引体系特别是对聚合索引创建更应精益求精以使您数据库能得到高性能发挥
当然在实战中作为个尽职数据库管理员您还要多测试些方案找出哪种方案效率最高、最为有效
2、改善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='张 3'
价格>5000
5000<价格
Name='张 3' and 价格>5000
如果个表达式不能满足SARG形式那它就无法限制搜索范围了也就是SQL SERVER必须对每行都判断它是否满足WHERE子句中所有条件所以个索引对于不满足SARG形式表达式来说是无用
介绍完SARG后我们来整理总结下使用SARG以及在实战中遇到和某些资料上结论区别经验:
1、Like语句是否属于SARG取决于所使用通配符类型
如:name like ‘张%' 这就属于SARG
而:name like ‘%张' ,就不属于SARG
原因是通配符%在串开通使得索引无法使用
2、or 会引起全表扫描
Name='张 3' and 价格>5000 符号SARG而:Name='张 3' or 价格>5000 则不符合SARG使用or会引起全表扫描
3、非操作符、引起不满足SARG形式语句
不满足SARG形式语句最典型情况就是包括非操作符语句如:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等另外还有下面就是几个不满足SARG形式例子:
ABS(价格)<5000
Name like ‘% 3'
有些表达式如:
WHERE 价格*2>5000
SQL SERVER也会认为是SARGSQL 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但事实上我试验了下发现 2者无论是前面带不带not 2者的间执行效率都是样涉及子查询我们试验这次用SQL SERVER自带pubs数据库运行前我们可以把SQL SERVERstatistics 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)
第 2句执行结果为:
表 '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这个关键词
到此为止我们上面讨论了如何实现从大容量数据库中快速地查询出您所需要数据思路方法当然我们介绍这些思路方法都是“软”思路方法在实战中我们还要考虑各种“硬”原因如:网络性能、服务器性能、操作系统性能甚至网卡、交换机等
3、实现小数据量和海量数据通用分页显示存储过程
建立个web 应用分页浏览功能必不可少这个问题是数据库处理中十分常见问题经典数据分页思路方法是:ADO 纪录集分页法也就是利用ADO自带分页功能(利用游标)来实现分页但这种分页思路方法仅适用于较小数据量情形游标本身有缺点:游标是存放在内存中很费内存游标建立就将相关记录锁住直到取消游标游标提供了对特定集合中逐行扫描手段般使用游标来逐行遍历数据根据取出数据条件区别进行区别操作而对于多表和大表中定义游标(大数据集合)循环很容易使进入个漫长等待甚至死机
更重要是对于非常大数据模型而言分页检索时如果按照传统每次都加载整个数据源思路方法是非常浪费资源现在流行分页思路方法般是检索页面大小块区数据而非检索所有数据然后单步执行当前行
最早较好地实现这种根据页面大小和页码来提取数据思路方法大概就是“俄罗斯存储过程”这个存储过程用了游标由于游标局限性所以这个思路方法并没有得到大家普遍认可
后来网上有人改造了此存储过程下面存储过程就是结合我们办公自动化例子写分页存储过程:
CREATE procedure pagination1
(@pagesize , --页面大小如每页存储20条记录
@pageindex --当前页码
)
as
nocount _disibledevent=> @PageUpperBound=@PageLowerBound+@pagesize
rowcount @PageUpperBound
insert o @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate) order by fariqi desc
select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
nocount off
以上存储过程运用了SQL SERVER最新技术――表变量应该说这个存储过程也是个非常优秀分页存储过程当然在这个过程中您也可以把其中表变量写成临时表: CREATE TABLE #Temp但很明显在SQL SERVER中用临时表是没有用表变量快所以笔者刚开始使用这个存储过程时感觉非常不错速度也比原来ADO好但后来我又发现了比此思路方法更好思路方法
笔者曾在网上看到了篇小短文从数据表中取出第n条到第m条记录思路方法全文如下:
从publish 表中取出第 n 条到第 m 条记录:
SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
　　　　(SELECT TOP n-1 id
　　　　 FROM publish))
id 为publish 表关键字
我当时看到这篇文章时候真是精神为的振觉得思路非常得好等到后来我在作办公自动化系统(ASP.NET+ C#＋SQL SERVER)时候忽然想起了这篇文章我想如果把这个语句改造下这就可能是个非常好分页存储过程于是我就满网上找这篇文章没想到文章还没找到却找到了篇根据此语句写个分页存储过程这个存储过程也是目前较为流行种分页存储过程我很后悔没有争先把这段文字改造成存储过程:
CREATE PROCEDURE pagination2
(
@SQL nVARCHAR(4000), --不带排序语句SQL语句
@Page , --页码
@RecsPerPage , --每页容纳记录数
@ID VARCHAR(255), --需要排序不重复ID号
@Sort VARCHAR(255) --排序字段及规则
)
AS
DECLARE @Str nVARCHAR(4000)
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str
GO
其实以上语句可以简化为:
SELECT TOP 页大小 *
FROM Table1
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
但这个存储过程有个致命缺点就是它含有NOT IN字样虽然我可以把它改造为:
SELECT TOP 页大小 *
FROM Table1
WHERE not exists
(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id )
order by id
即用not exists来代替not in但我们前面已经谈过了 2者执行效率实际上是没有区别
既便如此用TOP 结合NOT IN这个思路方法还是比用游标要来得快些
虽然用not exists并不能挽救上个存储过程效率但使用SQL SERVER中TOP关键字却是个非常明智选择分页优化最终目就是避免产生过大记录集而我们在前面也已经提到了TOP优势通过TOP 即可实现对数据量控制
在分页算法中影响我们查询速度关键原因有两点:TOP和NOT INTOP可以提高我们查询速度而NOT IN会减慢我们查询速度所以要提高我们整个分页算法速度就要彻底改造NOT IN同其他思路方法来替代它
我们知道几乎任何字段我们都可以通过max(字段)或min(字段)来提取某个字段中最大或最小值所以如果这个字段不重复那么就可以利用这些不重复字段max或min作为分水岭使其成为分页算法中分开每页参照物在这里我们可以用操作符“>”或“<”号来完成这个使命使查询语句符合SARG形式如:
Select top 10 * from table1 where id>200
于是就有了如下分页方案:
select top 页大小 *
from table1
where id>
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T
)
order by id
在选择即不重复值又容易分辨大小列时我们通常会选择主键下表列出了笔者用有着1000万数据办公自动化系统中表在以GID(GID是主键但并不是聚集索引)为排序列、提取gid,fariqi,title字段分别以第1、10、100、500、1000、1万、10万、25万、 50万页为例测试以上 3种分页方案执行速度:(单位:毫秒)
页 码
方案1
方案2
方案3
1
60
30
76
10
46
16
63
100
1076
720
130
500
540
12943
83
1000
17110
470
250
1万
24796
4500
140
10万
38326
42283
1553
25万
28140
128720
2330
50万
121686
127846
7168
从上表中我们可以看出 3种存储过程在执行100页以下分页命令时都是可以信任速度都很好但第种方案在执行分页1000页以上后速度就降了下来第 2种方案大约是在执行分页1万页以上后速度开始降了下来而第 3种方案却始终没有大降势后劲仍然很足
在确定了第 3种分页方案后我们可以据此写个存储过程大家知道SQL SERVER存储过程是事先编译好SQL语句它执行效率要比通过WEB页面传来SQL语句执行效率要高下面存储过程不仅含有分页方案还会根据页面传来参数来确定是否进行数据总数统计
-- 获取指定页数据
CREATE PROCEDURE pagination3
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回列
@fldName varchar(255)='', -- 排序字段名
@PageSize = 10, -- 页尺寸
@PageIndex = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
@doCount != 0
begin
@strWhere !=''
@strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

@strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码意思是如果@doCount传递过来不是0就执行总数统计以下所有代码都是@doCount为0情况

begin
@OrderType != 0
begin
@strTmp = "<(select min"
@strOrder = " order by [" + @fldName +"] desc"
--如果@OrderType不是0就执行降序这句很重要！
end

begin
@strTmp = ">(select max"
@strOrder = " order by [" + @fldName +"] asc"
end
@PageIndex = 1
begin
@strWhere != ''
@strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder

@strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--如果是第页就执行以上代码这样会加快执行速度
end

begin
--以下代码赋予了@strSQL以真正执行SQL代码
@strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
@strWhere != ''
@strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec (@strSQL)
GO
上面这个存储过程是个通用存储过程其注释已写在其中了
在大数据量情况下特别是在查询最后几页时候查询时间般不会超过9秒；而用其他存储过程在实战中就会导致超时所以这个存储过程非常适用于大容量数据库查询
笔者希望能够通过对以上存储过程解析能给大家带来定启示并给工作带来定效率提升同时希望同行提出更优秀实时数据分页算法
4、聚集索引重要性和如何选择聚集索引
在上节标题中笔者写是:实现小数据量和海量数据通用分页显示存储过程这是在将本存储过程应用于“办公自动化”系统实战中时笔者发现这第 3种存储过程在小数据量情况下有如下现象:
1、分页速度般维持在1秒和3秒的间
2、在查询最后页时速度般为5秒至8秒哪怕分页总数只有3页或30万页
虽然在超大容量情况下这个分页实现过程是很快但在分前几页时这个1－3秒速度比起第种甚至没有经过优化分页思路方法速度还要慢借用户话说就是“还没有ACCESS数据库速度快”这个认识足以导致用户放弃使用您开发系统
笔者就此分析了下原来产生这种现象症结是如此简单但又如此重要:排序字段不是聚集索引！
本篇文章题目是:“查询优化及分页算法方案”笔者只所以把“查询优化”和“分页算法”这两个联系不是很大论题放在起就是 2者都需要个非常重要东西――聚集索引
在前面讨论中我们已经提到了聚集索引有两个最大优势:
1、以最快速度缩小查询范围
2、以最快速度进行字段排序
第1条多用在查询优化时而第2条多用在进行分页时数据排序
而聚集索引在每个表内又只能建立个这使得聚集索引显得更加重要聚集索引挑选可以说是实现“查询优化”和“高效分页”最关键原因
但要既使聚集索引列既符合查询列需要又符合排序列需要这通常是个矛盾
笔者前面“索引”讨论中将fariqi即用户发文日期作为了聚集索引起始列日期精确度为“日”这种作法优点前面已经提到了在进行划时间段快速查询中比用ID主键列有很大优势
但在分页时由于这个聚集索引列存在着重复记录所以无法使用max或min来最为分页参照物进而无法实现更为高效排序而如果将ID主键列作为聚集索引那么聚集索引除了用以排序的外没有任何用处实际上是浪费了聚集索引这个宝贵资源
为解决这个矛盾笔者后来又添加了个日期列其默认值为getdate用户在写入记录时这个列自动写入当时时间时间精确到毫秒即使这样为了避免可能性很小重合还要在此列上创建UNIQUE约束将此日期列作为聚集索引列
有了这个时间型聚集索引列的后用户就既可以用这个列查找用户在插入数据时某个时间段查询又可以作为唯列来实现max或min成为分页算法参照物
经过这样优化笔者发现无论是大数据量情况下还是小数据量情况下分页速度般都是几十毫秒甚至0毫秒而用日期段缩小范围查询速度比原来也没有任何迟钝
聚集索引是如此重要和珍贵所以笔者整理总结了下定要将聚集索引建立在:
1、您最频繁使用、用以缩小查询范围字段上；
2、您最频繁使用、需要排序字段上
结束语:
本篇文章汇集了笔者近段在使用数据库方面心得体会是在做“办公自动化”系统时实战经验积累希望这篇文章不仅能够给大家工作带来定帮助也希望能让大家能够体会到分析问题思路方法；最重要是希望这篇文章能够抛砖引玉掀起大家学习和讨论兴趣以共同促进共同为公安科技强警事业和金盾工程做出自己最大努力
最后需要介绍说明是在试验中我发现用户在进行大数据量查询时候对数据库速度影响最大不是内存大小而是CPU在我P4 2.4机器上试验时候查看“资源管理器”CPU经常出现持续到100%现象而内存用量却并没有改变或者说没有大改变即使在我们HP ML 350 G3服务器上试验时CPU峰值也能达到90%般持续在70%左右
本文试验数据都是来自我们HP ML 350服务器服务器配置:双Inter Xeon 超线程 CPU 2.4G内存1G操作系统Windows Server 2003 Enterprise Edition数据库SQL Server 2000 SP3

延伸阅读

• 2011-9-19-- 数据库优化,小温谈数据库优化--数据设计篇
• 2010-12-15-- 系统维护优化,数据库系统的维护与优化（二）
• 2011-7-30-- 数据库优化,SQL SERVER 2005 数据库优化顾问(
• 2010-12-14-- 数据库性能优化,数据库瓶颈之关系型数据库性能优化总结
• 2011-6-27-- 数据库优化,小温谈数据库优化--数据设计篇
• 2010-12-9-- 数据库算法,海量数据库的查询优化及分页算法方案集合2/2
• 2010-12-12-- 数据库性能优化,关系型数据库性能优化总结
• 2010-12-15-- 数据库优化,数据库操作优化一例
• 2010-12-9-- 数据库算法,海量数据库的查询优化及分页算法方案集合1/2
• 2009-8-16-- 数据库算法:C#附加数据库的思路跟算法