MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询
时间:2019-07-20 18:09

  限盐少许,上实际实验过程,以下是在实验的过程中做一些操作,以及踩过的一些坑,我觉得坑对于读者来讲是非常有用的。

  首先:建立一个现金流量表,交易历史是各个金融体系下使用率最高,历史存留数据量最大的数据类型。现金流量表的数据搜索,◆◁•可以根据时间范围,和个人,以及金额进行搜索。

  这个存储过程建立好了之后,发现插入数据特别的慢,一天一晚上也插入不到100万条数据,平均每秒40~60条数据,中间我停过几次,以为是随机函数的问题,都变成常数,但效果一样,还是很慢,当时让我对这个MySQL数据库感觉到悲观,毕竟Oracle用惯了,那插速是真的很快,不过功夫不负有心人,原来可以用另外一种写法造数据,速度很快,上代码。★▽…◇◆●△▼●

  就是在循环里,用这种格式造很多数据,VALUES后面以,隔开,然后把数据写上去,我用Excel造了1万条数据,按照语句格式粘贴了出来,就变成每循环一次,就1万条数据,这样没多久1亿数据就造好了。

  通过查看文件,是7.78GB,看来如果字段不是很多,数据量大的话,其实不是什么问题,•☆■▲这其实作为架构师来讲,在估算机器配置硬盘冗余的时候,这是最简单直接粗暴的换算思路。

  呵呵了,▲★-●Out of memory,看来这个查询是真往内存里整,内存整冒烟了,看来7.8G的数据是往内存里放,我内存没那么大导致的。

  我去,脑补一下,当你拿这支付宝查历史资金明细的时候,○▲-•■□56条信息,103.489秒,也就是将近2分钟的查询速度,你会是怎样的体验。哦 哦,不对,这个还没加用条件,★◇▽▼•那下面单独试试某个用户不限时间范围的条件是怎样的。

  由于时间的范围返回是56条数据,数据量比较小,▲●…△所以速度快可能与这个有关,那实验下条件多的数据效果会是什么样。

  小总结四:条件返回的数据统计量越多,速度就越慢,◆■超过1000万就慢的离谱,1秒左右就是100万的量才行。

  那。。▼▼▽●▽●。。。。。△▪▲□△。。。▲=○▼。。咱们程序猿都知道,我们在做数据的时候,都要用到分页。分页一般会用到LIMIT,比如每页10行,第二页就是LIMIT 10,10,得试试在分页的时候,◇=△▲哪些页的情况下,会是什么样的效果呢?

  小总结五:LIMIT 参数1,参数2 在随着参数1(开始索引)增大时候,这个速度就会越来越慢,如果要求1秒左右返回时候的速度是100万数据,在多在大就慢了,也就是,如果10条一页,•□▼◁▼当你到第10万页之后,就会越来越慢。如果到30万页之后,可能就会到不到一般系统的3秒要求了。

  小总结六:也就是说,按照这样的速度插入,并发量一但大的情况下,操作起来会很慢。所以在有索引的条件下插入数据,△▪▲□△▷•●要么索引失效,要么插入会特别慢。

  分库分表的思维,一个大表返回那么多数据慢,▪▲□◁●那我把它变成若干张表,然后每张表count(*)后,我统计累加一下,一合计,▼▲就是所有数据的查询结果的条数,然后就是到第多少页,我先算一下这页在哪个库,哪张表,•●在从那张表读不就完了。通过之前 的总结,100万数据返回为1秒,所以就一张表里放100万个数据,1亿的数据就100张表。

  是不是很酷,▪•★这表分的,▪…□▷▷•□◁绝了,满库全是表。那还得往每张表里整100万的数据。◇…=▲这部分代码就不写了,可以参考前面的改,相信能把文章看到这的都是懂行的人,也是对这方面有一腚追求的人。

  坑二:我高估了我的计算机的并行计算能力,当我启用100个线程同时玩我自己电脑的数据库连接的时候,到后期给我反馈的结果是这样的。

  说白了,连接满了,超时,数据库都不给我返回值了,所以这种实验,不找100台机器,也别可一台机器去霍霍,因为如果能快,那个1个亿的大表,返回的也不会慢。这时候拼的就是计算能力了,都在一台机器上去做实验,会让你怀疑人生的。

  那咋办, 这地方我就假装返回都是1000毫秒,也就1秒,然后每个线秒的时候都给我返回值,这个值我写死,可以看看多线程分布式统计count的效果。◁☆●•○△

  最后总体耗时,就是最后那个返回时间最长的线程返回的时间,所以理论上100个线秒完成,但线程这玩意有快有慢,所以1秒多一点,也是可以接受的。如果碰上都是机器性能好的时候,所有数据库返回都在1秒以内,那么也就是1秒了。

  这个多线程编程可以试试类似Java的countDownLatch/AKKA 将异步多线程结果同步返回。

  yun_cashflow_2,和yun_cashflow_3,这个时候,就需要把两个表的结果都查询出来,进行merge。相信程序员们对两个表的结果集合并逻辑都不是什么难事,这地方不多解释。

  这样做的好处,主要是每次重建索引的时候,就不用整个1个亿的大表进行重建,而是只重建最近的1百万的那张分出来的表,速度会很快的。◇•■★▼返回搜狐,查看更多◆▼★-●=•▽☆△◆▲■