PostgreSQL数据库优化思维导图模板_ProcessOn思维导图、流程图

硬件优化

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%

不支持 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 并非单调递增，或者即便为追加表，但数据量存在突增的情况， 那么执行时预估算法就会有问题，导致走索引以后也奇慢无比

Oracle 里面叫 Zone Map

模糊匹配试试 GIN 索引

常规模糊匹配中 like 'xxx%' 或 ~ '^xxx' 按理说也可以走B-Tree索引，但如果 operator classes 选择的不对，索引也可能用不上

统计查询提速方法

Index Scan Backward，有个前提是索引树中应该能找到该值， 如果索引 where 条件中的值不存在，就会造成整个遍历索引树， 外加回表操作，效率会严重降低，还抵不上单纯的全表遍历。

explain analyse SELECT MAX(test_date) FROM dw.fact_pca_yield_unit where wc ='15' and pdline='P41' ; 若 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

模型设计

业务字段定义标准化

名称前缀，后缀

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 业务，以及时序类应用场景