mysql 数据结构优化_四、数据结构优化

## 1、选择合适的数据类型

`数据类型的选择，重点在于'合适'二字，如何确定选择的数据类型是否合适？`

* 使用可以存下你的数据的最小的数据类型。

* 使用简单的数据类型。Int 要比 varchar 类型在 MySQL 处理上简单。

* 使用int来存储日期时间，利用FROM\_UNIXTIME\(\),UNIX\_TIMESTAMP\(\)两个函数来进行转换；

* 尽可能的使用 not null 定义字段。

* 尽量少用text类型，非用不可时最好考虑分表。

* 使用bigint来存储IP地址，利用INET\_ATON\(\),INET\_NTOA\(\)两个函数来进行转换；

## 2、数据库表的范式化优化

* 范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。


* 上图存在以下传递函数依赖关系：

\(商品名称\)->\(分类\)->\(分类描述\)

也就是说存在非关键字段"分类描述"对关键字段"商品名称"的传递函数依赖。

* 不符合第三范式要求的表存在下列问题：

* 数据冗余：\(分类，分类描述\)对于每一个商品都会进行记录；

* 数据的插入异常；

`当表中没有饮料商品时，就没有地方记录饮料分类的相关信息。`

* 数据的更新异常；

`当想更新饮料商品的描述信息，就要把表中所有的饮料分类描述信息更新`

* 数据的删除异常。

`删除饮料的所有商品后，也就无法选择饮料这个分类，此称为'删除异常'`


## 3、数据库表的反范式化优化

`反范式化是指为了查询效率的考虑把原本符合第三范式的表‘适当’的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。`


* 思考如何查询订单信息？

```sql

SELECT b.用户名,b.电话,b.地址,a.订单ID,SUM(C.商品价格*c.商品数量) as 订单价格

FROM '订单表' a

JOIN '用户表' b ON a.用户ID=b.用户ID

JOIN '订单商品表' c ON c.订单ID=b.订单ID

GROUP BY b.用户名,b.电话,b.地址,a.订单ID

```

* 对上面的表进行了反范式化后


```sql

SELECT a.用户名,a.电话,a.地址,a.订单ID,a.订单价格 FROM '订单表' a;

```

## 4、数据库表的垂直拆分

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：


* 把不常用的字段单独存放到一个表中。

* 把大字段单独存放到一个表中。

* 把经常一起使用的字段放到一起。

>垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可;

## 5、数据库表的水平拆分

表的水平拆分主要是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。

常见的拆分方法为：

* 对 customer\_id 进行 hash 运算，如果要拆分成5个表则使用mod\(customer\_id,5\) 取出0-4个值；

* 针对不同的hashID把数据存到不同的表中。

### 水平拆分的一些技巧

**1. 拆分原则**

通常情况下，我们使用取模的方式来进行表的拆分;比如一张有400W的用户表`users`，为提高其查询效率我们把其分成4张表`users1，users2，users3，users4`

通过用ID取模的方法把数据分散到四张表内`Id%4+1 = [1,2,3,4]`

然后查询,更新,删除也是通过取模的方法来查询

```

$_GET['id'] = 17,

17%4 + 1 = 2,

$tableName = 'users'.'2'

Select * from users2 where id = 17;

```

在insert时还需要一张临时表uid_temp来提供自增的ID,该表的唯一用处就是提供自增的ID;

```

insert into uid_temp values(null);

```

得到自增的ID后,又通过取模法进行分表插入;

>注意,进行水平拆分后的表,字段的列和类型和原表应该是相同的,但是要记得去掉auto_increment自增长

**另外**

* 部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;

* 进行拆分后的表，只能满足部分查询的高效查询需求，这时我们就要在产品策划上，从界面上约束用户查询行为。比如我们是按年来进行归档拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询;

* 在做分析或者统计时，由于是自己人的需求,多点等待其实是没关系的,并且并发很低,这个时候可以用union把所有表都组合成一张视图来进行查询,然后再进行查询;

```

Create view users as select from users1 union select from users2 union.........

```