SQL优化：分页方法从top->row_number->offset的演化

 

1、top

这种方法主要是在sql server 2000中使用，效率较差，这里就不详细说了。

2、row_number函数

这种方法是sql server 2005以后，支持了row_number函数后，才开始使用的。

一开始是这么写的，但是效率不好：

 

declare @page_size int;
declare @page_num int;

--比如：每页10条记录
set @page_size = 10;

--比如：先取第2页
set @page_num = 2;


select *
from
(
select *,

       (row_number() over(order by 图书编号) - 1) / @page_size as rownum
from test 
)t
where rownum = @page_num - 1

修改一下，直接用范围来限制：

 

 

declare @page_size int;
declare @page_num int;

--比如：每页10条记录
set @page_size = 10;

--比如：先取第2页
set @page_num = 2;


select *
from
(
select *,
       row_number() over(order by 图书编号) as rownum
from test 
)t
where rownum between (@page_num-1)*@page_size+1 and @page_num*@page_size

 

3、SQL Server 2012开始支持的 offset fetch next 方法

 

declare @page_size int;
declare @page_num int;

--比如：每页10条记录
set @page_size = 10;

--比如：先取第2页
set @page_num = 2;

select *
from test
order by 图书编号
offset (@page_num-1)*@page_size rows
fetch next @page_size rows only

 

附录:

建表代码，表test包含主键，且为自增列：

 

CREATE TABLE [dbo].[test](
	[图书编号] [decimal](10, 0) IDENTITY(1,1) NOT NULL,
	[书名] [varchar](80) NOT NULL,
	[作者] [varchar](80) NOT NULL,
	[出版社] [int] NOT NULL,
	[出版日期] [datetime] NOT NULL,
	[图书类型] [int] NOT NULL,
	[ISBN号] [varchar](40) NOT NULL,
	[定价] [float] NOT NULL,
	[图书数量] [tinyint] NOT NULL
) ON [PRIMARY]


insert into test
(
[书名]
      ,[作者]
      ,[出版社]
      ,[出版日期]
      ,[图书类型]
      ,[ISBN号]
      ,[定价]
      ,[图书数量]
)
select [书名]
      ,[作者]
      ,[出版社]
      ,[出版日期]
      ,[图书类型]
      ,[ISBN号]
      ,[定价]
      ,[图书数量]
from test
go 15

alter table [test]
add constraint pk_test primary key([图书编号])

 

数据条数为432万条：

 

--4325376
select count(*) from test