北京南站地铁几号线,购物网站的首页是静态,免费创意logo一键生成器,营销通移动互联网时代#xff0c;海量的用户数据每天都在产生#xff0c;基于用户使用数据的用户行为分析等这样的分析#xff0c;都需要依靠数据都统计和分析#xff0c;当数据量小时#xff0c;数据库方面的优化显得不太重要#xff0c;一旦数据量越来越大#xff0c;系统响…移动互联网时代海量的用户数据每天都在产生基于用户使用数据的用户行为分析等这样的分析都需要依靠数据都统计和分析当数据量小时数据库方面的优化显得不太重要一旦数据量越来越大系统响应会变慢TPS直线下降直至服务不可用。可能有人会问为何不用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万加项目背景要求保存客户的银行卡绑定关系客户的证件绑定关系以及客户绑定的业务信息。此业务背景下该如何设计数据库呢。项目一期的时候我们建立了一张客户业务绑定关系表里面冗余了每一位客户绑定的业务信息。基本结构大致如下查询时对银行卡做索引业务编号做索引证件号做索引。随着需求大增多这张表的索引会达到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个小文件解决了这两个查询的问题还剩下一个问题就是业务编号呢怎么办一个客户有多个签约业务如何进行保存这时候采用分区的方案就不太合适了它需要用到分表的方案。分库分表如何进行分库分表目前互联网上有许多的版本比较知名的一些方案阿里的TDDLDRDS和cobar京东金融的sharding-jdbc民间组织的MyCAT360的Atlas美团的zebra其他比如网易58京东等公司都有自研的中间件。但是这么多的分库分表中间件方案归总起来就两类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次也是及其困难多我们采用了实时计算统计的方式来解决了这个问题相关的技术涉及到实时计算消息队列缓存中间件等内容尽请期待吧推荐阅读Elasticsearch --让你的搜索引擎全面发展rdc.hundsun.com如何高效操作Redis数据库 - 数据库 - 恒生研究院rdc.hundsun.com
