分页时的索引设计和分页处理算法

有一个BBS的topic表,topicid为主键,boardid,deletemark,istop都为int,但deletemark和istop多数为零,表中有20W数据,用如下的语句进行分页,其中boardid=12402符合条件的纪录为40000条,

select top 20 * from tb_club_topic a where boardID=12402 And deletemark=0
and topicid not in
(select top 100 topicid from tb_club_topic where boardID=12402
and deletemark=0
order by istop desc, LastDateTime desc
)
order by istop desc, LastDateTime desc

建立如下复合索引

CREATE
INDEX [index1] ON [dbo].[Tb_Club_Topic] ([BoardID], [DeleteMark], [IsTop] desc , [LastDateTime] desc )
WITH
FILLFACTOR = 80
ON [PRIMARY]

top的值为100,即5页的时候,执行时间在0-30ms
top值为1000,即50页的时候,执行时间为700ms
top值为10000,即500页时,招待时间为300ms

但我在调整索引时,似乎出现过top值为20000,1000页的时候只需要0-30ms的情况,但不记得当时的索引是怎样设计的了,请高手帮帮我看此索引还有没有优化的可能 。
---------------------------------------------------------------
---------------------------------------------------------------

--建议楼主这样处理
--写成一个存储过程,这样方便调用
create proc p_qry
@currentpage int,
@pagesize int=20 --默认每页20条记录
as
set nocount on

--查询处理
if @currentpage=1 --第一页就直接查询
begin
set rowcount @pagesize
select * from tb_club_topic
where boardID=12402 And deletemark=0
order by istop desc, LastDateTime desc
end
else
begin --如果查询的不是第一页
declare @i int

--处理查询的 topicid
set @i=@currentpage*@pagesize
set rowcount @i
select topicid into #t from tb_club_topic
where boardID=12402 And deletemark=0
order by istop desc, LastDateTime desc

set @i=@i-@pagesize
set rowcount @i
delete from #t

--返回查询结果
select * from tb_club_topic a
where exists(
select * from #t where topicid=a.topicid)
order by istop desc, LastDateTime desc
end
set rowcount 0
go

--调用实现分页查询
exec p_qry @currentpage=2

---------------------------------------------------------------
---------------------------------------------------------------
数据测试,表数据为94万,最多的一个board为47万纪录 结果如下

select top 方法:
页数 纪录数 执行时间(ms)
5页 100 0-30
50页 1000 400ms
500页 10000 等了很长时间没有结果
1000页 20000 1200 (可能因为我增加了纪录数的原因,和上午的结果已经不一样了,not in的效率与纪录数以及选出的结果数有很大关系)
>2000 >40000 >3000 (选取结果数很大时,基本上select top 方法都要较长的时间,但相差不多,维持在3000-7000之内)

临时表方法:
页数 纪录数 执行时间(ms)
5页 100 30 (在页数很小时,因为临时表要执行额外的操作,所以会比select top 慢一丁点)
1000页 20000 300
2000页 40000 500
3000页 60000 700
.... ... ...
10000页 100000 2400 (基本上执行时间随页数增长,每增长1000页20000条纪录增长200ms),不受符合条件总纪录数限制,即纪录数多的board和纪录数小的board执行时间相差不大。

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus