不用Oracle基于MySQL数据库下亿级数据的分库分表

创业指导 阅读(781)
188金博网

作者:周

在移动互联网时代,每天都会产生大量的用户数据。在分析用户使用数据等的基础上,有必要依靠数据统计和分析。当数据量很小时,一旦数据量增加,数据库的优化就不是很重要。很大,系统响应将减慢,TPS将直接下降,直到服务不可用。

有人可能会问,为什么不使用Oracle?实际上,许多开发人员在编写代码时并不关心SQL。任何性能问题都留给DBA进行SQL优化。但是,并非每个项目都有DBA,所有项目也不会使用Oracle数据库和Oracle。数据库在大数据量的上下文中,解决性能问题,并不是一件很容易的事情。

那么,MySQL能支持数十亿的数据吗?我的回答是肯定的。大多数互联网公司都使用数据存储解其中大多数是基于MySQL的,不是坏钱。该银行以Oracle为基础,拥有全职DBA为您服务。

本文将以实际项目应用程序为例,分析如何优化数据库。该项目的背景是企业级统一消息处理平台。客户数据为5000万以上,消息流量为每分钟1000万。每日消息流量约为1亿。

虽然MySQL单表可以存储10亿级别的数据,但这次性能非常差。由于表无法解决,所以我必须找到一种方法将数据放在多个地方来解决问题。因此,生成数据库子数据库子表方案。目前,有三种常见的方案:分区和子库NoSQL/NewSQL。

在实际项目中,通常将这三种解决方案结合起来解决问题。目前,大多数系统的核心数据主要基于RDBMS存储,辅以NoSQL/NewSQL存储。

分区

分区方案

没有分片列,它工作正常(只是在这个时候性能)。但是,它的缺点是显而易见的:许多资源受到单个机器的限制,例如连接数,网络吞吐量。如何划分是实际应用中最重要的元素之一。在我们的项目中,以客户信息为例,客户数据量为5000万以上。项目背景需要保存客户的银行卡绑定关系,客户的文档绑定关系以及客户的绑定业务信息。在此业务环境中,如何设计数据库。在项目的第一阶段,我们建立了一个客户业务绑定关系表,它冗余地捆绑了每个客户绑定的业务信息。基本结构大致如下:

dd0aff5d864948f39e054278f21f75c6

数据,只是绑定的状态是不同的。假设我们拥有5000万客户,5种业务类型,平均每个客户2张卡。该表的数据量将达到惊人的5亿。事实上,我们系统中的用户数量并未超过一百万。它已不再可能。在数据库中,MySQL数据库中的数据存在于磁盘上。默认位于/mysql/data下(可以通过my.cnf中的datadir查看)。一个表主要对应三个文件,一个是frm。表结构,一个是myd来存储表数据,一个是myi表索引。这三个文件非常大,尤其是.myd文件,即5G。第一个分区优化在下面执行。 MySQL支持四种分区模式:

581ebab9274245889255b87a77fab0ff

在我们的项目中,范围分区和列表分区没有使用场景。如果范围或列表分区是基于绑定号完成的,则绑定号没有实际的业务含义,无法通过它查询。因此,我们有HASH分区和KEY分区,HASH分区只支持int类型列的分区,并且是其中一列。查看我们的库表结构,发现没有列的类型为int。怎么做分区?您可以添加列,绑定时间列,将此列设置为int类型,然后根据绑定时间进行分区,并将每天绑定的用户划分到同一区域。在这个优化之后,我们的插入速度要快得多,但查询仍然很慢,为什么呢,因为当我们进行查询时,我们只根据银行卡或ID号查询,而不是根据时间查询,这相当于每个查询。 MySQL将再次查询所有分区表。

