0%

MySQL数据库开发规范

本文描述数据库表的设计流程以及DDL和DML的设计规约;

表设计流程

  1. 表设计:用StarUML等UML建模工具中的ER图来设计表结构;
  2. 表创建:导出DDL语句(yyyyMMddHHmmss_.sql)至项目的Flyway数据迁移目录,启动项目完成表的初始化;
  3. ORM代码生成
    • Idea中安装插件free-mybatis-plugin
    • 在database视图,连接db,选择生成的表右击选择mybatis-generator,在gui界面配置package和目录,完成生成DAO层的DaoMapper接口和Mapper.xml文件;

DDL设计规约

存储引擎

通常不需要手动设置数据库存储引擎(如innodb,myisam),按数据库默认值即可;

  • 原因:客户的数据库可能是基于MySQL/MariaDB封装了自己的存储引擎,如果指定了会导致flyway初始化异常;

字符集

新系统的数据库本身库、表、列所有字符集必须保持一致,使用utf8mb4;

  • 示例:create table(...) default charset = utf8mb4
  • 原因:utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符;采用utf8mb4编码的好处是存储与获取数据的时候,不用再考虑emoji字符的编码与解码问题;
  • 注意:utf8mb4的最低mysql版本支持版本为5.5.3+;

字段类型和长度

  • 整数类型
    • int:次数,状态等类型字段存储
  • 小数类型
    • decimal:禁止使用float和double。float和double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储。
  • 布尔类型
    • unsigned_tinyint(1):1表示true,0表示false
  • 文本类型
    • 短字符:varchar(64),如name,code,id
    • 中字符:varchar(190),如description
      • 需要建索引的字段,长度最好不超过190,因为utf8mb4编码单个索引最大长度是767字节(767/4约等于191)
    • 短文本:text,最大64kb
    • 中文本:mediumtext,最大16MB,如存储HTML页面内容
  • 时间类型
    • 记录经常变化的时间:timestamp,时间范围(1970-2038),如更新/创建/发布/日志时间/注册时间等,并且是近来的时间,够用,区自动处理,比如说做海外购或者业务可能拓展到海外;
    • 记录固定时间:datetime,时间范围(1000-9999),如服务器执行计划任务时间/健身锻炼计划时间等,在任何时区都是需要一个固定的时间要做某个事情;
    • 自定义时间字段:bigint,不确定的时间范围存储;

命名规则

库名、表名、列名的修改代价很大,命名时需要慎重考虑

公共命名规则

  1. 【强制】表名、列表都必须有comment注释;
  2. 【强制】表名、列名只能使用字母、数字和下划线,禁止出现数字开头,一律小写;

库名

库名与应用名称尽量一致。

表名

  1. 【强制】表的命名规则:业务名_表的作用,如scheduler_job
  2. 【强制】表名带当前系统前缀,如mlp_,便于后续数据平台聚合使用;
  3. 【强制】表名不使用复数名词。

列名

  1. 【强制】列的命名规则:实体类型_字段含义,如user_name、user_id
  2. 【强制】FK外键保持原字段名,方便join;
  3. 【强制】字段名禁用数据库保留字;
  4. 表中所有字段必须都是NOT NULL属性,业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

索引名

  1. 【强制】主键索引:uk_字段名;
  2. 【强制】普通索引:idx_字段名;
  3. 【强制】唯一索引:uk_字段名;

表必备字段

  1. 【强制】{entity}_id:主键
  2. 【强制】create_time:记录创建时间,date_time,数据库默认值;
  3. 【强制】update_time:记录更新时间,date_time,数据库自动设置;
  4. is_deleted:unsigned_inyint(1),是否删除,需支持软删除的表必备;
  5. create_by:创建人,varchar(64),需权限控制的数据表必备;
  6. update_by:更新人,varchar(64),需权限控制的数据表必备;

DML设计规约

  1. 【强制】alter table:对于超过100W行的大表进行alter table,必须经过DBA审核,并在业务低峰期执行;
    • 因为alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响。
  2. 【强制】select或insert语句必须指定具体字段名称,禁止写成*
    • 因为select *会将不该读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但model层没有来得及更新的话,系统会报错;
  3. 【强制】除静态表或小表(100行以内),DML语句必须有where条件
  4. 【强制】where条件需用explain确认会使用索引;索引至少要达到 range 级别,要求是ref级别,如果可以是consts最好;
  5. 【强制】where条件中等号左右字段类型必须一致,且不要使用函数或表达式,否则无法利用索引;
  6. 【强制】where条件中禁止只使用全模糊的like条件进行查找,必须有其它等值或范围查询条件,否则无法利用索引。
  7. select语句不要使用union,推荐使用union all,并且union子句个数限制在5个以内;
    • 因为union all不需要去重,节省数据库资源,提高性能;