我的博客
01/27
2015

数据库设计那些事儿

第1章 需求分析

1-1 数据库设计简介

什么是数据库设计？

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

关系型数据库系统：MySQL、Oracle、SQLServer、PgSql
NoSQL系统：Mongo、Memcache、Redis

为什么要进行数据库设计？

优良的设计

  • 减少数据冗余
  • 避免数据维护异常
  • 节约存储空间
  • 高校的访问

糟糕的设计

  • 存在大量数据冗余
  • 存在数据插入、更行、删除异常
  • 浪费大量存储空间
  • 访问数据低效

1-2 数据库设计的步骤

需求分析->逻辑设计->物理设计->维护优先

数据分析

  • 数据是什么？
  • 数据有哪些属性？
  • 数据和属性各自的特点有哪些？

逻辑设计

  • 使用ER图对数据库进行逻辑建模

物理设计

  • 根据数据库自身的特点把逻辑设计转换为物理模型

维护优化

  • 新的需求进行建表
  • 索引优先
  • 大表拆分

1-3 需求分析的重要性简介

为什么要进行需求分析？

  • 了解系统中所要存储的数据
  • 了解数据的存储特点
  • 了解数据的生命周期

例如，有些数据具有时效性，就要过期清理或以归档方式存储；有些数据数据量很大，增长很快，但是并非核心数据，采取分库分表的方式存储，例如日志类数据，不适宜存储在数据库中，如果非要存储，就要订立归档和清理规则。

要搞清楚的一些问题

  • 实体及实体之间的关系（1对1,1对多，多对多）
  • 实体所包含的属性有什么
  • 那些属性或属性的组合可以唯一标识一个实体
  • 实体的特性，数据量、增长速度等

1-4 需求分析举例

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

各个模块之间的关系如下图： model_relationship

实例演示-用户模块

用于记录注册用户信息

包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称
可选唯一标识属性:用户名、身份证、电话
存储特点： 随系统上线时间逐渐增加，需要永久存储

实例演示-商品模块

包括属性：商品编码、商品名称、商品描述、商品品类、供应商名称、重量、有效期、价格
可选唯一标识属性：（商品名称，供应商名称）、（商品编码）
存储特点：对于下线商品可以归档存储

实例演示-订单模块

用于用户订购商品的信息

包括属性： 订单号、用户姓名、用户电话、收货地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型
可选唯一标识属性：（订单号）
存储特点：永久存储（分表、分库存储）

实例演示-购物车模块

用于保存用户购物时选对的商品

包括属性： 用户名、商品编号、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量
可选唯一标识： （用户名、商品编号、加入时间）、（购物车编号）
存储特点：不用永久存储（设置归档、清理规则）

实例演示-供应商模块

用于保存所销售商品的供应商信息

包括属性： 供应商编号、供应商名称、联系人、电话、营业执照号、地址、法人
可选唯一标识：（供应商编号）、（营业执照号）
存储特点： 永久存储

第2章 逻辑设计

逻辑设计是做什么的？

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

2-1 ER图

常见的ER图如下：

er_picture

ER图例说明

  • 矩形：表示实体集，矩形内写实体集的名字
  • 椭圆：表示实体的属性
  • 菱形：表示联系集
  • 线段：将属性连接到实体集，或将实体集连接到联系集

2-2 设计范式概要

用户信息和购物车信息可以放一张表中，也可以放两张表中

什么是数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
常见的数据库设计范式包括：第一范式，第二范式，第三范式及BC范式。

2-3 第一范式

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

first_nf

2-4 第二范式

数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。部分函数依赖是指存在着组合关键字中的每一关键字决定非关键字的情况。就是所有但关键字段的表都是符合第二范式。

second_nf

2-5 第三范式

对任意候选关键字段的传递函数依赖则符合第三范式。

third_nf

数据库设计如果不符合范式要求，一般会出现如下问题：
数据库操作异常：插入异常、更新异常、删除异常 。
数据冗余：数据在多个地方存在，或者说表中的某个列可以由其他列计算得到。

第3章 物理设计

3-1 物理设计要做什么

选择合适的数据库管理系统

  • 定义数据库、表及字段的命名规范
  • 根据所选的DBMS系统选择合适的字段类型
  • 反范式化设计（以空间换时间）

3-2 选择哪种数据库

Oracle SQLServer商用数据库，成本较高，适合企业级项目，MySQL、PgSQL开源数据库，更适合互联网项目，Oracle适合大的事务性操作，MySQL小的事务性操作。

3-3 MySQL存储引擎

常见存储引擎有如下几种：

mysql_engine

