项目开发-数据库篇(设计规范)

在项目开发中,数据库的设计规范性是项目协调、团队合作的前提。例如,数据库中表、字段统一命名规范,在设计合理的同时增加数据库的可维护性。

数据库命名规范

数据库、表及字段的命名,需要“见名知意”。与此同时,命名及字段的类型和注释也会影响由工具生成的数据库文档、接口文档等,因此,在数据库设计过程中,需要遵守约定的命名规范。(数据库中的设计规范)

  • 可用字符

    数据库、表、字段等所有名称的可用字符范围为:A-Z,a-z,0-9和_下划线。数据库及表名均不允许使用数字,而字段名除特殊情况外不允许使用数字。

    为了方便阅读和操作,数据库、表和字段名一般不使用关键字保留字。

  • 命名方式

    数据库、表、字段等所有名称需要清晰明了,使用含义对应的英文单词、英文短语或相应的缩写,禁止使用汉语拼音,且均使用单数名。

    Oracle表、字段等名称统一使用大写,单词间用_下划线分隔;SQLServer数据库、表等名称采用Pascal命名法,字段名称采用Camel命名法,大小写字母混排;MySQL数据库、表、字段等名称统一使用小写,单词间用_下划线分隔。

    临时库、表以temp为前缀,日期为后缀。备份表以bak为前缀,日期为后缀。

    • PS 命名方法

    匈牙利命名法。由微软的一位匈牙利程序员Charles Simonyi提出,相对复杂,首字母小写,基本原则是:变量名=属性+类型+对象描述,其中每一对象的名称都要求有明确含义,可以取对象名字全称或名字的一部分。匈牙利命名法主要在C或C++这种面向过程的程序语言中使用。

    Camel命名法。即骆驼式命名法,首字母小写,采用该命名法的名称看起来就像骆驼的驼峰一样高低起伏。Camel命名法有两种形式:
    第一种是混合使用大小写字母,例如englishName、fartherCode。在Java中,属性名和方法名一般都采用这种命名方式,在C#中只有属性名采用这种命名方式,SQLServer中字段的命名也采用这种方式。
    第二种是单词之间加下划线,例如english_name、farther_code。Oracel和MySQL表、字段的命名都采用这种方式,不过Oracle全部使用大写字母,MySQL全部使用小写字母。在Java、C#,甚至是在JavaScript中,所有的常量,都使用这种命名方式,不过和Oracle表字段的命名方式一样要全部使用大写字母。

    Pascal命名法。即帕斯卡命名法,与Camel命名法类似,不过是首字母大写。在C#中,类名和方法名一般采用这种命名方式,在Java中类名一般采用这种方式。SQLServer中数据库、表的命名也采用这种方式。

  • 长度限制

    数据库 表名 字段名
    MySQL 64 64
    SQLServer 128 128
    Oracle 30 30
    Acess 30 30
    DB2 128 128
    Informix 18 18

    若表名和字段名的长度在长度限制内,但是仍然过长,生成的相关类名变量名都不利于后期的编写,因此需要将表名和字段的最终长度控制在大约30个字符以内。同时,在名称超过一定字符就使用缩写。

    缩写的规则一般为:
    字典中单词的缩写,eg:December -> Dec ;
    删除单词中的重复字母和单词元音,eg:Error -> Err

