震精 - 数据库还能这样玩 - 三十六计 (下)-阿里云开发者社区

开发者社区> 阿里云数据库> 正文
登录阅读全文

震精 - 数据库还能这样玩 - 三十六计 (下)

简介:

PostgreSQL 三十六计 - 下

25. 数据库端编程,处理复杂业务逻辑。

在传统企业、电商、运营商等涉及用户交互、或者多个系统交互的业务场景中,通常一个事务涉及到很复杂的业务逻辑,需要保证数据的一致性,同时还需要与数据库多次交互。

比如银行开户,涉及的业务系统多,逻辑复杂。在传统企业中,通常也使用商业数据库的过程函数,实现此类复杂的逻辑。

PostgreSQL的数据库过程函数支持的语言非常丰富,比如plpgsql(可与Oracle pl/sql功能比肩),另外还支持语言的扩展,编程语言可以热插拔,例如支持python,perl,java,c,r 等等作为数据库的过程函数语言。

对于开发人员来说,几乎可以在PostgreSQL数据库中处理任何业务逻辑。使用数据库端编程,可以在保证一致性的前提下,处理复杂的业务逻辑,减少数据库与程序的交互次数,降低整个事务的RT。

26. 善用ECPG,C嵌入式开发

在一些对性能要求非常高的场景,同时考虑开发效率,C嵌入式开发是一个非常好的选择。

比如在很多金融行业的开发商,ECPG和Oracle Pro*C一样具有同等地位。

27. 数据库水平拆分、跨平台数据融合

PostgreSQL 从 2011年的9.1版本引入FDW开始,发展到现在已经支持几乎所有的外部数据源读写操作,例如mysql,oracle,pgsql,redis,mongo,hive,jdbc,odbc,file,sqlserver,es,S3,......。

https://wiki.postgresql.org/wiki/Fdw

开放的接口,允许用户自己添加外部数据源的支持。

9.6针对postgres_fdw(即PostgreSQL外部数据源)再次增强,开始支持对sort, where, join的下推,支持remote cancel query, 用户使用FDW可以对应用透明的实现数据库的sharding,单元化需求。

内核层支持sharding,这种分片技术相比中间件分片技术的好处:

1. 支持跨库JOIN

2. 支持绑定变量

3. 支持master(coordinator)节点水平扩展

4. 支持segment(datanode)节点水平扩展

5. 支持函数和存储过程

6. 支持sort, where, join的下推,支持remote cancel query,10.x支持聚合算子的下推。

7. 支持单一SQL,在shard节点并行RUN QUERY。

ps: 目前还不支持分布式事务(需要用户干预2PC) ,10.x的版本会增加内核层面的分布式事务控制。

28. 开发规约 - 命名

1. 长度不要超过63个字符。

2. 不要使用关键字作为对象名。

3. 不要使用小写字母、数字和下划线以外的字符作为对象名。

4. 不要使用多字节字符作为注释。

29. 开发规约 - 设计

1. 同类属性,将来可能涉及JOIN,FK,务必对齐数据类型,包括长度。

2. BTREE索引字段,长度不建议超过2000字节,否则请使用hash索引或函数索引。

3. 频繁更新的表,建议设置足够大的fillfactor。

4. 定期删除历史数据的场景,建议使用分区表或继承表的方式,TRUNCATE或DROP或断开继承的方式删除分区或继承的数据。

5. 全球化业务,建议使用UTF-8字符集。

6. 使用能描述清楚数据的类型。能不用字符串的时候,尽量不要用字符串。

7. 不建议使用无序的UUID,也不建议在无序的UUID上建立索引,包括PK。

8. 根据不同的数据类型,数据的查询需求,使用合适的索引方法(Btree, GiST, GIN, SP-GiST, HASH, rum, BRIN, bloom)。

9. 建议不需要的大对象,要清理,否则会造成空间泄露。

10. 不建议使用public schema(不同业务共享的对象可以使用public schema),应该为每个应用分配对应的schema,schema_name最好与username一致。

11. 用户应该对频繁访问的大表(通常指超过8GB的表,或者超过1000万记录的表)进行分区,从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等,(PostgreSQL支持多核创建索引后,可以适当将这个限制放大)。

12. 多个业务共用一个PG集群时,建议为每个业务创建一个数据库。 如果业务之间有数据交集,或者事务相关的处理,强烈建议在程序层处理数据的交互。

30. 开发规约 - QUERY

1. 返回大量结果集的时候,建议使用游标的方式流式返回。

2. 翻页时,不建议使用order by offset的方式,因为越到末尾,消耗越大。建议使用游标的方式翻页。

3. 对于经常使用表达式作为查询条件的语句,应使用表达式或函数索引加速查询。

4. 中文分词的查询,建议使用PostgreSQL的分词插件zhparser或jieba,用户还可以通过接口自定义词组。

5. 规则表达式查询,文本近似度查询,前后模糊查询。建议对字段使用trgm的gin、gist索引。中文的支持确保数据库的lc_collate, lc_ctype不等于C。

6. 不要使用count(列名)或count(常量)来替代count(),count()就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。

