SQL Server 索引构造及其利用（三）[MSSQL防范]

SQL Server索引构造及其利用（一）
SQL Server索引构造及其利用（二）
SQL Server索引构造及其利用（三）
SQL Server索引构造及其利用（四）

实现小数据量和海量数据的通用分页显示存储历程

　　成立一个 Web 利用,分页浏览功效必不可少.这个问题是数据库处理中非常常见的问题.经典的数据分页办法是:ADO 记录集分页法,也就是操纵ADO自带的分页功效（操纵游标）来实现分页.但这种分页办法仅实用于较小数据量的情形,因为游标本身有缺陷:游标是存放在内存中,很费内存.游标一成立,就将相关的记录锁住,直到撤消游标.游标供应了对特定调集合逐行扫描的手段,普通利用游标来逐行遍历数据,按照取出数据条件的差别举行差别的操作.而关于多表和大表中定义的游标（大的数据调集）循环很简单使程序进入一个冗长的等候乃至死机.
　　更重要的是,关于非常大的数据模子而言,分页检索时,假如按照传统的每次都加载整个数据源的办法是非常浪费资源的.目前风行的分页办法普通是检索页面大小的块区的数据,而非检索全部的数据,然后单步履行当前行.
　　最早较好地实现这种按照页面大小和页码来提取数据的办法大约就是"俄罗斯存储历程".这个存储历程用了游标,由于游标的范围性,所以这个办法并没有得到大家的广泛承认.
　　后来,网上有人改革了此存储历程,下面的存储历程就是结合我们的办公自动化实例写的分页存储历程:
CREATE procedure pagination1
(@pagesize int, --页面大小,如每页存储20条记录
@pageindex int --当前页码
)
as

set nocount on

begin
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
set rowcount @PageUpperBound
insert into @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

set 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 int, --页码
@RecsPerPage int, --每页包容的记录数
@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,但我们前面已经谈过了,二者的履行效率实际上是没有辨别的.既便如此,用TOP 结合NOT IN的这个办法还是比用游标要来得快一些.
　　固然用not exists并不能解救上个存储历程的效率,但利用SQL SERVER中的TOP关键字倒是一个十清楚智的挑选.因为分页优化的终究目的就是避免产生过大的记录集,而我们在前面也已经提到了TOP的上风,通过TOP 便可实现对数据量的掌握.
　　在分页算法中,影响我们查询速度的关键因素有两点:TOP和NOT IN.TOP可以提高我们的查询速度,而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万页为例,测试以上三种分页筹划的履行速度:（单位:毫秒）

页码 筹划1 筹划2 筹划3
1 60 30 76
10 46 16 63
100 1076 720 130
500 540 12943 83
1000 17110 470 250
10000 24796 4500 140
100000 38326 42283 1553
250000 28140 128720 2330
500000 121686 127846 7168

　　从上表中,我们可以看出,三种存储历程在履行100页以下的分页号令时,都是可以信任的,速度都很好.但第一种筹划在履行分页1000页以上后,速度就降了下来.第二种筹划大约是在履行分页1万页以上后速度开始降了下来.而第三种筹划却始终没有大的降势,后劲仍旧很足.
　　在肯定了第三种分页筹划后,我们可以据此写一个存储历程.大家知道SQL SERVER的存储历程是事前编译好的SQL语句,它的履行效率要比通过WEB页面传来的SQL语句的履行效率要高.下面的存储历程不但含有分页筹划,还会按照页面传来的参数来肯定能否举行数据总数统计.

--获得指定页的数据:

CREATE PROCEDURE pagination3
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = ''*'', -- 需求返回的列
@fldName varchar(255)='''', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 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) -- 排序范例

if @doCount != 0
begin
if @strWhere !=''''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--以上代码的意思是假如@doCount传送过来的不是0,就履行总数统计.以下的全部代码都是@doCount为0的情形:

else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--假如@OrderType不是0,就履行降序,这句很重要!

end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end

if @PageIndex = 1
begin
if @strWhere != ''''

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "
　　　　　　　　from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else

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

end
else
begin
--以下代码赋予了@strSQL以真正履行的SQL代码
　

set @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

if @strWhere != ''''
set @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秒;而用其他存储历程,在实践中就会招致超时,所以这个存储历程非常实用于大容量数据库的查询. 笔者但愿可以通过对以上存储历程的解析,能给大家带来一定的启迪,并给工作带来一定的效率晋升,同时但愿同行提出更优异的及时数据分页算法.

（待续...） 　　以上是“SQL Server 索引构造及其利用（三）[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：