MySQL 库内分库分表解读
对于轻量级数据库而言,总会有一定的瓶颈出现。硬件资源上的瓶颈,比如:IO,CPU,网络,内存等。硬件瓶颈到了一定程度是无法提升的,或无法提供更多的资源,那剩下的只能从数据库软件,设计,使用方面进行优化,达到预期目标。
当这些都无法满足的情况下,才会用到分布式数据库分库分表方式。但同比单实例投入的资源非常多的。
那么数据库内怎样进行分库分表:
(1)什么时候才需要分库分表?评判标准又是什么?
(2)按照数据增长速度,一张表存储了多少数据的时候,才需要考虑分库分表?
(3)MySQL内如何分库分表?
下面围绕着MySQL的这些问题,开始经验之旅。
数据量
从MySQL的处理能力为什么说,最高数据行只能到千万级?
按照官方给的参考值,单个表空间可以是TB级别的:
但如实际情况如何:
MySQL是索引组织表,所有数据是按照B+Tree 结构构成的。B树是一种自平衡树,新增节点和删除节点后,都会按照规则进行平衡,已满足B树的规则。
如果是3层数最多进行3次磁盘I/O觉得这样在索引设计良好的情况下,或者采用覆盖索引的情况下只进行3此I/O,复杂度为log(n)并不会太影响效率。
比如:bigint是8字节,指针又占用6字节,所以指针可以指向16384 /(8+6)= 1170 个非叶子节点同理,指向叶子节点的个数也可以是1170个。
3层高的一颗B+树可以存储:
第一层指向1170个非叶子节点,第二层再指向1170个叶子节点,
1170 * 1170 = 1368900 个叶子节点
如:一条记录1KB大小,那么一页16KB就可以存储16行,
16行 * 1368900 个叶子节点 = 21902400 行。
机械硬盘时代B树不要超过3层而得来的,固态硬盘时代,多几次IO一点问题都没有。目前按照接触的普遍经验单表数据行5000W~8000W内,也能达到比较好的性能。这样的数据量,实际数据文件大小在10G左右。
提问:数据量是否还可以在继续提升吗?
承载数据量当然可以继续提升,只是性能维持困难,超过1亿行之后,就会非常明显发现性能下降。这时候的瓶颈在MySQL的底层实现上。
应用瓶颈
无法提升硬件资源,满足不了业务需求,就需要数据库内分库分表。
-
对于TPS/QPS要求比较高,有一定影响单表的处理能力
普遍MySQL稍微高配置环境 TPS/QPS普遍都能维持在1W/10W以内指标,操过就无法满足性能要求。 -
发现某张表的不停的访问,内存中的命中率非常低,处理能力逐步衰落,特别是并发比较大的情况下。
通过应用处理能力的延迟反馈 和 数据库指标命中率和慢日志积累结合,进行有效的判断:
#如Status值:
Innodb_buffer_pool_read_requests表示read请求的次数,
Innodb_buffer_pool_reads表示从物理磁盘中读取数据的请求次数
缓存命中率的计算方法是:
(1- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) *100 %,
#如慢日志:
通过历史对比慢日志记录,进行判断。数据量无明显变化,执行效率变低 等
提问:添加多个应用节点 或 缓存机制(redis), 读写分离 是否能解决?
可能缓解有一定的作用,但根原解决不了。
- 多个应用最终还是会汇集到数据库上,压力还会上来,问题依然存在;
- 缓存有效解决查询上的问题,但无法解决增,删,更改的业务;
- 读写分离,因为不是实时同步,数据肯定存在一定延迟;
安全性和可用性
因设计方面考虑诸多系统类型契合,并且库表之间存在一定的耦合性,所以使用一个数据库:
- 当处于这种情况,因为每个业务的数据量、访问量都不同,不能因为一个业务把数据库搞挂而牵连到其他业务。
- 当一个数据库出现问题时,不会影响到其他业务,每个库只承担一部分业务,这样整体的可用性就能提高,安全也随着提高。就像比喻:“不要把鸡蛋放在一个篮子里”
提问:对于公用一个数据库实例,是否可用账号,资源方面 进行隔离?
账号隔离可以实现,但这只是表面,资源隔离才能完全解决这种情况。但资源隔离 目前MySQL无法实现。虽然MySQL8.0有这方面一些功能(资源组,连接内存限制)但不完善,还有MySQL底层还是单进程多线程,线程部分无法控制。
分库分表
那库内怎样进行有效的分库分表:
-
分库
随着业务推广力度加大,数据库终于成为了瓶颈,这个时候多个服务共享一个数据库基本不可行。需要将每个服务相关的表拆出来单独建立一个数据库,进行“分库”。
单数据库能够支撑的并发量是有限,拆成多个库可以使服务间不用竞争,提升服务的性能。
-
分表
目前常用的有2个维度,一是水平拆分和垂直拆分,二是拆分多个表:
水平拆分和垂直拆分:
水平拆分和垂直拆分意义在于大表拆分多个小表,不需要的数据,就不需要访问原则,单表处理能力提升,加快数据库的处理能力。
- 垂直拆分:基于表或字段划分,表结构不同。
- 水平拆分:基于数据划分,表结构相同,数据不同
拆分表:
冷热数据分离,按照规律数据拆分,单表处理能力提升,加快数据库的处理能力。这是分布式数据库之前 最常用的方式,目前也普遍使用。
- 取模方式表:hash取模 ,range,id 为奇数偶数 或 除于某个值 获取剩余值 等方式
- 每日表:只存储当天的数据。
- 每月表:只存储当月数据。
- 历史表:超过一定时间的数据迁移到 history表
总结
对于分库分表,首先得知道使用的数据库的处理能力,同时瓶颈在哪里,然后才能合理地拆分,只要能满足需求,拆分规则越简单越好。
分库分表的确解决了诸多问题,但是也给系统带来了很多复杂性:
- 跨库关联查询
- 聚合操作
- 定期维护,
- 单表数据量过大
不到万不得已不要轻易使用分库分表这个大招,避免"过度设计"和"过早优化"。分库分表之前,不要为分而分,先尽力去做力所能及的事情,例如:升级硬件、升级网络、读写分离、索引优化等等。当各方面达到瓶颈时候,再考虑分库分表。
免责声明:
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源,请您支持作者,购买正版内容。
6、资源失效,请下方留言,欢迎分享资源链接
文章评论