数据库设计指南 - 整理的数据库设计中注意的问题

Dear 丶 2022-11-29 11:26 167阅读 0赞

**目录**

一、基础部分

二、命名规范

三、字段设计及约束

四、索引规范

五、SQL语句规范

六、操作建议

七、设计checklist

--------------------

# 一、基础部分 #

*  表存储引擎必须使用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图是否满足: 结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余？