数据库设计方法

在做后端开发之前是没有完整的接触过数据库的（非科班的遗憾），之后做移动开发一般都是把数据库做缓存使用（移动端的数据库使用是比较简单的），随着对后端学习的深入，逐渐发现后端的数据库设计是很重要的一个话题，数据库是整个应用的基石，需要考虑的场景涵盖的内容都会很复杂。所以近期又系统的学习了数据库相关的知识，遂有本文的总结。
历史

在数据库诞生之前使用最多的是文件系统，常用的格式是 CSV - CommaSeperatedValues 格式，典型的CSV文件：
Year,Make,Model
1997,Ford,E350
2000,Mercury,Cougar

这种数据格式以,号作为分隔符，可以用EXCEL之类的表格处理软件打开。使用文件系统存在的典型问题是：如果需要从表中查找到 Year 为 2000 这一行的数据，就需要把文件全部读入内存，然后在内存中做搜索（或者是开一个文件流，每次读一行检查一次）。
这种方式不利于大量内容的管理，所以有了数据库技术（偷懒了直接上wiki链接）：

1960s, navigational DBMS

1970s, relational DBMS
Integrated approach
Late 1970s, SQL DBMS
1980s, on the desktop
1990s, object-oriented
2000s, NoSQL and NewSQL

现在看来，最终遗世独立的只有两种：基于SQL的RelationalDBMS和NoSQL。前者有Oracle、SQLServer、DB2等大厂作品，也有MySQL、PgSQL等开源实现。后者细分为几类，面向KV存储的如Redis，面向列存储的如HBase，面向文档的MongoDb，这类数据库这几年在互联网领域也应用广泛。
关系型数据库-印象

以MySQL为例
关系型数据库的特点是表和表之间的关系。表存储的是一行一行的结构化数据，比如：
mysql> select * from user;
±--------±----------±---------±-----------±--------+
| user_id | user_name | password | last_visit | last_ip |
±--------±----------±---------±-----------±--------+
| 1 | admin | 123456 | NULL | NULL |
±--------±----------±---------±-----------±--------+

表与表之间是有关联的，这会通过外键来实现。上面的user表仅仅是一个孤零零的，没有任何关联的表。假如我们还需要记录用户的地址，那么可以再创建一个地址表，然后把地址表关联到用户表，这个例子可以体现表之间的关联关系。
// 创建一个新的表 addr - 记录地址信息
mysql> select * from addr;
±—±---------±-------±-------±-------+
| id | province | city | area | street |
±—±---------±-------±-------±-------+
| 1 | 江苏 | 苏州 | 吴中 | 1003 |
±—±---------±-------±-------±-------+

// 修改 user 表，添加一列 addr，这一列记录的是 addr 表的 id
mysql> select * from user;
±--------±----------±---------±-----------±--------±-----+
| user_id | user_name | password | last_visit | last_ip | addr |
±--------±----------±---------±-----------±--------±-----+
| 1 | admin | 123456 | NULL | NULL | 1 |
±--------±----------±---------±-----------±--------±-----+

在这个例子中，通过修改 user 表添加了一列 addr，并用这列记录 addr 表的 id，如此，如果需要查询 “admin” 的地址的话，可以先通过user表的addr 列拿到地址的索引，然后再在 addr 表中找到 id=1 的那一行获取地址。
这就是典型的关系型数据库，表是结构化的，表与表之间是有关联的。看到这里没有学过关系型数据库相关知识的同学可能会疑惑为什么不直接把地址的数据库存在user表中而是存在addr表再索引过来，这不是很麻烦吗？问题确实是“很麻烦”，但是这样做的一个直接好处是数据隔离的更清楚了，并且可以减少数据冗余。在关系型数据库的发展中，逐渐发展出一系列的最佳实践，这些“最佳实践”演化成关系型数据库设计的基本规则 - 范式（NormalForms）。
范式

关系型数据库中共有“六”种范式，每满足一级对数据库的设计要求都会拔高。范式的定义是非常晦涩的，常用的是前4种范式（第四种是也叫3.5NF），如下：

