数据库结构的优化

数据库结构的优化

1、选择合适的数据类型
数据类型的选择，重点在于“合适”二字，如何确定选择的数据类型是否合适了？
1、使用可以存下你的数据的最小的数据类型。（时间类型数据：可以使用varchar类型，可以使用int类型，也可以使用时间戳类型）
2、使用简单的数据类型，int要比varchar类型在mysql处理上简单。（int类型存储时间是最好的选择）
3、尽可能的使用not null定义字段。（innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
4、尽量少用text类型，非用不可时最好考虑分表。
2、案例
案例一：int类型存储时间-时间转换
使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。
创建表：

create table test(
id int auto_increment not null,
timestr int ,
primary key(id)
);

导入数据：

insert into test (timestr) values (unix_timestamp('2018-05-29 16:00:00'));

查询数据：如下图所示：

时间进行转换：

select FROM_UNIXTIME(timestr) from test;

结论：
1、unix_timestamp()函数是将日期格式的数据转换为int类型
2、FROM_UNIXTIME(timestr)函数是将int类型转换为时间格式
案例二：ip地址的存储
在我们的外部应用中，都要记录ip地址，大部分场合都是varchar（15）进行存储，就需要15个字节进行存储，但是bigint只需要8个字节进行存储，当数据量很大的时候（千万级别的数据），相差7个字节，但是不能小看这7个字节，给大家算一下。

一个字段就多这么多，那如果我们这样的字段需要上万个字段了？是需要很多的存储空间的。
使用bigint（8）来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换。

创建表：

create table sessions(
id int auto_increment not null,
ipaddress bigint,
primary key (id)
);

导入数据：

insert into sessions (ipaddress)values (inet_aton('192.168.0.1'));

转换：

select inet_ntoa(ipaddress) from sessions;  

检索:

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

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

不符合第三范式要求的表存在以下问题：
1、数据冗余：（分类，分类描述）对于每一个商品都会进行记录。
2、数据的插入异常
3、数据的更新异常
4、数据的删除异常（删除所有数据，分类和分类描述都会删除，没有所有的记录）

如何转换成符合第三范式的表（拆分表）：
将原来的不符合第三范式的表拆分为3个表
商品表、分类表、分类和商品的关系表

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

如何查询订单信息？

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的查询效率及优化。
3、数据库表的垂直拆分
1、垂直拆分定义
所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。
2、垂直拆分原则
通常垂直拆分可以按以下原则进行：
1、把不常用的字段表单独存放到一个表中。
2、把大字段独立存放到一个表中。
3、把经常一起使用的字段放到一起。
例子：以film表为例

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

2、

4、数据库表的水平拆分
1、为什么水平拆分
表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的，以下面的peyment表为例来说明

desc payment;

show create table payment;

CREATE TABLE `payment` (
  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  KEY `inx_paydate` (`payment_date`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8

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