TDSQL PostgreSQL-开发注意事项

KindnessCN
470 阅读5分钟

命名规范

数据库对象（database、schema、table、column、view、index、sequence、function、trigger 等名称）

  1. 建议使用小写字母、数字、下划线的组合
  2. 建议不使用双引号"包围，除非必须包含大写字母或空格等特殊字符
  3. 长度不能超过63个字符
  4. 不建议以 pg_ 开头或者 pgxc_（避免与系统 DB object 混淆），不建议以数字开头
  5. 禁止使用 SQL 关键字，例如 type、order 等
  6. 临时或备份的 DB object:table、view 等，建议加上日期，如 dba_ops.b2c_product_summay_2014_07_12 (其中 dba_ops 为 DBA 专用 schema)
  7. index 命名规则：普通索引为表名_列名_idx，唯一索引为表名_列名_uidx，如 student_name_idx,student_id_uidx 总结如下：使用小写字母、数字及下划线命名、不使用pg_或者pgxc开头、不使用数字开头、不使用SQL关键字、index命名规则

Column设计

  1. 建议使用数值类型，不用字符类型
  2. 建议使用 varchar(N) ，不用 char(N)，以节省存储空间
  3. 建议使用 varchar(N) ，不用 text、varchar
  4. 建议使用 default NULL，不用default ''，以节省存储空间
  5. 建议国际业务时，使用 timestamp with time zone(timestamptz)，不用 timestamp without time zone，避免时间函数在不同时区的时间点返回值不同，也为业务国际化扫清障碍
  6. 建议使用 NUMERIC(precision, scale) 来存储货币金额和其它要求精确计算的数值，不建议使用 real、double precision

总结如下：使用数值类型、varchar(N)、numeric类型及default NULL

Constriants设计

  1. 建议每个 table 都使用 shard key 作为主键或者唯一索引
  2. 建议建表时一步到位，一起建立主键或者唯一索引
  3. 非 shard key 不可以建立 primary key 或者 unique index 总结如下：必须建shard key、使用shard key作为主键或者唯一索引、建表一步到位

Index设计

  1. TDSQL PostgreSQL版 提供的 index 类型：B-tree、Hash、GiST (Generalized Search Tree)、SP-GiST (space-partitioned GiST)、GIN (Generalized Inverted Index)、BRIN (Block Range Index)，目前不建议使用 Hash，通常情况下使用 B-tree
  2. 建议 create 或 drop index 时，加 CONCURRENTLY 参数，可以达到与写入数据并发的效果（CONCURRENTLY参数有什么作用呢？）
  3. 建议对频繁 update、delete 的包含于 index 定义中的 column 的 table，使用 create index CONCURRENTLY、drop index CONCURRENTLY 的方式进行维护其对应 index
  4. 建议用 unique index 代替 unique constraints，便于后续维护（unique constraints指的是哪部分？为什么这样就便于维护呢）
  5. 建议对 where 中带多个字段 and 条件的高频 query，参考数据分布情况，建多个字段的联合 index
  6. 建议对固定条件的（一般有特定业务含义）且数据占比低的 query，建议带 where 的 Partial Indexes
  7. 建议对经常使用表达式作为查询条件的 query，可以使用表达式或函数索引加速 query 总结如下：index使用B-tree类型、联合index、Partial Indexes、表达式或函数索引、index个数一般不超过6个（核心表可以适当增加）

关于NULL

  1. NULL 的判断：IS NULL、IS NOT NULL
  2. 注意 boolean 类型取值 true、false、NULL
  3. 注意 NOT IN 集合中带有 NULL 元素
  4. 建议对字符串型 NULL 值处理后，进行 || 操作
  5. 建议使用 count(1) 或 count(*) 来统计行数，不建议使用 count(col) 来统计行数，因为 NULL 值不会计入

    count(多列列名)时，多列列名必须使用括号，例如 count( (col1,col2,col3) )，注意多列的 count，即使所有列都为 NULL，该行也被计数，所以效果与 count(*) 一致。

  6. count(distinct col) 计算某列的非 NULL 不重复数量，NULL 不被计数。

    count(distinct (col1,col2,...) ) 计算多列的唯一值时，NULL 会被计数，同时 NULL 与 NULL会被认为是相同的。

  7. 两个 NULL 的对比方法 总结如下：null判断、null处理、count(1)、count(*)、count(col)、count(多列列名)、count(distinct col)、count(distinct(col1,col2,...))

开发规范

  1. 建议对 DB object 尤其是 COLUMN 加 COMMENT，便于后续新人了解业务及维护
  2. 建议非必须时避免 select *，只取所需字段，以减少包括不限于网络带宽消耗
  3. 建议 update 时，尽量做 <> 判断，如 update table_a set column_b = c where column_b <> c
  4. 建议将单个事务的多条 SQL 操作，分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量 lock 少的资源，避免 lock 、dead lock 的产生
  5. 建议大批量的数据入库时，使用 copy ，不建议使用 insert，以提高写入速度。如下，性能相差5倍
  6. 建议对报表类的或生成基础数据的查询，使用物化视图 (MATERIALIZED VIEW) 定期固化数据快照，避免对多表（尤其多写频繁的表）重复跑相同的查询，且物化视图支持 REFRESH MATERIALIZED VIEW CONCURRENTLY，支持并发更新（使用物化视图会有什么风险麻？比如没把刚插入的数据统计进去）
  7. 建议复杂的统计查询可以尝试窗口函数
  8. 两表 join 时，尽量的使用分布 key 进行 join
  9. 分布键用唯一索引代替主键。因为唯一索引后期的维护成本比主键要低很多。
  10. 分布键无法建立唯一索引，则要建立普通索引，提高查询的效率
  11. 不对字段建立外键 总结如下：comment、禁止使用select *、事务粒度尽可能小、大批量数据导入使用copy、使用物化视图、使用窗口函数、分布KeyJoin、分布键使用唯一索引代替主键、不建立外键
avatar
文章
阅读
粉丝