3-4 表和字段的命名规则

  • 可读性（一般是驼峰式，但是注意mysql大小写敏感）
  • 表意性（通过名字可以描述出对象的含义）
  • 长名原则（尽量不是用缩写，避免出现歧义）

3-5 字段类型选择原则

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

常用字符串大小如下图所示： character_select

根据上面原则选择字段类型时要考虑以下两个方面：

1、在对数据进行比较（查询条件、JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢。

原因：字符串类型的处理与当前的排序规则有关，它要去查询我么当前所使用数据字典的顺序来确定字符串的比较和排序顺序，二进制和数字类型不需要参考数据字典，效率要比字符串高。

2、在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

原因：数据库是存储是页为单位，MySQL在Innodb中默认是16K字节一个页，列的长度越小，在一个页中存取的数据量越多，加载相同的数据时，如果列越小，加载的页数越少，I/O性能就会提高。

3-6 如何具体选字段类型

整型和时间类型占用的磁盘空间较小，而字符串类型是要根据所存储的字节数决定。但是除了选择占用空间比较小的存储类型，还要考虑以下几个方面：

char与varchar如何选择？

  • 如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。

  • 如果列中的最大数据长度小于50Byte，则一般也考虑用char（当然，如果这个列很少用，则基于节省空间和减少I/O的考虑，还是可以选择用varchar）

  • 一般不宜定义大于50Byte的char类型列。

decimal与float如何选择？

  • decimal用于存储精确数据，而float只能用于存储非精确数据。故精确数据只能选择用decimal类型

  • 由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节）故非精确数据优先选择float类型

时间类型如何存储？

  • 使用int来存储时间字段的优缺点

优点：字段长度比datetime小，int占用4byte，datetime占用8个byte。
缺点：使用不方便，页面展现时，要进行函数转换。
限制：范围有限制，只能存储到2038-1-19 11:14:07即232为2147483648。

  • 需要存储的时间粒度的类型 年月日小时分秒周

3-7 数据库设计其它注意事项

如何选择主键

  • 区分业务主键和数据库主键 业务主键用于标识业务数据，进行表与表之间的关联，数据库主键为了优化数据存储。

原因：Innodb要求每一个表中有一个主键的。因为Innodb是按照主键的逻辑顺序进行存储的，如果没有主键，Innodb会优先选择所有非空列的唯一索引作为主键，如果没有主键和非空索引的话，Innodb会生成6个字节的隐含主键，所以最好人为定义主键，不要使用隐含的，Innodb表是按照主键的逻辑顺序存储，最好主键是按照逻辑顺序进行增长，这样它就不会进行数据的逻辑迁移，对I/O有好处，主键也是越小越好，跟页存储有关

  • 根据数据库，有些数据库是按主键的顺序逻辑存储的。
  • 主键的字段类型所占空间要尽可能的小。对于使用聚集索引方式存储的表，每个索引后都会附加主键信息

避免使用外键约束

外键是用来保持数据完整性的一种方式，但是高并发的互联网网站中使用外键会到来一些负面的影响：

  • 降低数据导入的效率，每写入一条数据都会查询是否符合外键约束，符合插入，不符合拒绝，高并发情况下很耗费时间。
  • 增加维护成本
  • 虽然不建议使用外键约束，但是相关联的列上一定要建立索引

避免使用触发器

我们经常会使用触发器来减少我们对程序的逻辑处理，比如再操作某张表的时候，同时记录日志。会同时使用触发器像一张表中插入日志。

  • 降低数据的导入的效率
  • 可能会出现意想不到的数据异常
  • 使业务逻辑变得复杂

关于预留字段

  • 无法准确的知道预留字段的类型
  • 无法准确的知道预留字段中所存储的内容
  • 后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的
  • 严禁使用预留字段，没有任何意义

3-8 反范式化设计

反范式化是针对范式化而言的，所谓反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反方式化就是使用空间来换取时间。

为什么反范式化？

  • 减少表的关联数量，减少了磁盘的I/O操作
  • 增加数据的读取效率
  • 反范式化一定要适度，适量

具体事例：

遵循标准范式化设计：

nf_design

如何查询订单详情信息？

SELECT   
b.用户名,  
b.电话,  
b.地址,  
a.订单ID,  
sum(c.商品价格 * c.商品数量)AS 订单价格,  
c.商品价格,  
d.商品名称  
FROM '订单表' a  
JOIN '用户表' b ON a.用户ID = b.用户ID  
JOIN '订单商品表' c ON c.订单ID = b.订单ID  
JOIN '商品表' d ON d.商品ID = c.商品ID  
GROUP BY b.用户名,b.电话,b.地址,a.订单ID,c.商品价格,d.商品名称

