春风如贵客,一到便繁华。各位看官点赞再看,养成好习惯(●´∀`●)
目录:
前言:
目前很多互联网系统都存在单表数据量过大的问题,这就降低了查询速度,影响了客户体验。为了提高查询速度,我们可以优化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时需要修改代码
五、你们具体是如何对数据库如何进行垂直拆分或水平拆分的?
答:同第一问答案。
创作不易,各位的支持和认可,就是我创作的最大动力,
【转载请联系本人】 如有问题,请联系我。欢迎斧正!不胜感激 !
求点赞👍 求关注❤️ 求分享👥 求留言📪