小皮博客 | Xiaopi's Blog

93-数据库设计指南

干货来了, 数据库设计最佳实践.

基础部分

  • 表存储引擎必须使用InnoDB。
  • 表字符集默认使用utf8,必要时候使用utf8mb4。
  • 数据库,表,字段字符集一定要统一,统一字符集可以避免由于字符集转换产生的乱码。
  • 不允许使用存储过程,视图,触发器,Event。
  • 禁止在数据库中存储大文件,例如照片,可以将大文件存储在对象存储系统,数据库中存储访问路径。
  • 禁止在线上环境做数据库压力测试。各个环境的数据库应该隔离。
  • 事物尽可能的小,不要在代码层面直接一个大的transaction。
  • 我们不删除记录,我们总是用删除标记(软删除)来进行”删除”操作。
  • UUID (),USER () 这样的 MySQL INSIDE 函数对于复制来说是很危险的,会导致主备数据不一致,所以请不要使用。如果一定要使用 UUID 作为主键,让应用程序来产生。
  • 控制单表数据量,最佳实践是在500万条以下,应该在一开始就考虑: 可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小。
  • 字段约束和索引应该分开来设计。

命名规范

  • 库名,表名,列名必须用小写,采用下划线分隔。因为默认情况下,mysql对大小写敏感,mysql数据库本质上是linux系统下的一个文件,而linux系统是大小写敏感的。
  • 库名,表名,列名必须见名知义,长度不要超过32字符
  • 建议: 字典表以 ${domain缩写dict}为前缀。如 cm_dict_, 关联表以${domain缩写relation}为前缀。
  • 有数据库对象名称禁止使用mysql保留关键字。
  • 所有存储相同数据的列名和列类型必须一致,比如user表中的id和order表中的user_id。

字段设计及约束

  • 禁止对数据库表字段进行删除和修改的操作。
  • 不要使用联合主键,目前我们规范中推荐的是使用业务无关的自增主键。
  • 在设计时尽量包含两个日期字段:create_time (创建日期),update_time (修改日期) 且 非空,对表的记录进行更新的时候,必须包含对 update_time 字段的更新。
  • 建议将大字段,访问频度低的字段拆分到单独的表中存储,分离冷热数据。
  • 设计表时尽量用“小数据类型”,比如尽量避免text,blob,如果必须使用,应该在单独的表中进行存储,实际上更应该存储到别的存储系统并且在mysql中保存链接即可。
  • 禁止使用外键,如果要保证完整性,应由代码来实现。
  • 根据业务区分使用tinyint/int/bigint,分别会占用1/4/8字节。
  • 字段长度比较固定,或者长度近似的业务场景,适合使用char,能够减少碎片,查询性能高。
  • 字段长度相差较大,或者更新较少的业务场景,适合使用varchar(n),能够减少空间。其中n表示字符数而不是字节数。
  • 建议: 必须把字段定义为NOT NULL并设默认值。
  • 使用varchar(20)存储手机号,不要使用整数。或者char(20)。
  • 使用INT UNSIGNED存储IPv4,不要用char(15)。
  • 使用TINYINT来代替ENUM。
  • 不要在表中建立预留字段,新增字段的代价并不是很大。
  • 对于非负型的数据(如自增ID、整型IP)来说,要优先使用无符号整型来存储。
  • 同财务相关的金额类数据必须使用decimal类型,可以保证不出现精度丢失。
  • 手机号,身份证号等可以作为索引,可以建立唯一约束,但是不要作为主键。

索引规范

  • 在select,delete,update的where从句中的列,包含在order by,group by,distinct字段中的列和多表join的关联列建立索引
  • 唯一索引使用uniq_[字段名]来命名。
  • 非唯一索引使用idx_[字段名]来命名。
  • 单张表索引数量建议控制在5个以内。
  • 索引会影响写的性能。
  • 异常复杂的查询需求,可以选择ES等更为适合的方式存储。
  • 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数)。
  • 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO性能也就越好)。
  • 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。
  • 组合索引字段数不建议超过5个。
  • 不建议在频繁更新的字段上建立索引。
  • 非必要不要进行JOIN查询,如果要进行JOIN查询,被JOIN的字段必须类型相同,并建立索引
  • 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)。
  • 对于非主键唯一的int类型字段,没有将其单独设置为索引比必要。设置索引后反倒增大了内存的占用,检索时的性能也不见得会有提升。
  • 对于频繁的查询优先考虑使用覆盖索引(只在必要且数据量较大频繁使用的场景)。覆盖索引:就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引。

SQL语句规范

  • 禁止使用select *,只获取必要字段。
  • insert必须指定字段,禁止使用insert into T values()。指定字段插入,在表结构变更时,能保证对应用程序无影响。
  • 不允许在where条件列使用函数。
  • 建议: 不要负向查询以及%开头的模糊查询。
  • 同一个字段上的OR必须改写为IN,IN的值必须少于50个。
  • 应用程序必须捕获SQL异常,并且记录日志。
  • SQL事物不能设计太大,比如一次性提交10W条insert,一般来说insert事物的话,5K~1W来做批处理就可以了。
  • 过大的SQL应该拆分成小的,减少锁的时间。同时也更容易运用CPU的性能。
  • update时,where语句尽量要走索引,不然会全表扫描,一般情况下1G的数据至少需要10S。
  • 禁止使用order by rand 进行随机排序。

操作建议

  • 进行非查询操作时,尽量使用begin来开启事务。否则可能因为少写了where条件或者其他误操作无法回滚。
  • 应用程序中的数据库账号应该只赋予所需的最小权限。最佳实践是不分配DELETE的权限。
  • 禁止从开发环境,测试环境直连生产环境数据库。
  • 合理使用临时表,备份表,报表,而不是直接操作原有数据表。

设计checklist

  • 是否详细定义了数据库的组织结构和内容?
  • 是否有合理的数据库规范。
  • 是否按照组件进行了合理的划分。
  • 是否指出了所用关键的业务规则,并描述其对系统的影响?
  • 是否有不是必须的实体?
  • 是否梳理清楚了实体与设计之间的关系。
  • 基本表的性质: 在有必要的场景,是否设计了合适的结果表(存储统计数据)。
  • 原子性。基本表中的字段是不可再分解的。
  • 原始性。基本表中的记录是原始数据(基础数据)的记录。
  • 演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。
  • 稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。
  • 是否在设计范式和性能之间做了良好的权衡,并且在此基础上做了合理的字段冗余。
  • 是否识别与正确处理多对多的关系。
  • E-R图是否满足: 结构清晰、关联简洁、实体个数 适中、属性分配合理、没有低级冗余。

版权声明

本文标题:93-数据库设计指南

文章作者:盛领

发布时间:2019年11月22日 - 11:53:21

原始链接:http://blog.xiaoyuyu.net/post/17a93bf0.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

如您有任何商业合作或者授权方面的协商,请给我留言:sunsetxiao@126.com

盛领 wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!