达梦数据库利用存储过程快速分页

在实际应用中，我们经常会碰到对一个大的结果集进行分页的操作，常见的有：第一页，上一页，下一页，最后一页和跳转到N页等，
常规的SQL语句如select * from tb where condition order by col limit n offset m缺点比较明显，一是数据量增大后，性能明显下降，二是前几页速度快，越往后速度越慢。
这里，我写了一个存储过程来解决此类问题，原理是将limit n offset m 化为 between n and m，更有效的利用索引。

示例数据如下：

--建立示例表
create table tbpage(id int, name varchar(20), deptname varchar(50), room int, address varchar(1000));

--插入200万行示例数据
insert into tbpage 
select rownum,trunc(rand()*10000000,0),rownum%100, rownum%10, repeat(trunc(rand()*10000000,0),100) 
from sysobjects a,sysobjects b;

--建立索引
create cluster index CIX_TBPAGE on tbpage(id);
create index IX_TBPAGE_DEPTNAME on tbpage(deptname);

–翻页查询，查询第一页，速度很快

select * from tbpage where deptname = '1' order by id limit 1,10;
-------------------
--执行成功, 执行耗时2毫秒. 执行号:245

--查询第1000页
select * from tbpage where deptname = '1' order by id limit 10000,10;
--执行成功, 执行耗时11秒 63毫秒. 执行号:249

--查询第3000页
select * from tbpage where deptname = '1' order by id limit 30000,10;
--执行成功, 执行耗时22秒 657毫秒. 执行号:253

可以看到，随着页数的增大，越往后翻，速度越慢。针对这种情况，我写了下面的存储过程：

create or replace procedure sp_getRowbyPage
(
	PageId int, 	--页码
	PageRow int		--每页行数
)
as
declare
	--定义索引表存储主键值
	TYPE idList IS TABLE OF INT INDEX BY INT;
	x idList;
	--当前行号
	iRow int;
	--动态SQL
	strSQL varchar(4000) := '';
begin
	--将符合条件的行的主键按顺序装入索引表
	select id BULK COLLECT INTO x from tbpage where deptname = '1' order by id;
	
	--判断是否超出索引表上限
	iRow = (PageId-1)*PageRow;
	IF x.COUNT >= iRow+1 THEN
		FOR I IN 1 .. PageRow LOOP
			EXIT WHEN iRow+I > x.COUNT;	--最后一页，行数不足PageRow
			strSQL := strSQL || ',' || x(iRow+I);
		END LOOP;
	END IF;
	
	IF strSQL <> '' THEN
		strSQL := 'select * from tbpage where id in (' || STUFF(strSQL,1,1,'') || ');';
		EXECUTE IMMEDIATE strSQL;
	END IF;
	--PRINT strSQL;
end;

–创建了存储过程后，只需要输入页码、每页行数即可调用

--第1页
call sp_getRowbyPage(1,10);
--执行成功, 执行耗时23毫秒. 执行号:262

--第1000页
call sp_getRowbyPage(1000,10);
--执行成功, 执行耗时16毫秒. 执行号:260

--第2000页
call sp_getRowbyPage(2000,10);
--执行成功, 执行耗时16毫秒. 执行号:258

可以看到，页数的变化对于性能基本没有影响。