SQLServer索引结构及其使用（三）

实现小数据和大数据的通用分页显示存储过程。

要构建一个Web应用，分页浏览功能是必不可少的。这个问题在数据库处理中很常见。经典的数据分页方法是:ADO记录集分页方法，即利用ADO自带的分页功能(使用光标)实现分页。但是这种分页方式只适用于数据量较小的情况，因为游标本身就有一个缺点:游标存储在内存中，很消耗内存。一旦创建了游标，相关记录就会被锁定，直到游标被取消。光标提供了一种逐行扫描特定集合的方法。一般用游标逐行遍历数据，根据取数据的不同条件进行不同的操作。但是对于多表和大表中定义的游标(大数据集)循环，程序很容易进入长时间等待甚至崩溃。
更重要的是，对于非常大的数据模型，按照传统的分页检索的方法，每次都要加载整个数据源，这是一种资源浪费。现在流行的分页方式一般是检索一个页面大小的块的数据，而不是检索所有数据，然后一步执行当前行。
按照页面大小和页码提取数据，最早也是最好的方法，大概就是“俄语存储过程”。此存储过程使用游标。由于游标的局限性，这种方法还没有得到普遍认可。
后来网上有人修改了这个存储过程。以下存储过程是结合我们的办公自动化示例编写的分页存储过程:

创建过程分页1

(@ pagesizeint，-页面大小，比如每页存储20条记录。

@ page index int-当前页码

)

如同

设置nocount on

开始

declare @ Indextable Table(id int identity(1，1)，NID int)-定义表变量。

Declare @PageLowerBound int -定义这个页面的底层代码。

Declare @PageUpperBound int -定义这个页面的顶层代码。

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

set @ PageUpperBound = @ PageLowerBound+@ pagesize

设置rowcount @PageUpperBound

insert into @indextable(nid)从TGongwen中选择gid

其中，法里基> dateadd(day，-365，getdate())由法里基·desc排序

从TGongwen O，@indextable t中选择O.gid，O.mid，O.title，O.fadanwei，O.fariqi

其中O.gid=t.nid和t.id>@PageLowerBound

和t.id

目标

Set count off
以上存储过程使用了SQL SERVER的最新技术——表变量。应该说这个存储过程也是一个优秀的分页存储过程。当然，在这个过程中，你也可以把表变量写成临时表:CREATE TABLE #Temp。但显然，在SQL SERVER中，使用临时表没有使用表变量快。所以刚开始用这个存储过程的时候，感觉非常好，速度比原来的ADO要好。但是后来，我发现了一个比这个更好的方法。
笔者曾经在网上看到一篇短文《从数据表中取出第N条到第M条记录的方法》。全文如下:

从发布表中取出文章N到M的记录:

选择m-n+1 *

从发布

我不在

(选择n-1个id

来自发布))

是id发布表的关键词
看到这篇文章的时候，真的觉得神清气爽，觉得这是一个非常好的想法。后来，当我在做一个办公自动化系统(ASP。NET+C #+SQL Server)，突然想起了这篇文章。我认为如果修改这个语句，它可能是一个非常好的分页存储过程。所以我在网上搜了这篇文章。没想到，文章还没找到，我就发现了一个按照这个语句写的分页存储过程。这个存储过程也是目前比较流行的分页存储过程。很遗憾，我没有急于将这段文字转换成存储过程:

创建过程分页2

（

@ sqlnvarchar (4000)，-不带排序语句的SQL语句

@ pageint，-页码

@ recsperpageint，-每页的记录数

@ idvarchar (255)，-要排序的非重复ID号

@ sort varchar(255)-排序字段和规则

)

如同

声明@Str nVARCHAR(4000)

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

(' '+@SQL+'') T其中T.''+@ID+' ' '不在(SELECT ' '+CAST((@ RecsPerPage *(@ Page-1))

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

打印@Str

EXEC sp_ExecuteSql @Str

GO
实际上，上述语句可以简化为:

选择页面尺寸*

来自表1，其中(id不在(从按id排序的表中选择页面大小*页面ID))

Order ID
但是这个存储过程有一个致命的缺陷，就是它包含了NOT IN这个词。虽然我可以把它转换成:

选择页面尺寸*

从不存在的表1

(select * from(select top * from table 1 order by id)b其中b.id = a.id)

