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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

分页是一个非常常见的应用场景,然而恐怕没有多少人想过其优化方法。
确一味的责怪为什么数据库用count(*)计算分页数是如此的慢。
很多开发人员喜欢用count先算一下结果集的大小,然后就知道需要排多少页。
然后再从数据库取出对应的数据,并展示给用户。
问题1
count会扫一遍数据,然后取数据又扫一遍数据。重复劳动。
问题2,很多人喜欢用order by offset limit来展示分页。
其实也是一个非常大的问题,因为扫描的数据也放大了,即使在order by 的列上用到了索引也会放大扫描的数据量。
因为offset的row也是需要扫的。

问题1的优化
使用评估行数,方法如下
创建一个函数,从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的优化
问题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)

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

优化手段

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;
。。。

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

相关文章
|
11月前
|
SQL 存储 关系型数据库
mysql查询 limit 1000,10 和limit 10 速度一样快吗?如果我要分页,我该怎么办?
mysql查询 limit 1000,10 和limit 10 速度一样快吗?如果我要分页,我该怎么办?
130 0
|
SQL 搜索推荐 关系型数据库
mysql 分页offset过大性能问题解决思路
mysql 分页offset过大性能问题解决思路
217 0
|
机器学习/深度学习 存储 SQL
别再用 offset 和 limit 分页了,性能太差
别再用 offset 和 limit 分页了,性能太差
1420 0
别再用 offset 和 limit 分页了,性能太差
|
SQL 关系型数据库 MySQL
为啥count(*)会这么慢?
本没想着写这篇文章的,因为我觉得这个东西大多数有经验的开发遇到过,肯定也了解过相关的原因,但最近我看到有几个关注的技术公众号在推送相关的文章。实在令我吃惊!
121 0
|
索引 SQL
Select Count 聚合函数三种不同写法区别与效率性能比较
Select Count 聚合函数三种不同写法区别与效率性能比较
442 0
Select Count 聚合函数三种不同写法区别与效率性能比较
|
SQL 关系型数据库 MySQL
数据库面试题【十八、优化关联查询&优化子查询&优化LIMIT分页&优化UNION查询&优化WHERE子句】
数据库面试题【十八、优化关联查询&优化子查询&优化LIMIT分页&优化UNION查询&优化WHERE子句】
153 0
|
SQL 算法 关系型数据库
MySQL 分页优化中的 “ INNER JOIN方式优化分页算法 ” 到底在什么情况下会生效?
最近无意间看到一个 MySQL 分页优化的测试案例,并没有非常具体地说明测试场景的情况下,给出了一种经典的方案。因为现实中很多情况都不是固定不变的,能总结出来通用性的做法或者说是规律,是要考虑非常多的场景的;同时,面对能够达到优化的方式要追究其原因,同样的做法,换了个场景,达不到优化效果的,还要追究其原因。
6808 0