数据库设计：透过现象看本质

﹏ヽ暗。殇╰゛Y 2022-12-09 05:28 115阅读 0赞

### 前言 ###

需求分析完成后就确定了产品的功能结构，在此基础上进行数据库设计，将每个具体的功能数据化，为接口文档的设计和代码设计做铺垫，本篇将带领大家完成 **Keler 云笔记**项目的数据库设计。

### 设计原则 For what ###

数据是所有业务的基础，没有数据，业务也就没法进行下去了；数据更是业务的灵魂，所有的业务操作都是为了得到期望的数据，没有了数据，业务操作也就没有了意义。因此在编写代码前，需要先将数据设计完毕，一个好的数据库设计应该有这样的特点：数据完整、操作灵活、有可扩展性。

#### **数据完整** ####

数据完整是对数据库设计的基本要求，要求存储数据的精确性、可靠性，要求存储的一组数据有意义。

数据有意义包含：

1.  字段名有意义，能根据字段名知道该字段要存储什么样的数据，必要时要加上合适的字段说明；
2.  字段的存在有意义，该字段的存在是必要的，是其他字段无法替代的；
3.  数据表中的所有字段组合在一起有意义，能实现一个或多个业务的需要。

好的表设计，往往查询一到两个表就能得到一个业务所需要的数据。如果你设计出来的数据库在使用时发现：一个业务要查询三四张表，那么就要认真考虑一下你是不是来给程序猿们作对的。

数据完整还包括约束的完整性和用户定义的完整性，如：有适当的主键和索引、有适当的冗余字段、有适当的存储过程和清理机制等。

#### **操作灵活** ####

操作灵活是指数据间耦合度低，使用起来灵活方便，不会出现一个表又当爹又当妈的情况，具体说明我总结了如下几点：

*  将不是强关联的数据分开存储
 *  将高频次修改的数据和低频次修改的数据分开存储
 *  将高访问量的数据和低访问量的数据分开存储
 *  将记录数据和业务数据分开存储
 *  将历史数据和应用数据分开存储

当数据表之间的耦合度降低到合适的程度后，既能保证数据的完整性，又能避免多个业务交叉操作同一张表的场景，降低了业务逻辑的复杂度和出错的几率。

#### **有可扩展性** ####

扩展性是指设计数据库时不要仅仅只是为了满足当下要求，要预见到未来可能出现的变化或者使用中可能出现的问题，一个设计经验比较足的 DBA 通常会考虑到这些：

*  设计表时考虑下该表的数据在删除时有没有需要找回数据的场景，如果有，就设计成可以软删除的表（加一个 isDelete 的字段）
 *  如果一个表数据可能会有多人修改，添加字段分别记录最后一次修改人、最后一次修改时间，用以在出现意外时辅助定位原因
 *  如果有登录注册功能，设计一个登录日志表、注册日志表，用以出现相关问题时的定位及后续可能使用的日活、日增长量等统计工作

### 数据库设计 How we do it ###

明确好设计原则后，我们来做云笔记项目的数据库设计。数据库设计工作往往是在需求分析完毕之后开始根据功能树和业务逻辑关系来进行的，下图是在上一篇需求分析中完成的功能树：

![功能树][cea58a30-71eb-11ea-bf08-1f004b4222a3]

仔细分析功能树，发现它已经将功能模块分为了用户功能、管理员功能两大类，其中：用户功能又可细分为用户信息和内容区（也就是笔记相关）两类。那么我们在设计数据库时也可以按照用户信息、笔记、管理员功能这三部分来做。

### 用户信息 Users ###

从功能树上可以看到，与用户信息相关的功能就是登录注册、用户名片相关。按照我们上面说的设计原则，可以将其分为：

*  用户基本信息表
 *  存储用户账号、密码、类型标示等基本信息
 *  修改频率低
 *  主要用于登录和注册功能
 *  用户名片表
 *  存储用户昵称、头像等名片信息
 *  修改频率高
 *  主要用于用户名片相关的功能
 *  邮件发送记录表
 *  存储邮件验证码发送记录
 *  单独的业务逻辑，修改频率低
 *  用于用户邮箱注册、使用邮箱验证码登录和室友邮箱验证码找回密码的功能

具体设计如下。

#### **用户基本信息表 user\_info** ####

create table user_info
    (
     id int auto_increment primary key,
     type int not null comment '用户类型',
     password varchar(200) null comment '密码',
     email varchar(200) not null comment '注册邮箱',
     createTime datetime null,
     status int null comment '用户账号状态，如账号锁定、未激活等',
     isDelete int null,
     updateTime datetime null,
     updateUserId int null
    )comment '用户信息表';
    create index user_info_index_email on user_info (email);
    create index user_info_index_status on user_info (status);
    create index user_info_index_type on user_info (type);

#### **用户名片表 user\_card** ####

create table user_card
    (
     userId int primary key,
     nickName varchar(200) null,
     email varchar(200) null comment '展示在名片上的邮箱地址',
     imgUrl varchar(200) null comment '头像原图',
     thumUrl varChar(200) null comment '头像缩略图',
     createTime datetime null,
     updateTime datetime null,
     updateUserId int null
    )comment '用户名片表';

#### **邮件发送记录表 email\_log** ####

