数据库设计指南整理的数据库设计中注意的问题
熊孩纸
阅读:688
2021-03-22 20:41:59
评论:0
一、基础部分
- 表存储引擎必须使用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图是否满足: 结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余?
声明
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。