当前位置：网站首页>几种常见SQL分页方式效率比较

几种常见SQL分页方式效率比较

2022-08-06 00:33:00 【51CTO】

分页很重要，面试会遇到。不妨再回顾总结一下：

一：创建测试环境，(插入100万条数据大概耗时5分钟)。

       
       create database DBTest
       
use DBTest
      
1.
2.

二：--创建测试表

       
       create table pagetest
       
(
       
id int identity(1,1) not null,
       
col01 int null,
       
col02 nvarchar(50) null,
       
col03 datetime null
       
)
      
1.
2.
3.
4.
5.
6.
7.

三 --1万记录集

       
       declare @i int
       
set @i=0
       
while(@i<10000)
       
begin
       
    insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()
       
    set @[email protected]+1
       
end
      
1.
2.
3.
4.
5.
6.
7.

四：.几种典型的分页sql，下面例子是每页50条，198*50=9900，取第199页数据。

       
       --写法1，not in/top
       
select top 50 * from pagetest 
       
where id not in (select top 9900 id from pagetest order by id)
       
order by id
       
--写法2，not exists
       
select top 50 * from pagetest 
       
where not exists 
       
(select 1 from (select top 9900 id from pagetest order by id)a  where a.id=pagetest.id)
       
order by id
       
--写法3，max/top
       
select top 50 * from pagetest
       
where id>(select max(id) from (select top 9900 id from pagetest order by id)a)
       
order by id
       
--写法4，row_number()
       
select top 50 * from 
       
(select row_number()over(order by id)rownumber,* from pagetest)a
       
where rownumber>9900
       
select * from 
       
(select row_number()over(order by id)rownumber,* from pagetest)a
       
where rownumber>9900 and rownumber<9951
       
select * from 
       
(select row_number()over(order by id)rownumber,* from pagetest)a
       
where rownumber between 9901 and 9950
       
--写法5，在csdn上一帖子看到的，row_number() 变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号
       
select *
       
from (
       
    select row_number()over(order by tempColumn)rownumber,*
       
    from (select top 9950 tempColumn=0,* from pagetest where 1=1 order by id)a
       
)b
       
where rownumber>9900
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.

四：分别在1万，10万(取1990页)，100(取19900页）记录集下测试。

测试sql:

       
       declare @begin_date datetime
       
declare @end_date datetime
       
select @begin_date = getdate()
       
<.....YOUR CODE.....>
       
select @end_date = getdate()
       
select datediff(ms,@begin_date,@end_date) as '毫秒'
      
1.
2.
3.
4.
5.
6.
7.
8.

1万：基本感觉不到差异。

10万：

几种常见SQL分页方式效率比较_字段

100万：

几种常见SQL分页方式效率比较_分页_02

五：结论：

1.max/top,ROW_NUMBER()都是比较不错的分页方法。相比ROW_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同时适用于sql2000,access。

2.not exists感觉是要比not in效率高一点点。

3.ROW_NUMBER()的3种不同写法效率看起来差不多。

4.ROW_NUMBER() 的变体基于我这个测试效率实在不好。

PS.上面的分页排序都是基于自增字段id。测试环境还提供了int,nvarchar,datetime类型字段，也可以试试。不过对于非主键没索引的大数据量排序效率应该是很不理想的。

六：简单将ROWNUMBER,max/top的方式封装到存储过程。

ROWNUMBER():

       
       create proc [dbo].[spSqlPageByRownumber]
       
@tbName varchar(255),        --表名
       
@tbFields varchar(1000),      --返回字段
       
@PageSize int,                --页尺寸
       
@PageIndex int,                --页码
       
@strWhere varchar(1000),    --查询条件
       
@StrOrder varchar(255),  --排序条件
       
@Total int output            --返回总记录数
       
as
       
declare @strSql varchar(5000)    --主语句
       
declare @strSqlCount nvarchar(500)--查询记录总数主语句
       
--------------总记录数---------------
       
if @strWhere !=''
       
begin
       
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName + ' where '+ @strWhere
       
end
       
else
       
begin
       
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName
       
end
       
--------------分页------------
       
if @PageIndex <= 0
       
begin
       
  set @PageIndex = 1
       
end
       
set @strSql='Select * from (Select  row_number() over('[email protected]+') rowId,'+ @tbFields
       
+' from ' + @tbName + ' where 1=1 ' + @strWhere+' ) tb where tb.rowId >'+str((@PageIndex-1)*@PageSize)
       
+' and tb.rowId <= ' +str(@PageIndex*@PageSize)
       
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
       
exec(@strSql)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

Max/top:(简单写了下，需要满足主键字段名称就是"id")

       
       create proc [dbo].[spSqlPageByMaxTop]
       
@tbName varchar(255),        --表名
       
@tbFields varchar(1000),      --返回字段
       
@PageSize int,                --页尺寸
       
@PageIndex int,                --页码
       
@strWhere varchar(1000),    --查询条件
       
@StrOrder varchar(255),  --排序条件
       
@Total int output            --返回总记录数
       
as
       
declare @strSql varchar(5000)    --主语句
       
declare @strSqlCount nvarchar(500)--查询记录总数主语句
       
--------------总记录数---------------
       
if @strWhere !=''
       
begin
       
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName + ' where '+ @strWhere
       
end
       
else
       
begin
       
set @strSqlCount='Select @TotalCout=count(*) from  ' + @tbName
       
end
       
--------------分页------------
       
if @PageIndex <= 0
       
begin
       
  set @PageIndex = 1
       
end
       
set @strSql='select top '+str(@PageSize)+' * from ' + @tbName + '
       
where id>(select max(id) from (select top '+str((@PageIndex-1)*@PageSize)+' id from ' + @tbName + ''[email protected]+')a)
       
'[email protected]+''
       
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
       
exec(@strSql)
      
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

调用：

       
       declare @count int
       
--exec [dbo].[spSqlPageByRownumber]'pagetest','*',50,20,'','order by id asc',@count output 
       
exec [dbo].[spSqlPageByMaxTop]'pagetest','*',50,20,'','order by id asc',@count output 
       
select @count
      
1.
2.
3.
4.

原网站

版权声明
本文为[51CTO]所创，转载请带上原文链接，感谢
https://blog.51cto.com/u_10999550/5550539

当前位置：网站首页>几种常见SQL分页方式效率比较

几种常见SQL分页方式效率比较

边栏推荐

猜你喜欢

随机推荐