该查询需要关联多张表，然后再通过sum汇总出价格，查询效率不太高。 如果通过表中部分数据的冗余，进行反范式化设计，如下图：

opposite_nfdesign

简化sql的查询

SELECT  
    b.用户名,  
    b.电话,  
    b.地址,  
    a.订单ID,  
    a.订单价格,  
    c.商品价格,  
    c.商品名称  
FROM '订单表' a  
JOIN '用户表' b ON a.用户ID = b.用户ID  
JOIN '订单商品表' c ON c.订单ID = b.订单ID

互联网项目中，读写比率大概是3:1或是4:1的关系，读远远高于写，写的时候增加数据冗余，增加了读的效率，这样还是很值得的。

第4章 维护优化

由于需求不断变化，数据库的结构也要相应的做出改变，需求变化时，数据库结构要如何维护和优化。

4-1 维护和优化要做什么

  • 维护数据字典（清楚每一列的含义及存储内容，便于维护）
  • 维护索引（不需要的删除，建立新的索引）
  • 维护表结构（列的增加与删除，类型的更改等）
  • 在适当的时候对表进行水平拆分或垂直拆分（数据量增加后，查询变慢，可以考虑拆分）

4-2 如何维护数据字典？

  • 使用第三方工具对数据字典进行维护 *使用数据库本省的备注字段来维护数据字典。

增加备注字段：

CREATE TABLE customer(  
    cust_id INT auto_increment NOT NULL COMMENT '自增ID',  
    cust_name VARCHAR(10)NOT NULL COMMENT '客户姓名',  
    PRIMARY KEY(cust_id)  
)COMMENT '客户表'

导出数据字典:

SELECT  
    a.table_name,  
    b.table_comment,  
    a.column_name,  
    a.column_type,   
    a.column_comment  
FROM  
    information_schema. COLUMNS a  
JOIN information_schema. TABLES b   
ON a.table_schema = b.table_schema  
AND a.table_name = b.table_name  
WHERE a.table_name = 'customer'

导出的表记录如下：

table_nametable_commentcolumn_namecolumn_typecolumn_comment
customer客户表cust_idint(11) 自增ID
customer客户表cust_namevarchar(10)客户姓名

4-3 如何维护索引？

  • 出现在where从句，group by从句，order by从句中的列，可选择性高的列要放到索引的前面。

数据库中，所有的sql在执行之前，都要通过sql优化器进行重新编译，编译过程中，优化器就会按照索引和统计信息对查询列进行统计和优化，自动选择适合sql中的索引。

  • 索引中不要包括太长的数据类型，因为数据库是以页进行存储。一页就是16K，一页中存储的数据条数越多，查询越快。

注意事项：

  • 索引并不是越多越好，过多的索引不但会降低写的效率，而且也会降低读的效率。sql优化器会在多种可用的索引之间进行选择，会降低查询效率。 定期维护索引碎片。

  • 在MySQL数据库中建议SQL语句中不要使用强制索引关键字(索引变更，导致查询出错)。

4-4 数据库中适合的操作

如何维护表结构？

  • 使用在线变更表结构的工具
    • MySQL5.5之前可以使用pt-online-schema-change
    • MySQL5.6之本身支持在线表结构的变更，效率较低版本要高
  • 同时对数据字典进行维护
  • 控制表的宽度和大小

数据库中适合的操作

  • 批量操作和逐条操作，适合批量操作
  • 禁止使用select * 这样的查询
  • 控制使用用户自定义函数
  • 不要使用数据库中的全文索引（需要另外的建立索引文件进行维护，对中文支持不太好）

4-5 表的垂直和水平拆分

表的垂直拆分

随着需求越来越多，某一张表的列越来越增加，为了控制表的宽度可以进行表的垂直拆分。 将表进行垂直拆分： vertical_demoliton

原因：数据库以页存储，表越宽，每一行的数据越大，一页中所能存储的行数就会越来越少。拆分成多张窄表，每一张表中所含长度不会大，优化了IO效率。
原则：

  • 经常一起查询的列放到一张小表中，减少表关联
  • text，blob等大字段拆分出到附加表中

表的水平拆分

原来一张大表有上亿数据，需要减少表中的数据量，为了控制表的大小可以进行表的水平拆分。 将表进行水平拆分：

horizontal_demolition

那么如何把一张大表中的数据，分配到多张小表中呢？拆分可以按照Hash方式，如下图： horizontal_hash_demolition

每一张表都拥有一个主键值，通过对主键值进行哈希操作，比如说主键按摩取值，把一张大表平均分配到几张小表中，解决了表中数据量的问题。

Comments