1NF - First Normal Form 符合1NF的关系中的每个属性都是原子的不可再分。

2NF - Second Normal Form 满足1NF，数据库表中的每列都完全依赖于主键。

3NF - Third Normal Form 满足2NF，要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

BCNF - Boyce–Codd Normal Form 满足3NF，主属性内部不能部分或传递依赖

对范式的理解，仅仅看上面的简短说明还是无法有切身体会的，网络上有很多人已经对此做过图文并茂的说明，可以参考：

知乎问题：解释一下关系数据库的第一第二第三范式？
数据库范式那些事

注：范式真的好难理解

关系

在关系型数据库中，弄清各个模块（或者叫实体或者叫表）之间的关系非常重要。关系型数据库中有三种基本关系：

1 - 1，比如一个国家对应一个首都
1 - N，比如一个分类下可以有多个商品，一个班级有多个学生，这种关系往往存在从属关系
N - N，比如学生和课程，一个学生可以选多门课，不同的学生也可以选择同一门课

对于这三种关系的理解往往并不像上面描述的那样简单（为了便于理解，举了比较简单的例子），在真实的系统模块之间的关系往往含混不清的。1-1和1-N关系是比较明确的（1-N关系通常在现实意义中存在从属关系）。比较复杂是N-N的关系界定和实现，还是用上面的学生和课程的例子说明，我们会发现几个问题：

学生和课程之间没有必然联系 在没有开始选课之前学生和课程是可以独立演进，互不相干的
无法直接建立两个表之间的N-N关系 N-N的关系，只能通过第三张表来实现把N-N转换成两个1-N

这里的第三张表即为 “学生-课程” 关系表。“学生” 和 “学生-课程” 的关系为1-N，“课程” 和 “学生-课程” 的关系为1-N。此时如果从1-N的角度来理解 “学生”表和 “学生-课程” 关系表的关系，是很令人费解的 - 无法从直觉上鉴定出 “学生” 表和 “学生-课程” 关系表的1-N关系。如果把 “学生-课程” 更名为 “课程选择表”，表中的每一列认为是 “课程选择” 结果，从直观上更有利于理解。这样的话，可以认为一个学生对应了多个课程选择操作，特定的课程选择操作只能属于一个学生，所以这是一个1-N的关系。同样，对于 “课程” 表来说，一个课程可以被选择多次，一次选择只能选择一个课程，所以这也是一个1-N的关系。
把 “学生-课程” 表更名成 “课程选择表” 方便理解了相关之间的1-N的关系，但是在开发环境中却会导致新的误解，对于开发人员来说，我们通常认为“表名1-表名2”结构命名的表是关系表。而“课程选择表”的重命名会让开发人员不能理解明白表的作用和含义。
下面再看一个用户-产品-订单的案例，这种关系变得更为复杂。从直观上理解：

用户-产品是N-N的关系，一个用户可以购买多个产品，一个产品也可以卖给多个用户
用户-订单是1-N的关系，一个用户可以下多个订单，但一个订单仅能属于一个用户
产品-订单是N-N的关系，一个产品可以被计入多个订单，一个订单也可以包含多个产品

按照这种思路，可以设计出下面的数据库结构：

Customer/Order/Product

但是略一思考，就会发现图中的问题 - 订单表实际上就是一张 “用户-产品” 的关系表而已，它记录的数据分别是 customer_id 和 product_id ，这分别是 Customer 表和 Product 表的索引。那么产品和订单的关系只能是1-N的关系，而不是图中所画的N-N，所以问题出在什么地方呢？
产品-订单真的是N-N的关系吗？仔细斟酌 “一个产品可以被计入多个订单，一个订单也可以包含多个产品” 这句话，会发现有一个概念被泛化了，这就是 “一个订单也可以包含多个产品”，在上图的设计中一个订单仅仅包含了一个产品（和数量），而不是多个。而被误解的“订单”实际上指的是整个订单表 - 即Order这张表。这个误解告诉我们在理解1-N和N-N关系的时候要注意，这里所指的单位都是表中的特定一列。
当我们发现一个订单仅包含一个产品之后会对整个表的结构有更清晰的认识。因为在购物网站中，一个订单往往会包含多个产品，为了达到这种效果，需要设计一个新的表来记录一次订单包含的所有产品 - BoughtItem 。