然后是第二个解决方案优化,因为散列分区和密钥分区要求其中一个列必须是int类型,然后创建一个int类型来列出分区是否正常。分析发现,银行卡串有一个秘密。银行卡通常是数字字符串,范围从16到19位。将其中一个作为表分区是否可行?通过分析发现其中一个数字随机为0到9。生成的,不同的卡片串长度,这个位是不同的,不是最后一个,最后一个数字通常是一个校验位,而不是随机的。我们新设计的解决方案基于银行卡号+随机位进行KEY分区。每次进行查询时,通过计算拦截随机数,并将卡号与查询组合以实现分区查询的目的。是的,在分区之后,创建的索引也必须是分区列。否则,MySQL仍将查询所有分区表中的数据。然后解决了通过银行卡号查询绑定关系的问题,然后解决了文档号,如何通过文档号查询绑定关系。正如我之前所说,索引必须在分区上完成,否则会导致全表扫描。

我们创建了一个新表来保存客户的ID号绑定关系。每个客户的ID号都是唯一的。在新的ID号绑定关系表中,ID号用作主键。如何计算这个分区健康状况?但是,客户的证书信息相当复杂,有身份证号码,港澳台通行证,机动车驾驶证等。如何在无序的身份证号码中查找分区健康状况。

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

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

分库分表

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

阿里的TDDL,DRDS和科巴,京东金融的分片-JDBC;间组织的MyCAT; 360组的图谱;美团的斑马;其他比如网易,58,京东等公司都有自研的中间件

但是这么多的分库分表中间件方案,归总起来,就两类:客户端模式和代理模式

830492b7232c4db7a8df50f3ecf8f0cc

客户机模式

50ad6a20a64743df97269b9253d53296

代理模式

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

1b52b3a5bfb64f09a7c4bffdfc531de4

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

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

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

垂直分库

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

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

a3b5420c0f774c1786d7e15333b1eb1e

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

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

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

a94f4a712f6243e59e18cfa3b8947298

件计算出来,数据会落在那个分区里面,直接到对应的分区表中检索即可,避免了全表扫描。

XX当然,对于每天流量超过1亿元的数据来说,这是进行历史数据迁移的工作。客户要求流量数据需要保存半年,一些关键水需要存储一年。虽然删除数据有很多冲动,但是删除数据是不可能的,也无法运行。实际上,不可能实时删除数据。首先消除删除性能差,截断速度不快。我们采用了更聪明的方法,具体步骤如下:

Create a temporary table with the same original table create table test_a_serial_1 like test_a_serial; name the original table as temporary table 2 alter table test_a_serial rename test_a_serial_{date}; change the temporary table to the original table alter table able test_a_serial_1 rename able test_a_serial; On the same day, the flow meter is a new empty table, and the current flow is saved. The temporary table 2 saves the data of yesterday and some of today's data. The date of the temporary table 2 to the name is obtained by calculation. Yesterday's date; a temporary table with a date of yesterday is generated every day, and the data in each table is about 10 million. The historical data in the current day table is migrated to the yesterday's flow table to go to the current day table. Therefore, we finally need to insert the historical flow data in the current day table into the yesterday table; insert into test_a_serial_{date}(cloumn1,cloumn2.) select(cloumn1,cloumn2.) from test_a_serial where LEFT(create_time,8) > ; CONCAT(date); commit;

In this way, the migration of the flow data is completed;

According to business needs, some business data needs to be stored for half a year, and deleted for more than half a year. When deleting, you can filter out the flow directly by _{date} in the table name for more than half a year;

xx在半年内,业务流量计将有180多个表,每个表有20个分区表。怎么查询?由于我们项目对流查询的实时要求不是特别高,我们在进行查询时根据查询时间间隔进行了路径查询。

对于每个表,在查询的时间间隔内转到每个表,将查询结果保存到临时表,然后查询临时表以获取最终查询结果。

以上是大数据量场景下数据库级别的相应优化。一张每天1亿元的表,拆分后,每个表分区的数据大约是500万。在这个设计之后,我们还遇到了一些其他的问题,比如自来水的统计问题,如此大量的数据,项目的统计维数达到100以上,即使每天计数100次,它也是更难,我们使用实时计算解决这个问题的统计方法,相关技术涉及实时计算,消息队列,缓存中间件等内容,敬请期待!