ITPub博客

首页 > 数据库 > PostgreSQL > Postgresql 31条数据库开发规范

Postgresql 31条数据库开发规范

原创 PostgreSQL 作者:jaymarco 时间:2020-12-02 14:27:23 0 删除 编辑

为了加强各系统软件开发标准规范,进一步提高各系统的稳定性,因此对现有的数据库开发规范进行了梳理,制定31条数据库开发管理规范.此规范主要是指导开发人员在进行开发时编写高效的SQL语句和以合适的方式创建表和索引,已达到系统在不断更新和升级时仍能保持良好的稳定性。

1 大批量插入数据

如果同时执行大量的插入,建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快(方法一),一般情况下批量插入效率有几倍的差别。

方法一:

insert into tablename values(1,2);

insert into tablename values(1,3);

insert into tablename values(1,4);

方法二:

Insert into tablename values(1,2),(1,3),(1,4);

选择后一种方法的原因有二。

1.减少SQL语句解析的操作, polardb没有类似Oracle的share pool,采用方法二,只需要解析一次就能进行数据的插入操作;

2.SQL语句较短,可以减少网络传输的IO。

2 避免出现select *

         select * 操作在任何类型数据库中都不是一个好的SQL开发习惯。

         使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

         建议评估业务实际需要的列数,指定列名以取代select *

         规范:

             Select col1,col2,col3… from t1;

         不规范:

    Select * from t1;

3 避免出现count(*)

使用 SELECT 1 FROM tbl WHERE xxx LIMIT 1 判断是否存满足条件的列,要比Count快。

可以使用 select exists(select * FROM app.sjqq where xxx limit 1) 将存在性结果转换为布尔值。

4 优化group by语句

默认情况下,Polardb排序所有 “GROUP BY col1,col2,....;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,...;” 如果显式包括一个包含相同的列的 ORDER BY子句,Polardb可以毫不减速地对它进行优化,尽管仍然进行排序。

如果查询包括 GROUP BY 但你想要避免排序结果的消耗,你可以指定 ORDER BY NULL禁止排序。

例如:

SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY NULL;

5 优化order by语句

在某些情况中,Polardb 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。where 条件和 order by 使用相同的索引,并且 order by 的顺序和索引顺序相同 ,并且 order by 的字段都是升序或者都是降序。

例如:下列 SQL 可以使用索引。

SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以上复合索引包含字段key_part1,key_part2...

 

但是以下情况不使用索引:

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

以上由于order by 的字段混合 ASC和 DESC

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1;

以上用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT col1 FROM t1 ORDER BY key1, key2;

对不同的索引关键字使用 ORDER BY:

6 优化join语句

Polardb中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。

例子:

假设要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

     SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID

     FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作,速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话,性能将会更好,查询如下:

     SELECT col1 FROM customerinfo

     LEFT JOIN salesinfoON ON customerinfo.CustomerID=salesinfo.CustomerID

     WHERE salesinfo.CustomerID IS NULL

7 优化or条件

对于 or 子句,如果要利用索引,则or 之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。

8 优化union查询

Posgresql通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行,否则建议使用union all。原因在于如果没有all这个关键词,Polardb会给临时表加上distinct选项,这会导致对整个临时表的数据做唯一性校验,这样做的消耗相当高。

高效:

       SELECT COL1, COL2, COL3

        FROM TABLE

        WHERE COL1 = 10

UNION ALL

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效

       SELECT COL1, COL2, COL3

        FROM TABLE

        WHERE COL1 = 10

UNION

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

9 优化IN操作符

        ·          使用 EXISTS 子句代替 IN 操作符,效果更佳。

        ·          使用 =ANY(ARRAY[1,2,3,4]) 代替 IN (1,2,3,4) ,效果更佳。

10   不建议使用左模糊搜索

        ·    左模糊搜索WHERE col LIKE '%xxx'无法充分利用B树索引,如有需要,可用reverse表达式函数索引。

11   使用truncate代替delete

当删除全表中记录时,使用delete语句的操作会被记录到undo块中,删除记录也记录pg_wal日志,当确认需要删除全表时,会产生很大量的pg_wa并占用大量的undo数据块,此时既没有很好的效率也占用了大量的资源。使用truncate替代,不会记录可恢复的信息,数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外,使用truncate可以回收表的水位。

12   使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率

针对展现等分页需求,合适的分页方式能够提高分页的效率

案例1:

     select * from t

     where thread_id = 10000

     and deleted = 0

     order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此,该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。

适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。

 

