数据优化——分库分表(二)策略讲解
数据优化——分库分表(三)中间件讲解
数据优化——分库分表(四)高级策略
1 数据优化的前提是数据预估
数据的量永远是在增长的,不会是死的, 所以你优化的前提是对数据的预估,确保自己的方案某时间段内某数据量内有用(没有对的方案,只有适合的方案)。
要学会跟业务交流协商拟定数据量,比如2年内,用户数量500个W
- 一个用户数据是10/年,一年的重量就是5000W条
2 优化思路(面试题)
按上面的的数据规格出题,提高数据检索性能,有的朋友可能就很粗暴,“假设机器以1000W性能为优,直接分5张表”。也不能说是错,只是这样粗暴的分析不一定能得到最好的效果和ROI(产出比)。
- 不分库分表
- 软优化
- 数据库参数的调整,例如连接数,内存量。
- 慢SQL的检索,分析执行计划,进行SQL和程序的优化
- 优化数据库的索引结构
- 优化数据库的表结构(该冗余的还是要冗余的)
- 引⼊NOSQL和程序架构调整(比如ES宽表,比如读写分离)
- 硬优化
- 提升系统硬件(更快的IO、更多的内存):带宽、CPU、硬盘
- 分库分表
- 根据业务情况而定,选择适合的分库分表策略(策略因业务而变),比如外卖,电商,物流,甚至不同领域里面的不同业务都会不同。
- 先看只分表能不能满足业务现有问题和持续增长的量级问题
- 分表⽆法给数据库的并发操作带来效率上的提⾼,分表的实质还是在⼀个数据库上进⾏的操作,受数据库IO性能的限制。如果单分表满⾜不了需求,再分库分表⼀起
- 结论
在数据量及访问压⼒不是特别⼤的情况,⾸先考虑缓存、读写
分离、索引技术等⽅案
如果数据量极⼤,且业务持续增⻓快,再考虑分库分表⽅案(因为分库分表有利,也有弊)
3 分库分表的优点
3.1分库分表解决的现状问题
-
解决数据库本身瓶颈
-
连接数: 连接数过多时,就会出现‘too many connections’的错误,访问量太⼤或者数据库设置的最⼤连接数太⼩的原因,Mysql默认的最⼤连接数为100.可以修改,⽽mysql服务允许的最⼤连接数为16384
-
数据库分表可以解决单表海量数据的查询性能问题
-
数据库分库可以解决单台数据库的并发访问压⼒问题
-
解决系统本身IO、CPU瓶颈
例如:
磁盘读写IO瓶颈,热点数据太多,尽管使⽤了数据库本身缓存,但是依旧有⼤量IO,导致sql执⾏速度慢。
⽹络IO瓶颈,请求的数据太多,数据传输⼤,⽹络带宽不够,链路响应时间变⻓ 。
CPU瓶颈,尤其在基础数据ᰁ⼤单机复杂SQL计算,SQL语句执⾏占⽤CPU使⽤率⾼,也有扫描⾏数⼤、锁冲突、锁等待等原因
4 分库分表的缺点
- 跨节点数据库Join关联查询和多维度查询
- 数据库切分前,多表关联查询,可以通过sql join进⾏实现,分库分表后,数据可能分布在不同的节点上,sql join带来的问题就⽐较麻烦。
- 不同维度查看数据,利⽤的partitionKey是不⼀样的,订单表 的partionKey是user_id,⽤户查看⾃⼰的订
单列表⽅便,但商家查看⾃⼰店铺的订单列表就麻烦,分布在不同数据节点,查询结果需要聚合。
- 分库操作带来的分布式事务问题
- 操作内容同时分布在不同库中,不可避免会带来跨库事务问题,即分布式事务
- 执⾏的SQL排序、翻⻚、函数计算问题
- 分库后,数据分布再不同的节点上, 跨节点多库进⾏查询时,会出现limit分⻚、order by排序等问题 。
- ⽽且当排序字段⾮分⽚字段时,更加复杂了,要在不同的分⽚节点中将数据进⾏排序并返回,然后将不同分⽚返回的结果集进⾏汇总和再次排序。(也会带来更多的CPU/IO资源损耗)
- 数据库全局主键重复问题
- 常规表的id是使⽤⾃增id进⾏实现,分库分表后,由于表中数据同时存在不同数据库中,如果⽤⾃增id,则会出现冲突问题。
- 容规划,分库分表后⼆次扩容问题
- 业务发展快,初次分库分表后,满⾜不了数据存储,导致需要多次扩容
- 分库分表技术选型问题
- 市场分库分表中间件相对较多,框架各有各的优势与短板,应该如何选择(可能有的技术方案今年适用,明年就不适合了,又需要迭代)
5 垂直分表-垂直分库
5.1 垂直分表
适合: 表字段太多,每个字段访问频次不⼀样,浪费了IO资源,需要进⾏优化
- 也就是“⼤表拆⼩表”,基于列字段进⾏的。
- 拆分原则⼀般是表中的字段较多,将不常⽤的或者数据较⼤,⻓度较⻓的拆分到“扩展表 如text类型字段。
- 访问频次低、字段⼤的商品描述信息单独存放在⼀张表中。
- 访问频次较⾼的商品基本信息单独放在⼀张表中
例子:
//拆分前
CREATE TABLE `product` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(524) DEFAULT NULL COMMENT '视频标
题',
`cover_img` varchar(524) DEFAULT NULL COMMENT '封⾯
图',
`price` int(11) DEFAULT NULL COMMENT '价格,分',
`total` int(10) DEFAULT '0' COMMENT '总库存',
`left_num` int(10) DEFAULT '0' COMMENT '剩余',
`learn_base` text COMMENT '课前须知,学习基础',
`learn_result` text COMMENT '达到⽔平',
`summary` varchar(1026) DEFAULT NULL COMMENT '概
述',
`detail` text COMMENT '视频商品详情',
//拆分后
//适合列表查询,多频
CREATE TABLE `product` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(524) DEFAULT NULL COMMENT '视频标
题',
`cover_img` varchar(524) DEFAULT NULL COMMENT '封⾯
图',
`price` int(11) DEFAULT NULL COMMENT '价格,分',
`total` int(10) DEFAULT '0' COMMENT '总库存',
`left_num` int(10) DEFAULT '0' COMMENT '剩余',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT
CHARSET=utf8;
//适合详情查询,较低频(也减少了大字段冗余)
CREATE TABLE `product_detail` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) DEFAULT NULL COMMENT '产品主
键',
`learn_base` text COMMENT '课前须知,学习基础',
`learn_result` text COMMENT '达到⽔平',
`summary` varchar(1026) DEFAULT NULL COMMENT '概
述',
`detail` text COMMENT '视频商品详情',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT
CHARSET=utf8;
5.2 垂直分库
适合: 项⽬⾥⾯单个数据库的CPU、内存⻓期处于90%+的利⽤率,数据库连接经常不够,需要进⾏优化
- 垂直分库针对的是⼀个系统中的不同业务进⾏拆分, 数据库的连接资源⽐较宝贵且单机处理能⼒也有限。
- 没拆分之前全部都是落到单⼀的库上的,单库处理能⼒成为瓶颈,还有磁盘空间,内存,tps等限制 。
- 拆分之后,避免不同库竞争同⼀个物理机的CPU、内存、⽹络IO、磁盘,所以在⾼并发场景下,垂直分库⼀定程度上能够突破IO、连接数及单机硬件资源的瓶颈。
- 垂直分库可以更好解决业务层⾯的耦合,业务清晰,且⽅便管理和维护。
- ⼀般从单体项⽬升级改造为微服务项⽬,一定程度上来说就是垂直分库(因为业务区分了,每个业务也都是自己独立的库)。
问题: 垂直分库分表可以提⾼并发,但是依然没有解决单表数据量过⼤的问题
6 水平分表-水平分库
6.1 水平分表
适合: 当⼀张表的数据达到⼏千万时,查询⼀次所花的时间⻓,需要进⾏优化,缩短查询时间。
- 都是⼤表拆⼩表(垂直分表:表结构拆分;⽔平分表:数据拆分)
- 把⼀个表的数据分到⼀个数据库的多张表中,每个表只有这个表的部分数据。
- 核⼼是把⼀个⼤表,分割N个⼩表,每个表的结构是⼀样的,数据不⼀样,全部表的数据合起来就是全部数据。
- 针对数据ᰁ巨⼤的单张表(⽐如订单表),按照某种规则(RANGE,HASH取模等),切分到多张表⾥⾯去。
- 但是这些表还是在同⼀个库中,所以单数据库操作还是有IO瓶颈,主要是解决单表数据ᰁ过⼤的问题。
- 减少锁表时间,没分表前,如果是DDL(create/alter/add等)语句,当需要添加⼀列的时候mysql会锁表,期间所有的读写操作只能等待。
6.1 水平分库
适合: ⾼并发的项⽬中,⽔平分表后依旧在单个库上⾯,1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢,需要进⾏优化。
- 把同个表的数据按照⼀定规则分到不同的数据库中,数据库在。
- ⽔平分库是把不同表拆到不同数据库中,它是对数据⾏的拆分,不影响表结构。
- 每个库的结构都⼀样,但每个库的数据都不⼀样,没有交集,所有库的并集就是全量数据。
- ⽔平分库的粒度,⽐⽔平分表更⼤。
7 总结
分库分表是有利,有弊的,在可以单表单库(读写分离)优化完成的情况下一般不会选择封库分表。即时,使用了分库分表,也是在对DB单库单表的优化基础上使用的,所以单库单表优化的能力和还是十分重要的。