mysql专题15 性能优化之数据库结构优化

文章目录

1. 1. 列数据类型优化
   1. 1.1. 示例分析
2. 2. 表范式化优化
   1. 2.1. 示例分析
3. 3. 表反范式化优化
   1. 3.1. 示例分析
4. 4. 表垂直拆分
5. 5. 表水平拆分
6. 6. 总结

除了常见的SQL语句优化及索引优化等数据库优化手段外, 对数据库结构进行优化也是可以考虑的一个重要方向;

从以下几个方面来探讨数据库结构方面的优化实践,

• 列
  • 数据类型优化
• 数据表
  • 范式化优化
  • 反范式化优化
  • 垂直拆分
  • 水平拆分

列数据类型优化

如何选择合适的列数据类型?

1. 使用可以存下目标数据的最小的数据类型;
2. 使用简单的数据类型, Int要比varchar类型在mysql处理上简单;
3. 尽可能的使用NOT NULL定义字段;
4. 尽量少用text类型, 非用不可时最好考虑分表;

示例分析

使用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('2016-06-11 12:11:00'));

select FROM_UNIXTIME(timestr) from test;

• 在具体使用时也应用结合业务使用场景来进行优化，如业务经常需要对时间进行各种格式化转换, 甚至需要在不同的语言中转换, 则应存储成标准的时间格式 time.Time, 以免引起精度损失问题;

使用bigint来存储IP地址, 利用INET_ATON(), INET_NTOA()两个函数进行转换;

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

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

select inet_ntoa(ipaddr) from sessions;

• bigint 大概需要8个字节， 而varchar大概需要15个字节

表范式化优化

表范式化一般指第三设计范式, 即要求数据表中不存在非关键字段对任意候选键字段的传递函数依赖则符合第三范式;

示例分析

商品名	价格	重量	有效期	分类	分类描述
可乐	3.00	250ml	2015.6	饮料	碳酸饮料
雪碧	3.00	250ml	2015.6	饮料	碳酸饮料

显然上述商品表中存在如下传递函数依赖关系
(商品名)–>(分类)–>(分类描述)

也就是说存在非关键字”分类描述”对关键字”商品名”的传递函数依赖; 显然不符合第三设计范式, 不符合设计范式一般存在4大问题,

1. 数据冗余(分类, 分类描述) 对同一分类商品是一样的;
2. 数据插入异常; 插入了就能查到, 没有数据就查不到某些关键信息;
3. 更新异常;
4. 删除异常;

一般这种问题可以通过将表设计拆分为两张实体表, 另外在增加一个关系表; 如,

商品表(商品ming,价格,重量,有效期)

分类表(分类,分类描述)

商品分类(分类, 商品名)

表反范式化优化

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

示例分析

用户表(用户ID,姓名,电话,地址,邮编)
订单表(订单ID,用户ID,下单时间,支付类型,订单状态)
订单商品表(订单ID, 商品ID, 商品数量,商品价格)
商品表(商品ID,名称,描述,过期时间)

问题: 查询订单详情

常规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;

上述查询显然要关联多张表; 此外在group by 中如果存在非关键字索引字段， 则可能使用临时表来辅助查询, 那这样效率就比较低;

可以通过将订单表中冗余一部分用户信息, 这样可以通过查询订单表一张表即可完成查询需求;

订单表(订单ID,用户ID,下单时间,支付类型,订单状态,订单价格,用户名,电话,地址)

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

表垂直拆分

表的垂直拆分, 就是把原来有很多列的表拆分为多个表, 垂直拆分原则,

1. 把不常用的字段单独存放到一张表中;
2. 把大字段独立放到一个表中;
3. 把可能存在关联关系常用字段放到一张表中;

表水平拆分

表的水平拆分是为了解决单表的数据量过大的问题, 水平拆分的表每一个表的结构都是完全一致的; 通过取某个primary key 或者uniquekey 的值进行hash计算然后决定把数据存入tb1,tb2,…,tbn中某个表中;

但在以下场景中对sql操作会带来一些挑战,
1.跨分区表进行数据查询;
2.统计及后台报表操作;

总结

• 选择合适的列字段类型;
• 启用空间换时间的办法, 做一些反范式化设计冗余字段提高查询等操作效率;
• 针对数据表字段过多及数据量过多的情况的两种简单设计技巧的说明, 实际情况中会通过分库分表来做, 可以封装一套mysql来支持， 也可以采用现成的开源db, 如tidb等方案;