案例2:

     select t.* from (

     select id from t

     where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t

     where a.id = t.id;

上述例子必须满足t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果(例子中是15行)对应的表数据IO。因此,该写法每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。

适用场景:当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。

13   避免出现不确定结果的函数

  特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用QUERY CACHE。

14 在线查询必须有配套索引

        · 所有在线查询必须针对其访问模式设计相应索引,除极个别小表外不允许全表扫描。

        · 索引有代价,不允许创建不使用的索引。

15 禁止在大字段上建立索引

        · 被索引字段大小无法超过2KB(1/3的页容量),原则上禁止超过64个字符。

        ·  如有大字段索引需求,可以考虑对大字段取哈希,并建立函数索引。或使用其他类型的索引(GIN)

16 明确空值排序规则

        ·    如在可空列上有排序需求,需要在查询与索引中明确指定 NULLS FIRST 还是 NULLS LAST

        ·   注意, DESC 排序的默认规则是 NULLS FIRST ,即空值会出现在排序的最前面,通常这不是期望行为。

        ·   索引的排序条件必须与查询匹配,如: create index on tbl (id desc nulls last);

17 利用GiST索引应对近邻查询问题

        ·   传统B树索引无法提供对KNN问题的良好支持,应当使用GiST索引。

18 利用函数索引

         · 任何可以由同一行其他字段推断得出的冗余字段,可以使用函数索引替代。

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

         ·   典型场景:建立大字段上的哈希函数索引,为需要左模糊查询的文本列建立reverse函数索引。

19 字符编码必须为UTF8

        ·   禁止使用其他任何字符编码。

20 容量规划

        ·   单表记录过亿,或超过10GB的量级,可以考虑开始进行分表。

        ·   单表容量超过1T,单库容量超过2T。需要考虑分片。

21 不要滥用存储过程

         ·   存储过程适用于封装事务,减少并发冲突,减少网络往返,减少返回数据量,执行少量自定义逻辑。

         · 存储过程不适合进行复杂计算,不适合进行平凡/频繁的类型转换与包装。

22 主键与身份列

         · 每个表都必须有身份列,原则上必须有主键,最低要求为拥有非空唯一约束。

         · 身份列用于唯一标识表中的任一元组,逻辑复制与诸多三方工具有赖于此。

23 外键

      ·   不建议使用外键,建议在应用层解决。使用外键时,引用必须设置相应的动作: SET NULL SET DEFAULT CASCADE ,慎用级联操作。

24 慎用宽表

         ·   字段数目超过15个的表视作宽表,宽表应当考虑进行纵向拆分,通过相同的主键与主表相互引用。

         ·    因为MVCC机制,宽表的写放大现象比较明显,尽量减少对宽表的频繁更新

25 配置合适的默认值

         · 有默认值的列必须添加 DEFAULT子句指定默认值。

         · 可以在默认值中使用函数,动态生成默认值(例如主键发号器)。

26 合理应对空值

        ·   字段语义上没有零值与空值区分的,不允许空值存在,须为列配置NOT NULL约束。

27 唯一约束通过数据库强制

         · 唯一约束须由数据库保证,任何唯一列须有唯一约束。

         · EXCLUDE 约束是泛化的唯一约束,可以在低频更新场景下用于保证数据完整性。

28  主键类型

         ·   主键通常使用整型,建议使用BIGINT,允许使用不超过64字节的字符串。

         ·   主键允许使用Serial自动生成,建议使用Default next_id()发号器函数。

29   选择合适的类型

         · 能使用专有类型的,不使用字符串。(数值,枚举,网络地址,货币,JSON,UUID等)

         · 使用正确的数据类型,能显著提高数据存储,查询,索引,计算的效率,并提高可维护性。

30   使用枚举类型

         · 较稳定的,取值空间较小(十几个内)的字段应当使用枚举类型,不要使用整型与字符串表示。

         · 使用枚举类型有性能、存储、可维护性上的优势。

31   选择合适的文本类型

         ·    PG的文本类型包括 char char(n) varchar ,varchar(n) text 。除 char(n) 外并无本质存储区别。

         ·    带有 (n) 修饰符的类型会检查字符串长度,会导致微小的额外开销,对字符串长度有限制时应当使用 varchar(n) ,避免插入过长的脏数据。

         · 避免使用 char(n) ,为了与SQL标准兼容,该类型存在不合直觉的行为表现,且并没有存储和性能优势。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28833846/viewspace-2738785/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
负责数据库、中间件、大数据等基础软件建设、优化和业务保障工作。具有10年的电信与银行企业一线/二线运维管理经验。目前专注研究云计算、中间件和数据库等领域技术研究。持有Oracle OCP、weblogic OCP、Docker容器、PGCE和阿里云ACP等认证

注册时间:2020-06-22

  • 博文量
    76
  • 访问量
    38928