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