作者
digoal
日期
2016-05-06
标签
PostgreSQL , 行评估 , 分页
背景
分页是一个非常常见的应用场景，然而恐怕没有多少人想过其优化方法。
确一味的责怪为什么数据库用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)
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
