offset 分页查询 优化

offset优化

 

offset使用的时候跳过的行数越多结果集返回的就越慢。这是由于数据库的机制决定的，需要读取每一条可能满足要求的数据确认数据有效性（是否对当前xid可见）。offset的行数越多就导致需要判断更多的数据行能找到满足条件的数据。

offset是将执行结果的一部分过滤掉，不发送给客户端，实际上这些过滤掉的结果是实际发生了计算的。

 

额外计算

select f(),* from tbl_a offset 3 limit 2;  -- f()会调用5次

改成两层调用 select f(),* from (select * from tbl_a offset 3 limit 2) as t; --f()调用2次

 

同理排序条件里面的函数计算也是会执行比预想更多的次数。（这种场景做计算后结果的物化或是使用表达式索引）

 

额外扫描

优化方案1 ：使用游标

begin;

declare cur1 cursor for select res_id from tbl_res_withauth_by_user_6 where res_type_id ='1001' AND res_tree_level3_id = '545643' ORDER BY res_name_gbk ASC  ,res_id;

fetch 100 from cur1;

rollback;

commit;

使用游标如果返回的结果数非常多会导致长事物。

 

 

优化方案2 ：使用位点

每次使用 排序字段 > 上次查询最后一条记录的排序字段值 来起到offset的功能。

如果排序字段不是唯一键的话，需要附带上唯一键作为检查条件。类似于

排序字段 > 上次查询最后一条记录的排序字段值 and 唯一键字段 NOT IN （之前的最大排序值的唯一键列表）

如果排序字段没有包含PK或是UK，性能要更好的话最好是自建位点，强制增加一个UK满足排序条件和唯一性条件。

 

优化方案 3 ：使用index only scan完成offset

原理是利用index only scan检索替代index scan减少回表查询确认可见性的时间。

 

（这个方案还有点疑问没搞清楚，不建议使用。）发现offset数量上去后什么都不改执行计划由index only scan变成了Bitmap Index Scan，导致额外的排序。

 

推测是因为走index only scan检索完成offset本质上还是需要检索offset部分的数据的，当这部分数据比较大的时候因为物理存储的不连续性，优化器做了位图扫描优化（将随机的堆表访问优化成顺序的堆表访问），但实际上这一步我们不需要访问堆表感觉优化器有点过度优化了，导致后面的额外排序。

 

 

tbl_res_withauth_by_user_6上有索引

"idx_tbl_res_withauth_by_user_6_level3" btree (res_name_gbk, res_id, res_tree_level3_id, res_type_id)

 

修改前：

select res_id,phy_res_code,res_type_id,res_subtype_id,parent_res_id,res_level,res_name,res_desc,res_belongin,is_foreign,res_attribute,res_order_number

from tbl_res_withauth_by_user_6 where res_type_id ='1001' AND res_tree_level3_id = '545643' ORDER BY res_name_gbk ASC  ,res_id offset 130000 limit 200;

-- 325ms

 

修改后：

select res_id,phy_res_code,res_type_id,res_subtype_id,parent_res_id,res_level,res_name,res_desc,res_belongin,is_foreign,res_attribute,res_order_number

from tbl_res_withauth_by_user_6

where res_id = any(

         select res_id::integer from tbl_res_withauth_by_user_6

         where res_type_id ='1001' AND res_tree_level3_id = '545643' ORDER BY res_name_gbk ASC  ,res_id offset 130000 limit 200

)

ORDER BY res_name_gbk ASC  ,res_id; 

-- 260ms  -- offset数量小的时候性能也还OK，大的时候又是位图扫描了

 

 

-- 疑问 什么情况下有btree索引的排序不走btree索引的Index Only Scan要走位图扫描？

select res_id from tbl_res_withauth_by_user_6 where res_type_id ='1001' AND res_tree_level3_id = '545643'

ORDER BY res_name_gbk ASC  ,res_id offset 2000 limit 200;

 

select res_id from tbl_res_withauth_by_user_6 where res_type_id ='1001' AND res_tree_level3_id = '545643'

ORDER BY res_name_gbk ASC  ,res_id offset 130000 limit 200;

 

 

 

大数据量的时候尽量避免查询总数，采用只显示下一页的方式处理。

如果对总数要求不是那么精确的话可以采用执行计划中的统计信息作为总数的估值。

imos=# explain select count(*) from tbl_event_record where user_code_list @> '{loadmin}';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1984691.07..1984691.08 rows=1 width=8)
   ->  Append  (cost=0.00..1875977.93 rows=43485254 width=0)
         ->  Seq Scan on tbl_event_record  (cost=0.00..0.00 rows=1 width=0)
               Filter: (user_code_list @> '{loadmin}'::imos_code[])
         ->  Seq Scan on tbl_event_record_2019_01_21_01  (cost=0.00..1075256.70 rows=28191976 width=0)
               Filter: (user_code_list @> '{loadmin}'::imos_code[])
         ->  Seq Scan on tbl_event_record_2019_01_21_02  (cost=0.00..583294.96 rows=15293277 width=0)
               Filter: (user_code_list @> '{loadmin}'::imos_code[])
(8 rows)

Time: 0.453 ms
imos=#