数据库优化----结构优化

前面介绍了索引，其实最重要的是数据库表结构对数据库的影响，良好的数据库逻辑设计和物理设计是数据库获取高性能的基础，数据库结构优化的目的：1、减少数据冗余，数据冗余是指相同的数据在多个地方存在，表中的某个列可以在其他某个列中获取到，2、尽量避免数据维护中出现更新、插入和删除异常，插入异常指的是，3、节约数据库存储空间。
一.首先我们选择合适的数据类型
数据类型的选择，重点在于“合适”二字，如何确定选择的数据类型是否合适了？

1. 使用可以存下你的数据的最小的数据类型。（时间类型数据：可以使用varchar类型，可以使用int类型，也可以使用时间戳类型）
2. 使用简单的数据类型，int要比varchar类型在mysql处理上简单。（int类型存储时间是最好的选择）
3. 尽可能的使用not null定义字段。（innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
4. 尽量少用text类型，非用不可时最好考虑分表。

二、数据库表的范式化优化
表范式化
范式化是指数据库设计的规范，目前说道范式化一般是指第三设计范式。也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。设计出没有数据冗余和数据维护异常的数据结构。

存在以下传递函数依赖关系： （商品名称）->（分类）->（分类描述） 也就是说存在非关键字段 “分类描述”对关键字段“商品名称”的传递函数依赖。
不符合第三范式要求的表存在以下问题：
1、数据冗余：（分类，分类描述）对于每一个商品都会进行记录。
2、数据的插入异常
3、数据的更新异常
4、数据的删除异常（删除所有数据，分类和分类描述都会删除，没有所有的记录）
如何转换成符合第三范式的表（拆分表）：
将原来的不符合第三范式的表拆分为3个表
商品表、分类表、分类和商品的关系表

数据库的第一范式：数据库表中的所有字段都只具有单一属性，单一属性的列是由基本的数据类型所构成的，设计出来的表都是简单的二维表，

数据库的第二范式：要求一个表中只有一个业务主键,也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系

数据库的第三范式：指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖。
范式化设计的优缺点

优点：可以尽量的减少数据冗余

       范式化的更新操作比范式化更快

       范式化的表通常比反范式化更小

缺点：对于查询需要对多个表进行关联

       更难进行索引优化

反范式化设计的优缺点

优点：可以减少表的关联

       可以更好的进行索引优化

缺点：存在数据冗余及数据维护异常

       对数据的修改需要更多的成本

反范式化
反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。
提高查询效率或者为了业务需求，使用冗余数据或反范式的设计。
减少表关联，更好地进行索引优化。
存在数据冗余和数据维护异常，对数据修改需要更多的成本。

查询订单信息

select b.用户名，b.电话，b.地址，a.订单ID，sum（c.商品价格*c.商品数量）as 订单价格
from 订单表 as a
join 用户表 as b on a.用户ID=b.订单ID
join 订单商品表 as c on c.订单ID=b.订单ID
group by b.用户名，b.电话，b.地址，a.订单ID

对于这样的表结构，对于sum（），group by会产生临时表，增加IO量。我们怎么优化都效率不高，那我们怎么样才能让它效率高了，就需要一些字段进行冗余。

订单表中增加了冗余字段，那SQL该怎么写了？

select a.用户名，a.电话，a.地址，a.订单ID,a.订单价格  from 订单表 as a

说明：表结构的设计直接涉及到SQL的查询效率及优化。
范式化设计和反范式化设计的对比：

1、范式化可以尽量的减少数据冗余

2、范式化的更新操作比反范式化更快

3、范式化的表通常比反范式化的表要小

4、反范式化减少表的关联

5、反范式化相比范式化可以更好的对索引进行优化，例如使用覆盖索引
三、数据库表的垂直拆分
垂直拆分定义
将一个属性较多，一行数据较大的表，将不同的属性拆分到不同的表中，以降低单库（表）大小，达到提升性能的目的的方法，这解决了表的宽度问题。
特点：
（1）每个库（表）的结构都不一样

（2）一般来说，每个库（表）的属性至少有一列交集，一般是主键

（3）所有库（表）的并集是全量数据
当一个表属性很多时，如何来进行垂直拆分呢？如果没有特殊情况，拆分依据主要有几点：

（1）将长度较短，访问频率较高的属性尽量放在一个表里，这个表暂且称为主表

（2）将字段较长，访问频率较低的属性尽量放在一个表里，这个表暂且称为扩展表

