一文搞懂数据库分库分表最佳实践及面试题

24 篇文章 2 订阅
8 篇文章 0 订阅

春风如贵客,一到便繁华。各位看官点赞再看,养成好习惯(●´∀`●)

目录:

前言:

水平拆表:

水平拆分的优点:

水平切分的缺点:

垂直拆表:

垂直切分的优点:

垂直切分的缺点:

面试题:


前言:

目前很多互联网系统都存在单表数据量过大的问题,这就降低了查询速度,影响了客户体验。为了提高查询速度,我们可以优化sql语句,优化表结构和索引,不过对那些百万级千万级的数据库表,即便是优化过后,查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量,从而提高查询速度,

分表的方式有两种:水平拆分和垂直拆分,两者各有利弊,适用于不同的情况。

分库的工具有很多:这里推荐两种(工具的利弊,请自行调研,官网和社区优先,面试题部分会简单介绍下。)

  • Sharding-Sphere:jar,前身是sharding-jdbc;

  • Mycat:中间件。

首先要明确的一点是,分库分表,首先得知道瓶颈在哪里,然后才能合理地拆分。明确了瓶颈在哪里,才可以选择合适的方法进行操作,做到事半功倍的效果。


水平拆表:

水平拆分:指的是按照数据库表行的拆分。

根据阿里巴巴设计规范:单表一到两年内数据量超过500w或数据容量超过10G考虑分表。这时可以把一张的表的数据拆成多张表来存放。

以user表为例:有两种拆分方法:

1、提前建好若干张表。

经过业务评估,创建5张user表可以满足业务未来三到五年的业务增长量,那么可以采用此方法。我们要使用一张新的用户id自增表,表中只保留用户主键,方便水平拆表查询数据的时候对用户id进行取模。

eg:将原来user表拆分成 -->  user1,user2,user3,user4,user5,user_temp。一共六张表。user_temp表用来存放用户主键,其余的五张表的字段和user 表字段一直,但是拆分的user表要将主键自增给去除掉。user1...user5表中的主键字段只是用来保留user_temp 表的主键映射(类似于外键的概念)

新增用户的时候:第一步,要将用户主键保留到user_temp表。第二步,将新增得到的主键userId=7进行取模,这里建立了5张分表,那么就对5取模:userId % 5。将取模后得到的数字与user表名进行拼接,即可得到 一个完整的表名。即(7%5=2)->user2表。

2、按照一个标准自动创建表。

业务是持续性的增长,如果采用上面的方法,一到两年后是会形成5张超级大表。那么我们可以采用一个固定的策略进行分表操作。

eg:经业务评估,user表每一个月就会有500W的数据,那么可以在新增user表的时候,判断当前月份,比如判断当前时间是3月份,那么就创建在3月份的表:user_2020_03 即可。具体新增和查找方法就不细说了,有问题的留言即可哦。

库内分表只解决了单一表数据量过大的问题,但没有将表分布到不同机器的库上,因此对于减轻MySQL数据库的压力来说,帮助不是很大,大家还是竞争同一个物理机的CPU、内存、网络IO,最好通过分库分表来解决。

水平拆分的优点:

  • 表关联基本能够在数据库端全部完成;
  • 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题;
  • 应用程序端整体架构改动相对较少;
  • 事务处理相对简单;
  • 只要切分规则能够定义好,基本上较难遇到扩展性限制;

水平切分的缺点:

  • 切分规则相对更为复杂,很难抽象出一个能够满足整个数据库的切分规则;
  • 后期数据的维护难度有所增加,人为手工定位数据更困难;
  • 应用系统各模块耦合度较高,可能会对后面数据的迁移拆分造成一定的困难。

垂直拆表:

垂直拆分:指的是按照数据表列的拆分。

垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似,按业务分类进行独立划分,与"微服务治理"的做法相似。适用于:把一张列比较多的表拆分为多张表。表的记录并不多,但是字段却很长,表占用空间很大,检索表的时候需要执行大量的IO,严重降低了性能。这时需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。

岔开个题外话,如果严格按照阿里巴巴设计规范:

  • 单条记录大小禁止超过8k;
  • 日志类数据不建议存储在MySQL上;
  • 禁止使用blob、text类型保留大文本、文件、图片,建议使用其他方式存储(TFS/SFS),MySQL只保存指针信息。

但是很多软件开发公司,因为历史遗留或者技术选型的问题,做不到如上的要求,但是确实有将大字段拆分出去的需求,也是可以进行垂直拆分的哦。不要着急。

还是以user表为例:

通常我们按以下原则进行垂直拆分:

1、把不常用的字段单独放在一张表;

2、把text,blob,mediumtext等大字段拆分出来放到附表中;

3、将经常组合查询到列放到一张表中。(什么你说你要对大字段加索引??那么可以考虑对大字段进行md5加密,然后将md5值添加索引)

通过"大表拆小表",更便于开发与维护,也能避免跨页问题,MySQL底层是通过数据页存储的,一条记录占用空间过大会导致跨页,造成额外的性能开销。另外数据库以行为单位将数据加载到内存中,这样表中字段长度较短且访问频率较高,内存能加载更多的数据,命中率更高,减少了磁盘IO,从而提升了数据库性能。

垂直切分的优点:

  • 数据库的拆分简单明了,拆分规则明确;
  • 应用程序模块清晰明确,整合容易;
  • 数据维护方便易行,容易定位;

垂直切分的缺点:

  • 部分表关联无法在数据库级别完成,需要在程序中完成;
  • 对于访问极其频繁且数据量超大的表仍然存在性能平静,不一定能满足要求;
  • 分布式事务处理复杂;
  • 依然存在单表数据量过大的问题(需要水平切分)


面试题:

一、谈谈什么是分库分表(为什么要分库分表)。

答:你都看到这里了,如果脑海里还没有一些逻辑的话,那么请再看一遍。

二、他们都有什么优缺点。

答:同第一问答案。

三、了解那些分库分表中间件(用过哪些分库分表中间件)?

答:sharding-jdbc 和 mycat之类的。具体请百度官网,就不贴废话了。

四、不同的分库分表中间件都有什么优点和缺点?

答:

sharding-jdbc

1.可适用于任何基于java的ORM框架,如:JPA、Hibernate、Mybatis、Spring JDBC Template,或直接使用JDBC

2.可基于任何第三方的数据库连接池,如:DBCP、C3P0、Durid等

3.分片策略灵活,可支持等号、between、in等多维度分片,也可支持多分片键。

4.SQL解析功能完善,支持聚合、分组、排序、limit、or等查询,并支持Binding Table以及笛卡尔积表查询。

5.性能高,单库查询QPS为原生JDBC的99.8%,双库查询QPS比单库增加94%。

缺点:

1.理论上可支持任意实现JDBC规范的数据库。目前仅支持mysql

2.维护会比较麻烦,需要逐个项目的修改配置。不能进行跨库连接,代码需要进行改造。

3.在扩展数据库服务器时需要考虑一致性哈希问题,或者采用分片键局部取模方式,也难免要进行部分的数据迁移。

mycat

优点:

1.支持Mysql集群,可以作为Proxy使用

2.支持JDBC连接ORACLE、DB2、SQL Server,将其模拟为MySQL Server使用

3.自动故障切换,高可用性

4.支持读写分离,支持Mysql双主多从,以及一主多从的模式 ,支持全局表,数据自动分片到多个节点,用于高效表关联查询

5.支持独有的基于E-R 关系的分片策略,实现了高效的表关联查询

6.多平台支持,部署和实施简单

缺点:

1.mycat不支持二维路由,仅支持单库多表或多库单表 由于自定义连接池,这样就会存在mycat自身维护一个连接池,MySQL也有一个连接池,任何一个连接池上限都会成为性能的瓶。

综合比较

1、mycat是一个中间件的第三方应用,sharding-jdbc是一个jar包

2、使用mycat时不需要改代码,而使用sharding-jdbc时需要修改代码

五、你们具体是如何对数据库如何进行垂直拆分或水平拆分的?

答:同第一问答案。


创作不易,各位的支持和认可,就是我创作的最大动力,

【转载请联系本人】 如有问题,请联系我。欢迎斧正!不胜感激 !

求点赞👍 求关注❤️ 求分享👥 求留言📪

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值