2020-08-30

数据库规范

标准化和规范化

数据的标准化有助于消除数据库中的数据冗余。标准化有好几种形式，但 Third Normal Form (3NF) 通常被认为在性能、扩展性和数据完整性方面达到了最好平衡。简单来说，遵守 3NF 标准的数据库的表设计原则是：

第一范式 (1NF) 无重复的列

所谓第一范式 (1NF) 是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式 (1NF) 中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。说明：在任何一个关系数据库中，第一范式 (1NF) 是对关系模式的基本要求，不满足第一范式 (1NF) 的数据库就不是关系数据库。
第二范式 (2NF) 属性完全依赖于主键 (每个表要定义主键，如无意义自增长 id)

第二范式 (2NF) 是在第一范式 (1NF) 的基础上建立起来的，即满足第二范式 (2NF) 必须先满足第一范式 (1NF)。第二范式 (2NF) 要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。例如员工信息表中加上了员工编号 (emp_id) 列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。

第二范式 (2NF) 要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。
第三范式（3NF）属性不依赖于其它非主属性 (表中最多包含其他表中的主键，即外键)

满足第三范式 (3NF) 必须先满足第二范式 (2NF)。简而言之，第三范式 (3NF) 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号 (dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式 (3NF) 也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

字符集

数据库、表都必须使用 UTF-8 或者 UTF8MB4 字符集。

CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

命名规范

采用 26 个英文字母 (只能用小写) 和 0-9 这 10 个自然数，加上下划线 _ 组成,共 37 个字符，不能出现其他字符 (注释除外)。

注意事项:

命名都不得超过 30 个字符的系统限制，变量名的长度限制为 29 (不包括标识字符@)
数据对象、变量的命名都采用英文字符，禁止使用中文命名，绝对不要在对象名的字符之间留空格
小心保留词，要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

禁用保留字

禁用保留字，如 desc、range、match、delayed 等，参考官方保留字，如果非要用，字段名使用反引号扩起来:

1	SELECT `desc`, `match` FROM question

Keywords and Reserved Words in MySQL 5.7

数据库名

数据表名使用小写英文以及下划线组成，尽量说明是哪个应用或者系统在使用的。

库名与应用名称尽量一致。

表名字段名

规则:

表名不使用复数名词

表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。
表名、字段名必须使用小写字母或数字
单词间使用下划线 _ 分隔
使用常用英语命名，尽量不要使用编码或者拼音首字母缩写
和其他表关联的列名要以对应表名为前缀，例如 blog 里关联了 author 的，所以需要一列 author_id
表与表之间的相关联字段要用统一名称

禁止:

禁止出现数字开头
禁止两个下划线中间只出现数字

正例：getter_admin，task_config，level3_name

反例：GetterAdmin，taskConfig，level_3_name

是与否的字段

表达是与否概念的字段，也即是 Boolean，有些公司要求必须使用 is_xxx 的方式命名，为了 ORM 自动映射，还是不用 is_ 开头了吧，数据类型是 unsigned tinyint (1 表示是，0 表示否)。

表必备字段

表必备三字段：tablename_id, created_at, updated_at:

tablename_id (推荐使用 Snowflake 算法生成 ID):
- Snowflake 算法生成 ID: uuid 虽然能保证分布式唯一，但不是自增长的，容易造成存储离散，降低效率，Snowflake 能生成不重复的、有序的 ID
- tablename_id + table_name_uuid:
  - tablename_id: 自增长 id，无意义的主键，为了提高插入性能，如果用 uuid 作为主键，新记录的插入会比较分散，效率不高
  - table_name_uuid 为不重复列，表之间使用这一列进行关联而不是 id，uuid 便于同一个软件的不同部署的数据迁移合并（一般 char(36) 就够了，为了保险起见，使用 64 吧）

created_at: 为 datetime 类型，创建时自动赋值
updated_at: 为 timestamp 类型，不可以自定义值，MySQL 在记录更新时会自动更新它的值

参考 http://qtdebug.com/mysql-datetime-timestamp/

虽然这样会占用多点空间，但是空间是问题吗？服务器 SSD 是问题吗？

字段类型

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

正例: 人的年龄用 unsigned tinyint（表示范围 0-255，人的寿命不会超过 255 岁）；海龟就必须是 smallint，但如果是太阳的年龄，就必须是 int；如果是所有恒星的年龄都加起来，那么就必须使用 bigint。

规则: 用尽量少的存储空间来存数一个字段的数据

比如能用 int 的就不用 char 或者 varchar
能用 tinyint 的就不用 int
能用 varchar(20) 的就不用 varchar(255)
有多国语言相关的时间戳字段尽量用 bigint 型，如 created_at: 表示从’1970-01-01 08:00:00’开始的int秒数

小数类型

小数类型为 decimal，禁止使用 float 和 double。float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

字符串

如果存储的字符串长度几乎相等，使用 char 定长字符串类型
如果不定，但是又不太长，使用 varchar

varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
如果是比较长的，使用 text，最大长度 65535 个字元 (2^16-1)
mediumtext 最大长度 16777215 个字元 (2^24-1)
longtext 最大长度 4294967295 个字元 (2^32-1)

字段冗余

字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循：

不是频繁修改的字段
不是 varchar 超长字段，更不能是 text 字段

正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取

分库分表

单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

反例：某业务三年总数据量才 2 万行，却分成 1024 张表，问：你为什么这么设计？答：分 1024 张表，不是标配吗？

逻辑删除

数据敏感的记录，增加数据删除标记列 deleted，禁止操作时直接从数据库删除记录造成灾难。

文档规范

所有数据库设计要写成文档，文档以模块化形式表达，大致格式如下，对每一列最好都有注释：

#-------------------------------------------
# 表名：question
# 作者：二狗
# 日期：2018-04-01
# 版本：1.0
# 描述：保存题目
#------------------------------------------
DROP TABLE IF EXISTS question;

CREATE TABLE question (
    id bigint(20) unsigned NOT NULL COMMENT '题目 ID',
    type varchar(8) DEFAULT ''      COMMENT '题目类型',
    content mediumtext              COMMENT '题目内容：题干+选项',
    analysis mediumtext             COMMENT '题目解析',
    answer text                     COMMENT '题目答案',
    demand varchar(32) DEFAULT ''   COMMENT '教学要求',
    score double DEFAULT 0          COMMENT '题目分值',
    difficulty int(11) DEFAULT 0    COMMENT '题目难度',
    original_id varchar(64) DEFAULT  ''        COMMENT '题目在乐教乐学数据库中的 ID',
    subject_code varchar(64) DEFAULT ''        COMMENT '题目的科目编码',
    knowledge_point_code varchar(8) DEFAULT '' COMMENT '题目的知识点编码',
    knowledge_point_id bigint(20) DEFAULT 0    COMMENT '题目的知识点 ID',
    marked tinyint(4) DEFAULT 0                COMMENT '是否被标记过，0 为未标记，1 为已标记',
    created_at datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP                             COMMENT '创建时间',
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB;

SQL 语句规范

所有 SQL 关键词全部大写，比如 SELECT，UPDATE，FROM，ORDER BY 等

1	SELECT COUNT(*) FROM `user` WHERE username = 'alex' AND password='Passw0rd';

外键约束

外键约束用来保证数据完整性的，这一点我很赞同，但是不建议在数据库表中加外键约束，因为在数据表中添加外键约束，会影响性能 (例如: 每一次修改数据时，都要在另外的一张表中执行查询)，数据迁移的时候也是很头疼的问题。

建议：在应用层，也就是代码层面，来维持外键关系。

存储过程

现代编程不推荐使用存储过程，数据库的核心的存储数据，业务计算在代码中实现。