如果1和2都满足，还可以考虑第三点：

（3）经常一起访问的属性，也可以放在一个表里

优先考虑1和2，第3点不是必须。另，如果实在属性过多，主表和扩展表都可以有多个
一般来说，数据量并发量比较大时，数据库的上层都会有一个服务层。需要注意的是，当应用方需要同时访问主表和扩展表中的属性时，服务层不要使用join来连表访问，而应该分两次进行查询：
原因是，大数据高并发互联网场景下，一般来说，吞吐量和扩展性是主要矛盾：

（1）join更消损耗数据库性能

（2）join会让base表和ext表耦合在一起（必须在一个数据库实例上），不利于数据量大时拆分到不同的数据库实例上（机器上）。毕竟减少数据量，提升性能才是垂直拆分的初衷。

为什么要这么这么拆分

为何要将字段短，访问频率高的属性放到一个表内？为何这么垂直拆分可以提升性能？因为：

（1）数据库有自己的内存buffer，会将磁盘上的数据load到内存buffer里（暂且理解为进程内缓存吧）

（2）内存buffer缓存数据是以row为单位的

（3）在内存有限的情况下，在数据库内存buffer里缓存短row，就能缓存更多的数据

（4）在数据库内存buffer里缓存访问频率高的row，就能提升缓存命中率，减少磁盘的访问
举个例子
假设数据库内存buffer为1G，未拆分的user表1行数据大小为1k，那么只能缓存100w行数据。

如果垂直拆分成user_base和user_ext，其中：

（1）user_base访问频率高（例如uid, name, passwd, 以及一些flag等），一行大小为0.1k

（2）user_ext访问频率低（例如签名, 个人介绍等），一行大小为0.9k

那边内存buffer就就能缓存近乎1000w行user_base的记录，访问磁盘的概率会大大降低，数据库访问的时延会大大降低，吞吐量会大大增加。
垂直拆分原则：
1、把不常用的字段表单独存放到一个表中。
2、把大字段独立存放到一个表中。
3、把经常一起使用的字段放到一起。

在该表中，title和description这两个字段占空间比较大，况且在使用频率也比较低，因此可以将其提取出来，将上面的一个达标垂直拆分为两个表（film和film_ext）：如下所示：


四、数据库表的水平拆分
垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。
相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中 的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式，如图：


优点：

    1. 不存在单库大数据，高并发的性能瓶颈。

    2. 对应用透明，应用端改造较少。     

    3. 按照合理拆分规则拆分，join操作基本避免跨库。

    4. 提高了系统的稳定性跟负载能力。

缺点：

    1. 拆分规则难以抽象。

    2. 分片事务一致性难以解决。

    3. 数据多次扩展难度跟维护量极大。

    4. 跨库join性能较差。

水平不拆分原因
如果单表的数据量达到上亿条，那么这时候我们尽管加了完美的索引，查询效率低，写入的效率也相应的降低。
如何将数据平均分为N份
通常水平拆分的方法为：
1、对customer_id进行hash运算，如果要拆分为5个表则使用mod（customer_id，5）取出0-4个值。
2、针对不动的hashid把数据存储到不同的表中。
水平拆分面临的挑战
1、夸分区表进行数据查询
前端业务统计：
业务上给不同的用户返回不同的业务信息，对分区表没有大的挑战。
2、统计及后台报表操作
但是对后台进行报表统计时，数据量比较大，后台统计时效性比较低，后台就用汇总表，将前后台的表拆分开。

总：
拆分的处理难点两张方式共同缺点

    1. 引入分布式事务的问题。
    2. 跨节点Join 的问题。
    3. 跨节点合并排序分页问题。

针对数据源管理，目前主要有两种思路：

    A. 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个 数据库，在模块内完成数据的整合。 
    优点：相对简单，无性能损耗。   
    缺点：不够通用，数据库连接的处理复杂，对业务不够透明，处理复杂。

   B. 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明；   
    优点：通用，对应用透明，改造少。   
    缺点：实现难度大，有二次转发性能损失。

拆分原则

    1. 尽量不拆分，架构是进化而来，不是一蹴而就。(SOA)
    2. 最大可能的找到最合适的切分维度。
    3. 由于数据库中间件对数据Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取  尽量少使用多表Join -尽量通过数据冗余，分组避免数据垮库多表join。
    4. 尽量避免分布式事务。
    5. 单表拆分到数据1000万以内。