数据库的那些事儿

本文主要是介绍数据库的那些事儿，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

什么是数据库设计？

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型，并建立数据库中的表结构以及表与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已存储的数据进行访问。

数据库设计的步骤？
需求分析——数据是什么，数据具有哪些属性，数据与属性的特点是什么。
逻辑分析——使用ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统。
物理设计——根据数据库自身的特点把逻辑设计转换为物理设计。
维护设计——对新的需求进行建表；索引优化；大表拆分。
需求分析

为了设计最优化的数据库，便于后期的扩展和维护，数据越来越多，越来越大会浪费空间，越来越杂乱，后期是很难处理和维护的。

了解系统中索要存储的数据。
了解数据的存储特点，比如有的数据有时效性，有的没有，有时效性的可以采取定期清理。
了解数据的生命周期。

要搞清楚的一些问题

实体及实体之间的关系（1对1，1对多，多对多）
实体所包含的属性有什么？属性有很多，哪些属性是可以标识出这个实体的？
哪些属性或属性的组合可以唯一标识一个实体？
存储上有什么特性，增长量是什么样？

实例演示

以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块，商品模块，订单模块，购物车模块，供应商模块。下面就对各个模块进行需求分析。

用户模块：用于记录注册用户信息
包括属性：用户名，密码，电话，邮箱，身份证号，地址，姓名，昵称…

可唯一标识属性：用户名，身份证号，电话

存储特点：随系统上线时间的增长，需要永久存储

商品模块：用于记录网站中说销售的商品信息
包括属性：商品编码，商品名称，商品描述，商品品类，供货商名称，重量，有效期，价格…

可唯一标识属性：商品编码，（商品名称，供货商名称）

存储特点：对于下线商品可以归档存储（不能直接删除，可能跟订单有关）

订单模块：用于用户订购商品的信息
包括属性：订单号，用户姓名，用户电话，收货地址，商品编号，商品名称，数量，价格，订单状态，支付状态…

可唯一标识属性：订单号

存储特点：永久存储（分表，分库存储）

购物车模块：用于保存用户购物时选的商品
包括属性：用户名，商品编号，商品名称，商品价格，加入时间，商品数量…
可唯一标识属性：（用户名，商品编号，加入时间），购物车编号

存储特点：不用永久存储（设置归档，清理规则）

供应商模块：用于保存供应商信息
包括属性：供应商编号，供应商名称，联系人，电话，营业执照，供应商品…

可唯一标识属性:供应商编号，营业执照

存储特点：永久存储

逻辑设计？
ER图（实体关系图）

ER图就是数据库模型关系的展示图。

将需求转化为数据库的逻辑模型
通过ER图的形式对逻辑模型进行展示
同所选用的具体的DBMS无关

表示方法

实体型：用矩形框，矩形框内写明实体名

属性：用椭圆型表示，并用无向边将其与相应的实体连接起来。（可以连实体，也可以连联系）

联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁边标上联系的类型。

数量：一对一（1:1），一对多（1：n），多对多（m:n）

实例演示

注：有的属性下面有下划线，就表示主键。

设计范式
设计范式概要

什么是数据库设计范式？

——也就是提供了一种准则，帮助我们建立简介高效且结构清晰的数据库设计，避免数据库插入、更新、删除、修改中的异常，并且为最大限度的避免数据库的冗余。

关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式（4NF）和第五范式（5NF）。 满足最低要求的范式是第一范式（1NF）。 在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。 … 越高的范式数据库的冗余度就越低。

但并不是说遵循的范式等级越高越好，范式过高虽然具有对数据关系有更好的约束性，但是也会导致表之间的关系更加繁琐，从而导致每次操作的表会变多，数据库性能下降。

通常，在我的设计中，最高也就遵循到 BCNF，普遍还是 3NF。

1NF

数据库中的所有字段都是单一属性，不可再分的。这个单一属性由基本数据类型所构成，例如整数、浮点数、字符串等。换句话说，第一范式要求数据库中的表都是二维表。
2NF

1NF 只是设计数据库最基本的要求，但是数据会存在大量的冗余，并存在删除异常、插入异常、更新异常。例如：
2NF 在 1NF 的基础上，消除了非主属性对码的部分函数依赖 。部分函数依赖，指的是存在组合关键字中的某一关键字决定非关键字的情况，比如（学号，课名）—> （姓名），只需要根据学号即可唯一确定姓名。

3NF

