07. 分页写法小结

分页的实现方式有这几种:
1. 在前台程序中,将所有的记录都读到本地,前台程序通过游标在数据集中上下移动,数据量大的话,性能很差,不推荐
2. 前台程序请求某一页数据时,到数据库做一次查询,返回符合条件的相应记录,这也是目前常用的方法
3. 对方式2的改进,当请求某一页时,同时将前后几页一并返回,用户翻页时就不需要反复请求数据库了。


对于2,3的实现,随着SQL Server版本的升级,常用的方法有三种:TOP,ROW_NUMBER,OFFSET/FETCH NEXT。
测试数据:

if OBJECT_ID('split_pages') is not null
drop table split_pages
GO
create table split_pages 
(
ID int primary key,
Name varchar(100) 
)
GO
declare @i int
set @i=1
while @i<=300
begin
insert into split_pages
select @i,'TEST' + CAST(@i as varchar(10))
set @i=@i+10
end
GO
select * from split_pages

一. TOP

SQL Server 2000时多用,另外,有人用过set rowcount来分页,原理类似TOP,不过需要反复设置set rowcount,不方便,而且set rowcount中的行数不能参与select语句的执行计划,只能起到控制行数的作用。
1. 哪种TOP写法更高效
(1) 利用order by正反排序

select * 
from (select top 10 * 
from (select top 20 * from split_pages order by ID) a 
order by ID desc) b 
order by ID

公式为:

declare @page_no int
declare @page_size int

select * 
from (select top @page_size * 
from (select top @page_size*@page_no * from split_pages order by ID) a 
order by ID desc) b 
order by ID

做完最里层select后,再对派生表查询时,index就没有效果了,而且越往后面要top更多的数据,这种写法会更慢。

(2) 利用NOT IN或者NOT EXISTS

select top 10 *
from split_pages
where ID NOT IN (select top 10 ID from split_pages order by ID)
order by ID

公式为:

declare @page_no int
declare @page_size int

select top @page_size *
from split_pages
where ID NOT IN (select top @page_size*(@page_no-1) ID from split_pages order by ID)
order by ID

通常在写SQL语句时,用IN/EXISTS不一样,如果逻辑不变的话, EXISTS的效率高。

不过,利用NOT IN分页,和用NOT EXISTS效果基本一样,因为都需要扫完全部数据。

(3) 利用ID大于MAX(ID)

select top 10 *
from split_pages
where ID > (select MAX(ID) from (select top 10 ID from split_pages order by ID) t)
order by ID

公式为:

declare @page_no int
declare @page_size int

select top @page_size *
from split_pages
where ID > (select MAX(ID) from (select top @page_size*(@page_no-1) ID from split_pages order by ID) t)
order by ID

在使用TOP分页时,这种用法效率最高。

2. SQL Server 2000与2005中TOP的区别
(1) SQL Server 2000时,top不支持变量,所以分页时,这些语句都要改成动态SQL
如下:

declare @page_no int
declare @page_size int
declare @sql varchar(8000)

set @page_no = 2
set @page_size = 10
set @sql = 
'select top ' + str(@page_size) + '*
from split_pages ' + '
where ID > (select MAX(ID) from (select top ' + str(@page_size*(@page_no-1))+ ' ID from split_pages order by ID) t)
order by ID'
exec(@sql)

(2) SQL Server 2005开始,top可以直接使用变量

如下:

declare @page_no int
declare @page_size int
set @page_no = 2
set @page_size = 10
select top (@page_size) *
from split_pages
where ID NOT IN (select top (@page_size*(@page_no-1)) ID from split_pages order by ID)
order by ID

二. ROW_NUMBER
SQL Server 2005开始的新语法,和ORACLE,DB2中的row_number()类似。性能比用TOP有所提升。

在利用ROW_NUMBER分页时,总页数/行数的计算,可以有这几种写法。
(1) 单独的SQL语句去获得总行数

select COUNT(*) AS TotRows
from split_pages
GO
declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;with tmp
AS
(
select *,
ROW_NUMBER() OVER(order by ID) num
from split_pages
)
select ID, Name
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

(2) 在ROW_NUMBER的同时用COUNT计算总行数

declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;WITH tmp
AS
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
COUNT(*) OVER() total
from split_pages
)
select ID, Name
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

(3) 仅使用ROW_NUMBER计算总行数,IO最少

declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;with tmp
as
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
ROW_NUMBER() OVER(order by ID desc) num_desc
from split_pages
)
select ID, Name, num_desc + num -1 as total
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

三. OFFSET/FETCH NEXT
SQL Server 2012的新语法,类似MYSQL,POSTGRESQL中的LIIMIT/OFFSET,据称性能比ROW_NUMBER又有了提升。

declare @page_no int
declare @page_size int

set @page_no = 3
set @page_size = 10

SELECT *,COUNT(*) OVER() AS Total
FROM split_pages
ORDER BY ID
OFFSET (@page -1) * @size ROWS
FETCH NEXT @size ROWS ONLY;

四. 其他方法
(1) 临时表
在SQL Server 2000时,利用临时表生成rownum来分页,不推荐。

select IDENTITY(int,1,1) as num,* into #t from split_pages
select * from #t where num between 11 and 20

(2) sp_cursoropen
利用游标,性能较差,原理类似在程序中把数据全部加载到本地,然后通过游标移动来分页。不作推荐。

原文地址:https://www.cnblogs.com/seusoftware/p/3272632.html