Sql Server 数据分页

转载 2013年12月03日 11:41:43

1.引言

在列表查询时由于数据量非常多,一次性查出来会非常慢,就算一次查出来了,也不能一次性显示给客户端,所以要把数据进行分批查询出来,每页显示一定量的数据,这就是数据要分页。

2.常用的数据分页方法

我们经常会碰到要取n到m条记录,就是有分页思想,下面罗列一下一般的方法。

我本地的一张表 tbl_FlightsDetail,有300多W记录,主键 FlightsDetailID(Guid),要求按照FlightsDetailID排序 取 3000001 到3000010 之间的10条记录,也是百万级。

方法1 定位法 (利用ID大于多少)

语句形式:

select top 10 * from tbl_FlightsDetail where FlightsDetailID>(
       select max(FlightsDetailID) from ( 
              select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
       ) as t
) order by FlightsDetailID

执行计划:

先查出 top 300000,再聚合取这个集合中最大的Id1,再过滤 id大于id1的集合(上图中使用到索引),再取top 10 条。

 

方法2 (利用Not In)


语句形式:

select top 10* from tbl_FlightsDetail where FlightsDetailID not in (
       select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
) order by FlightsDetailID

执行计划:

和方法一类似,只是过滤where条件不一样,这里用到的是not in,上图中没有用到索引,耗时8秒。如果 FlightsDetailID不是索引的话,方法1和该方法将差不多。

 

方法3 (利用颠颠倒倒top)

语句形式:

select top 10* from (
       select top 3000010* from tbl_FlightsDetail order by FlightsDetailID
) as t  order by t.FlightsDetailID desc

执行计划:

先取 前面3000010条记录,再倒序,这时再取前面10条即是300001 到300010条记录,没有用到索引,耗时11秒

 

方法4 (ROW_NUMBER()函数)

语句形式:

select * from (
       select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail
)  as t where t.rank between 3000001 and 3000010

 执行计划:

Sql 2005版本或以上支持,也没用到索引,耗时2秒,速度还不错。

方法5 (利用IN)

此方法是由 金色海洋(jyk)阳光男孩 回复的,飞常感谢,语句形式:

select top 10 * from tbl_FlightsDetail  where FlightsDetailID in( 
       select top 10 FlightsDetailID from(  
              select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
       ) as t order by t.FlightsDetailID desc 
) order by FlightsDetailID

执行计划:

多次执行之后一般维持在4秒左右,用到索引,非常不错,计划图还很长,只截取部分,可能是绕的多一点。

3.千万级分页存储过程

大家百度一下这个标题立马会出现很多相关信息,都大同小异,我自己拷贝的一个,应项目的需要,修改了一个排序的bug以及添加了返回总记录数,如下:

复制代码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--分页存储过程  
CREATE PROCEDURE [dbo].[sp_Paging] 
( 
@Tables nvarchar(1000),                --表名/视图名
@PrimaryKey nvarchar(100),             --主键
@Sort nvarchar(200) = NULL,            --排序字段(不带order by)
@pageindex int = 1,                    --当前页码
@PageSize int = 10,                    --每页记录数
@Fields nvarchar(1000) = N'*',         --输出字段
@Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)
@Group nvarchar(1000) = NULL,          --Group语句(不带Group By)
@TotalCount int OUTPUT                 --总记录数
) 
AS  
 
DECLARE @SortTable nvarchar(100) 
DECLARE @SortName nvarchar(100) 
DECLARE @strSortColumn nvarchar(200) 
DECLARE @operator char(2) 
DECLARE @type nvarchar(100) 
DECLARE @prec int 

--设定排序语句
IF @Sort IS NULL OR @Sort = ''     
   SET @Sort = @PrimaryKey      
IF CHARINDEX('DESC',@Sort)>0   
BEGIN         
    SET @strSortColumn = REPLACE(@Sort, 'DESC', '')         
    SET @operator = '<='     
END 
ELSE     
BEGIN                
    SET @strSortColumn = REPLACE(@Sort, 'ASC', '')                
    SET @operator = '>='     
END 
IF CHARINDEX('.', @strSortColumn) > 0     
BEGIN         
    SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))     
END 
ELSE     
BEGIN         
    SET @SortTable = @Tables         
    SET @SortName = @strSortColumn  
END 

--设置排序字段类型和精度 
SELECT @type=t.name, @prec=c.prec FROM sysobjects o 
       JOIN syscolumns c on o.id=c.id 
       JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName
        
IF CHARINDEX('char', @type) > 0    
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
   
DECLARE @strPageSize nvarchar(50) 
DECLARE @strStartRow nvarchar(50) 
DECLARE @strFilter nvarchar(1000) 
DECLARE @strSimpleFilter nvarchar(1000) 
DECLARE @strGroup nvarchar(1000)  
 
IF @pageindex <1     
   SET @pageindex = 1  
SET @strPageSize = CAST(@PageSize AS nvarchar(50)) 
--设置开始分页记录数 
SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  
--筛选以及分组语句
IF @Filter IS NOT NULL AND @Filter != ''     
BEGIN         
    SET @strFilter = ' WHERE ' + @Filter + ' ' 
    SET @strSimpleFilter = ' AND ' + @Filter + ' ' 
END 
ELSE     
BEGIN         
    SET @strSimpleFilter = ''         
    SET @strFilter = ''     
END 
IF @Group IS NOT NULL AND @Group != ''  
   SET @strGroup = ' GROUP BY ' 
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--执行查询语句    
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
复制代码

 现在我们来测试一下:

