作者
digoal
日期
2021-12-21
标签
PostgreSQL , 热门问题
- 问题说明(现象、环境)
- 分析原因
- 结论和解决办法
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的误用:
判断有无符合条件的记录, 错误方法:
select count(*) into cnt from xx where x;
if cnt >=1 then
建议方法:
perform 1 from x where x limit 1;
if found then
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可以减少扫描量.
create table a (c1 int, c2 int, c3 int, ...);
select count(*) from a where c1=? and c2=?
create index idx_a_1 on a (c1,c2);
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 (结合窗口实现同比、环比、滑窗分析等) - 流计算核心功能之一》
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
