查询优化：实现500万以上的数据查询分页在3秒以内

 

	描述信息	内容结果
count(*)比count(字段)	如果在开始时没有执行过count(最小字符的字段)来统计个数，count(*)不比count(字段)慢 Ge: declare @d datetime set @d=getdate() SELECT count(*)   FROM [TestPaging02].[dbo].[TGongwen] select [用时（毫秒）]=datediff(ms,@d,getdate()) -----用时：21193---	次数	Count(*)	Count(gid)
		1	743	720
		2	686	743
		3	753	686
字段提取要按照“需多少、提多少”的原则[H1]	declare @d datetime set @d=getdate() select top 10000 gid from tgongwen order by gid  desc select [用时（毫秒）]=datediff(ms,@d,getdate())	3000
	select top 10000 gid,fariqi,titlefrom tgongwen order by  gid desc	7436
	select top 10000 gid,fariqi,title,readerfrom tgongwen  order by gid desc	17180
order by按聚集索引列排序效率最高	select top 10000 gid,fariqi,reader,title  from tgongwen（没有order by操作）	533
	select top 10000 gid,fariqi,reader,title from tgongwen order by gidasc （备注：create clustered index t_gid on TGongwen(gid)聚集索引gid列）	363
	select top 10000 gid,fariqi,reader,title  from tgongwen order by  fariqi asc  不是索引fariqi列	60483
符合扫描参数（SARG）的优化格式：列名  操作符 <常数 或 变量> 或<常数  或 变量> 操作符列名eg: 5000<价格	select * from TGongWen where neibuyonghu='办公室' and gid > 10000	8473
	select * from TGongWen where gid > 10000 and  neibuyonghu='办公室'	8496
	select * from TGongWen where gid >  10000 and neibuyonghu  like '%公室' (不符合的形式)	14043
	select * from TGongWen where gid >  10000 and neibuyonghu  like '办公%'	9336

   

表变量分页：

USE [TestPaging02]

GO

/****** Object:  StoredProcedure [dbo].[pagination1]    Script Date: 02/05/2012 15:57:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- Batch submitted through debugger:SQLQuery12.sql|15|0|C:\Users\HP\AppData\Local\Temp\~vs44BA.sql

--=============================================

-- Author:          <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER procedure [dbo].[pagination1]

(@pagesize int,  --页面大小，如每页存储20条记录

@pageindex int   --当前页码

)

as

set nocount on

begin

declare @indextable [H2] 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[H3]  @PageUpperBound

insert into @indextable(nid) select gidfrom TGongwenorder by gid[H4] 

select O.gid from TGongwen O,@indextable twhere O.gid=t.nid

and t.id>@PageLowerBound andt.id<=@PageUpperBound order by t.id

end

set nocount off

-------------------------------------------------------计算时间

declare @d datetime

set @d=getdate()

exec pagination1 60,1/10/100/1000/10000/100000,

select [用时（毫秒）]=datediff(ms,@d,getdate())

 

Pagesize页面大小	当前页码	用时（毫秒）
60	100	63
	1000	170
	10000	2503
	100000	15666
10	100000	2446
15	100000	3176
20	100000	3446
25	100000	7913/4740

 

MAX的方法

USE[TestPaging02]

GO

/******Object:  StoredProcedure[dbo].[pagination2]    Script Date:02/06/2012 08:24:18 ******/

SETANSI_NULLS ON

GO

SETQUOTED_IDENTIFIER ON

GO

ALTERPROCEDURE [dbo].[pagination2]

@pagesizeint,@pageindex int

AS

declare@strSQL   varchar(5000)

declare@pSize int

declare@pIndex int

BEGIN

SETNOCOUNT ON

set@pSize=@pagesize

set@pIndex=@pageindex

set@strSQL='select top '+convert(varchar,@pSize)+' gid from TGongwen '+'where gid>(select max (gid) from (select top (('+convert(varchar,@pIndex)+'-1)*'+convert(varchar,@pSize)+') gid from TGongwen  order by gid) as T) order by gid'

--selecttop @pSize gid from TGongwen

--set@strSQL="select top"+str(@pagesize)+" gid from TGongwen

--whereid>(select max (id) from (select top(("+str(@pageindex)+"-1)*"+str(@pagesize)+") id fromTGongwen  order by id) as T)    

--order by id"

--selecttop @pagesize gid from TGongwen

--whereid>(select max (id) from (select top ((@pageindex-1)*@pagesize) id fromtable1 order by id) as T)    

--orderby id

END

exec(@strSQL)

 -------------------------------------------------------计算时间

declare@d datetime

set@d=getdate()

execpagination2 25,1000000

select [用时（毫秒）]=datediff(ms,@d,getdate())

Pagesize页面大小	当前页码	用时（毫秒）
60	100
	1000
	10000	276
	100000	2196/2133
10	100000	410
15	100000	566
20	100000	840
25	100000	883

 相关备注：

---

 [H1]其中字段的存储的内容的大小也严重影响查询速度

 [H2]生成一个临时表，存放主键gid

 [H3]可以限制输出，避免整表扫描

 [H4]之所以用order bygid [H4]，前提是：gid列聚集索引列

----------本文参考

 1、两种数据库，四种分页算法的效率比较

2、海量数据库的查询优化及分页算法方案(转载)

3、500万条数据的web翻页程序的sql语句如何写

4、精彩的近乎完美的分页存储过程

5、SQL Server 2000 Paging and Sorting Using ROWCOUNT and SQL_VARIANT

6、How do I page through a recordset?