7. 任何地方都不要使用 select * from t ,用具体的字段列表代替*,不要返回用不到的任何字段。另外表结构发生变化也容易出现问题。

31. 管理规约 - 安全与审计

1. 所有的DDL均应该被审计。

2. 数据库的非应用程序用户(例如DBA,分析师,开发账号),所有的操作均应该被审计。

3. 所有的登陆和退出操作必须被审计。

4. 关键表(例如账务表、密码表等)的所有操作(包括DML)应该被审计。

5. 为数据库访问账号设置复杂密码。

6. 业务系统,开发测试账号,不要使用数据库超级用户。

7. 应该为每个业务分配不同的数据库账号,禁止多个业务共用一个数据库账号。

8. 敏感数据应该加密存储,建议在服务端加解密,如果加密后的数据有等值查询以外的查询需求,建议使用数据库的透明加密技术。

9. 对安全要求较高的业务,网络传输应该被加密,使用数据库的SSL连接。

10. 不建议将数据库的监听端口暴露在公网。

11. 有公网访问需求时,建议开启SSL连接,前置防火墙并使用来访白名单,数据库PG_HBA.CONF也要配置白名单。

12. 不要泄露用户密码的MD5值,目前这么做有危险,(10.0 SASL上线后安全性会有提高)。

13. 使用auth_delay插件,可以防止暴力破解。

32. 管理规约 - 诊断

1. 统计信息活动视图(表、索引、库、STANDBY、SLOT、流复制、后台任务、bgwriter、归档、事务、活动会话等),均以pg_stat开头。

2. auto_explain插件,可以帮助用户分析慢SQL的执行计划。

3. pg_stat_statements插件,可以帮助用户找出资源开销的TOP SQL,(调用次数、IO耗时、CPU耗时、内存消耗)。优化数据库,应该从开销的TOP 1 SQL开始。

4. 建议配置慢SQL的时间阈值,超过阈值的SQL将被记录到日志文件中。

5. 建议将日志输出格式配置为CSV格式。

6. explain (analyze,verbose,timing,costs,buffers) 可以执行SQL,并输出详细的执行计划,帮助分析SQL的瓶颈,优化SQL。

7. explain analyze会真实的执行SQL,因此如果要用在DML语句上,请使用begin; explain analyze; rollback;的模式。

8. auto_explain插件,还可以帮助用户分析函数内部执行的SQL,以及他们的执行计划。

10. 数据库隐藏了一些开发参数,DEBUG参数,可以帮助用户进行跟深入的诊断。

11. perf可以从内核层面,帮助用户分析数据库软件本身的瓶颈。

33. 管理规约 - 优化

1. 大批量数据入库的优化,如果有大批量的数据入库,建议使用copy语法,或者 insert into table values (),(),...(); 的方式。 提高写入速度。

2. 不要使用delete 全表,性能很差,请使用truncate代替,(truncate是DDL语句,注意加锁等待超时)。

3. 两阶段提交的事务或者普通事务,要及时提交或回滚,否则可能导致数据库膨胀,年龄无法降低等问题。

4. 应用程序一定要开启autocommit,同时避免应用程序自动begin事务,并且不进行任何操作的情况发生,某些框架可能会有这样的问题。

5. 高并发的应用场合,务必使用绑定变量(prepared statement),防止数据库硬解析消耗过多的CPU资源。

6. 秒杀场景,一定要使用 advisory_lock先对记录的唯一ID进行锁定,拿到AD锁再去对数据进行更新操作。 拿不到锁时,可以尝试重试拿锁。

7. 在函数中,或程序中,不要使用count(*)判断是否有数据,很慢。 建议的方法是select 1 from table where ... limit 1; 然后使用FOUND变量的值判断前面这条QUERY有没有结果返回来判断是否有数据。

8. 分页评估,不需要精确分页数时,请使用快速评估分页数的方法(explain的评估)。

9. 避免频繁创建和删除临时表,以减少系统表资源的消耗,因为创建临时表会产生元数据,频繁创建,元数据可能会出现碎片。

10. 可以预估SQL执行时间的操作,建议设置语句级别的超时,可以防止雪崩,也可以防止长时间持锁。

11. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但是TRUNCATE是DDL,锁粒度很大,故不建议在开发代码中使用DDL语句,除非加了lock_timeout锁超时的会话参数或事务参数。

12. PostgreSQL支持DDL事务,支持回滚DDL,建议将DDL封装在事务中执行,必要时可以回滚,但是需要注意事务的长度,避免长时间堵塞DDL对象的读操作。

13. 如果用户需要在插入数据和,删除数据前,或者修改数据后马上拿到插入或被删除或修改后的数据,建议使用insert into .. returning ..; delete .. returning ..或update .. returning ..; 语法。减少数据库交互次数。

14. 自增字段建议使用序列,序列分为2字节,4字节,8字节几种(serial2,serial4,serial8)。按实际情况选择。 禁止使用触发器的方式产生序列值。

15. 树形查询应该使用递归查询,尽量减少数据库的交互或JOIN。

