网站项目如何做需求分析报告,外贸网站,wordpress mssql,廊坊做网站1766534168对MySQL的性能和亿级数据的处理方法思考#xff0c;以及分库分表到底该如何做#xff0c;在什么场景比较合适#xff1f;
比如银行交易流水记录的查询
限盐少许#xff0c;上实际实验过程#xff0c;以下是在实验的过程中做一些操作#xff0c;以及踩过的一些坑#…对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)
) ENGINEInnoDB AUTO_INCREMENT63 DEFAULT CHARSETutf8;
然后开始造1个亿的数据进去。 -- 循环插入drop PROCEDURE test_insert;
DELIMITER;;CREATE PROCEDURE test_insert()begin declare num int; set num0;while num 10000 doinsert 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 numnum1;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 userid21 也是将近1分半的速度那在试试金额的条件。
select count(*) from yun_cashflow where money62 and userid32 同样都是将近一分半的时间。
那把两个条件做下级联看看效果会是怎样。 一样也是将近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 money62 and userid32 and createtime between 2018-10-22 09:06:58 and 2018-10-23 09:06:59 小总结三建立完索引后这种级联性质的查询速度基本都很快数据量不大的情况下基本不会超过一秒。
由于时间的范围返回是56条数据数据量比较小所以速度快可能与这个有关那实验下条件多的数据效果会是什么样。先试试加完索引, 金额条件的效果。 2千5百万的数据返回时间为11.460秒。
加一个用户数量比较多的条件 UserID21 返回1000多万的数据用了6秒
在找一个用户数量比较少的userid34 返回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 i0; 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 i1; 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万左右条数据一张表另外在做一张时间范围的索引表如下
CreateTimeIndexTable
IDTableNameCreateTimeStartCreateTimeEnd1yun_cashflow_12018-10-22 09:06:582018-10-26 09:06:582yun_cashflow_22018-10-26 09:06:582018-10-29 09:06:583yun_cashflow_32018-11-12 09:06:582018-11-22 09:06:584yun_cashflow_42018-11-22 09:06:582018-11-26 09:06:58
当遇见这样语句需求的时候
select * from yun_cashflow where money62 and userid32 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 money62 and userid32 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百万的那张分出来的表速度会很快的。 3、根据小总结一和小总结三的特性把关键的字段加上索引用户时间这样保证查询的速度。
4、根据小总结四的特性尽量限制查询结果的数量范围比如单个人查自己的交易明细可以限制范围比如查询时间范围不超过三个月或半年或一年。
相关文章：