数据库设计规范

  • 字符集(Character Set)和字符序(Collation)规范

    字符集:给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合。

    字符序:指定数据集如何排序、及字符串间的比对规则。

    即,字符集定义了可以使用的字符,对应的字符序定义了字符之间的关系(参考)。并且字符序依赖字符集,eg:把gb18030_chinese_ci作为字符序,就要求字符集是gb18030,而不能是utf8mb4。

    可以对表进行字符集的设置,也可以单独对某个字段进行字符集的设置,优先级从高到底可分为四种:服务器层、数据库层、表层、字段层,真正决定性因素是在字段层,如果没有指定则默认从上一层继承。

    数据库、表、字段的字符序也可逐级覆盖,类似四种字符集设置方式间的优先级关系。

    建议数据库和表的字符集统一为utf8(MySQL为utf8mb4,MySQL中的utf8,更准确的名字是utf8mb3,一个字符最多使用3个字节来存储),字符序则根据实际需求选择,不同字段可以使用不同字符序(eg:大小写敏感度不同)。

    utf8是国际通用编码,几乎所有网络应用都使用了Unicode字符集。

    数据库和表的字符集统一,兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效。

    针对MySQL字符集为utf8mb4时,具体讨论utf8mb4_0900_ai_ci和utf8mb4_general_ci。

    utf8mb4_0900_ai_ci:中间的0900,它对应的是Unicode 9.0的规范,ai表示accent insensitivity,也就是“不区分音调”,而ci表示case insensitivity,也就是“不区分大小写”。

    MySQL 8.0之后,utf8mb4对应的默认字符序不再像之前版本一样是是utf8mb4_general_ci,而是统一更新成了utf8mb4_0900_ai_ci。

  • 表设计规范

    • 使用Innodb存储引擎,支持事务,行锁,高并发下性能更好

    • 表和字段都要有comment注释字段

    • 关于主键,命名为 id,类型为 int 或 bigint,且为 auto_increment

    • 尽量控制单表数据量的大小

      建议控制在500W以内,过大会造成修改表结构,备份,恢复都会有很大的问题。可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小

    • 谨慎使用分区表。分区表在物理上表现为多个文件,在逻辑上表现为一个表。谨慎选择分区键,跨分区查询效率可能更低 建议采用物理分表的方式管理大数据

    • 尽量做到冷热数据分离,减小表的宽度

    • 禁止在表中建立预留字段。预留字段的命名不容易见名知意,存储类型无法确认,对预留字段的修改会对表进行锁定

    • 禁止在数据库中存储图片,文件等大的二进制数据

    • 禁止在线上做数据库压力测试

    • 不得使用外键与级联,一切外键概念必须在应用层解决

      外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

  • 字段设计规范

    • 优先选择符合存储需要的最小的数据类型

      列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的IO次数也就越多, 索引的性能也就越差。

      eg: 对于非负型的数据(如自增ID、整型IP)来说,要优先使用无符号整型来存储

    • 尽可能把所有列定义为NOT NULL

      索引NULL列需要额外的空间来保存,所以要占用更多的空间;
      进行比较和计算时要对NULL值做特别的处理

    • 尽量不要定义 DEFAULT 值

      因为业务逻辑下放到了数据库层面,这是一种隐式行为,不利于开发和维护

    • 反范式设计

      当数据不怎么变化时,把经常需要 join 查询的字段,在其它表里冗余一份。如 username 属性在 user_account,user_login_log 等表里冗余一份,减少 join 查询。

    • 使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

      TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

      TIMESTAMP 占用4字节和INT相同,但比INT可读性高.

      超出TIMESTAMP取值范围的使用DATETIME类型存储。

      经常会有人用字符串存储日期型的数据(不正确的做法):

      缺点1:无法用日期函数进行计算和比较

      缺点2:用字符串存储日期要占用更多的空间

    • 同财务相关的金额类数据必须使用decimal类型

      非精准浮点:float,double ;
      精准浮点:decimal

      Decimal类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

    • 避免使用ENUM类型

      修改ENUM值需要使用ALTER语句

      ENUM类型的ORDER BY操作效率低,需要额外操作

      禁止使用数值作为ENUM的枚举值

    • 避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据

      建议把BLOB或是TEXT列分离到单独的扩展表中

      Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。

      而且对于这种数据,Mysql还是要进行二次查询,会使sql性能变得很差,但是不是说一定不能使用这样的数据类型。

      如果一定要使用,建议把BLOB或是TEXT列分离到单独的扩展表中,查询时一定不要使用select * 而只需要取出必要的列,不需要TEXT列的数据时不要对该列进行查询。

      TEXT或BLOB类型只能使用前缀索引

      因为MySQL对索引字段长度是有限制的,所以TEXT类型只能使用前缀索引,并且TEXT列上是不能有默认值的。

版权声明:本博客所有文章除特别声明外,均采用 CC BY 4.0许可协议,转载请注明出处
本文链接:https://blog.redamancy.tech/technique/14