浅谈数据规范

概览

做数据开发几年了，遇到的人、事凡几。

谈到规范这件事情，想到的是第二份工作中刚毕业的同事小x，小x是个很神奇的同事，能同时经受领导和甲方项目经理的教导，不过只要经手过他的代码，就觉得理所应当，毕竟建表字段全用拼音缩写，还不写注释，这实在让人痛苦，拼命猜了半天，最终与实际相去甚远。

后来自己独立负责做数据工作，对前人留下的大片毫无注释的sql文档以及没有comment的整个数仓结构，只能挠头硬接。期间补了些许规范以及血缘关系，即使这样，转交给下一位同事的时候，他也很痛苦。

库设计规范

我的工作中很少需要自己建库建schema，但是在使用中就会发现一个清晰的库名和schema名是多么有用，能够在跟实际相关联起来，节省很多时间：

1. 规范库名 ：库名与应用名称尽量一致，最好能表示其实际含义；

命名规范

1. 禁用保留字、常用访问方法
2. 表名、字段名只能使用英文字母、数字、下划线，英文字母开头，禁止两个下划线中间出现数字，对象名的修改代价很大，对下游容易造成影响，谨慎修改。
3. 临时表增加tmp标记，备份表增加bak标记，序列以seq标记，明确定位。
4. 主键使用pk_，唯一约束使用uk_（另外补充一点，主键和唯一约束的区别在于：主键约束只有一个，唯一约束有多个）. 普通索引使用idx_，唯一索引使用uidx_区分。

表设计规范

1. 表名和列名必须要有注释：这里就不需要多说了，没有中文解释纯靠猜的世界太痛苦了。

2. 禁止使用外键和级联。

   这里解释一下什么叫做级联，比如说学生表
   中student_id是主键，那么成绩表中的student_id就是外键，如果更新了学生表的student_id，就会触发更新成绩表中的student_id，即为级联更新。

   级联更新和外键适用于单机低并发，不适合高并发、分布式环境；级联更新是强阻塞，存在数据库风暴的危险，外键则会影响插入速度。

   那么什么时候使用外键呢？在数仓建模中，我们通常会明确外键，确定表之间的关联关系，数据开发完成后，外键的完整性就可以作为数据质量判断的一层标准。

字段设计规范

1. 表上用来做关联的字段，使用相同的数据类型，避免产生隐式转换
2. 字段设计尤其是索引字段，建议增加默认约束或非空约束，避免后期使用is null或者nvl。
3. 业务表中必备id，creattime，updattime，mysql中id为物理主键，设为AUTO_INCREMENT单表自增。createtime和updattime两列在追溯表是否正常插入、更新以及排查问题时非常有用，createtime可设sysdate，表示主动式创建，updattime表示被动式更新。同理在数仓中应明确业务主键，ETL批量时间，ETL加载时间。

索引设计规范

1. 复合索引字段尽量将选择性高的字段放在前面，选择性高指的是离散度低，字段中重复数据占比高。
2. 表中有大量DML操作的字段不适宜建索引，维护成本过高。

SQL规范

1. 避免select *
2. SQL语句中的where子句变量，都应使用绑定变量
3. where条件里的过滤字段上禁止使用函数或者计算操作，尤其是索引字段
4. 避免使用左模糊和全模糊，Like使用%前缀
5. 尽量delete where，删除明确的过滤数据或分区，减少使用exists。曾经因为在mysql中大量使用exist做delete删除筛选操作，导致整体数据库性能过慢，当然目前在公司中又遇到另一种情况，数仓批量中先删除数据后，insert报错，导致目标表无数据，这里就要求使用exists筛选删除了。

6. 在对表进行大量增、删操作后，及时对表和索引进行统计信息更新。
   

7. 增加必要的注释，例如在where a in (1,2,3)后增加代码说明，业务用途。
8. count(列名)和count(*)是有区别的，count(*)会统计Null值所在行，而count(列名)是不会统计null行。
9. 在某一列全为null时，count(col)不为0，但sum(col)是可以为0的，因此要注意用coalesce、nvl或者ifnull处理。

文献引用[^1]
[^]:Java开发手册