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 SQL语句查询分页数据的解决方案
本资源使用数据库SQL server数据库,使用DATAGRIDVIEW控件实现分页显示。通过按钮点击实现上一页,下一页,首页,尾页等功能。属于源码 DOME文件。可参考改编性强。
千万级数据分页查询存储过程SQLServer 有实例
java 连接sqlserver数据库查询,并分页显示
海量数据分页sql server经典.很好的数据库分页讲解
SQLserver 分页数据样例 只是一个分页数据 查询 比较适合小白
在sqlserver数据库中,本例采用TOP语句分页,100W条数据为例,效率还不错,有测试的屏幕录像程序,有相关代码。
自己封装超好用的分页高级查询基于ssm(sqlServer 数据库,底层为逆 向工程(配合我的sqlserver逆向工程使用),分页sql脚本为后添加,使用时需 要拷贝我做的分页sql,包括对应的【注意:引入这个标签<sql id=...
看过此博文后Oracle、MySql、SQLServer 数据分页查询,在根据公司的RegionRes表格做出了 SQLserver的分页查询语句: SELECT DISTINCT TOP 500 rr.ID AS id ,rr.strName AS name ,rr.nType AS res_type ,...
数据层数据分页技术 用于数据分页的SQL语句方法,文件为SQL文件,可在SQL Server环境下打开,资料为Microsoft Webcaster上的讲座听课后的整理结果
随着SQL Server的发布,其中的一些排序函数使得开发人员编写数据分页程序变得更加简单和高效。这些新的排序函数提供了统计数据集的数目,对数据集归类,按照 某种标准对数据集排序等功能。在本文中将着重介绍新增加...
这是我整理的,两种不同方式的分页方法,之前我做过测试的,如果数据量很大的话,然后对数据速度要求比较快的话,还是用sql存储过程分页比较好
SqlServer数据库中的分页语句,SqlServer分页SQL语句特点:一次查询,数据库只返回一页的数据。而不是取出所有的数据。
Sql Server 各种数据分页技术都包含在里面
2. 分页存储过程可以用于实现高效的数据分页。 3. ASP可以用于调用SQL Server存储过程实现数据的插入、更新和查询。 4. 使用参数化的存储过程可以提高数据库的安全性和性能。 5. 使用SQL Server的存储过程可以简化...
文件以一个实例的形式完成了jsp链接到sql数据库读取出内容并实现数据分页显示,有详细注释。
Java Web实例:Eclipse3.2+Tomcat5.5+Sql Server2000实现的分页数据查询
资源名称:SQL Server 2005 一千万条以上记录分页数据库优化经验总结 资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
SQL Server 获取数据的总记录数,有两种方式: 1.先分页获取数据,然后再查询一遍数据库获取到总数量 2.使用count(1) over()获取总记录数量 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) rn, ...
SQLSERVER高效率分页存储过程,数据量在2千万下查询分页只需要1-2秒,优化良好....