每天5分钟PG聊通透第11期,为什么count查询慢?

背景

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

链接、驱动、SQL

11、为什么count查询慢?

https://www.bilibili.com/video/BV1u44y177Ko/

count 需要做什么?
没有计数器, 必须计算. 以如下扫描方法为例:

• 1、seq scan
• 2、index scan
• 3、index only scan
• 4、bitmap scan

任何扫描方法在IO、CPU、memory方面的开销都比较大(相对直接读取计数器而言), 当然快不了.

为什么不用计数器呢? 计数器的问题:

• 1、计数器影响插入、删除性能, 因为要实时更新计数器, 成为并发写入、删除的热点问题.
• 2、通常只能做全表计数器, 使用场景非常有限.
• 3、计数器无法满足tuple可见性判断需求, 不符合ACID, 计数器本身没有版本信息, 怎么知道应该看到多少条呢?

最常见的count的误用:

判断有无符合条件的记录, 错误方法:

建议方法:

count 优化手段

1、使用计数器, 但是和前面的计数器略有不同
不仅仅是全表记录数计数, 还包括计算后的计数(例如实时PV,UV), 通常的解决方案:

• redis
• 物化视图
• 流计算(异步)

2、偶尔的count查询加速
并行计算

3、偏静态数据(例如日志表、历史表)
将存储转换为列存格式, 有助于大量记录的运算加速(jit, llvm)

4、偏静态数据(例如日志表、历史表)
在没有很好的列存储引擎的情况下, 也可以使用index only scan(索引包含所有要查询以及where过滤的字段)进行优化. 为什么也针对静态数据?

• 因为数据页内有新版本(插入、修改)时, index only scan对于这个页的访问就要回表查询(因为index没有版本信息, 无法判断记录的可见性), 从而导致index only scan 加速效果非常有限.

为什么index only scan的优化效果很有限?

• index only scan是逻辑顺序扫描, 而非物理顺序扫描, 所以加速效果非常有限(特别是机械盘、buffer命中率低时, 可能变得更慢), 除非是大宽表(存储占用非常之大), 使用index only scan可以减少扫描量.

5、行数估算

• 1、pg_class.reltuples
• 2、带条件的结果数估算(explain)、
• 《妙用explain Plan Rows快速估算行 - 分页数估算》
• 3、带条件的结果数估算(采样)
• 《PostgreSQL 任意列组合条件 行数估算 实践 - 采样估算》
• 《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》
• 4、HLL(PV、UV),
• 《PostgreSQL HLL 近似计算算法要点》
• 《PostgreSQL hll 在留存、UV统计中的通用用法》
• 5、cms_topn
• 《PostgreSQL count-min sketch top-n 概率计算插件 cms_topn (结合窗口实现同比、环比、滑窗分析等) - 流计算核心功能之一》

用好周边工具, 数据库管理水平战胜90%老司机

1、管控软件

云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.

• https://github.com/apecloud/kubeblocks

乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

通过信创并且开源的数据库:

PolarDB for PostgreSQL

• https://github.com/ApsaraDB/PolarDB-for-PostgreSQL