大数据量的分页问题经常会困扰我们。
这里分享一个简单实用的方法:
假设某个表 T 拥有 200万记录。
一般分页的SQL会是这样:
select * from
( select * ,row_number() over ( order by C1 ) as rn
from T ) a
where rn >= start_row and rn <= end_row
一般这个会比较慢,因为T表很大,全表排序效率很低,
而且每查一次都会做一次,数据库压力太大。
其实仔细想一下,一般来讲用户不需要这么多页。
如果一页20条记录,则会有10万页的结果,根本看不完。
用户一般都会只关注前面几十页。
所以我们可以加上fetch first rows only 来限制一下结果集。
SQL改写如下:
select * from
( select * ,row_number() over ( order by C1 ) as rn
from T fetch fist 20000 rows only) a
where rn >= start_row and rn <= end_row
(20000 只是一个举例,可以按需求调整)
但是仅仅这样还不行,全表排序免不了。
所以我们想到了索引,
Create index idx1 on T (C1)
让DB2用索引排序代替全表排序。
这样就行了吗?答案是可能还不行。
因为你是select * ,所以DB2认为走索引还不如全表排序。
尤其你的索引 cluster 不是太好的时候。
其实我们的目的是尽快出前 20000 条,其他的我们不管。
所以这时我们可以用 optimize for N rows语句,告诉DB2
你只要尽快把 20000 条出给我就行了,我不关心全表排序要多长时间。
这时 DB2 就会选择索引扫描来排序了。。。
SQL改写如下:
select *
( select * ,row_number() over ( order by C1 ) as rn
from T fetch first 20000 rows only) a
where rn >= start_row and rn <= end_row
optimize for 20000 rows
在真实的案例中,这句optimize for N rows将
查询从40秒调高到了3秒。
收起