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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

分页是一个非常常见的应用场景,然而恐怕没有多少人想过其优化方法。
确一味的责怪为什么数据库用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;
。。。

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

目录
相关文章
|
5月前
|
SQL 数据挖掘 关系型数据库
SQL查询次数大于1的记录:高效技巧与方法
在数据库管理中,经常需要统计某些操作的次数,特别是当需要找出哪些记录或值出现的次数超过一定阈值(如大于1次)时
|
7月前
|
存储 缓存 数据库
别再用offset和limit分页了,性能太差!——探索高效分页技术
【8月更文挑战第27天】在Web开发领域,分页是处理大量数据展示时不可或缺的功能。然而,传统的基于offset和limit的分页方式,在数据量剧增时,其性能问题日益凸显。本文将深入探讨这一问题的根源,并介绍几种更为高效的分页策略,助力你的应用性能飞跃。
460 0
|
8月前
|
SQL 关系型数据库 MySQL
MySQL设计规约问题之为什么在使用LIMIT进行分页时要注意效率,并且LIMIT的值越大效率越低
MySQL设计规约问题之为什么在使用LIMIT进行分页时要注意效率,并且LIMIT的值越大效率越低
37zTree - 分页加载大数据量
37zTree - 分页加载大数据量
139 0
|
机器学习/深度学习 存储 SQL
别再用 offset 和 limit 分页了,性能太差
别再用 offset 和 limit 分页了,性能太差
1548 0
别再用 offset 和 limit 分页了,性能太差
|
索引 SQL
Select Count 聚合函数三种不同写法区别与效率性能比较
Select Count 聚合函数三种不同写法区别与效率性能比较
528 0
Select Count 聚合函数三种不同写法区别与效率性能比较
|
SQL 算法 关系型数据库
[MySQL优化案例]系列 — 分页优化
[MySQL优化案例]系列 — 分页优化
153 0
|
SQL PHP
【laralve】使用原先查询并带分页
【laralve】使用原先查询并带分页
124 0
|
SQL 关系型数据库 MySQL
数据库面试题【十八、优化关联查询&优化子查询&优化LIMIT分页&优化UNION查询&优化WHERE子句】
数据库面试题【十八、优化关联查询&优化子查询&优化LIMIT分页&优化UNION查询&优化WHERE子句】
174 0
|
SQL 算法 测试技术
分页解决方案 之 分页算法——Pager_SQL的思路和使用方法
      分页算法(也就是分页读取数据的时候使用的select 语句)面临两大难题:一个是不同的数据库使用的分页算法是不一样的(比如SQL Server 2000可以使用Max、表变量、颠倒Top,SQL Server 2005可以使用Row_Number,MySql可以使用limit ,Orcale可以使用ROWNUM等);另一个是,不同的分页需求,可以采用的分页算法也是不一样的(比如单字段排序和多字段排序)。
1098 0