论count与offset使用不当的罪名 和 分页的优化

3 minute read

背景

分页是一个非常常见的应用场景,然而恐怕没有多少人想过其优化方法。

确一味的责怪为什么数据库用count(*)计算分页数是如此的慢。

很多开发人员喜欢用count先算一下结果集的大小,然后就知道需要排多少页。

然后再从数据库取出对应的数据,并展示给用户。

问题1

count会扫一遍数据,然后取数据又扫一遍数据。重复劳动。

问题2,很多人喜欢用order by offset limit来展示分页。

其实也是一个非常大的问题,因为扫描的数据也放大了,即使在order by 的列上用到了索引也会放大扫描的数据量。

因为offset的row也是需要扫的。

分页总数count的优化手段

使用评估行数,方法如下

创建一个函数,从explain中抽取返回的记录数

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS    
$func$    
DECLARE    
    rec   record;    
    ROWS  INTEGER;    
BEGIN    
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP    
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');    
        EXIT WHEN ROWS IS NOT NULL;    
    END LOOP;    
    
    RETURN ROWS;    
END    
$func$ LANGUAGE plpgsql;    

评估的行数和实际的行数相差不大,精度和柱状图有关。

PostgreSQL autovacuum进程会根据表的数据量变化比例自动对表进行统计信息的更新。

而且可以配置表级别的统计信息更新频率以及是否开启更新。

评估行数

postgres=# select count_estimate('select * from sbtest1 where id between 100 and 100000');    
 count_estimate     
----------------    
         102166    
(1 row)    
    
postgres=# explain select * from sbtest1 where id between 100 and 100000;    
                                      QUERY PLAN                                           
---------------------------------------------------------------------------------------    
 Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..17398.14 rows=102166 width=190)    
   Index Cond: ((id >= 100) AND (id <= 100000))    
(2 rows)    

实际的行数

postgres=# select count(*) from sbtest1 where id between 100 and 100000;    
 count     
-------    
 99901    
(1 row)    

也就是说,应用程序完全可以使用评估的记录数来评估分页数。

这样做就不需要扫描表了,性能提升尤为可观。

分页数据获取的优化手段

问题2其实表现在数据可能被多次扫描,使用游标就能解决。

未优化的情况,取前面的记录很快。

postgres=# explain analyze select * from sbtest1 where id between 100 and 1000000 order by id offset 0 limit 100;    
                                                                QUERY PLAN                                                                    
------------------------------------------------------------------------------------------------------------------------------------------    
 Limit  (cost=0.43..9.74 rows=100 width=190) (actual time=0.019..0.088 rows=100 loops=1)    
   ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..93450.08 rows=1003938 width=190) (actual time=0.018..0.051 rows=100 loops=1)    
         Index Cond: ((id >= 100) AND (id <= 1000000))    
 Planning time: 0.152 ms    
 Execution time: 0.125 ms    
(5 rows)    

取后面的记录,因为前面的记录也要扫描,所以明显变慢。

postgres=# explain analyze select * from sbtest1 where id between 100 and 1000000 order by id offset 900000 limit 100;    
                                                                  QUERY PLAN                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------    
 Limit  (cost=83775.21..83784.52 rows=100 width=190) (actual time=461.941..462.009 rows=100 loops=1)    
   ->  Index Scan using sbtest1_pkey on sbtest1  (cost=0.43..93450.08 rows=1003938 width=190) (actual time=0.025..308.865 rows=900100 loops=1)    
         Index Cond: ((id >= 100) AND (id <= 1000000))    
 Planning time: 0.179 ms    
 Execution time: 462.053 ms    
(5 rows)    

如果有很多个分页,效率下降可想而知。

优化手段1,使用游标

postgres=# begin;    
BEGIN    
Time: 0.152 ms    
postgres=# declare cur1 cursor for select * from sbtest1 where id between 100 and 1000000 order by id;    
DECLARE CURSOR    
Time: 0.422 ms    
postgres=# fetch 100 from cur1;    

。。。

获取到数据末尾时,效率也是一样的不会变化。

