如何解决基于Mysql数据库亿级数据下的分库分表方…

2020-05-07 06:18:01来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

如何解决基于Mysql数据库亿级数据下的分库分表方案,Java架构必学

 

前言

移动互联网时代,海量的用户数据每天都在产生,基于用户使用数据等这样的分析,都需要依靠数据统计和分析,当数据量小时,数据库方面的优化显得不太重要,一旦数据量越来越大,系统响应会变慢,TPS直线下降,直至服务不可用。

补充

在mysql中,每个数据库最多可创建20亿个表,一个表允许定义1024列,每行的最大长度为8092字节(不包括copy文本和图像类型的长度)。当表中定义有varchar、nvarchar或varbinary类型列时,如果向表中插入的数据行超过8092字节时将导致Transact-SQL语句失败,并产生错误信息。SQL Server对每个表中行的数量没有直接限制,但它受数据库存储空间的限制。每个数据库的最大空间1048516TB,所以一个表可用的最大空间为1048516TB减去数据库类系zhidao统表和其它数据库对象所占用的空间。
这里还要注意:突破高薪Java架构项目经验永远是核心,如果你没有最新JAVA架构实战教程及大厂30k+面试宝典,可以去小编的Java架构学习.裙 :七吧伞吧零而衣零伞 (数字的谐音)转换下可以找到了,里面很多新JAVA架构项目教程,还可以跟老司机交流讨教! 

问题

可能有人会问,为何不用Oracle呢?确实,很多开发者写代码时并不会关心SQL的问题,凡是性能问题都交给DBA负责SQL优化,可是,不是每一个项目都会有DBA,也不是所有的项目都会采用Oracle数据库,而且,Oracle数据库在大数据量的背景下,解决性能问题,也不见的是一个非常轻松的事情。

那么,MySQL能不能支撑亿级的数据量呢,我的答案是肯定的,绝大部分的互联网公司,它们采用的数据存储方案,绝大部分都是以MySQL为主,不差钱的国企和银行,以Oracle为主,而且有专职的DBA为你服务。

本文会以一个实际的项目应用为例,层层向大家剖析[如何进行数据库的优化]项目背景是企业级的统一消息处理平台,客户数据在5千万加,每分钟处理消息流水1千万,每天消息流水1亿左右。

虽说MySQL单表可以存储10亿级的数据,但这个时候性能非常差。既然一张表无法搞定,那么就想办法将数据放到多个地方来解决问题吧,于是,数据库分库分表的方案便产生了,目前比较普遍的方案有三个:分区、分库分表、NoSQL/NewSQL。

在实际的项目中,往往是这三种方案的结合来解决问题,目前绝大部分系统的核心数据都是以RDBMS存储为主,NoSQL/NewSQL存储为辅。

分区


分区方案

分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引,从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。这个方案也不错,它对用户屏蔽了sharding的细节,即使查询条件没有sharding column,它也能正常工作(只是这时候性能一般)。不过它的缺点很明显:很多的资源都受到单机的限制,例如连接数,网络吞吐等。如何进行分区,在实际应用中是一个非常关键的要素之一。在我们的项目中,以客户信息为例,客户数据量5000万加,项目背景要求保存客户的银行卡绑定关系,客户的证件绑定关系,以及客户绑定的业务信息。此业务背景下,该如何设计数据库呢。项目一期的时候,我们建立了一张客户业务绑定关系表,里面冗余了每一位客户绑定的业务信息。基本结构大致如下:

 

" alt="image" data-src="https://user-gold-cdn.xitu.io/2020/4/30/171c984451b5cd35?imageView2/0/w/1280/h/960/format/webp/ignore-error/1" data-width="550" data-height="238">

 

查询时,对银行卡做索引,业务编号做索引,证件号做索引。随着需求大增多,这张表的索引会达到10个以上。而且客户解约再签约,里面会保存两条数据,只是绑定的状态不同。假设我们有5千万的客户,5个业务类型,每位客户平均2张卡,那么这张表的数据量将会达到惊人的5亿,事实上我们系统用户量还没有过百万时就已经不行了。MySQL数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看), 一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。这三个文件都非常的庞大,尤其是.myd文件,快5个G了。下面进行第一次分区优化,MySQL支持的分区方式有四种:

 

" alt="image" data-src="https://user-gold-cdn.xitu.io/2020/4/30/171c984451c8ca21?imageView2/0/w/1280/h/960/format/webp/ignore-error/1" data-width="550" data-height="222">

 

