数据库设计

---

一： 为什么要进行数据库的设计

1. 优良的数据设计 ： 减少数据冗余、避免数据维护异常、节约空间、高效访问
2. 糟糕的数据库设计： 存在大量的数据冗余，存在数据插入、删除、更新异常，浪费大量的存储空间
   访问效率低。

---

二： 数据库设计的步骤

需求分析--》 逻辑设计--》 物理设计 --》维护优化

1. 需求分析
   （1） 数据是什么
   （2）数据有哪些特点
   （3）数据和属性各自的特点有哪些
2. 逻辑设计
   使用ER图对数据库进行逻辑建模
3. 物理设计
   根据数据库自身的特点 将逻辑设计转化为物理设计
4. 维护和优化
   （1）新的需求进行建表
   （2）索引优化
   （3）大表拆分

---

三： 为什么进行需求的分析

（1）了解系统中所要存储的数据
（2）了解数据存储的特点
（3）了解数据的生命周期

需要注意一些问题：
（1）实体和实体间的关系（1对1 1对多 多对多）
（2）实体所包含的属性
（3）那些属性和属性之间的组合可以唯一标识一个实体

案例： 以一个电子商务网站为例，在这个电子商务网站中有几个核心的模块
用户模块、商品模块、订单模块、购物车模块、供应商模块
（1）用户模块：用于记录用户的信息
包括的属性：用户名（账号） 密码 电话 邮箱 身份证号 地址 姓名 昵称
可选唯一标识属性： 用户名（账号） 电话 身份证号
存储的特点： 随着系统上线时间的增加，需要永久存储
（2）商品模块：用于记录网站所销售的商品信息
包括的属性： 商品编码、商品名称、 商品描述、商品品类 、供应商名称 、重量、有效期 、价格 ...
可选的唯一标识属性： （商品名称+ 供应商名称） （商品编码）
存储的特点： 对下线商品可以归档存储
（3）订单模块
包括的属性： 订单号、用户姓名、用户电话、收获地址、商品编码、商品名称、数量、价格、订单状态、
支付状态、订单类型...
可选唯一标识属性： （订单号）
存储特点： 永久存储（分表、分库存储）
（4）购物车模块
包括的属性：用户名、商品编码、商品名称、商品价格、商品描述、商品分类、加入时间、商品数量...
可选唯一标识： （用户名、商品编号、加入时间） （购物车编号）
存储特点： 不用永久存储（设置归档、清理规则）
（5）供应商模块：用于保存所销售商品供应商的信息
包括属性：供应商编号、供应商名称、联系人、电话、营业执照、地址、法人...
可选唯一标识： （供应商编号）（营业执照）
存储特点： 永久存储

实体和实体之间的关系：

QQ截图20180402104703.png

---

四： 逻辑设计

（1） 将需求转化为数据库的逻辑模型
（2）通过ER图对逻辑模型进行展示
（3）同所选的DBMS无关

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

   
   QQ截图20180402143004.png

2. 数据库设计的范式
   第一范式： 数据表中所有字段都是单一属性，不可再分
   第二范式： 在第一范式的基础上，数据表中不存在非关键字段对任意候选关键字段的部分函数依赖
   注：部分函数依赖是指，存在组合关键字中的某一关键字决定非关键字的情况，所有的单关键字都符合第二范式
   第三范式： 在第二范式的基础上，数据表中不存在非关键字段，对任意候选关键字段的传递依赖
   BC范式：在第三范式的基础上，数据表中不存在任何字段对任一候选关键字段的传递依赖
   注：也就是说如果是复合关键字，则复合关键字之间不能存在函数依赖关系

3. 数据操作异常和冗余
   （1）插入异常
   如果某个实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常
   （2）更新异常
   如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说这个表存在更新异常
   （3）删除异常
   如果删除表的某一行来反映某实体实例，失效时导致另一个不同 实体实例信息丢失，那么这个表就存在删除异常
   （4） 数据冗余
   是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到，这样就说表中存在数据冗余

---

五：物理设计

物理设计需要考虑的问题
1 选择合适的数据库管理系统
2 定义数据库表及字段命名规范
3 根据所选的DBMS系统选择合适的字段类型
4 反范式化设计

（1）mysql常用的存储引擎

存储引擎	事物	锁粒度	主要应用	忌用
MyISIM	不支持	支持并发插入的表级锁	select insert	读写操作频繁
MRG_MyISIM	不支持	支持并发插入的表级锁	分段归档，数据仓库	全局查找过多的场景
innodb	支持	支持MVCC的行级锁	事物处理	无
Archive	不支持	行级锁	日志记录只支持insert selec	需要随机读取更新删除
Ndb cluster	支持	行级锁	高可用性	大部分应用