3NF 就是在 2NF 的基础上，消除非主属性对码的传递函数依赖。传递函数依赖，指的是通过码唯一确定一个属性，然后通过该属性可以唯一确定另一个属性，所以就演变为了可以通过码唯一确定一个无函数依赖的属性（码——关系中的某个或者某几个属性的集合，用于唯一地标识每一条数据）。

BCNF

不存在主属性对于码的部分函数依赖和传递函数依赖，那么即符合 BCNF（前面的 2NF、3NF都是非主属性对码的部分函数依赖和传递函数依赖）。

例如下例，假设供应商联系只能受雇于一家供应商，每个供应商可以供应多个商品，则存在关系（供应商，商品ID）—> （供应商联系人，商品数量）与（供应商联系人，商品ID）—> （供应商，商品数量），不符合BCNF。
物理设计

物理设计要做什么？

选择合适的数据库管理系统（应用特点和成本）
定义数据库、表以及字段的命名规范（不同数据库对命名的规范是有差异的）
根据所选的DBMS系统选择合适的字段类型
反范式化设计（为了效率的提升而做的，可能是数据冗余）

数据库系统选择
MySQL数据库存储引擎

表及字段命令

可读性规则

使用大写和小写来格式化的库对象名字以获得良好的可读性。

表意性原则

对象的名字应该能够描述它所标识的对象。

长命原则

尽量少使用或者不使用缩写。

字段类型

列的数据类型一方面影响数据存储空间的开销，另一方面也会影像数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数据类型，其次时日期或二进制类型，最后时字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

例如：
类型 数据
char(10) ‘1978-03-01’
vachar(20) ‘1978-03-01’
datetime 1978-03-01
int 257529600

那么int > datetime > char(10) > vachar(20)，int最优。

以上选用主要从两个角度考虑：

同样的数据，字符处理往往比数字处理慢。
列的长度越小，有利于性能的提升。

char与varchar

如果列中要存储的数据长度差不多是一致的，则优先选择char。
列中最大数据长度小于50byte，则一般考虑使用char。

decimal与float

decimal主要用于存储数据比float要精确。
float占用空间较小，当数据要求不精确优先选择。

时间类型

上面的举例已经透出一些选择规则：

优先选择用int或者timestamp来存储时间数据。缺点是使用不方便，需要进行函数转换。

同时需要关注时间存储的粒度——年 月 日 时 分 秒。

反范式化设计

上面已经提到，范式过高也会导致表之间的关系更加繁琐，从而导致每次操作的表会变多，数据库性能下降。

而反范式化就是为了性能和读取效率，适当地对范式进行违反，本质上就是用空间来换取时间，把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联。

例如下面遵守范式设计的几个表：
反范式的设计：

这样，查询订单信息就大大简化了：

其实在日常生活中，大多数数据库都时读比写多。反反范式化设计虽然在写的时候增加了冗余，但是在读的时候我们大大的提高了效率。

维护优化
维护和优化主要做什么？

维护数据字典——数据大量冗余，查询性能较快。
维护索引——随着数据量和查询的不断变化，需要优化索引。
维护表结构——随着需求的不断变化，需要对表进行扩充或裁剪。
适当时候对表进行水平拆分或垂直拆分——随着数据量的增大，达到表的存储瓶颈，使操作变慢，需要拆分表。

如何维护数据字典

使用第三方工具对数据字典进行维护

利用数据库本身的备注字段来对数据字典进行维护，后面直接导出数据字典即可。例如：

如何维护索引

如何选择合适的列建立索引？
    出现在where, group by, order by从句后的列。
    可选择性高的列要放在索引的前面
    索引中不要包含太长的数据类型。
索引要适量，不是越多越好，大量的索引会导致操作效率降低。
定期维护索引碎片
在SQL语句中不要强制使用索引关键字。

表结构的维护

1.MySQL5.5之前使用在线工具pt-online-schema-change调整表结构。
2.MySQL5.6自身支持在线表结构的变更。
3.同时对数据字典进行维护。
4.控制表的宽度和大小。

数据库中适合的操作

1.尽量使用批量操作，少用逐条操作。
2.尽量少用使用SELECT *语句。
3.尽量少用用户自定义函数。
4.谨慎使用用数据库全文索引。

垂直拆分和水平拆分

表的垂直拆分——减小表的宽度。
    将经常查询的列放在一起，不经常查询的列单独拆成一张附表。
    表中数据量不发生变化。

表的水平拆分——减小表的高度。
    通过主键hash的方式将一张大表拆分成n个小表。
    表中各个列不发生变化。

参考

数据库设计的基础——数据库范式
https://www.imooc.com/learn/117

这篇关于数据库的那些事儿的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！