create table email_log
    (
     id int auto_increment
      primary key,
     type int not null comment '邮件发送类型,不能为空',
     email varchar(50) not null comment '收件人，不能为空',
     title varchar(200) not null comment '邮件标题，不能为空',
     content varchar(500) not null comment '邮件内容，不能为空',
     code varchar(50) null comment '验证码',
     result varchar(500) null comment '发送结果描述，如：发送失败的原因等',
     statusCode int not null comment '发送状态',
     createTime datetime not null comment '发送时间',
     isUsed int null comment '验证码是否已使用'
    );
    create index email_log_index_email on email_log (email);
    create index email_log_index_type on email_log (type);

在 **email\_log** 的设计时，要考虑到灵活性：对于验证码邮件来说，重要的是里面的验证码，因此可以为验证码单独设计一个字段保存。这样，在邮件内容里面有验证码，是拼接好的完整邮件内容，以展现给用户；**code** 字段单独存储一个验证码，方便系统取用。

### 笔记 Notes ###

从功能树上看到，与笔记相关的业务有：笔记本、笔记内容的增删改查，因此数据表可以分为：

*  笔记本表
 *  存储笔记本名称、所属用户、笔记本说明等与笔记本相关的信息
 *  数据量小，适合频繁读写
 *  用于显示用户笔记本列表的功能
 *  笔记表
 *  存储笔记内容、笔记类型等笔记详情
 *  数据量大（一篇笔记可能有几千、几万字）
 *  用于笔记内容修改、预览等功能

具体设计如下。

#### **笔记本表 notes\_info** ####

create table notes_info
    (
     id int auto_increment primary key,
     userId int null comment '用户Id',
     title varchar(200) null comment '标题',
     subTitle varchar(200) null comment '副标题',
     createTime datetime null comment '创建日期',
     noteNumber int null comment '笔记数量',
     sort int null comment '排序',
     isDelete int null,
     updateTime datetime null,
     updateUserId int null
    )comment '笔记本';
    create index notes_info_index_userId on notes_info (userId);

#### **笔记表 note\_info** ####

create table note_info
    (
     id int auto_increment primary key,
     userId int null comment '用户ID',
     notesId int null comment '笔记本Id',
     title varchar(200) null comment '标题',
     contentMD mediumtext null comment 'MD内容',
     content mediumtext null comment '内容',
     sort int null comment '排序',
     createTime datetime null,
     isDelete int null,
     updateTime datetime null,
     updateUserId int null
    )comment '笔记';
    create index note_info_index_notesId on note_info (notesId);
    create index note_info_index_userId on note_info (userId);

在这两张表的设计中，有一点需要注意：**不要使用外键，外键的关系在应用层实现**。

这么说是因为：

1.  使用外键会**降低系统性能**：使用外键的表在 **Insert**、**Update**、**Delete**操作时都要额外去查询对应的表中相关数据，增加数据库负担、增加系统响应时间、降低系统性能
2.  使用外键**更容易造成数据库死锁**：由于每次操作都要额外查询对应的表，就需要多获取一个锁，在高并发的场景中更容易死锁，引起并发问题
3.  使用外键**不利于系统的扩展性**：外键、触发器、存储过程等可以在应用层控制的逻辑尽量在应用层控制，这样在数据迁移、数据扩容的过程中工作量会大大减少

另外，如果频繁使用外键、触发器、存储过程等数据库的判断逻辑，也就意味着对数据库的性能安全性需要更大的依赖，可能要专门的 DBA 维护数据库，无形中增加了小企业小项目的维护成本和复杂度。

### 管理员 Administrator ###

通过功能树可以看到，在本项目中，管理员功能主要是可以查看用户统计信息，为了实现用户增长量和用户活跃度的统计，设计注册日志表和登录日志表，具体设计如下。

#### **注册日志表 register\_log** ####

create table register_log( 
    id int auto_increment primary key,
    userId int comment '用户ID', 
    ip varchar(50) comment '注册IP', 
    createTime datetime comment '注册时间' 
    ) comment '注册日志表';

#### **登录日志表 login\_log** ####

create table login_log( 
    id int auto_increment primary key,
    userId int comment '用户ID', 
    ip varchar(50) comment '登录IP', 
    createTime datetime comment '登录时间' 
    ) comment '登录日志表';

你会发现，两张表的数据结构一致，但是因为如下原因，将其分开存储：

1.  对应的业务不同：注册日志表对应用户增长量；登录日志表对应用户活跃度。
2.  数据量和使用频率不同：注册日志表数据量小，和用户数量相当，每次有新用户注册才会增加数据；登录日志表数据量大，每次用户登录都会新增数据。
3.  扩展性需要不同：注册日志表随着用户量的增加，扩展的需求不大；登录日志表可能随着用户量的增加，数量产生几何倍数增加，很有可能产生分库分表的需求。

另外，注册日志表也和用户基本信息表的内容有高度重合，将其分开设计的原因如下：

*  数据性质不同
 *  用户基本信息表作为用户基本信息的维护，可能有修改、删除的操作
 *  注册日志表作为用户的注册日志，没有修改、删除的可能性
 *  使用场景不同
 *  用户基本信息表在用户登录、注册时使用，作为重要的判断依据
 *  注册日志表主要用于数据统计

这种物理隔离的设计能保证在大用户量的场景中不会因为频繁查询统计数据而影响用户登录、注册的性能，也增强了数据的扩展性，为以后大型分布式系统做准备。

### 小结 ###

本篇带领大家从数据完整性、操作灵活性、可扩展性等角度考虑，完成 **Keller 云笔记**项目的数据库设计。数据作为所有操作的基础，合理的数据库设计可以使后续的开发过程快捷顺畅。

[cea58a30-71eb-11ea-bf08-1f004b4222a3]: /images/20221123/3fd890c14f33490f8f7049bf618d5cee.png