（2）表及字段的命名规则
可读性原则： 使用大写和小写来格式化的库对象名字，获得良好的可读性。
表意性原则： 对象的名字应该能够描述它所标识的对象
长名性原则： 尽可能少使用或者不适用缩写

（3）字段类型的选择原则
3.1 列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询的性能。当一个列可以选择多种数据类型的时候
，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。
以上选择 主要从 下面两个角度考虑
a: 在对数据进行比较（查询条件、join 条件及排序）操作时 ：同样的数据，字符处理往往比数字处理慢
b: 在数据库中，数据处理以页为单位，列的长度越小，利于提升性能。
3.2 char 和 varchar 如何选择
a: 如果列中要存储的数据长度差不多是一致的，则应该考虑用char ;否则应该考虑用varchar 。
b: 如果列中最大数据长度小于50字节，则一般也考虑用char
c: 一般不宜定义大于50个字节的char类型列
3.3 decimal 与 float 如何选择
a: decimal用于存储精确数据 而float用于存储非精确数据。故精确数据类型只能选择用 decimal 类型
b: 由于float的存储空间开销比decimal小（精确到7位小数只需要4个字节 而精确到15位小数只需要8个字节）故非精确数据优先选择float类型
3.4 时间类型如何存储
a: int 类型存储时间 字段的优缺点
优点： 字段长度比 datetime 小
缺点： 使用不方便，要进行函数转化
限制： 只能存储到 2038-1-19 11：14：07 即 2^32
b 需要存储的时间粒度
年月日 小时 分 秒 周

(4) 需要考虑的其他问题
4.1 如何选择主键
a: 区分业务主键和数据库主键
业务主键用于标识业务数据 ，进行表与表之间的关联 ,数据库主键为了优化数据库存储 （innodb会生成6个字节的 隐含主键）
b: 根据数据库类型，考虑主键是否要顺序增长
有些数据库是按主键的顺序逻辑存储的
c: 主键字段所占空间尽可能的小
对于使用聚族索引方式存储的表 每个索引后都附加主键的信息

4.2 避免使用外键约束

• 降低数据导入的效率
• 增加维护的成本
• 虽然不建议使用外键约束，但是相关联的列上面一定要建立索引

4.3 避免使用触发器

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

4.4 关于预留字段

• 无法准确的知道预留字段的类型
• 无法准确的指导预留字段的内容
• 后期维护预留字段的成本同增加一个字段的成本是相同的
• 严禁使用预留字段

(5)反范式化设计
什么是反范式化？
反范式化是针对 范式化而言，所谓反范式化就是为了性能和读取效率考虑而适当对第三范式的要求进行违反，而允许存在少量的数据冗余，
换句话说反范式化就是用空间换取时间。
反范式化设计的优点：减少表的关联数量、增加数据的读取效率
反范式化一定要适度

---

六： 维护和优化

1. 维护索引
   如何维护索引？
   索引并不是越多越好，过多的索引不但会降低写的效率，而且会降低读的效率，定期维护索引碎片
   在sql 语句中不要使用强制索引关键字

2. 维护表结构
   如何维护表结构？
   mysql5.5 之前使用pt-online-schema-change
   mysql5.6 之后本身支持在线表结构的变更 同时对数据字典进行维护
   控制表的宽度和大小
   数据库中适合的操作

• 使用批量操作代替逐条操作
• 禁止使用select * 这样的查询
• 控制使用用户自定义函数
• 不要使用数据库的全文索引

3. 在适当的时候对表进行水平拆分和垂直拆分
   表的垂直拆分： 为了控制表的宽度而进行垂直拆分

• 经常一起查询的列放在一起
• text blob等大字段拆分到附加表中
  表的水平拆分： 为了控制表的大小进行水平拆分
  原理 对主键值进行hash 操作 （比如拆分成5张表 主键值除以5根据余数划分表）

---

二： 数据库优化

从索引的角度对数据库进行优化

1. 索引的分类
   （1）从数据结构角度
   B+树索引、hash索引、FULLTEXT索引、R-tree树索引
   （2） 从物理角度
   聚族索引、非聚族索引
   （3）从逻辑角度
   主键索引 唯一索引 多列索引 单列索引

