数据库规范篇：数据库建表规范

基本规范

表结构

• 表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写。
• 表名要求模块名强相关，如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。
• 建表必须有comment，包括字段和表。
• 数值表主键为id，类型为int或bigint，且为auto_increment
• 反范式设计：把经常需要join查询的字段，在其他表里冗余一份。如industry_name属性在v_industry等表里冗余一份，减少join查询。
• 计算过程中用的中间表，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头。中间表和备份表定期清理。

字段

• 标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引。
• 表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。
• 表中的自增列（auto_increment属性），使用bigint类型。因为无符号int存储范围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。
• 文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。

索引

• 主键索引的名称以“pk_”开头，唯一键索引以“uq_”开头，普通索引以“idx_”开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。
• 在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。
• 建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除

字符集

• 数据库本身库、表、列所有字符集必须保持一致，为utf8。
• 前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为utf8。

DML语句

1. SELECT语句必须指定具体字段名称，禁止写成*。
2. insert语句指定具体字段名称，不要写成insert into t1 values(…)。
3. in值列表限制在500以内。
4. SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件必需使用索引查找。
5. WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引。
6. 减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。
7. 分页查询，当limit起点较高时，可先用过滤条件进行过滤。如select a,b,c from t1 limit 10000,20;优化为: select a,b,c from t1 where id>10000 limit 20;。
8. 建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。

业务规范

业务表说明

业务表除了维表（也称字典表）、事实表（也称数据表）之外，通常还包括支持各类功能模块开发的模块数据表、用于存储中间计算过程的各类过程数据表。

其中字典表包括系统字典表、模块字典表，模块表用于存储模块原始数据、模块计算结果数据，中间过程表是临时表，用于一些复杂业务，需要多步计算，产生一些中间过程表，中间过程表要定期清理。

业务数据从维度上一般包含时间维度、地区维度、指标维度、其他特征维度，其中时间维度包含年、月、日、旬、季、小时等，地区维度包含国际、省际、地市等，指标维度包括经济指标、电力指标、能源指标、气象指标等，其他特征维度包括数值类型等。

各类数据表存储经济、电力、能源、气象等主题的多维时间序列数据。

 

命名规范

基本原则

1 使用英文简写  2 见名知意  3 保证表按模块集中 4  简短优雅

基本规律

指标一般与时间、主题紧密相关，所以字典表一般带时间和主题。

字典表

字典表，也称编码表或维度表，是某个维度、指标的编码表，不仅包含维度名称信息，还包括维度节点层级关系，以节点树结构存储。字典表一般包含的字段有key,name,pk&