没有合适的资源?快使用搜索试试~ 我知道了~
海量数据库的查询优化及分页算法方案
5星 · 超过95%的资源 需积分: 15 1 下载量 7 浏览量
2013-01-10
17:17:42
上传
评论
收藏 85KB DOC 举报
温馨提示
试读
26页
海量数据库的查询优化及分页算法方案 总结了一下,一定要将聚集索引建立在: 1、您最频繁使用的、用以缩小查询范围的字段上; 2、您最频繁使用的、需要排序的字段上。
资源推荐
资源详情
资源评论
海量数据库的查询优化及分页算法方案
随着“金盾工程”建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应
用在各警种、各部门。与此同时,应用系统体系的核心、系统数据的存放地――数据库也
随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了 1000 万条,
可谓海量。那么,如何实现快速地从这些超大容量的数据库中提取数据(查询)、分析、
统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题。
在以下的文章中,我将以“办公自动化”系统为例,探讨如何在有着 1000 万条数据的 MS
SQL SERVER 数据库中实现快速的数据提取和数据分页。以下代码说明了我们实例中数据
库的“红头文件”一表的部分数据结构:
CREATE TABLE [dbo].[TGongwen] ( --TGongwen 是红头文件表名
[Gid] [int] 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 ,
--需要浏览的用户。每个用户中间用分隔符“,”分开
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
下面,我们来往数据库中添加 1000 万条数据:
declare @i int
set @i=1
while @i<=250000
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,办公
室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,
外事科','这是最先的 25 万条记录')
set @i=@i+1
end
GO
declare @i int
set @i=1
while @i<=250000
1
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','办公室','办公室,通信
科,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,外事
科','这是中间的 25 万条记录')
set @i=@i+1
end
GO
declare @h int
set @h=1
while @h<=100
begin
declare @i int
set @i=2002
while @i<=2003
begin
declare @j int
set @j=0
while @j<50
begin
declare @k int
set @k=0
while @k<50
begin
insert into 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 万条记
录')
set @k=@k+1
end
set @j=@j+1
end
set @i=@i+1
end
set @h=@h+1
end
GO
declare @i int
set @i=1
while @i<=9000000
begin
2
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,办公
室,王局长,刘局长,张局长,admin,刑侦支队,特勤支队,交巡警支队,经侦支队,户政科,治安支队,
外事科','这是最后添加的 900 万条记录')
set @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”。也就是说,字典的正文部
分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字
不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁
3
部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结
合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字 ,
我们可以看到在查部首之后的检字表中“张”的页码是 672 页,检字表中“张”的上面是“驰”字,
但页码却是 63 页,“张”的下面是“弩”字,页面是 390 页。很显然,这些字并不是真正的分
别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索
引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您
所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。
我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按
照一种方法进行排序。
(二)何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。
动作描述
使用聚集索引
使用非聚集索引
列经常被分组排序
应
应
返回某范围内的数据
应
不应
一个或极少不同值
不应
不应
小数目的不同值
应
不应
大数目的不同值
不应
应
频繁更新的列
4
不应
应
外键列
应
应
主键列
应
应
频繁修改索引列
不应
应
事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某
范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这
时您查询 2004 年 1 月 1 日至 2004 年 10 月 1 日之间的全部数据时,这个速度就将是很快的,
因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的
开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码
然后再根据页码查到具体内容。
(三)结合实际,谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中
以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇
到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然 SQL SERVER 默认是在
主键上建立聚集索引的。
通常,我们会在每个表中都建立一个 ID 列,以区分每条数据,并且这个 ID 列是自动增大
的,步长一般为 1。我们的这个办公自动化的实例中的列 Gid 就是如此。此时,如果我们
将这个列设为主键,SQL SERVER 会将此列默认为聚集索引。这样做有好处,就是可以让
您的数据在数据库中按照 ID 进行物理排序,但笔者认为这样做意义不大。
显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得
聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据
查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为 ID 号是自动生成的,
5
剩余25页未读,继续阅读
资源评论
- ajievip2017-11-02相当的不错!
acegi13579
- 粉丝: 115
- 资源: 5
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- JSP-JTBC-CMS(SQLITE).rar
- MC3362和MC145151调频无线接收器的设计.pdf
- MiniRenamer-v100.0一款简单易用的批量文件重命名工具(已注册PRO版本).rar
- 小狐狸Ai系统 小狐狸ai付费创作系统V2.8.0 ChatGPT智能机器人
- 公孙离-内衣-肚兜.zipgsl
- 快慢指针判断链表是否有环-go 语言实现
- 学生成绩管理系统的设计与实现-收藏备用.pdf
- JSP+SQL网站流量统计管理系统(源代码+论文).rar
- IBM-PC-XT微机过程...道中模拟量数据的采集和处理.pdf
- JSP+SQL网上选课系统(源代码+论文+答辩PPT).rar
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功