海量数据的分页问题
之前做的东西数据量都不大,没有太考虑到效率,这次意识到不足想看看大数据如何分页会比较好。
网络上搜索了一下发现大家主要也是评论这两种方式比较好
第一种:
第二种:
这种方式效率会高,但是个人觉得这种方式必须要求用来排序的字段不能重复,否则会有问题。
附上整理过的一个采用第二种方式的一个通用的存储过程(代码来源网络)
自己事后对第二种方式进行了测试
测试环境:自己的pc,对50000,000万条数据的表进行分页,每页10条数据
结果:
第500000页 耗时 2m
第1000000页 耗时 8m
第5000000页 耗时 36m
网络上搜索了一下发现大家主要也是评论这两种方式比较好
第一种:
select top 10 * from table1 where id not in (select top ((页码-1)*页大小) id from table1 where 条件 order by id) and where 条件 order by id
这种方式由于有 not in 所以效率会降低第二种:
select top 10 * from table1 where id > (select max(id) from (select top ((页码-1)*页大小) id from table1 where 条件 order by id) as T) and where 条件 order by id
这种方式效率会高,但是个人觉得这种方式必须要求用来排序的字段不能重复,否则会有问题。
附上整理过的一个采用第二种方式的一个通用的存储过程(代码来源网络)
/****************************************************
** 获取分页数据的通用存储过程
**根据sql语句:
** select top 10 * from table1 where id > (select max(id) from (select top ((页码-1)*页大小) id from table1 where .. order by id) as T) and where . order by id
*****************************************************/
ALTER PROCEDURE dbo.GetDataPagination
(
@tblName varchar(255), --表名
@strGetFields varchar(1000) = '*', --要显示的列的名称
@fldName varchar(255), --用来排序的字段名称
@orderType bit, --设置排序类型,升序,其他降序
@pageSize int, --一页显示的数据条数
@pageIndex int, -- 页码
@doCount bit, --返回记录总数,非则返回
@strWhere varchar(1500) = '' --查询条件
)
AS
declare @strSQL varchar(5000) -- 存放主SQL语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0 --需要返回记录总数时
begin
if @strWhere != ''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
else --不需要返回记录总数时
begin
if @orderType != 0
begin
set @strTmp = '< (select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '> (select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
-- 查询的如果是第一页时
if @pageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top '+ str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top '+ str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] '+ @strOrder
end
else --不是第一页时
begin
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + ']'
+ ' where [' + @fldName +'] ' + @strTmp + '(['+ @fldName + ']) from (select top '
+ str((@pageIndex-1)*@pageSize) + ' [' + @fldName + '] from [' + @tblName + '] '
+ @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + ']'
+ ' where [' + @fldName +'] ' + @strTmp + '(['+ @fldName + ']) from (select top '
+ str((@pageIndex-1)*@pageSize) + ' [' + @fldName + '] from [' + @tblName + '] '
+ ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec(@strSQL)
RETURN
** 获取分页数据的通用存储过程
**根据sql语句:
** select top 10 * from table1 where id > (select max(id) from (select top ((页码-1)*页大小) id from table1 where .. order by id) as T) and where . order by id
*****************************************************/
ALTER PROCEDURE dbo.GetDataPagination
(
@tblName varchar(255), --表名
@strGetFields varchar(1000) = '*', --要显示的列的名称
@fldName varchar(255), --用来排序的字段名称
@orderType bit, --设置排序类型,升序,其他降序
@pageSize int, --一页显示的数据条数
@pageIndex int, -- 页码
@doCount bit, --返回记录总数,非则返回
@strWhere varchar(1500) = '' --查询条件
)
AS
declare @strSQL varchar(5000) -- 存放主SQL语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0 --需要返回记录总数时
begin
if @strWhere != ''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
else --不需要返回记录总数时
begin
if @orderType != 0
begin
set @strTmp = '< (select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '> (select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
-- 查询的如果是第一页时
if @pageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top '+ str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top '+ str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] '+ @strOrder
end
else --不是第一页时
begin
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + ']'
+ ' where [' + @fldName +'] ' + @strTmp + '(['+ @fldName + ']) from (select top '
+ str((@pageIndex-1)*@pageSize) + ' [' + @fldName + '] from [' + @tblName + '] '
+ @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @strGetFields + ' from [' + @tblName + ']'
+ ' where [' + @fldName +'] ' + @strTmp + '(['+ @fldName + ']) from (select top '
+ str((@pageIndex-1)*@pageSize) + ' [' + @fldName + '] from [' + @tblName + '] '
+ ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec(@strSQL)
RETURN
自己事后对第二种方式进行了测试
测试环境:自己的pc,对50000,000万条数据的表进行分页,每页10条数据
结果:
第500000页 耗时 2m
第1000000页 耗时 8m
第5000000页 耗时 36m