MySQL优化 -- 数据库结构优化（三）

一、选择合适的数据类型

1、使用可以存下数据的最小的数据类型。

使用int来存储日期时间，利用from_unixtime(),unix_timestamp()两个函数来对日期格式进行转换：
insert into test(timestr) values(unix_timestamp('2020-02-21 15:34:33'));
select from_unixtime(timestr) from test;

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

使用bigint来存储IP地址，利用inet_aton(),inet_ntoa()两个函数来对IP格式进行转换：
insert into sessions(ipaddress) values(inet_aton('192.126.0.1'));
select inet_ntoa(ipaddress) from sessions;

3、尽可能的使用not null定义字段。
4、尽量少用text类型，非用不可时最好考虑分表。

二、表的范式化和反范式化

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

存在以下传递函数依赖关系：
商品名称 -> 分类 -> 分类描述 存在非关键字段“分类描述”对关键字段“商品名称”的传递函数依赖。
存在的问题：

1. 数据冗余：（分类，分类描述）对于每个商品都会进行记录。
2. 数据的插入，更新，删除异常：如果删除所有的商品，分类也就不存在了。

解决问题：拆分表结构，将分类，分类描述单独成表，建立关联即可。

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

三、数据表的拆分

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

1. 把不常用的字段单独存放到一个表中。
2. 把大字段独立存放到一个表中。
3. 把经常一起使用的字段放到一起。

电影表：

将title 和 description 大字段进行拆分：


2、水平拆分：为了解决单表的数据量过大的问题，水平拆分的表结构都是一样的。
常用水平拆分方法为：

1. 对id进行hash运算，如果要拆分成5个表则使用mod（id，5）取出0-4个值。
2. 针对不同的hashID把数据存到不同的表中。

面临的挑战：

1. 跨分区表进行数据查询。
2. 统计及后台报表操作。

解决：后台采用汇总表进行操作。