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

最新推荐文章于 2025-04-15 21:58:35 发布

原创最新推荐文章于 2025-04-15 21:58:35 发布 · 3.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#优化 #date #sql server #sorting #object #数据库

SQL 专栏收录该内容

121 篇文章

订阅专栏

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

	描述信息	内容结果
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]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- 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 --当前页码

)

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]

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

SETANSI_NULLS ON

SETQUOTED_IDENTIFIER ON

ALTERPROCEDURE [dbo].[pagination2]

@pagesizeint,@pageindex int

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()