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. 根据小总结四的特性,尽量限制查询结果的数量范围,比如,单个人查自己的交易明细,可以限制范围,比如查询时间范围不超过三个月,或半年,或一年。
Image placeholder
Charles2018
未设置
  51人点赞

没有讨论,发表一下自己的看法吧

推荐文章
MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适

Elasticsearch 亿级数据检索性能优化案例实战!

一、前言数据平台已迭代三个版本,从头开始遇到很多常见的难题,终于有片段时间整理一些已完善的文档,在此分享以供所需朋友实现参考,少走些弯路,在此篇幅中偏重于ES的优化,关于HBase,Hadoop的设计

干货 | 每天十亿级数据更新,秒出查询结果,ClickHouse在携程酒店的应用

本文转自 |携程技术中心 作者 |蔡岳毅作者简介蔡岳毅,携程酒店大数据高级研发经理,负责酒店数据智能平台研发,大数据技术创新工作。喜欢探索研究大数据的开源技术框架。一、背景1)携程酒店每天有上千表,累

百亿级数据分表后怎么分页查询?

推荐课程:PHP开发工程师--学习猿地精品课程 当业务规模达到一定规模之后,像淘宝日订单量在5000万单以上,美团3000万单以上。数据库面对海量的数据压力,分库分表就是必须进行的操作了。而分库分表之

【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

工商银行MySQL数据库架构解密

本文根据DTCC数据库大会分享内容整理而成,将介绍工行IT架构转型中传统OLTP数据库架构面临的挑战和诉求,构建基于MySQL分布式企业级解决方案实践历程,包括技术选择、高可用设计、两地三中心容灾、运

ElasticSearch 亿级数据检索案例实战

一、前言数据平台已迭代三个版本,刚开始遇到很多常见的难题,终于有时间整理一些已完善的文档了,在此分享一下。希望能帮助大家少走些弯路,在此篇幅中偏重于ES的优化。关于HBase,Hadoop的设计优化估

搞个大事情,阿里如何实现上亿级数据的精准计数?

背景关系型数据库在执行计数任务时,其执行效率会随着数据量级的增长而降低;当数据量达到亿级别时,计数任务的执行效率已经低到令人不忍直视。在闲鱼团队的关系系统中,我们采用了这样一种方式来实现亿级别数据的毫

中信银行信用卡业务数据库实现国产替换,湖北银行新核心系统项目正式验收,阿里云与MongoDB达成战略合作

中信银行信用卡业务数据库实现国产替换10月31日,由IT168旗下ChinaUnix社区主办的第十一届中国系统架构师大会(SACC2019)在北京召开。会上,中信银行软件开发中心/技术平台开发处副处长

使用Jupyter NoteBook进行IB查询和交易,以及使用算法交易示例

在搞好IB盈透接口后,试了下客户端交易,但是最终目的还是使用程序化交易。发现vnpy已经提供的Script_engine来支持JupyterNoteBook交易的,而且非常方便调用。 这里就用写了基于

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

现阶段数据库优“O”和去“O”矛盾吗?

国内去“IOE”已经进行了十多年,近两年被提起的频率又多了起来,几年前有人提出这样一个观点:要激进地去“I”(IBM小型机),淡定地去“E”(EMC存储),但是要谨慎地去“O”(Oracle数据库)。

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

杭州银行批量交易平台(HZBAT)技术内幕

1 概述杭州银行批量交易平台(HZBAT)是我基于DC4C自研的面向批量交易的技术平台。DC4C是我在2015年完全独立自研的分布式批量计算框架。目前HZBAT已用于综合积分系统(2015年投产)、E

一文带你掌握常见的Pandas性能优化方法,让你的pandas飞起来!

微信公众号:「Python读财」如有问题或建议,请公众号留言Pandas是Python中用于数据处理与分析的屠龙刀,想必大家也都不陌生,但Pandas在使用上有一些技巧和需要注意的地方,尤其是对于较大

Vue转React两个月来总结的性能优化方法

课程推荐:前端开发工程师--学习猿地精品课程 换了新公司,工作中使用的技术栈也从Vue换到了React,作为一个React新人,经常的总结和思考才能更快更好的了解这个框架。这里分享一下我这两个月来使用

去哪儿网React Native 优先的多端统一化方案

课程推荐:前端开发工程师--学习猿地精品课程 一、方案背景长期以来APP、H5、小程序等各个端的定位和发展历程都不一样,各端技术栈差异性也较大,基于成本和效率考虑并不追求各端一致性,结果就是各端真的就

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 数据库操作:删除数据库

使用MySQL的DROPDATABASE命令可以很容易的删除一个数据库。数据库删除的同时,所属的数据表将一起被删除。如果删除的数据库不存在,则会引发错误。为了避免错误的发生,可以在DROPDATABA

10分钟搞懂:亿级用户的分布式数据存储解决方案!

来源:IT进阶思维原创,转载请注明原出处内容提供:李智慧,前阿里巴巴技术专家,《大型网站技术架构》作者6月6日晚,林志玲与Akira公布婚讯、徐蔡坤祝福高考同学超常发挥,粉丝们百万的转发和点赞造成微博

Nebula 架构剖析系列(二)图数据库的查询引擎设计

摘要上文(存储篇)说到数据库重要的两部分为存储和计算,本篇内容为你解读图数据库Nebula在查询引擎QueryEngine方面的设计实践。在Nebula中,QueryEngine是用来处理Nebula

PB级数据实时查询,滴滴Elasticsearch多集群架构实践

Elasticsearch是基于Lucene实现的分布式搜索引擎,提供了海量数据实时检索和分析能力。Elastic 公司开源的一系列产品组成的ElasticStack,可以为日志服务、搜索引擎、系统监

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL优化之覆盖索引的使用

查看测试表结构:mysql>showcreatetableim_message\G ***************************1.row**************************