<address id="p95bn"><output id="p95bn"><strike id="p95bn"></strike></output></address>

<sub id="p95bn"></sub>
    <meter id="p95bn"><cite id="p95bn"></cite></meter>

      <sub id="p95bn"></sub>

        <sub id="p95bn"><ruby id="p95bn"><rp id="p95bn"></rp></ruby></sub>

        <sub id="p95bn"></sub>

          <cite id="p95bn"></cite>
          <sub id="p95bn"></sub>
            数据库

            MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询

            作者:逸宸a

            链接:https://www.jianshu.com/p/cbdef47fb837

            对MySQL的性能和亿级数据的处理方法思考,以及分库分表到底该如何做,在什么场景比较合适?

            比如银行交易流水记录的查询

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

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

            — 建立一张 现金流量表

            DROP?TABLE?IF?EXISTS?`yun_cashflow`;CREATE?TABLE?`yun_cashflow`?(??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,??`userid`?int(11)?DEFAULT?NULL,??`type`?int(11)?DEFAULT?NULL?COMMENT?'1、入账,2提现',??`operatoruserid`?int(11)?DEFAULT?NULL?COMMENT?'操作员ID',??`withdrawdepositid`?bigint(20)?DEFAULT?NULL?COMMENT?'提现ID',??`money`?double?DEFAULT?NULL?COMMENT?'钱数',??`runid`?bigint(20)?DEFAULT?NULL?COMMENT?'工单ID',??`createtime`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,??PRIMARY?KEY?(`id`))?ENGINE=InnoDB?AUTO_INCREMENT=63?DEFAULT?CHARSET=utf8;

            然后开始造1个亿的数据进去。

            — 循环插入

            drop?PROCEDURE?test_insert;DELIMITER;;CREATE?PROCEDURE?test_insert()begin?declare?num?int;?set?num=0;????????while?num?<?10000?do????????????insert?into?yun_cashflow(userid,type,operatoruserid,withdrawdepositid,money)?values(FLOOR(7?+?(RAND()?*?6))+FLOOR(22?+?(RAND()?*?9)),1,FLOOR(97?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)),FLOOR(17?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)),FLOOR(5?+?(RAND()?*?6))+FLOOR(2?+?(RAND()?*?9)));????????????set?num=num+1;????????end?while;??END;;call?test_insert();

            坑一:

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

            INSERT?INTO?example(example_id,?name,?value,?other_value)VALUES(100,?'Name?1',?'Value?1',?'Other?1'),(101,?'Name?2',?'Value?2',?'Other?2'),(102,?'Name?3',?'Value?3',?'Other?3'),(103,?'Name?4',?'Value?4',?'Other?4');

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

            select?count(*)?from?yun_cashflow

            我还比较好奇,8个字段1亿条数据,到底占了多大的地方,通过以下语句找到数据的路径。

            show?global?variables?like?"%datadir%";

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

            首先,啥条件不加看看咋样。

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

            资金流水一般会按照时间进行查询,看看这速度到底怎样。

            select?*?from?yun_cashflow??where?createtime?between?'2018-10-23?09:06:58'?and?'2018-10-23?09:06:59'

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

            select?count(*)?from?yun_cashflow?where?userid=21

            也是将近1分半的速度,那在试试金额的条件。

            select?count(*)?from?yun_cashflow?where?money<62?and?userid=32

            同样都是将近一分半的时间。
            那把两个条件做下级联,看看效果会是怎样。
            一样,也是将近1分半的时间。

            小总结一:在不加索引的情况下,无论单独,还是联合条件查询,结果都是1分多钟不到2分钟。

            好吧,那就加上索引试试,看看到底会有啥样奇迹发生。
            给用户加索引

            ALTER?TABLE?yun_cashflow?ADD?INDEX?index_userid?(userid)

            `
            给金额加索引

            ALTER?TABLE?yun_cashflow?ADD?INDEX?index_money?(money)

            给时间加索引

            ALTER?TABLE?yun_cashflow?ADD?INDEX?index_createtime?(createtime)

            小总结二: 建立索引的时间平均在1400秒左右,大概在23分钟左右。
            索引都建立完了,在开始以前的条件查询,看看效果。

            1、时间范围查询

            select?*?from?yun_cashflow??where?createtime?between?'2018-10-23?09:06:58'?and?'2018-10-23?09:06:59'

            2、用户查询与钱的联合查询

            3、用户查询与钱与时间三个条件的联合查询

            select?*?from?yun_cashflow?where?money<62?and?userid=32?and??createtime?between?'2018-10-22?09:06:58'?and?'2018-10-23?09:06:59'

            小总结三:建立完索引后,这种级联性质的查询,速度基本都很快,数据量不大的情况下,基本不会超过一秒。

            由于时间的范围返回是56条数据,数据量比较小,所以速度快可能与这个有关,那实验下条件多的数据效果会是什么样。
            先试试加完索引, 金额条件的效果。
            2千5百万的数据,返回时间为11.460秒。
            加一个用户数量比较多的条件 UserID=21
            返回1000多万的数据,用了6秒
            在找一个用户数量比较少的userid=34
            返回4000多条,用不到1秒。

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

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

            • limit在1千时候速度
            • limit在1百万时候速度
            • limit在1千万时候速度

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

            数据库都建上索引了,那我插数据速度有没有影响呢,那试试
            也就是说100条数据插了将近5秒,平均每秒插20条。

            小总结六:也就是说,按照这样的速度插入,并发量一但大的情况下,操作起来会很慢。所以在有索引的条件下插入数据,要么索引失效,要么插入会特别慢。
            分库分表的思维,一个大表返回那么多数据慢,那我把它变成若干张表,然后每张表count(*)后,我统计累加一下,一合计,就是所有数据的查询结果的条数,然后就是到第多少页,我先算一下这页在哪个库,哪张表,在从那张表读不就完了。通过之前 的总结,100万数据返回为1秒,所以就一张表里放100万个数据,1亿的数据就100张表。

            BEGIN?????????DECLARE?`@i`?int(11);????????????DECLARE?`@createSql`?VARCHAR(2560);?????????DECLARE?`@createIndexSql1`?VARCHAR(2560);????????????DECLARE?`@createIndexSql2`?VARCHAR(2560);????????DECLARE?`@createIndexSql3`?VARCHAR(2560);????????set?`@i`=0;?????????WHILE??`@i`<?100?DO????????????????????????????????????????????SET?@createSql?=?CONCAT('CREATE?TABLE?IF?NOT?EXISTS?yun_cashflow_',`@i`,'(`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,????????????????????????????????`userid`?int(11)?DEFAULT?NULL,????????????????????????????????`type`?int(11)?DEFAULT?NULL??,????????????????????????????????`operatoruserid`?int(11)?DEFAULT?NULL??,????????????????????????????????`withdrawdepositid`?bigint(20)?DEFAULT?NULL??,????????????????????????????????`money`?double?DEFAULT?NULL??,????????????????????????????????`runid`?bigint(20)?DEFAULT?NULL??,????????????????????????????????`createtime`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,????????????????????????????????PRIMARY?KEY?(`id`)????????????????????????????????)'????????????????????????????);?????????????????????????????prepare?stmt?from?@createSql;?????????????????????????????execute?stmt;??????????

            — 创建索引

            ??????set?@createIndexSql1??=?CONCAT('create?index?`t_money`?on?yun_cashflow_',`@i`,'(`money`);');????????????????????????????prepare?stmt1?from?@createIndexSql1;?????????????????????????????execute?stmt1;?????????????????????????????set?@createIndexSql2??=?CONCAT('create?index?`t_userid`?on?yun_cashflow_',`@i`,'(`userid`);');????????????????????????????prepare?stmt2?from?@createIndexSql2;?????????????????????????????execute?stmt2;?SET?`@i`=?`@i`+1;?????????????END?WHILE;END

            表建完了,库里的效果是酱样的。

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

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

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

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

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

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

            最后是在数据库数据量比较大的时候,通过MySQL以上的特性,进行不同场景应用的思考。

            场景:银行交易流水记录的查询

            1. 根据小总结六的特性,操作表和历史查询表一定要时间可以分开,由于带索引的历史表,插入会很慢,所以要插入到操作表内,操作表和历史表的字段是一样的。
            2. 根据小总结二特性,然后固定某个时间点,比如半夜12点,或者固定日期,或者选择非交易查询活跃的时间,把操作表里的数据往历史表里插一下,由于重建索引也用不了太久,一样半个小时左右。让两种表并存。还有另外一种策略,由于流水主要以时间做为排序对象,可以按照时间顺序,也就是ID自增长的顺序进行分库分表,就像试验的那样,100万左右条数据一张表,另外在做一张时间范围的索引表,如下:
            CreateTimeIndexTableID??TableName???CreateTimeStart?CreateTimeEnd1???yun_cashflow_1??2018-10-22?09:06:58?2018-10-26?09:06:582???yun_cashflow_2??2018-10-26?09:06:58?2018-10-29?09:06:583???yun_cashflow_3??2018-11-12?09:06:58?2018-11-22?09:06:584???yun_cashflow_4??2018-11-22?09:06:58?2018-11-26?09:06:58

            当遇见这样语句需求的时候:

            select?*?from?yun_cashflow?where?money<62?and?userid=32?and??createtime?between?'2018-10-27?09:06:58'?and?'2018-10-28?09:06:59'

            1)、就改写成这样的顺序

            select?TableName?from?CreateTimeIndexTable?where?CreateTimeStart>??'2018-10-27?09:06:58'?and?CreateTimeEnd?<?'2018-10-28?09:06:59'

            2)、当得到TableName的时候,结果是yun_cashflow_2,在进行语句的查询

            select?*?from?yun_cashflow_2?where?money<62?and?userid=32?and??createtime?between?'2018-10-27?09:06:58'?and?'2018-10-28?09:06:59'

            这样,两遍就可以查询到结果。
            不过也有可能查询的结果是多个,比如

            select?TableName?from?CreateTimeIndexTable?where?CreateTimeStart>??'2018-10-27?09:06:58'?and?CreateTimeEnd?<?'2018-11-13?09:06:59'

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

            这样做的好处,主要是每次重建索引的时候,就不用整个1个亿的大表进行重建,而是只重建最近的1百万的那张分出来的表,速度会很快的。

            1. 根据小总结一和小总结三的特性,把关键的字段加上索引,用户,时间,这样保证查询的速度。
            2. 根据小总结四的特性,尽量限制查询结果的数量范围,比如,单个人查自己的交易明细,可以限制范围,比如查询时间范围不超过三个月,或半年,或一年。
            我还没有学会写个人说明!

            深度复盘GitHub发展史:如何在短短10年内改变了人们的编程方式?

            上一篇

            老程序员都去哪了?

            下一篇

            你也可能喜欢

            MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询

            长按储存图像,分享给朋友

            ITPUB 每周精要将以邮件的形式发放至您的邮箱


            微信扫一扫

            微信扫一扫
            亚洲黄色片视频,光棍电影韩国伦理网,女神吧,伊人电影在线观看