请高手位帮我设计分页时的索引

Question

另外我想问一下，为什么top为1000的时候比10000的时候还要慢

Answer 1

不會是那樣吧﹖是不是你的語句在運行select top 1000 * from ....時﹐服務器比較忙嗎﹖所以用的時間多﹖要不然我覺得不會出現這樣的情況

Answer 2

参照这个分页，它会比你的效率高。
编制分页存储过程:CREATE procedure fy
@rows int ,
@pages int
as
declare @stra varchar(300)            --如果数据类型的长度不够,那么存储过程将不会执行.(造成空格的丢失,)
declare @strb varchar(300)
declare @strc varchar(300)
declare @strd varchar(300)
set @stra=' select top  '+cast(@rows as varchar(10))+' * from  people  where [id] >  '
set @strb=' (select max([id]) from  (select  top  '+cast((@rows*(@pages-1)) as varchar(10))+'  [id]  from people order by [id] asc) a) ' 
set @strc='  order by  [id]  asc  '
set @strd=' select  top  '+cast(@rows as varchar(10))+'  *  from  people '
if @pages=1
begin 
exec(@strd)
end
else
begin
exec(@stra+@strb+@strc)
end
GO你的程序中用的是not in ，带有非词not的查询效率本来就很低，而且in 的性能也是很低的，改用“>”就会好很多。

Answer 3

楼上的是不对的,楼主的用了 order by ,并不能保证id的顺序,所以根本无法用>的处理方式.

Answer 4

--建议楼主这样处理--查询参数定义
declare @pagesize int,@currentpage int
select @pagesize=20 --每页的大小
,@currentpage=10 --查询第几页--查询处理
if @currentpage=1 --第一页就直接查询
begin
set rowcount @pagesize
select * from tb_club_topic 
where boardID=12402 And delete=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 delete=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

Answer 5

--可以把它写成一个存储过程,这样方便调用
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 delete=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 delete=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

Answer 6

多谢 邹大哥 的存储过程，这是一个新的思路，我以前也用过临时表，不过，是用的生成自增列的方式插入的，这样在处理大量数据时不实用，因为每次查询都要插入上万条纪录，你现在用的这个临时表的插入方式要先进一些，在前面页的时候会很高效，我会试试这个方法。另外我想问一下后面从临时表中取数据时用exists方法和 select * from tb_club_topic a,#t b where a.topicid=b.topicid 的方式有什么区别吗？还有，我想问索引是否设计正确了

Answer 7

回 hdhai9451(※★開拓者...准備去長安☆※)服务器没有忙，我做多过次测试，结果都是那样的。我想可能是因为not in的排除算法导至的吧  
 

Answer 8

exists的方法和join的方法差不多
select * from tb_club_topic a,#t b where a.topicid=b.topicid -->应该是这样,否则多一个 topicid 字段
select a.* from tb_club_topic a,#t b where a.topicid=b.topicid 2.索引没有什么问题,如果 topicid 不是主键的话,在该字段上建立主键

Answer 9

用in與not in算法是速度慢的一個重要原因﹐如果有其他條件能不用的﹐最好不用

Answer 10

回zjcxc(邹建) ，你的这个方法非常好用，我做了测试，表数据为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执行时间相差不大。再次感谢邹大哥