基于Mysql数据库亿级数据下的分库分表方案
移动互联网时代,海量的用户数据每天都在产生,基于用户使用数据的用户行为分析等这样的分析,都需要依靠数据都统计和分析,当数据量小时,问题没有暴露出来,数据库方面的优化显得不太重要,一旦数据量越来越大时,系统响应会变慢,TPS直线下降,直至服务不可用,可能有人会提出来,为何不用Oracle呢,确实,很多开发者写代码时并不会关心SQL的问题,凡是性能问题都交给DBA负责SQL优化,可是,不是每一个项目都会有DBA,也不是所有的项目都会采用Oracle数据库,而且,Oracle数据库在大数据量的背景下,解决性能问题,也不见的是一个非常轻松的事情。那么,Mysql能不能支撑亿级的数据量呢,我的答案是肯定的,绝大部分的互联网公司,它们采用的数据存储方案,绝大部分都是以Mysql为主,不差钱的国企和银行,以Oracle为主,而且有专职的DBA为你服务。
本文会以一个实际的项目应用为例,层层向大家剖析如何进行数据库的优化。项目背景是企业级的统一消息处理平台,客户数据在5千万加,每分钟处理消息流水1千万,每天消息流水1亿左右。虽说Mysql单表可以存储10亿级的数据,但这个时候性能非常差,项目中大量的实验证明,Mysql单表容量在500万左右,性能处于最佳状态,此时,Mysql的BTREE索引树高在3~5之间。既然一张表无法搞定,那么就想办法将数据放到多个地方来解决问题吧,于是,数据库分库分表的方案便产生了,目前比较普遍的方案有三个:分区,分库分表,NoSql/NewSql。
在实际的项目中,往往是这三种方案的结合来解决问题,目前绝大部分系统的核心数据都是以RDBMS存储为主,NoSql/NewSql存储为辅。
分区
首先来了解一下分区方案。
分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引,从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。这个方案也不错,它对用户屏蔽了sharding的细节,即使查询条件没有sharding column,它也能正常工作(只是这时候性能一般)。不过它的缺点很明显:很多的资源都受到单机的限制,例如连接数,网络吞吐等。如何进行分区,在实际应用中是一个非常关键的要素之一。在我们的项目中,以客户信息为例,客户数据量5000万加,项目背景要求保存客户的银行卡绑定关系,客户的证件绑定关系,以及客户绑定的业务信息。此业务背景下,该如何设计数据库呢。项目一期的时候,我们建立了一张客户业务绑定关系表,里面冗余了每一位客户绑定的业务信息。基本结构大致如下:
查询时,对银行卡做索引,业务编号做索引,证件号做索引。随着需求大增多,这张表的索引会达到10个以上。而且客户解约再签约,里面会保存两条数据,只是绑定的状态不同。假设我们有5千万的客户,5个业务类型,每位客户平均2张卡,那么这张表的数据量将会达到惊人的5亿,事实上我们系统用户量还没有过百万时就已经不行了。mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看), 一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。这三个文件都非常的庞大,尤其是.myd文件,快5个G了。下面进行第一次分区优化,Mysql支持的分区方式有四种:
在我们的项目中,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。其他比如网易、京东等公司都有自研的中间件。但是这么多的分库分表中间件方案,归总起来,就两类:client模式和proxy模式。
client模式
proxy模式
无论是client模式,还是proxy模式,几个核心的步骤是一样的:SQL解析,重写,路由,执行,结果归并。个人比较倾向于采用client模式,它架构简单,性能损耗也比较小,运维成本低。如果在项目中引入mycat或者cobar,他们的单机模式无法保证可靠性,一旦宕机则服务就变得不可用,你又不得不引入HAProxy来实现它的高可用集群部署方案, 为了解决HAProxy的高可用问题,又需要采用Keepalived来实现。
我们在项目中放弃了这个方案,采用了shardingjdbc的方式。回到刚才的业务问题,如何对业务类型进行分库分表。分库分表第一步也是最重要的一步,即sharding column的选取,sharding column选择的好坏将直接决定整个分库分表方案最终是否成功。而sharding column的选取跟业务强相关。在我们的项目场景中,sharding column无疑最好的选择是业务编号。通过业务编号,将客户不同的绑定签约业务保存到不同的表里面去,查询时,根据业务编号路由到相应的表中进行查询,达到进一步优化sql的目的。
前面我们讲到了基于客户签约绑定业务场景的数据库优化,下面我们再聊一聊,对于海量数据的保存方案。
垂直分库
对于每分钟要处理近1000万的流水,每天流水近1亿的量,如何高效的写入和查询,是一项比较大的挑战。还是老办法,分库分表分区,读写分离,只不过这一次,我们先分表,再分库,最后分区。我们将消息流水按照不同的业务类型进行分表,相同业务的消息流水进入同一张表,分表完成之后,再进行分库。我们将流水相关的数据单独保存到一个库里面去,这些数据,写入要求高,查询和更新到要求低,将它们和那些更新频繁的数据区分开。分库之后,再进行分区。
这是基于业务垂直度进行的分库操作,垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库,以达到系统资源的饱和利用率。这样的分库方案结合应用的微服务治理,每个微服务系统使用独立的一个数据库。将不同模块的数据分库存储,模块间不能进行相互关联查询,如果有,要么通过数据冗余解决,要么通过应用代码进行二次加工进行解决。若不能杜绝跨库关联查询,则将小表到数据冗余到大数据量大库里去。假如,流水大表中查询需要关联获得渠道信息,渠道信息在基础管理库里面,那么,要么在查询时,代码里二次查询基础管理库中的渠道信息表,要么将渠道信息表冗余到流水大表中。
将每天过亿的流水数据分离出去之后,流水库中单表的数据量还是太庞大,我们将单张流水表继续分区,按照一定的业务规则,(一般是查询索引列)将单表进行分区,一个表编程N个表,当然这些变化对应用层是无法感知的。
分区表的设置,一般是以查询索引列进行分区,例如,对于流水表A,查询需要根据手机号和批次号进行查询,所以我们在创建分区的时候,就选择以手机号和批次号进行分区,这样设置后,查询都会走索引,每次查询Mysql都会根据查询条件计算出来,数据会落在那个分区里面,直接到对应的分区表中检索即可,避免了全表扫描。
对于每天流水过亿的数据,当然是要做历史表进行数据迁移的工作了。客户要求流水数据需要保存半年的时间,有的关键流水需要保存一年。删数据是不可能的了,也跑不了路,虽然当时非常有想删数据跑路的冲动。其实即时是删数据也是不太可能的了,delete的拙劣表演先淘汰了,truncate也快不了多少,我们采用了一种比较巧妙方法,具体步骤如下:
-
创建一个原表一模一样的临时表1 create table test_a_serial_1 like test_a_serial;
-
将原表命名为临时表2 alter table test_a_serial rename test_a_serial_{date};
-
将临时表1改为原表 alter table able test_a_serial_1 rename able test_a_serial; 此时,当日流水表就是一张新的空表了,继续保存当日的流水,而临时表2则保存的是昨天的数据和部分今天的数据,临时表2到名字中的date时间是通过计算获得的昨日的日期;每天会产生一张带有昨日日期的临时表2,每个表内的数据大约是有1000万。
-
将当日表中的历史数据迁移到昨日流水表中去 这样的操作都是用的定时任务进行处理,定时任务触发一般会选择凌晨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次,也是及其困难多,我们采用了实时计算统计的方式来解决了这个问题,相关的技术涉及到实时计算,消息队列,缓存中间件等内容,以后再和大家分享吧。
粉丝福利
福利一:
长按扫码关注下方二维码,回复「后端爱码士」四个字,即可领取后端技术资料包(由号主(阿里p7)和另外四位BAT等互联网大厂技术专家级朋友倾力总结,包括java并发,mysql,redis,kafka,zookeeper原理以及面试套路等)
福利二:
长按扫描下方二维码,加号主微信,然后将本文转发朋友圈,攒够30个赞,截图反馈给号主,就能获得如下福利:
-
获邀进入号主创建的互联网大厂面试讨论群。
-
以6折优惠价(原价1499元/个)获得笔者一对一收徒第三期的名额(前提是需要有一定的基础,需要通过考核),先到先得,每期5个名额,前两期10名学徒全部收获大厂offer,平均月薪28k以上。
-
阿里,腾讯,美团,滴滴,字节,百度等大厂内推机会。