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

这篇博客探讨了如何在SQL Server中优化大规模数据的分页查询,通过表变量和MAX方法实现高效分页。内容包括两个存储过程示例,展示了如何利用ORDER BY和ROWCOUNT限制来避免全表扫描,从而提高查询速度。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

描述信息

内容结果

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]可以限制输出,避免整表扫描

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值