使用游标的话,如果返回的结果数非常多,可能导致事务很长,长事务不好的,有很多负面影响。所以还有优化手段2.

优化手段2,使用位点

《分页优化 - order by limit x offset y performance tuning》

优化手段3,使用位点(自建位点)

当排序字段没有包含PK字段,或者排序字段不是UK字段时。位点无法明确指定(因为位点部分可能有重复值)

这种情况下,可以强制增加一个PK,并且确保PK值不变。就可以使用位点了。

原始结构与SQL:

create table test(id int, c1 int, c2 int, c3 int, c4 int, crt_time timestamp);  
  
create index idx_test_1 on test(c1, crt_time);  
  
insert into test select id, random()*10, random()*10, random()*10, random()*10, clock_timestamp() from generate_series(1,10000000) t(id);  
  
select * from test where c1=1 and c2 between 1 and 10 order by crt_time limit 10 offset ?;  

增加PK,SQL改成如下:

create table test1(pk serial8 primary key,  -- 新增PK  
id int, c1 int, c2 int, c3 int, c4 int, crt_time timestamp);  
  
create index idx_test1_1 on test1(c1, crt_time, pk);  -- 索引修改  
  
insert into test1 (id,c1,c2,c3,c4,crt_time) select id, random()*10, random()*10, random()*10, random()*10, clock_timestamp() from generate_series(1,10000000) t(id);  
  
select * from test1 where c1=1 and c2 between 1 and 10   
  and crt_time>=?  -- 位点为上一次请求的最大crt_time  
  and pk>?         -- 位点为上一次请求的最大pk值  
  order by crt_time,pk limit 10;  -- 改成order by crt_time,pk,同时去掉offset  

offset 100000后,性能对比:

explain (analyze,verbose,timing,costs,buffers)   
select * from test   
where c1=1 and c2 between 1 and 10 order by crt_time limit 10 offset 100000;  
  
                                                                  QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=11739.53..11740.70 rows=10 width=28) (actual time=53.373..53.379 rows=10 loops=1)  
   Output: id, c1, c2, c3, c4, crt_time  
   Buffers: shared hit=8506  
   ->  Index Scan using idx_test_1 on public.test  (cost=0.56..110585.65 rows=942034 width=28) (actual time=0.039..45.239 rows=100010 loops=1)  
         Output: id, c1, c2, c3, c4, crt_time  
         Index Cond: (test.c1 = 1)  
         Filter: ((test.c2 >= 1) AND (test.c2 <= 10))  
         Rows Removed by Filter: 5222  
         Buffers: shared hit=8506  
 Planning time: 0.135 ms  
 Execution time: 53.406 ms  
(11 rows)  

对比

explain (analyze,verbose,timing,costs,buffers)   
select * from test1   
where c1=1 and c2 between 1 and 10   
  and crt_time>='2018-07-25 18:31:14.860328'  -- 位点为上一次请求的最大crt_time  
  and pk>1048412         -- 位点为上一次请求的最大pk值  
  order by crt_time,pk limit 10;  -- 改成order by crt_time,pk,同时去掉offset  
  
  
                                                                   QUERY PLAN                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.56..2.17 rows=10 width=36) (actual time=0.042..0.049 rows=10 loops=1)  
   Output: pk, id, c1, c2, c3, c4, crt_time  
   Buffers: shared hit=9  
   ->  Index Scan using idx_test1_1 on public.test1  (cost=0.56..124129.55 rows=770820 width=36) (actual time=0.040..0.045 rows=10 loops=1)  
         Output: pk, id, c1, c2, c3, c4, crt_time  
         Index Cond: ((test1.c1 = 1) AND (test1.crt_time >= '2018-07-25 18:31:14.860328'::timestamp without time zone) AND (test1.pk > 1048412))  
         Filter: ((test1.c2 >= 1) AND (test1.c2 <= 10))  
         Buffers: shared hit=9  
 Planning time: 0.174 ms  
 Execution time: 0.075 ms  
(10 rows)  

Flag Counter

digoal’s 大量PostgreSQL文章入口