海量数据库的查询优化及分页算法方案集合1/2第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
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
    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”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。
我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。
(二)何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要)。
动作描述
 使用聚集索引
 使用非聚集索引
列经常被分组排序
 应
 应
返回某范围内的数据
 应
 不应
一个或极少不同值
 不应
 不应
小数目的不同值
 应
 不应
大数目的不同值
 不应
 应
频繁更新的列
 不应
 应

当前1/2页 12下一页阅读全文

(0)

相关推荐

  • 海量数据库的查询优化及分页算法方案集合1/2第1/2页

    随着"金盾工程"建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应用在各警种.各部门.与此同时,应用系统体系的核心.系统数据的存放地――数据库也随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了1000万条,可谓海量.那么,如何实现快速地从这些超大容量的数据库中提取数据(查询).分析.统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题. 在以下的文章中,我将以"办公自动化"系统为例,探讨如何在有着1000万

  • 海量数据库的查询优化及分页算法方案集合2/2

    FROM publish  WHERE (id NOT IN  (SELECT TOP n-1 id   FROM publish))  id 为publish 表的关键字  我当时看到这篇文章的时候,真的是精神为之一振,觉得思路非常得好.等到后来,我在作办公自动化系统(ASP.NET+ C#+SQL SERVER)的时候,忽然想起了这篇文章,我想如果把这个语句改造一下,这就可能是一个非常好的分页存储过程.于是我就满网上找这篇文章,没想到,文章还没找到,却找到了一篇根据此语句写的一个分页存储过程

  • 海量数据库的查询优化及分页算法方案

    海量数据库的查询优化及分页算法方案  原出处不详 摘自:www.21php.com 随着"金盾工程"建设的逐步深入和公安信息化的高速发展,公安计算机应用系统被广泛应用在各警种.各部门.与此同时,应用系统体系的核心.系统数据的存放地――数据库也随着实际应用而急剧膨胀,一些大规模的系统,如人口系统的数据甚至超过了1000万条,可谓海量.那么,如何实现快速地从这些超大容量的数据库中提取数据(查询).分析.统计以及提取数据后进行数据分页已成为各地系统管理员和数据库管理员亟待解决的难题. 在以下

  • 海量数据库的查询优化及分页算法方案 2 之 改良SQL语句

    二.改善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’ 一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tI

  • 海量数据库查询语句

    以下代码说明了我们实例中数据库的"红头文件"一表的部分数据结构: 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 ,--发布日期  

  • Angularjs实现分页和分页算法的示例代码

    对于大多数web应用来说显示项目列表是一种很常见的任务.通常情况下,我们的数据会比较多,无法很好地显示在单个页面中.在这种情况下,我们需要把数据以页的方式来展示. 页面展示效果: 页面HTML代码: <table class="table table-striped" style="margin: 0px;"> <thead> <tr> <td>选择</td> <td>企业名称</td&g

  • C#基于数据库存储过程的AJAX分页实例

    本文实例讲述了C#基于数据库存储过程的AJAX分页实现方法.分享给大家供大家参考.具体如下: 首先我们在数据库(SQL Server)中声明定义存储过程 复制代码 代码如下: use sales    --指定数据库    if(exists(select * from sys.objects where name='proc_location_Paging')) --如果这个proc_location_paging存储过程存在则删除  drop proc proc_location_Pagin

  • php 3行代码的分页算法(求起始页和结束页)

    一个好的分页算法, 应该具有下面的优点: 当前页码应该尽量在正中间. 如果"首页"和"尾页"不可用(当前处于第一页或最后一页), 不要隐藏这两组文字, 以免链接按钮位置变动. 算法简单. 下面的算法具有前面1和3两个优点. 复制代码 代码如下: // $curr_index, 当前页码. // $link_count, 链接数量. // $page_count, 当前的数据的总页数. // $start, 显示时的起始页码. // $end, 显示时的终止页码. $

  • java-SSH2实现数据库和界面的分页

    分页应该是在我们开发web应用时经常要做的工作,能够比较简洁的实现数据库和视图层的分页十分重要. 在数据库层利用hibernate进行数据库的分页,将从数据库中查询出的数据封装为javabean:在视图层就可以方便的实现分页. 创建PageBean package com.fishing.common.bean; import java.util.List; @SuppressWarnings("unchecked") public class PageBean { private L

  • golang实现分页算法实例代码

    前言 本文主要给大家介绍了关于golang分页算法的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 示例代码如下: //分页方法,根据传递过来的页数,每页数,总数,返回分页的内容 7个页数 前 1,2,3,4,5 后 的格式返回,小于5页返回具体页数 func Paginator(page, prepage int, nums int64) map[string]interface{} { var firstpage int //前一页地址 var lastpage i

随机推荐