2. 创建索引的一些基本原则：
   （1）多列索引需要满足左前缀原则
   （2）= 和 in 可以乱序， 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引
   可以识别的形式
   （3）尽量选择区分度高的列作为索引（区分度越高，我们扫描的记录就越少）
   （4）索引列不能参与计算（索引列一旦参与函数计算，会使得索引失效）
   （5）尽量扩展索引，不要新建索引，比如表中已经有a索引，现在要加（a,b）索引只需要修改原来的索引即可。

注：一些误区： 多列索引只能用一个，所以我们单独在每个列上面加索引只能有一个列发挥作用，通常我们在这些列上加一个联合索
引这样 发挥索引的列数更多，查询更快。（联合索引发挥作用要遵循左前缀原则）

那么理想的索引是什么样的呢？
1.查询频繁 2. 区分度高 3. 长度小 4.尽量能覆盖常用的查询字段

3. explain 分析sql

explain 输出解释：id | select_type| table|type|possible_keys|key|ken_len|ref|rows|Extra
3.1 id
sql执行的顺序标识 sql从小到大执行
3.2 select_type

        (1)SIMPLE
        简单的SELECT(不适用UNION或子查询)
        (2)PRIMARY
        最外层的select
        (3)UNION
        UNION中的第二个或后面的SELECT语句
        (4)DEPENDENT UNION
        UNION 中的第二个或后面的SELECT语句，取决于外面的查询
        (5)UNION RESULT
        UNION 的结果
        (6)SUBQUERY
        子查询中的第一个select
        (7)DEPENDENT SUBQUERY
        子查询中的第一个select取决于外面的查询
        (8)派生的select （FROM子句的子查询）

3.3 table
显示这一行数据是关于那张表的,有时候不是真实的表 看到的是derivedx(临时表)
3.4 type
显示使用哪种类别有无使用索引 从好到差 const eq_ref ref range index All

    (1) system
    这是const 联接类型的一个特例。表示仅有一行满足条件
    (2) const
    表最多有一个匹配行，它将在查询开始时被读取，因为仅有一行，在这行的列值可被优化器剩余部分认为是常数， const表很快，
    因为他们只读取一次。
    (3) eq_ref
    对于每个来自前面的表的行组合，从该表中读取一行。这可能是最好的连接类型，除了const类型，它用在一个索引的所有部分被
    连接使用，并且索引是UNIQUE和PRIMARYKEY. eq_ref 可以用= 比较带索引的列。 比较的值可以为常量或一个使用在该表前面
    所读取的表的列的表达式。
    (4) ref
    对于每个来自前面表的行组合，所有有匹配索引值的行将从这张表读取。如果连接只是用键的最左边前缀，或如果键不是UNIQUE
    或 PRIMARY KEY 则使用ref 如果使用的键仅仅匹配少量的行，该连接类型是不错的。ref 可以使用= 或 <=>操作符的带索引的列
    (5) ref_or_null
    该列类型如同ref 但是添加了MYSQL 可以专门搜索NULL值的行。在解决子查询时经常使用该连接类型优化。
    (6)index_merge
    该连接类型表示使用了 索引合并优化。在这种情况下 key 列包含使用索引的清单 ，key_len 包含使用索引最长的关键元素
    (7)unique_subquery
    该类型替换了下面形式的IN子查询的ref：
    value IN (SELECT primary_key FROM single_table WHERE some_expr)
    unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
    (8) index_subquery
    该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：
    value IN (SELECT key_column FROM single_table WHERE some_expr)
    (9)range
    只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型
    中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以
    使用range
    (10) index
    该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部
    分的列时，MySQL可以使用该联接类型
    (11)all
    对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。
    通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出

3.5 possible_key
possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys
中的某些键实际上不能按生成的表次序使用。

3.6 key
key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询
中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

3.7 key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。 使用的索引的长度。在不损失精确性的情况下，长度越短越好

3.8 ref
ref列显示使用哪个列或常数与key一起从表中选择行。

3.9 rows
rows列显示MySQL认为它执行查询时必须检查的行数。(优化sql主要降低rows的数量)

3.10 Extra
该列包含mysql解决查询的详细信息

    (1)Distinct
    一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
    (2)Not exists
    MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不在搜索了
    (3)Range checked for each
    没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的
    最慢的连接之一
    (4)Using filesort
    看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和
    匹配条件的全部行的行指针来排序全部行
    (5)Using index
    列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
    (6)Using temporary
    看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，
    而不是GROUP BY上
    (7).Using where
    使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就
    会发生，或者是查询有问题