数据库设计相关
一、数据库设计
数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS（数据库管理系统），为这个业务系统构造出最优的数据存储模型，并建立好数据库中的表结构以及表与表之间的关系的过程，使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。
- 常用的关系型数据库：MySQL、Oracle、SQLServer、PgSql
- NoSQL系统：Mongo、Memcache、Redis
有效存储、高效访问！
好的数据库设计
- 减少数据冗杂；else(大量数据冗杂)
- 避免数据维护异常；else（更新、插入、删除异常）
- 节约存储空间；else（浪费大量的存储空间）
- 高效的访问；else（访问数据低效）
二、数据库设计步骤
需求分析
- 数据是什么
- 属性有哪些
- 数据和属性各自特点有哪些
逻辑设计
使用ER图进行逻辑设计
物理设计
结合自身情况选择数据库 mysql sqlserver oracle
维护优化
- 新的需求进行建表
- 索引优化
- 大表的拆分
三、需求分析
为什么要进行需求分析？
-
了解系统中所要存储的数据
时效性，周期性 -
了解数据存储的特点
增长量大，不属于核心数据 -
了解数据的声明周期
过期清理归档，分库分表
要搞清楚的一些问题
-
实体与实体之间的关系（1对1，1对多，多对1）
-
实体包含哪些属性
-
哪些属性组合可以唯一标识一个实体
举例说明：
以一个小型的电子商务网站为例，电商网站系统包括了几个核心模块：（用户，商品，订单，购物车，供应商）
记录用户注册信息的：
*包括属性：用户名、密码、电话、邮箱、身份证号...
可选唯一标示属性：用户名，身份证
存储特点：随着系统上线时间增加，需要永久存储（不能删除归档，可以考虑分表）
记录商品信息的：
包括属性：编码，名称，描述，种类，重量，价格，有效期...
可选唯一标示属性：商品编码
存储特点：对于下线的商品可以归档存储
记录购物车信息的：
包括属性：用户名，商品信息，加入时间，数量...
可选唯一标示属性：用户名+商品编号+加入时间，购物车编号
存储特点：不用永久存储（设置归档、清理规则）
记录订单信息：
包括属性：订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型...
可选唯一标识属性：（订单号）
存储特点：永久存储（分表分库存储）
Paste_Image.png
关系
用户->n订单
用户->n购物车
n订单->n商品
n商品->n订单
四、逻辑设计
-
将需求转化为数据库的逻辑模型
关系：表与表之间的联系
元组：表中一行为一个元组
属性：表中一列即为一个属性；每个属性都有一个名称成为属性名
候选码：表中的某个属性，可以唯一确定一个元组（主键，一个或一组属性，唯一索引）
主码：一个关系有多个候选码，选定其中一个为主码（主键）
域：属性的取值范围（男，女）
分量：元组中的一个属性值（具体值）
-
通过er图的模型对逻辑模型进行展示
矩形：实体集，矩形内写实体集的具体名字
菱形：标示联系集
椭圆：标示实体具体的属性
线段：讲属性连接到实体集，或实体集联系到关系
er图
-
同所选用的具体的dbms系统无关
三大范式
如果要存储用户信息和购物车信息是用一张表呢还是多张表呢？
包括：第一范式，第二范式，第三范式
这是目前我们大多数据库设计所要遵循的范式
操作异常：
-
插入异常
如果某实体随着另一个实体的的存在而存在，即缺少某个实体时就无法表示这个实体，那么这个表就存在插入异常。 -
更新异常
如果更改表所对应的某个实体实例的单独属性时，需要将多行更改，那么就说这个表存在更新异常。 -
删除异常
如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表就存在删除异常。 -
数据冗余：
是指相同的数据在多个地方存在，或者说表中的某个列可以由其它列计算得到，这样就说表中存在着数据冗余。
第一范式（列不可再分）：
数据库表中所有字段都是单一属性，不可再分的，这一单一属性由基本的数据类型所构成，如int，char，varchar
| id | name | tel | pwd |
|---|---|---|---|
| tel1 tel2 | pwd1 pwd2 | ||
| 1 | dp | 027-88888888 13554852557 | 456 123456 |
第二范式（行不可再分）：
数据库中的表，不存在非关键字段对任一候选字段的部分函数依赖的情况
部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字
Paste_Image.png
第三范式：
如果数据表中不存在非关键字段对任意候选字段的传递函数依赖则符合第三范式
Paste_Image.png
五、物理设计
1.选择合适的数据库系统
oracle、sqlserver 商业数据库，成本
mysql、pgsql开源
oracle 事务成本低，适合大型事务操作，性能好
sqlserver windows系统
sqlserver开发语言 .net
-
运用场景
企业级项目 oracle、sqlserver
互联网项目 mysql、pgsql -
mysql常用引擎
myisam 不支持事务 支持并发插入表级缩
innodb 支持事务 支持并发行入表级锁 5.5之后
mysql可以针对不同的存储引擎的需求可以选择最优的存储引擎
2.定义数据库，表以及字段的命名规范
-
可读性原则：
字段使用大写和小写来格式化 驼峰命名法 -
表意性原则：
表名显示功能 -
长名原则：
尽量少用或者不用缩写
3.根据所选dbms系统选择合适的字段类型
生日：
char(10) '1990-02-19' 占用空间小
varchar(20) '1990-02-19'
datetime 1990-02-19
int 146564344
列的数据类型一方面影响数据存储的空间开销，另一方面也会影响数据查询的性能，当一个列可以选择多种数据类型时，优先选择数字类型，其次是日期或二进制类型，最后是字符类型。对于同级别的数据类型，应该优先选择占用空间小的。
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8
datetime 3
timestamp 4
char n
varchar n
对数据进行比较操作时，字符串处理比数字慢
数据库中，数据以页为单位，列的长度越小，利于性能提升。
char varchar
（1）如果列中存储的数据长度差不多一致的，应该考虑char；身份证 手机号
（2）大于50字节用varchar
utf-8 一个字符三个字节
decimal float
（1）decimal用于存储精确数据，float用于存非精确数据
（2）float存储空间开销比decimal小
时间类型
int：字段小，但是需要时间函数转换
4.反范式化设计
反范式化是针对范式化而言，为了性能和读取效率考虑而是当的对第三范式的要求进行违反，而允许存在少量的数据冗余。
订单表 用户表 商品表 属性表
六、数据库维护和优化
1.维护数据字典
Paste_Image.png
第三方工具 写好注释 nacicat
mysql comment
2.维护索引
表结构，数据量不断变化，索引也可能不适用，当需求发生该变的时候，需要建立新的索引
3.维护表结构
变更表结构，插入列，删除列
数据字段进行维护
控制表的宽度和大小
数据库中适合的操作
批量操作 > 逐条操作
尽量少用select * 这样的查询
控制使用用户自定义函数
不要使用数据库中的全文检索
4.在适当的时候对表进行水平拆分或垂直拆分
为了控制表的宽度可以进行表的拆分
1.经常一起查询的列放到一起
2.text等大字段拆分出到附加表中
为了控制表的大小可以进行表的水平拆分分
