文档章节

sql server分页查询

c
 caiyezi
发布于 2016/11/08 20:24
字数 1512
阅读 2
收藏 1
点赞 0
评论 0

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中常用的分页方法中的 的一种语句,掌握了常用的数据分页方法,大家就可以自己写了,当然还有其它的方法,大家可以分享出来。

 

 

作者:Qlin 
出处:http://www.cnblogs.com/qqlin/ 
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利

本文转载自:http://www.cnblogs.com/vipzhou/p/4935498.html

共有 人打赏支持
c
粉丝 1
博文 108
码字总数 0
作品 0
西安
程序员
SQL Server多条件查询分页

用EasyUI做分页。。怎么在Mapper.xml 上写分页条件查询语句啊? 原来用MySql写的,现在要用SQL Server写。。不怎么了解SQL SERVER分页查询啊。 原先mysql做的如下 怎么改成SQL SERVER的分页查...

早起的虫儿被鸟吃
2017/03/23
328
1
浅谈SQL Server数据库分页

数据库分页是老生常谈的问题了。如果使用ORM框架,再使用LINQ的话,一个Skip和Take就可以搞定。但是有时由于限制,需要使用存储过程来实现。在SQLServer中使用存储过程实现分页的已经有很多方...

吞吞吐吐的
2017/11/15
0
0
Mysql 分页语句Limit用法

1、Mysql的limit用法 在我们使用查询语句的时候,经常要返回前几条或者中间某几行数据,这个时候怎么办呢?不用担心,mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [off...

李有常
2015/08/22
0
0
SQL 分页支持查询

SQL 分页支持查询 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo 分页支持查询 SELECT FROM (select ,ROWNUMBER() Over(order by [id]) as rowindex from [table] ) as [newtable] where ......

长征2号
2017/12/31
0
0
主流数据库分页查询介绍

1 背景概述 由于在项目中需要在页面上显示数量非常多的数据, 在进行数据库查询时首先会把所有的数据都查询出来,然后在进行显示,这时候分页查询的操作就必不可少了,本文介绍Mysql、Oracl...

数通畅联
2015/11/26
185
0
SQL Server 分页3种方案比拼

SQL Server 存储过程的分页,这个问题已经讨论过几年了,很多朋友在问我,所以在此发表一下我的观点 建立表: CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstNam...

华宰
2011/08/31
891
0
Hibernate中的query.setFirstResult(),query.setMaxResu

一、query.scroll()和query.setFirstResult(),query.setMaxResults();这两种方法都可以取到一定范围内的数据,用来数据分页显示。那么两者区别,以及两者的效率如何? 答:1.scroll是用JDBC2...

五大三粗
2015/11/06
0
0
ASP.NET代码优化浅析

ASP.NET代码优化一、页面和服务器控件处理 1、ASP.NET代码优化避免到服务器的不必要的往返行程 在某些情况下不必使用 ASP.NET 服务器控件和执行回发事件处理。例如,在 ASP.NET 网页中验证用...

老枪
2010/07/21
1K
0
实现小数据量和海量数据的通用分页显示存储过程

建立一个web 应用,分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法,也就是利用ADO自带的分页功能(利用游标)来实现分页。但这种分页...

五味格子
2011/05/11
0
0
报表性能优化方案之多数据集实现层式报表

问题描述 若报表使用的数据量是上百万条的话,觉得报表展现的速度慢,可以使用层式报表来提高报表展现速度。但由层式报表章节可以得知,层式报表必须是单数据集,若是多数据集的模板且数据量...

九月你好123
2015/10/09
41
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

轻松搭建svn版本管理工具+svnmanager管理客户端

前面的文章有写过svn版本管理工具的安装是基于svn的安装包进行安装,对于svn与apache的结合还得下svn和apache的模块进行结合过程比较繁琐,今天来介绍下通过centos的yum来安装svn能够快速安装...

javazyw
15分钟前
0
0
keepalived配置高可用集群

Linux集群概述 根据功能划分为两大类:高可用和负载均衡 高可用集群通常为两台服务器,一台工作,另外一台作为冗余,当提供服务的机器宕机,冗余将接替继续提供服务 实现高可用的开源软件有:...

TaoXu
20分钟前
0
0
mysql联表批处理操作

1 概述 mysql中的单表增删改查操作,可以说是基本中的基本. 实际工作中,常常会遇到一些基本用法难以处理的数据操作,譬如遇到主从表甚至多级关联表的情况(如一些历史问题数据的批量处理),考虑到...

社哥
23分钟前
0
0
IntelliJ IDEA 详细图解最常用的配置,适合刚刚用的新人。

刚刚使用IntelliJ IDEA 编辑器的时候,会有很多设置,会方便以后的开发,磨刀不误砍柴工。 比如:设置文件字体大小,代码自动完成提示,版本管理,本地代码历史,自动导入包,修改注释,修改...

kim_o
38分钟前
0
0
Google Java编程风格指南

目录 前言 源文件基础 源文件结构 格式 命名约定 编程实践 Javadoc 后记 前言 这份文档是Google Java编程风格规范的完整定义。当且仅当一个Java源文件符合此文档中的规则, 我们才认为它符合...

niithub
40分钟前
0
0
java.net.MalformedURLException异常说明

1.异常片段 Java代码中,在进行URL url = new URL(urllink)操作时,提示以下异常信息,该类异常主要问题出在参数urllink上面。 异常片段1 java.net.MalformedURLException at java.ne...

lqlm
40分钟前
1
0
CentOS7修改mysql5.6字符集

解决办法:CentOS7下修改MySQL数据库字符编码为UTF-8,UTF-8包含全世界所有国家所需要的字符集,是国际编码。 具体操作如下: 1.进入MySQL [root@tianqi-01 ~]# mysql -uroot -p Enter passw...

河图再现
42分钟前
0
0
DevExpress v18.1新版亮点——WPF篇(一)

用户界面套包DevExpress v18.1日前终于正式发布,本站将以连载的形式为大家介绍各版本新增内容。本文将介绍了DevExpress WPF v18.1 的新功能,快来下载试用新版本!点击下载>> Accordion Co...

Miss_Hello_World
45分钟前
0
0
Rancher 2.0集群与工作负载告警

Rancher 2.0操作指南。本文将step by step演示如何使用Rancher 2.0中集成的告警功能,包括设置通知程序、设置集群级别以及工作负载级别的告警。 在Rancher 1.x时期,告警功能是很多Rancher用...

RancherLabs
49分钟前
1
0
Python中字符串拼接的N中方法

python拼接字符串一般有以下几种方法: ①直接通过(+)操作符拼接 s = 'Hello'+' '+'World'+'!'print(s) 输出结果:Hello World! 使用这种方式进行字符串连接的操作效率低下,因为python中...

木头释然
51分钟前
9
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部