MySQL数据库规范

mysql数据库规范

• 基础规范

  • 【建议】使用InnoDB存储引擎

  • 【强制】无特殊要求必须使用UTF8字符集

  • 【强制】数据表、数据字段必须加入中文注释

  • 【强制】禁止使用存储过程、视图、触发器、Event。特殊情况申请评审

  • 【强制】不在数据库做运算，cpu计算务必移至业务层

• 命名规范

  • 【建议】 命名使用具有实际意义的英文词汇、词汇缩写，词汇之间使用下划线分隔；

  • 【强制】 命名只能使用小写英文字母、数字、下划线，且必须英文字母开头，下划线为分割符，不能超过32个字符，数据库对象名尽可能简短。避免使用MySQL的保留字

  • 【强制】普通表名命名规则为

    功能模块前缀_+tablename（login_users）；
    临时表：tmp前缀+tablename+8位时间后缀（tmp_users_20170501）；
    备份表：bak前缀+tablename+8位时间后缀（bak_users_20170501）；
    归档表命名规则：arch前缀+tablename+归档规则 (arch _users_2013）

  • 【强制】各表之间相同意义的字段必须同名，数据类型、长度、单位必须相同。

  • 【强制】索引以idx_开头

    唯一索引以uq_idx开头，后面跟索引所在字段名,多单词组成的列名，取尽可能代表意义的缩写，如t_user_contacts表member_id和friend_id上的组合索引：idx _mid_fid,,组合索引命名应注意字段顺序。如在字段member_id和字段user_id上创建组合索引，则可以命名为idx _uid_mid(userid, member_id)

  • 常用约定：

    【建议】序号列字段：以id为后缀，如：user_id表示用户编号
    【建议】编码字段：以code为后缀，如：cust_code表示客户编码
    【建议】布尔值字段：以“is_”前缀+字段描述+形容词。如member表上表示为enabled的会员的列命名为is_member_enabled。0：否；1：是
    【强制】状态字段：以“_status”为后缀，前面加业务逻辑名。如：用户状态可命名为user_status，订单状态为order_status 以此类推

• 表设计规范（***）

  • 【强制】表设计必须有表主键，并且主键不能提供给外部系统，给外部系统的必须使用业务主键，如user表的业务主键设计，如下

    id  表主键，自增，表主键不能像外部系统提供
    xxx_id 为业务主键，使用IdGenerater(id生成工具类生成，见附件)，可以提供给外部系统，使用bigint存储

  • 【强制】表必须有主键，如果使用auto_increment作为自增主键，注意导出初始化脚本时不要设置起始值。

  • 【强制】枚举类型使用tinyint类型

  • 【强制】单表字段数不要太多，最多不要大于50个，且尽可能的少用字符型数据类型

  • 【强制】日期的数据(不包含时分秒的)，使用int(11)存储（如，yyyy、yyyyMM、yyyyMMdd），时间的数据((包含时分秒的))，使用datetime存储。

  • 【强制】每个表都必须包含两个保留字段：create_time（创建时间），update_time（最后修改时间）creater varchar(50)（创建人）,updater varchar(50)（修改人），设置为非空字段属性。这两个字段不包含额外的业务逻辑。

  • 【强制】每个表设置is_del（0为未删除，1为删除）标记位字段，设置为非空，默认为0的字段属性，生产环境不允许物理删除。特殊表再议

  • 【强制】表和列定义的时候必须加上comment，并能精确描述表和列的含义。类型、状态等字段必须明确给出各个值代表的含义；金钱等计量字段必须给出精确的计量单位；外键字段必须明确给出关联的表和字段

  • 【强制】若需要JOIN的字段（连接键），字段名称、数据类型、长度和单位必须保持绝对一致，避免隐式转换

  • 【强制】禁止使用TEXT、BLOB类型（大文本、大文件、大照片存放在文件系统），可以把文件放到文件服务器中，数据库只存url

  • 【强制】不推荐使用enum，set。因为它们浪费空间，且枚举值写死了，变更不方便。推荐使用tinyint或smallint

  • 【强制】如果有业务流转的加字段：业务流水号

  • 【强制】如果一次操作多张表需要查看修改或者回退操作的，加操作流水号

  • 【强制】禁止创建外键约束，外键约束由应用程序控制。外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，影响sql 的性能，甚至会造成死锁。

  • 【强制】排序字段都不允许为空，并设置默认值。

• 字段设计规范

  • 【强制】字符串类型一律使用VARCHAR类型，对于明确长度的建议使用char，如身份证号等

  • 【强制】禁止使用TEXT、BLOB类型。会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

  • 【建议】字段定义为NOT NULL并且提供默认值。null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化；需要更多的存储空；只能采用is null或is not null，而不能采用=、in、='2017-02-15'

    -- 正确的写法是：
    SELECT uid FROM t_user WHERE day>= xxxfunc ('2017-02-15 00:00:00')

  • 【强制】禁止使用OR条件。使用IN或者UINON代替

  • 【强制】禁止大表使用JOIN查询，禁止大表使用子查询。极大影响数据库性能

  • 【强制】禁止负向查询，以及%开头的模糊查询。

    a）负向查询条件：NOT、!=、、!、NOT IN、NOT LIKE等，会导致全表扫描
    b）%开头的模糊查询，会导致全表扫描

  • 【强制】使用IN不能超过200

  • 【建议】UNION ALL 代替 UNION操作。

  • 【建议】order by的顺序尽量与索引保持一致

  • 【强制】大批量更新凌晨操作，避开高峰