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

描述信息内容结果count(*)比count(字段)如果在开始时没有执行过count(最小字符的字段)来统计个数，count(*)不比count(字段)慢Ge:declare@ddatetimeset@d=getdate()SELECTcount(*)FROM[TestPaging02].[dbo].[TGongwen]select[用时（毫秒）]=datediff(ms,@d,getdate())用时：21193次数Count(*)Count(gid)174372026867433753686字段提取要按照“需多少、提多少”的原则declare @d datetimeset @d=getdate()select top 10000 gid from tgongwen order by giddescselect [用时（毫秒）]=datediff(ms,@d,getdate())3000select top 10000 gid,fariqi,title from tgongwen order by giddesc7436select top 10000 gid,fariqi,title,reader from tgongwen order by giddesc17180order by按聚集索引列排序效率最高select top 10000 gid,fariqi,reader,title from tgongwen（没有order by操作）533select top 10000 gid,fariqi,reader,title from tgongwenorder by gidasc（备注：createclusteredindext_gidonTGongwen(gid)聚集索引gid列）363select top 10000 gid,fariqi,reader,title from tgongwenorder by fariqiasc不是索引fariqi列60483符合扫描参数（SARG）的优化格式：列名操作符 常数或变量或常数或变量 操作符列名eg: 5000价格select * from TGongWen where neibuyonghu=办公室 and gid 100008473select * from TGongWen where gid 10000 and neibuyonghu=办公室8496select * from TGongWen where gid 10000 and neibuyonghu like %公室 (不符合的形式)14043select * 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 ONGOSET QUOTED_IDENTIFIER ONGO-- 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](@pagesizeint, --页面大小，如每页存储20条记录@pageindexint --当前页码)assetnocount onbegindeclare @indextabletable(id int identity(1,1),nidint) --定义表变量declare @PageLowerBoundint --定义此页的底码declare @PageUpperBoundint --定义此页的顶码set @PageLowerBound=(@pageindex-1)*@pagesizeset @PageUpperBound=@PageLowerBound+@pagesizesetrowcount @PageUpperBoundinsert into @indextable(ni