复制代码
DECLARE    @return_value int,
        @TotalCount int

EXEC    @return_value = [dbo].[sp_Paging]
        @Tables = N'tbl_FlightsDetail',
        @PrimaryKey = N'FlightsDetailID',
        @Sort = N'FlightsDetailID',
        @pageindex = 299999,
        @PageSize = 10,
        @Fields = '*',
        @Filter = NULL,
        @Group = NULL,
        @TotalCount = @TotalCount OUTPUT

SELECT    @TotalCount as N'@TotalCount'

SELECT    'Return Value' = @return_value
复制代码

执行计划:

看时间的确是快,执行计划显示4个查询

查询1,是利用系统表获取排序字段、类型和精度,这个很快,全是索引。

查询2,返回总记录数,第一次会慢点,后面就很快了。

查询3 和查询4(用到索引) 才是我们要分页取的数据,查询3 是排序,取一个最大的值赋给变量,查询4是大于这个变量的值 取数据,直接看sql语句,把上面的exec动态语句改成如下:

复制代码
DECLARE @SortColumn varchar(40)
--即 top 3000001,取出最大的 id覆盖@SortColumn 
SET ROWCOUNT  3000001
SELECT @SortColumn= FlightsDetailID  FROM tbl_FlightsDetail ORDER BY FlightsDetailID  
--即 top 10
SET ROWCOUNT 10
SELECT *  FROM  tbl_FlightsDetail  WHERE FlightsDetailID   >= @SortColumn  ORDER BY  FlightsDetailID   
复制代码

你会发现,原来它跟我们标题2 常用的数据分页方法 中的 方法1 定位 类似,原来奥秘在这。

4.小结

还有一些用游标、表变量的那个性能差不作考虑。分页存储过程看起来挺复杂的,语句多,其实都在判断,在左组装,右组装,最终组装成类似 标题2中常用的分页方法中的 的一种语句,掌握了常用的数据分页方法,大家就可以自己写了,当然还有其它的方法,大家可以分享出来。



本文出处:http://www.cnblogs.com/qqlin/archive/2012/11/01/2745161.html

SQL server 海量数据库的查询优化及分页算法(收藏)

http://www.w17x.com/AritcleDisplay.aspx?id=661 具体如下: 如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数据分页...
  • hliq5399
  • hliq5399
  • 2016年05月19日 09:51
  • 4535

SQLServer千万数量级分页公共存储过程

转自:http://blog.csdn.net/tem168/article/details/6427123 SQLServer千万数量级分页公共存储过程   通用的数据库分页存...
  • chelen_jak
  • chelen_jak
  • 2014年12月11日 21:24
  • 3193

大数据量SqlServer效率分页

转载请注明出处http://blog.csdn.net/xugangjava SET @rowsPerPage = 10      SET @pageNum = 3         ...
  • xugangjava
  • xugangjava
  • 2011年05月16日 10:19
  • 1604

SQLServer大量数据高效率分页

以下为从大数据量表检索分页数据的有效方法 测试时,先从LargeTable表选出1000条记录分页呈现 --TIME_SEGMENT为数据表字段 declare @pagesize int--每页大...
  • idays021
  • idays021
  • 2016年04月21日 19:34
  • 1100

sql server 2012 分页 新方法

USE [stww] GO /****** Object: StoredProcedure [dbo].[SplitPage] Script Date: 2013/11/29 16:20:1...
  • z69183787
  • z69183787
  • 2013年11月29日 16:20
  • 1518

最优化的ms sql server分页sql语句

 作者:yanekemail:yanek@126.com特点:一次查询,数据库只返回一页的数据。而不是取出所有的数据。说明:pagesize: 每页显示记录数cureentpage:当前页数selec...
  • 5iasp
  • 5iasp
  • 2006年04月14日 15:47
  • 2873

sql2000,千万级大数据量分页存储过程效率测试附代码

在项目中,我们经常遇到或用到分页,那么在大数据量(百万级以上)下,哪种分页算法效率最优呢?我们不妨用事实说话。   测试环境 硬件:CPU 酷睿双核T5750  内存:2G 软...
  • zhenyuanjie
  • zhenyuanjie
  • 2012年07月24日 10:11
  • 2234

SQL server2008 多表联合查询,分页显示结果

数据库两表联立查询所需数据,组合成新表,并结果以分页显示,例:     select * from (select  row_number() over(order by a.id desc) as...
  • Annn_kk
  • Annn_kk
  • 2017年04月17日 09:53
  • 1541

sql server 纯sql语句分页 存储过程 返回结果集及页总数等

经常要对数据库中的数据进行大量的查询,特别是业务后台,但是EF等提供的语句应用起来可能看起来复杂、臃肿,我们需要借助SQL语句、或者存储过程来取得结果 1、.net EF 定义参数 using (Y...
  • hejisan
  • hejisan
  • 2016年07月05日 23:49
  • 1715

给初学者分享四种 Sql Server分页查询使用示例

 当我们显示列表信息的时候,我们常常以分页形式显示,当然在ASP.NET中的ListView和GridView控件等都自带了分页功能,但是效率很低,无法适用大量数据,并且灵活性不高。因而我们一般...
  • taotoxht
  • taotoxht
  • 2015年07月27日 16:09
  • 1059
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Sql Server 数据分页
举报原因:
原因补充:

(最多只允许输入30个字)