PostgreSQL数据库优化
2024-03-19 09:06:28 0 举报AI智能生成
登录查看完整内容
PostgreSQL数据库优化思维导图，持续完善中
作者其他创作
大纲/内容
SSD
Page Cache
缓存热数据，提高缓存命中率
内存
多核并行计算优势
CPU
网线 -- 百兆换成千兆
跨地域访问带宽问题 -- 能否走代理
网卡 + 带宽
硬件优化
提高大内存机器的内存使用率
overcommit参数调优
提升刷盘性能
脏页刷新策略
PG12+ 对 shm 有要求
shm参数调优
swapness
操作系统优化
Copy
reWriteBatchedInserts
合并数据
1000笔提交一次
合并事务
有序插入
Bulk Insert
批量插入
关联更新，但得控制批量处理的数据量
批量更新
关联删除，但得控制批量处理的数据量
批量删除
注意: 约束名不能随便动，有的人可能把约束名硬编码
一定要注意主键字段内容被更新的业务场景，主键值一改，upsert 会造成一条新记录插入
upsert
化零为整，多操作合并
常见于数据提取类业务
计算模式全量变增量，避免重复计算
分页查询
减少返回给客户端的数据量大小，尤其是应用跨网络环境访问数据库的场景
若用户查询较大时间范围的数据，应考虑对其限制
限制用户可查询的时间范围
必要的 DW/DM 模型完善，方便业务端查询
实现方案调整，选择Plan-B
因为异常操作写入了一些脏数据
查看一下是否有异常数据拖慢了整体的执行效率
削峰填谷
限制并发
Lock Timeout保护
保守策略是降低任务调用频率
任务排程优化，降低资源竞争，减少锁竞争
业务端优化
可配置 prepareThreshold 参数
Transcation mode 不支持 prepare 操作
明确指明表所属的 schema
不支持 JDBC 连接的 currentSchema 参数
连接池功能完爆 Pgpool
PgBouncer 连接池
语法解析
黑白名单
hint
按照 application_name
Pgpool 读写分离
实现层次从数据库迁移到代理层，更易定制
权限管控
危险 SQL 拦截
SQL审计
能否支持跨库关联是关键，还没找到一个支持度很好的产品
分库分表
数据库代理
postgres_exporter
Prometheus
pg_stat_statements
auto_explain
PostgreSQL Extension
QPS
TPS
数据库容量
...
监控大屏
TOPN SQL
CURRENT SQL
索引利用率
SQL监控
Grafana
数据库监控
Patroni
主从架构
Greenplum
分片架构
数据库架构
shared_buffers
maintenance_work_mem
work_mem
内存临时表的容量阀值
temp_buffer
Buffer
max_wal_size
checkpoint_timeout
Checkpoint
比如 HAProxy 默认最大连接数为 100，超限也会阻塞连接创建
最大连接数除了受数据库的限制，还受到代理层的限制
max_connections
statement_timeout
Connection
参数调优
碎片整理
Vacuum 膨胀
针对频繁Delete的表，需要关注索引膨胀的问题，否则会出现索引大小远超过数据大小的情况
Reindex 重建索引
主从切换之后
手动执行 analyze 以刷新 pg_statistic 表
pg_upgrade 大版本升级之后
表统计信息维护更新，对执行计划优化器比较友好
Analyse 统计信息
pigz
流式压缩，并行压缩
并行逻辑备份
并行备份
增量备份
Backup 数据备份
日常维护任务
执行计划可视化，https://explain.dalibo.com/
Function中临时表统计信息偏差
统计信息偏差太大
analyse
cost=10000000001.20..10000000001.22 rows=8 width=12
作用范围是同会话，设置以后记得在最后Reset
set enable_seqscan = off;
PG12 的一个坑啊
set jit = off;
并行执行
执行计划手动调控，https://www.postgresql.org/docs/12/runtime-config-query.html
尤其是关联Foreign数据源的时候，要力求缩小返回的结果集
优先要进行数据过滤
谓词下推
执行计划
写入成本增加
多个索引都包含某个字段，查询该字段可能造成索引选择错误
可能产生错误的执行计划
重复索引清理
无效索引清理
索引治理
可以参考雪花算法生成唯一约束
唯一索引
适用于所有BTree结构的索引
左前缀原则
目标是内存利用率最大化
索引越大，性能越不稳定，因为无法保证内存完全容纳下该索引
索引区分度
复合索引
最大化精简索引体积
稀疏索引
给形如 func(col) 的表达式创建索引
表达式索引
可以考虑一下 Brin 块级瘦索引，但要留意一点，cdt / udt 需要在 where 条件中才会用上该索引，比如 SELECT max(udt) FROM dw.fact_pca_yield_unit; 这种统计查询就用不到该索引，需要加上 where udt > ? 才行。另外如果表并非追加表时，即表的中 cdt / udt 并非单调递增，或者即便为追加表，但数据量存在突增的情况，那么执行时预估算法就会有问题，导致走索引以后也奇慢无比
Oracle 里面叫 Zone Map
大表查询 / 增量提取的时候如果依据时间维度，比如cdt/udt，可以考虑加 Brin 索引
常规模糊匹配中 like 'xxx%' 或 ~ '^xxx' 按理说也可以走B-Tree索引，但如果 operator classes 选择的不对，索引也可能用不上
模糊匹配试试 GIN 索引
explain analyse SELECT MAX(test_date) FROM dw.fact_pca_yield_unit where wc ='15' and pdline='P41' ;若 test_date 有索引， wc 和 pdline 没有索引的情况下，猜一下执行计划
Index Scan Backward，有个前提是索引树中应该能找到该值，如果索引 where 条件中的值不存在，就会造成整个遍历索引树，外加回表操作，效率会严重降低，还抵不上单纯的全表遍历。
统计查询提速方法
按理说能走 Index Only 但实际没有，可以看看是否是因为索引膨胀导致比表都大了。
区分一下覆盖索引和多列索引，PG 11 才引入的覆盖索引，相比于多列索引略显鸡肋
Index Only Scan
推荐采用默认的 B-Tree
鸡肋的Hash索引
最好选用自增字段做索引，否则等表大了(比如超过6000w)，再做批量插入的时候会躺坑
B+Tree 有何优势以及问题
B+Tree
B-Tree
单表查询-索引优化
Nested-Loop Join
如果统一了关联字段的类型但没有用上索引，此时可以 Reindex 看看
关联字段切记类型一致
Index Nested-Loop Join
Bitmap Index Join
PG 会自主选择
GP 的默认行为
小的结果集生成Hash Map，遍历大的结果集，去内存中的Map中进行数据比对
Hash Join
Merge Join
JOIN算法选择
如果 not in 的范围较大，比如超过 1k，此时更推荐使用 left join; 同理还有 <> 等算子
not in
多表查询-算法选择
ACID，原子性，一致性，隔离性，持久化
Debezium CDC
长事务意味着数据库日志的膨胀，潜在的主从不同步，尤其是逻辑复制
创建索引要异步
添加字段别加默认值
明确哪些操作不属于Online DDL
长事务可能会造成 DB Backup / Truncate 进入锁等待状态，进而引发后续大面积的阻塞
避免长事务
事务管理
消费CDC记录
人为约束查询范围，避免操作无关数据
Tableau 数据抽取
DW 模型存在的意义
采用中间表避免重复计算
中间表 (临时表 / DW 模型)
SQL优化
Tablespace
单批次拉取外的数据量大小fetch_size，默认配置较小(100)，可能会产生很多次的网络交互
谓词下推未必符合预期，建议使用的时候多看一下执行计划，比如涉及到 now() 时间函数的时候https://serverfault.com/questions/1058352/postgres-foreign-data-wrapper-query-with-now
需要特别注意
FDW 外部表
历史数据归档，存储介质切换
冷热分离
避免字段类型隐式转化
统一相同含义字段的数据类型
字段名称全小写，用_分割
避免使用数据库系统保留字
非自增的索引可能会降低插入的效能
利用单一字段进行全表数据去重
checksum字段
建议所以可以添加comment的地方均添加comment
timestamp 所占空间虽然和 bigint 相当，但入库时需要额外留意时区
跨国公司，数据要做全球同步的，时间字段最好使用 bigint 存毫秒值
分布式数据库外键的设置可能是个制约因素
避免使用外键
触发器的逻辑建议放到业务程序中，业务处理流程会更加清晰，且避免受制于数据库的效能
Trigger UDF 无法被监控
避免使用触发器
业务字段定义标准化
存储空间可能缩小百倍不止
宽表拆为多张表
3NF
分区剪枝
冷热分区表(按时间维度分区)
查询时直接跳过不相关的数据记录，类似于分表优化，或者就是数据写两份，用空间换时间
List分区
控制单表索引大小
分区表的唯一索引需要包含分区列，不支持像 Oracle 那样的 Globle Index
限制
表分区
偏向 OLAP 业务
列存
存储引擎
模型设计
数据库优化
PostgreSQL优化套路
0 条评论
回复 删除
下一页
