项目开发-数据库篇（设计规范）
在项目开发中，数据库的设计规范性是项目协调、团队合作的前提。例如，数据库中表、字段统一命名规范，在设计合理的同时增加数据库的可维护性。
数据库命名规范
数据库、表及字段的命名，需要“见名知意”。与此同时，命名及字段的类型和注释也会影响由工具生成的数据库文档、接口文档等，因此，在数据库设计过程中，需要遵守约定的命名规范。(数据库中的设计规范)
可用字符
数据库、表、字段等所有名称的可用字符范围为：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 ；
精准浮点：decimalDecimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每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