数据库端分页优化

--最近看到Paul White写的一篇数据库端分页优化的文章，感觉不错。所以把主要内容意译出来，作为分享。

概要

在SQL Server 2005及以后版本支持ROW_NUMBER函数。ROW_NUMBER函数可以很方便地用于数据库端分页。本文针对使用此函数进行分页的方法，进行优化和讨论。

需求场景

首先，我们假设一个需求场景，需要进行分页的数据集的宽度很大（每行数据的字段多，长度大）。可使用以下代码来模拟此表：

--创建测试表
create table Post
(
	post_id int identity not null,
	thread_id int not null,
	member_id int not null,
	create_dt datetime not null,
	title nvarchar(100) not null,
	body nvarchar(2500) not null,
	constraint PK_Post_post_id
		primary key clustered(post_id)
);
go

--填充10000条随机数据
With Numbers as
(
	select top(10000)
		row_number() over(order by (select 0)) as n
	from
		master.sys.columns C1,
		master.sys.columns C2,
		master.sys.columns C3
)
insert	Post
	(thread_id, member_id, create_dt, title, body)
select
	abs(checksum(newid()))%16 + 1 as thread_id,
	abs(checksum(newid()))%16384 + 1 as member_id,
	dateadd(minute, Numbers.n * 60, '20020901') as create_dt,
	replicate(nchar(rand(checksum(newid()))*26 + 65), rand(checksum(newid()))*70 + 30) as title,
	replicate(nchar(rand(checksum(newid()))*26 + 65), rand(checksum(newid()))*1750 + 750) as body
from
	Numbers;

我们后面的工作是从这张表里返回分页的数据集。客户端将提供页码和每页的行数。

初始解决方案

对于此问题的一个解决方案是使用通用表表达式（CTE），代码如下：

declare @PageNumber int;
declare @PageSize int;

set @PageNumber = 10;
set @PageSize = 50;	
	
With Paging as
(
	select
		ROW_NUMBER() over(order by P.post_id asc) as rn,
		post_id,
		thread_id,
		member_id,
		create_dt,
		title,
		body
	from
		Post P
)
select top(@PageSize)
	PG.rn,
	PG.post_id,
	PG.thread_id,
	PG.member_id,
	PG.create_dt,
	PG.title,
	PG.body
from
	Paging PG
where
	PG.rn > (@PageNumber * @PageSize) - @PageSize;

运行以上代码所生成的执行计划如下：

看上去这是一个简单且高效的执行计划，但随着@PageNumber增大，扫描一个宽表很快就会变得昂贵。我们分别取@PageNumber等于1，10，50，100，200来看一下此查询的logical reads。打开SET STATISTICS IO ON，并在每次查询前清除缓存：

DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');

运行所得结果如下表所示：

Page Number	1	10	50	100	200
logical reads	29	282	1390	2793	5590

聚集索引扫描成本的快速增长归因于单行数据较大。虽然聚集索引的键post_id很窄，只有4byte，但聚集索引扫描却需要逐一扫描每行所有数据的页。

替代解决方案

我们可以考虑只在post_id列上创建非聚集索引，这个新索引很窄，使执行计划快速找到需要的页，之后再查找剩余列的数据。使用如下代码来创建此索引：

create unique nonclustered index un_idx_Post_post_id
on Post(post_id asc)

我可以按如下3步来重写上面的查询：

1. 对非聚集索引进行部分扫描，添加row numbers

2. 过滤数据行得到我们所要的数据集

3. 对步骤2中的50条结果进行lookup，得到其他列的数据组成最后的结果

此解决方案的代码如下：

declare @PageNumber int;
declare @PageSize int;

set @PageNumber = 10;
set @PageSize = 50;

With Keys as
(
	--为最少的行加上行号
	select top(@PageNumber * @PageSize)
		row_number() over(order by P1.post_id asc) as rn,
		P1.post_id
	from
		Post P1
	order by
		P1.post_id asc
),
SelectedKeys as
(
	--获得需要数据集的主键
	select top(@PageSize)
		SK.rn,
		SK.post_id
	from
		Keys SK
	where
		SK.rn > ((@PageNumber - 1) * @PageSize)
	order by
		SK.post_id ASC
)
select --获得每行的其他列
	SK.rn,
	P2.post_id,
	P2.thread_id,
	P2.member_id,
	P2.create_dt,
	P2.title,
	P2.body	
from
	SelectedKeys SK
	join
	Post P2
	on
		P2.post_id = SK.post_id;

运行以上代码所生成的执行计划如下：

同样我们分别取@PageNumber等于1，10，50，100，200来查看此查询的logical reads。打开SET STATISTICS IO ON，并在每次查询前清除缓存：

DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');

运行所得结果如下表所示：

Page Number	1	10	50	100	200
logical reads	388	390	389	392	397

实验结论

比较2种方案的逻辑读数据，可以看出替代方案（键查找方案）可以获得更为平稳和可预期的性能。

有人还提出过把2种方案进行结合的方案，在获取前几页数据时使用聚集索引扫描，当需要获取之后的数据时切换为键查找。

（完）