Customer/Order/Product/BoughtItem

在这种结构中，Order 得到简化，并添加了 BoughtItem 表（它实际上的作用是Order和Product的关联表）来记录一次购买的多个产品（注意：和产品是N-1关系）。订单(Order)和BoughtItem也是1-N关系，一个订单包含多个BoughtItem。

注：表名非常容易引起各种困惑，所以起好名字很关键(这里的BoughtItem起的并不好，或者该叫OderProduct)。

DB设计

DB的设计往往需要对需求有一个清晰的理解，否则后面会很难办（我刚入职某宝的时候，一个PM（ProjectManager）跟我说，我们走敏捷路线，先做技术后确定需求，于是我蒙了，那时候连要做什么都不知道，唯一知道的是我们要干一件大事 - 而PM要我们把代码先写好）。这会有以下几个阶段：

需求分析
ER设计
物理设计

需求分析阶段的最佳实践是头脑风暴，相关的同学在一起，把需求理解透彻。ER（EntiyRelation）设计阶段要确定各个模块和模块之前的关系，用来表达的语言就是ER图，就像上一节中画的图，可以让人清晰的了解到表的设计和关系。物理设计阶段，需要做具体的技术选型，选择合适的RDMS（比如Oracle、MySQL等等），设计表的字段类型，给表取一个 更好的名字。

这部分内容取自：慕课网上的一个教学视频 - 数据库的那些事。总共一个多小时，讲的挺不错的，循序渐进对于补充数据库的基础知识很有帮助。讲师虽然英语差了些，但是技术还是到位的，一些经验性的东西坦诚告之，分享的精神值得肯定。

下面记录一些物理设计阶段的最佳实践：
数据类型选择

数据类型选择 int > datetime > char > varchar
char 是定长字符， varchar 是变长字符，一般小于50个字节的时候推荐用 char （除了个别很少用到的字段，也可用varchar来节省空间）
int 分tinyint(1byte)\smallint(2byte)\mediumint(3byte)\int(4byte)
decimal类型可以存储精确的小数位，float比较粗略但是开销相对较少
使用int来存储时间只能存到2038-1-19 11：14：07年即2^32=2147483648，在使用的时候需要转换，如果需要经常使用还是用Datetime来存储

表设计

一般表除了“语义上”的主键之外最好有一个自增主键
避免使用外键约束，触发器
不要用预留字段
反范式设计提高效率

维护与优化

维护数据字典 使用 COMMENT 命令记录数据字典 (数据字典类似备注，java doc的机制)
维护索引 思路，可以从where,group by, order by 中的列做索引，索引中的列不要太长（每次插入数据会更新索引，那么过多的索引会导致写变慢）。索引中不要强制使用索引关键字

维护表结构更改

数据库尽量使用批量操作

禁止使用 Select *
控制自定义函数
尽量少使用数据库全文索引功能 （可以使用一些开源搜索引擎代替）

表的水平和垂直拆分

控制表的宽度 - 垂直拆分，把多列拆分成多张表每张表的列少一些（把经常会一起查询的列分在一张表中，大字段放在一张表中）
控制标的大小 - 水平拆分，CASE1：按照主键Hash的方式，把数据分流到子表

完

看了很多数据库设计的知识，依然设计不出一个典型电子商务网站的数据库，我突然明白一件事情，我对电商根本就不了解，所以无法得到抽象的模型也无法设计出具体的表。所以想了一个办法，强记下典型系统的表结构设计，来强化自身的设计能力。
操千曲而后晓声，观千剑而后识器， 所以有了下面的习题：

用户登录系统设计
权限系统设计
任务管理系统设计
流程引擎设计
电子商户系统设计
微博数据库设计

刷完这六关再谈数据库设计。

作者：ntop
链接：https://www.jianshu.com/p/f59d372ee201
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。