16. 批量导入、迁移数据时。推荐的步骤,建表(不要建索引,约束,主键);关闭autovacuum;导入;改大maintenance_work_mem;创建索引、约束、主键;analyze;打开autovacuum。

17. 尽量使用数组变量来代替临时表。如果临时表有非常庞大的数据时,才考虑使用临时表。

18. GIN索引的写优化,因为GIN的索引列通常是多值列,所以一条记录可能影响GIN索引的多个页,为了加快数据插入和更新删除的速度,建议打开fastupdate,同时设置合适的gin_pending_list_limit(单位KB)。autovacuum,vacuum,或者cache满时会自动合并,为了提高查询效率,可以将cache合并后进行查询。

19. BRIN索引优化,根据数据的相关性,以及用户需求的查询的范围,设置合适的pages_per_range=n。

20. b-tree索引优化,不建议对频繁访问的数据上使用非常离散的数据,例如UUID作为索引,索引页会频繁的分裂,重锁,重IO和CPU开销都比较高。

34. 管理规约 - 备份与恢复

1. 备份分为逻辑备份和物理备份,逻辑备份支持跨版本恢复,物理备份不支持跨版本恢复。

2. 逻辑备份不支持时间点恢复,逻辑备份为库级一致性备份。

3. 逻辑备份需要持有表的共享锁,与DDL会冲突,因此如果业务系统有可能执行DDL时,不建议使用逻辑备份。

4. 物理备份需要开启归档,备份时需要备份数据文件以及归档。

5. 物理备份不会堵塞任何SQL操作。

6. 小库,没有时间点恢复需求时,可以使用逻辑备份。

7. 大库,有时间点恢复需求时,请使用物理备份。

8. 物理备份的恢复速度取决于需要APPLY多少归档日志,有恢复时间SLA要求的场景,请评估好全量备份与归档备份的时间间隔。

9. 对于归档生成速度很快的数据库实例,建议使用快照的方式备份全量数据,或者使用数据库的块级别增量的方式备份。加快恢复的速度。

35. 管理规约 - 日常维护

1. 长事务,两阶段事务。长事务,长时间未关闭的两阶段事务,会导致数据库膨胀,无法冻结,年龄无法下降的问题。务必监控,必要时杀死(例如不正常跑几天的事务的情况)。

2. TOP SQL。通过关注TOP SQL,逐个优化。

3. VACUUM freeze 风暴。数据库通过vacuum freeze降年龄,如果大表同时爆发freeze,会导致大量写REDO,数据文件,IO暴增。必须预测和freeze smooth化。

4. 年龄。必须关注数据库的年龄,不能大于20亿。(虽然数据库会自动降年龄)。

5. 膨胀。必须关注表、索引的膨胀。超出设置的阈值时,应该找原因,同时vacuum full(DDL)或者使用pg_repack处理膨胀。

6. sort(cluster)。如果频繁的访问范围数据,应该对该列执行cluster进行heap sort。减少离散IO和IO放大的问题。

7. 瓶颈分析。关注LONG SQL,TOP SQL,perf。找出瓶颈,分析原因。

8. 读写分离。有必要时,可以使用物理流复制,逻辑流复制的方式建立备库,使用中间件或业务程度多数据源的方式,实现读写分离。同时关注备库的健康和延迟。

9. 垂直拆分。不同的业务,不相干的业务,当发展到单库瓶颈时,建议垂直拆分。

10. 水平分库。同一个业务,不相干的数据,当发展到单库瓶颈时,建议水平拆分。

11. 任务。关注数据库任务的执行状态,成功与否,错误原因。

12. HA健康。关注HA的健康,心跳,备库的延迟等。

13. 监控。监控各项用户或业务关心的指标。

14. 死锁。关注日志中输出的死锁信息,找出业务逻辑问题。

15. QUERY响应时间变化。关注重点QUERY(TOP SQL)的响应时间的变化,找出问题。

16. 表空间增速。关注表空间增速,提前做好扩容,迁移对象或表空间的准备。

17. 数据库增速。关注数据库的增速,提前做好扩容,清理、迁移数据的准备。

18. 对象增速。关注对象增速,提前做好扩容,分区的准备。

19. 分区表。关注分区表的门限,提前创建分区。

20. 日志文件增速。日志文件(指错误日志),及时压缩归档和清理。

21. 临时文件。数据库某些查询会使用临时文件,关注临时文件的大小,找到异常QUERY(例如递归死循环),通过加大work_mem或者其他方式减少临时文件的使用。使用参数控制单个QUERY的临时文件的上限。

22. 入侵检测。分析日志,找到SQL注入,找到密码暴力破解的安全问题。

23. 日志文件分析,错误类型统计。从日志中,分析出错误日志,找到业务逻辑的问题,数据库本身的问题等。

24. 不使用的索引。通过pg_stat*统计信息,找到不使用的索引,删除。

25. 不合理的全表扫描。通过auto_explain,慢SQL,TOP SQL,找出给系统带来负担的不合理全表扫描SQL,优化之。

36. 开箱即用,用为上计

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接