在我们的项目中,range分区和list分区没有使用场景,如果基于绑定编号做range或者list分区,绑定编号没有实际的业务含义,无法通过它进行查询,因此,我们就剩下 HASH 分区和 KEY 分区了, HASH 分区仅支持int类型列的分区,且是其中的一列。看看我们的库表结构,发现没有哪一列是int类型的,如何做分区呢?可以增加一列,绑定时间列,将此列设置为int类型,然后按照绑定时间进行分区,将每一天绑定的用户分到同一个区里面去。这次优化之后,我们的插入快了许多,但是查询依然很慢,为什么,因为在做查询的时候,我们也只是根据银行卡或者证件号进行查询,并没有根据时间查询,相当于每次查询,MySQL都会将所有的分区表查询一遍。

然后进行第二次方案优化,既然hash分区和key分区要求其中的一列必须是int类型的,那么创造出一个int类型的列出来分区是否可以。分析发现,银行卡的那串数字有秘密。银行卡一般是16位到19位不等的数字串,我们取其中的某一位拿出来作为表分区是否可行呢,通过分析发现,在这串数字中,其中确实有一位是0到9随机生成的,不同的卡串长度,这一位不同,绝不是最后一位,最后位数字一般都是校验位,不具有随机性。我们新设计的方案,基于银行卡号+随机位进行KEY分区,每次查询的时候,通过计算截取出这位随机位数字,再加上卡号,联合查询,达到了分区查询的目的,需要说明的是,分区后,建立的索引,也必须是分区列,否则的话,MySQL还是会在所有的分区表中查询数据。那么通过银行卡号查询绑定关系的问题解决了,那么证件号呢,如何通过证件号来查询绑定关系。前面已经讲过,做索引一定是要在分区健上进行,否则会引起全表扫描。

我们再创建了一张新表,保存客户的证件号绑定关系,每位客户的证件号都是唯一的,新的证件号绑定关系表里,证件号作为了主键,那么如何来计算这个分区健呢,客户的证件信息比较庞杂,有身份证号,港澳台通行证,机动车驾驶证等等,如何在无序的证件号里找到分区健。

为了解决这个问题,我们将证件号绑定关系表一分为二,其中的一张表专用于保存身份证类型的证件号,另一张表则保存其他证件类型的证件号,在身份证类型的证件绑定关系表中,我们将身份证号中的月数拆分出来作为了分区健,将同一个月出生的客户证件号保存在同一个区,这样分成了12个区,其他证件类型的证件号,数据量不超过10万,就没有必要进行分区了。这样每次查询时,首先通过证件类型确定要去查询哪张表,再计算分区健进行查询。

作了分区设计之后,保存2000万用户数据的时候,银行卡表的数据保存文件就分成了10个小文件,证件表的数据保存文件分成了12个小文件,解决了这两个查询的问题,还剩下一个问题就是,业务编号呢,怎么办,一个客户有多个签约业务,如何进行保存,这时候,采用分区的方案就不太合适了,它需要用到分表的方案。

分库分表


如何进行分库分表,目前互联网上有许多的版本,比较知名的一些方案:

  • 阿里的TDDL,DRDS和cobar,

  • 京东金融的sharding-jdbc;

  • 间组织的MyCAT;

  • 360的Atlas;

  • 美团的zebra;

  • 其他比如网易、58、京东等公司都有自研的中间件。

但是这么多的分库分表中间件方案,归总起来,就两类:client模式和proxy模式。

 

image

 

client模式

 

image

 

proxy模式

无论是client模式,还是proxy模式,几个核心的步骤是一样的:SQL解析,重写,路由,执行,结果归并。个人比较倾向于采用client模式,它架构简单,性能损耗也比较小,运维成本低。如果在项目中引入mycat或者cobar,他们的单机模式无法保证可靠性,一旦宕机则服务就变得不可用,你又不得不引入HAProxy来实现它的高可用集群部署方案, 为了解决HAProxy的高可用问题,又需要采用Keepalived来实现。

 

" alt="image" data-src="https://user-gold-cdn.xitu.io/2020/4/30/171c9844522e99e6?imageView2/0/w/1280/h/960/format/webp/ignore-error/1" data-width="550" data-height="610">

 

我们在项目中放弃了这个方案,采用了shardingjdbc的方式。回到刚才的业务问题,如何对业务类型进行分库分表。分库分表第一步也是最重要的一步,即sharding column的选取,sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。

而sharding column的选取跟业务强相关。在我们的项目场景中,sharding column无疑最好的选择是业务编号。通过业务编号,将客户不同的绑定签约业务保存到不同的表里面去,查询时,根据业务编号路由到相应的表中进行查询,达到进一步优化SQL的目的。

前面我们讲到了基于客户签约绑定业务场景的数据库优化,下面我们再聊一聊,对于海量数据的保存方案。

垂直分库


对于每分钟要处理近1000万的流水,每天流水近1亿的量,如何高效的写入和查询,是一项比较大的挑战。还是老办法,分库分表分区,读写分离,只不过这一次,我们先分表,再分库,最后分区。