Order id
也就是用not exists代替not in，但是我们之前已经讲过了，两者的执行效率其实没有区别。即便如此，使用这种组合NOT IN的方法也比使用cursor要快。
虽然使用not exists无法保存最后一个存储过程的效率，但是在SQL SERVER中使用关键字是非常明智的选择。因为分页优化的最终目的是避免过多的记录集，而上面我们已经提到了优点，我们可以通过它来控制数据量。
在分页算法中，影响我们查询速度的关键因素有两个:和不在。它可以提高我们的查询速度，但NOT IN会减慢我们的查询速度，所以要提高我们整个分页算法的速度，就必须彻底改造NOT IN，用其他方法代替。
我们知道，几乎在任何一个字段中，我们都可以通过max (field)或min (field)提取某个字段的最小值或最大值，所以如果这个字段没有重复，那么我们就可以用这些不重复字段的max或min作为分水岭，使之成为分页算法中分离每一页的参考。这里，我们可以使用运算符“>”或“200
，因此我们有以下分页方案:

选择页面尺寸*

来自表1

其中id >

(从中选择最大值(id)

(从表1中选择top((页码-1)*页面大小)order by id)作为T

)

Order id
在选择不重复值且易于区分大小的列时，我们通常选择主键。下表列出了作者在有1000万数据的办公自动化系统中使用的表，这些表是用GID(GID是主键，但不是聚簇索引)索引的。)对列进行排序，提取gid、fariqi、title字段，以第1页、第10页、第100页、第500页、第1000页、第100000页、第100000页、第250000页和第500000页为例，测试上述三种分页方案的执行速度:(单位:毫秒)

页码1方案2方案3 1 60 30 76 10 46 16 63 100 1076 720 130 540 12943 83 1000 17110 470 250 10000 24796 4500 140 100000 38326 42283 1553 250000 28140 12 820 2330 500000 1211然而，在第一种方案中，在分页超过1000页之后，速度下降。第二种方案是分页超过10000页后速度开始变慢。但第三方案一直没有大的下跌趋势，后劲还是很足的。
确定了第三种分页方案后，我们就可以根据它编写一个存储过程了。众所周知，SQL SERVER的存储过程是预先编译好的SQL语句，其执行效率比通过网页传输的SQL语句要高。下面的存储过程不仅包含分页方案，还根据来自页面的参数确定是否对总数据进行计数。

-获取指定页面的数据:
创建过程分页3

@ tblname varchar (255)，-表名

@ strgetfields varchar (1000) ='' * '，-要返回的列

@ fldname varchar (255) = ' ' '，-排序的字段名称

@ pagesizeint = 10，-页面大小

@ pageindexint = 1，-页码

@ docount bit = 0，-返回记录总数，否则不为0则返回。

@ ordertypebit = 0，-设置排序类型，非零值按降序排列。

@ strhere varchar(1500)= ' ' '-查询条件(注意:不要加where)

如同

declare @ strsqlvarchar(5000)-主语句

declare @ strtmpvarchar(110)-临时变量

declare @ strordevarchar(400)-排序类型

if @doCount！= 0

开始

if @strWhere！=''''

set @ strSQL = " select count(*)as Total from["+@ TB lname+"]where "+@ str where

其他

set @ strSQL = " select count(*)as Total from["+@ TB lname+"]"

end
-上面的代码意味着如果@doCount传递非0的值，将执行总计数。以下所有代码都是@doCount为0的情况:

其他

开始

if @OrderType！= 0

开始

set @strTmp = "

set @ stroder = " order by["+@ fld name+"]desc "
-如果@OrderType不为0，则执行降序。这句话很重要！

目标

其他

开始

set @strTmp = " >(选择最大值"

set @ strOrder = " order by["+@ fld name+"]ASC "

目标

如果@PageIndex = 1

开始

if @strWhere！= ''''

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"

from [" + @tblName + "]其中"+ @strWhere + " " + @strOrder

其他

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"

from["+@ TB lname+"]"+@ stroder
-如果是第一页，执行上面的代码，会加快执行速度。

目标

其他

begin
-下面的代码为@strSQL提供了实际执行的SQL代码

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["

+ @tblName + "]其中["+@ fld name+"]"+@ strTmp+"(["+@ fld name+"])

from(select top "+str((@ PageIndex-1)* @ PageSize)+"["+@ fld name+"]

from["+@ TB lname+"]"+@ strOrder+")as TBL tmp)"+@ strOrder

if @strWhere！= ''''

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["

+ @tblName + "]其中[" + @fldName + "]" + @strTmp + "(["

+@ fldName+"])from(select top "+str((@ PageIndex-1)* @ PageSize)+"["

+@ fld name+"]from["+@ TB lname+"]where "+@ str where+" "

+ @strOrder +")作为tblTmp)和"+ @strWhere + " " + @strOrder

目标

目标

exec (@strSQL)

去

上面的存储过程是一般的存储过程，它的注释已经写在里面了。在数据量较大的情况下，尤其是查询最后几页时，查询时间一般不会超过9秒；但是使用其他存储过程在实践中会导致超时，所以这个存储过程非常适合查询大容量的数据库。希望通过以上存储过程的分析，能给大家一些启示，提高工作效率，也希望同行提出更好的实时数据分页算法。