一、数据类型选择
1、使用可以存下你的数据的最小的数据类型。(时间类型数据:可以使用varchar类型,可以使用int类型,也可以使用时间戳类型)
2、使用简单的数据类型,int要比varchar类型在mysql处理上简单。(int类型存储时间是最好的选择)
3、尽可能的使用not null定义字段。(innodb的特性所决定,非not null的值,需要额外的在字段存储,同时也会增加IO和存储的开销)
4、尽量少用text类型,非用不可时最好考虑分表。
二、数据库表的范式化优化
- 第一范式:每个列都是不可拆分的最小数据单元,数据原子性。
- 第二范式:必须满足第一范式,必须有一个(及以上)主键,非主键列必须全部依赖于全部主键。
- 第三范式:必须满足第二范式,非主键列必须直接依赖于主键,不能传递依赖。
如下表:
存在以下传递函数依赖关系:
(商品名称)->(分类)->(分类描述)
也就是说存在非关键字段 “分类描述”对关键字段“商品名称”的传递函数依赖。
不符合第三范式要求的表存在以下问题:
1、数据冗余:(分类,分类描述)对于每一个商品都会进行记录。
2、数据的插入异常
3、数据的更新异常
4、的删除异常(删除所有数据,分类和分类描述都会删除,没有所有的记录)
但是我们在设计表的时候一般都会采用反范式化。
反范式化是指为了查询效率的考虑把原本符合第三范式的表“适当”的增加冗余,以达到优化查询效率的目的,反范式化是一种以空间来换取时间的操作。
比如如下用户表和订单表:
在查询订单的时候,我们需要知道用户名、用户电话、用户地址,那么我们就需要两表联查,这样就增加了查询的复杂度,效率降低。
如果采用反范式化设计的话,如下:
这样我们直接查订单表就可以得到我们想要的所有信息。
三、数据库表的垂直拆分
所谓的垂直拆分,就是把原来一个有很多列的表拆分成多个表,这解决了表的宽度问题。
通常垂直拆分可以按以下原则进行:
1、把不常用的字段表单独存放到一个表中。
2、把大字段独立存放到一个表中。
3、把经常一起使用的字段放到一起。
四、数据库表的水平拆分
- 为什么水平拆分?
表的水平拆分是为了解决单表数据量过大的问题,水平拆分的表每一个表的结构都是完全一致的 - 水平不拆分原因?
如果单表的数据量达到上亿条,那么这时候我们尽管加了完美的索引,查询效率低,写入的效率也相应的降低。 - 如何将数据平均分为N份?
通常水平拆分的方法为:
1、对customer_id进行hash运算,如果要拆分为5个表则使用mod(customer_id,5)取出0-4个值。
2、针对不动的hashid把数据存储到不同的表中。 - 水平拆分面临的挑战
1、夸分区表进行数据查询
前端业务统计:业务上给不同的用户返回不同的业务信息,对分区表没有大的挑战。
2、统计及后台报表操作
但是对后台进行报表统计时,数据量比较大,后台统计时效性比较低,后台就用汇总表,将前后台的表拆分开。
MyCat中间件
MyCAT是一款由阿里Cobar演变而来的用于支持数据库读写分离、分片的分布式中间件。MyCAT可不但支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库,同时也支持MongoDB等非关系型数据库。基础架构如下:
myCat实现水平拆分:
同一张表的不同记录,根据表的某个字段的某种规则拆分到多个数据库(主机)上,这既是水平拆分。
单库业务表可能会过于庞大,存在单库读写与存储瓶颈,这种情况可以通过水平拆分解决,水平拆分基本架构如下:
常用水平拆分规则:
- ID
- 日期
- 特定字段取模
优点:
- 拆分规则抽象好,join操作基本可以数据库内完成
- 不存在单库大数据,高并发的性能瓶颈
- 应用端改造少
- 提高了系统稳定性和负载能力
缺点:
- 拆分规则难以抽象
- 分片事务一致性难以解决
- 数据多次扩展难度跟维护量极大
- 跨库join性能较差