我们将消息流水按照不同的业务类型进行分表,相同业务的消息流水进入同一张表,分表完成之后,再进行分库。我们将流水相关的数据单独保存到一个库里面去,这些数据,写入要求高,查询和更新到要求低,将它们和那些更新频繁的数据区分开。分库之后,再进行分区。

 

image

 

这是基于业务垂直度进行的分库操作,垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库,以达到系统资源的饱和利用率。这样的分库方案结合应用的微服务治理,每个微服务系统使用独立的一个数据库。将不同模块的数据分库存储,模块间不能进行相互关联查询,如果有,要么通过数据冗余解决,要么通过应用代码进行二次加工进行解决。

若不能杜绝跨库关联查询,则将小表到数据冗余到大数据量大库里去。假如,流水大表中查询需要关联获得渠道信息,渠道信息在基础管理库里面,那么,要么在查询时,代码里二次查询基础管理库中的渠道信息表,要么将渠道信息表冗余到流水大表中。

将每天过亿的流水数据分离出去之后,流水库中单表的数据量还是太庞大,我们将单张流水表继续分区,按照一定的业务规则,(一般是查询索引列)将单表进行分区,一个表编程N个表,当然这些变化对应用层是无法感知的。

 

image

 

分区表的设置,一般是以查询索引列进行分区,例如,对于流水表A,查询需要根据手机号和批次号进行查询,所以我们在创建分区的时候,就选择以手机号和批次号进行分区,这样设置后,查询都会走索引,每次查询MySQL都会根据查询条件计算出来,数据会落在那个分区里面,直接到对应的分区表中检索即可,避免了全表扫描。

对于每天流水过亿的数据,当然是要做历史表进行数据迁移的工作了。客户要求流水数据需要保存半年的时间,有的关键流水需要保存一年。删数据是不可能的了,也跑不了路,虽然当时非常有想删数据跑路的冲动。其实即时是删数据也是不太可能的了,delete的拙劣表演先淘汰了,truncate也快不了多少,我们采用了一种比较巧妙方法,具体步骤如下:

  1. 创建一个原表一模一样的临时表1 create table test_a_serial_1 like test_a_serial;

  2. 将原表命名为临时表2 alter table test_a_serial rename test_a_serial_{date};

  3. 将临时表1改为原表 alter table able test_a_serial_1 rename able test_a_serial; 此时,当日流水表就是一张新的空表了,继续保存当日的流水,而临时表2则保存的是昨天的数据和部分今天的数据,临时表2到名字中的date时间是通过计算获得的昨日的日期;每天会产生一张带有昨日日期的临时表2,每个表内的数据大约是有1000万。

  4. 将当日表中的历史数据迁移到昨日流水表中去 这样的操作都是用的定时任务进行处理,定时任务触发一般会选择凌晨12点以后,这个操作即时是几秒内完成,也有可能会有几条数据落入到当日表中去。因此我们最后还需要将当日表内的历史流水数据插入到昨日表内;insert into test_a_serial_{date}(cloumn1,cloumn2….) select(cloumn1,cloumn2….) from test_a_serial where LEFT(create_time,8) > CONCAT(date); commit;

如此,便完成了流水数据的迁移;

根据业务需要,有些业务数据需要保存半年,超过半年的进行删除,在进行删除的时候,就可以根据表名中的_{date}筛选出大于半年的流水直接删表;

半年的时间,对于一个业务流水表大约就会有180多张表,每张表又有20个分区表,那么如何进行查询呢?由于我们的项目对于流水的查询实时性要求不是特别高,因此我们在做查询时,进行了根据查询时间区间段进行路由查询的做法。

大致做法时,根据客户选择的时间区间段,带上查询条件,分别去时间区间段内的每一张表内查询,将查询结果保存到一张临时表内,然后,再去查询临时表获得最终的查询结果。

以上便是我们面对大数据量的场景下,数据库层面做的相应的优化,一张每天一亿的表,经过拆分后,每个表分区内的数据在500万左右。这样设计之后,我们还面临了一些其他问题,例如流水的统计问题,这么大量的数据,项目中的统计维度达到100多种,哪怕是每天count100次,也是及其困难多,我们采用了实时计算统计的方式来解决了这个问题,相关的技术涉及到实时计算,消息队列,缓存中间件等内容,尽请期待吧!


最后注意:突破高薪Java架构项目经验永远是核心,如果你没有最新JAVA架构实战教程及大厂30k+面试宝典,可以去小编的Java架构学习.裙 :七吧伞吧零而衣零伞 (数字的谐音)转换下可以找到了,里面很多新JAVA架构项目教程,还可以跟老司机交流讨教! 

本文的文字及图片来源于网络加上自己的想法,仅供学习、交流使用,不具有任何商业用途,版权归原作者所有,如有问题请及时联系我们以作处理


原文链接:https://www.cnblogs.com/chengxuyuanaa/p/12841657.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Java Web基础

下一篇:java redis面试题附pdf答案(最全版本持续更新)