-- 建立一张 现金流量表

DROPTABLEIFEXISTS`yun_cashflow`;

CREATETABLE`yun_cashflow`(

`id`bigint(20)NOTNULLAUTO_INCREMENT,

`userid`int(11)DEFAULTNULL,

`type`int(11)DEFAULTNULLCOMMENT'1、入账，2提现',

`operatoruserid`int(11)DEFAULTNULLCOMMENT'操作员ID',

`withdrawdepositid`bigint(20)DEFAULTNULLCOMMENT'提现ID',

`money`doubleDEFAULTNULLCOMMENT'钱数',

`runid`bigint(20)DEFAULTNULLCOMMENT'工单ID',

`createtime`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

PRIMARYKEY(`id`)

)ENGINE=InnoDBAUTO_INCREMENT=63DEFAULTCHARSET=utf8;


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

-- 循环插入

dropPROCEDUREtest_insert;

DELIMITER;;
CREATEPROCEDUREtest_insert()
begin
declarenumint;
setnum=0;
whilenum<10000do
insertintoyun_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)));
setnum=num+1;
endwhile;
END;;

calltest_insert();


坑一：

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

INSERTINTOexample
(example_id,name,value,other_value)
VALUES
(100,'Name1','Value1','Other1'),
(101,'Name2','Value2','Other2'),
(102,'Name3','Value3','Other3'),
(103,'Name4','Value4','Other4');


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

selectcount(*)fromyun_cashflow


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

showglobalvariableslike"%datadir%";


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

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

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

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

select*fromyun_cashflowwherecreatetimebetween'2018-10-2309:06:58'and'2018-10-2309:06:59'


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

selectcount(*)fromyun_cashflowwhereuserid=21


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

selectcount(*)fromyun_cashflowwheremoney<62anduserid=32


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

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

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

ALTERTABLEyun_cashflowADDINDEXindex_userid(userid)


`
给金额加索引

ALTERTABLEyun_cashflowADDINDEXindex_money(money)


给时间加索引

ALTERTABLEyun_cashflowADDINDEXindex_createtime(createtime)


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

select*fromyun_cashflowwherecreatetimebetween'2018-10-2309:06:58'and'2018-10-2309:06:59'
2、用户查询与钱的联合查询3、用户查询与钱与时间三个条件的联合查询

select*fromyun_cashflowwheremoney<62anduserid=32andcreatetimebetween'2018-10-2209:06:58'and'2018-10-2309: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`<100DO
SET@createSql=CONCAT('CREATETABLEIFNOTEXISTSyun_cashflow_',`@i`,'(
`id`bigint(20)NOTNULLAUTO_INCREMENT,
`userid`int(11)DEFAULTNULL,
`type`int(11)DEFAULTNULL,
`operatoruserid`int(11)DEFAULTNULL,
`withdrawdepositid`bigint(20)DEFAULTNULL,
`money`doubleDEFAULTNULL,
`runid`bigint(20)DEFAULTNULL,
`createtime`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARYKEY(`id`)
)'
);
preparestmtfrom@createSql;
executestmt;


-- 创建索引

set@createIndexSql1=CONCAT('createindex`t_money`onyun_cashflow_',`@i`,'(`money`);');
preparestmt1from@createIndexSql1;
executestmt1;
set@createIndexSql2=CONCAT('createindex`t_userid`onyun_cashflow_',`@i`,'(`userid`);');
preparestmt2from@createIndexSql2;
executestmt2;
SET`@i`=`@i`+1;
ENDWHILE;
END


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

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

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

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

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

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

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

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

根据小总结六的特性，操作表和历史查询表一定要时间可以分开，由于带索引的历史表，插入会很慢，所以要插入到操作表内，操作表和历史表的字段是一样的。

根据小总结二特性，然后固定某个时间点，比如半夜12点，或者固定日期，或者选择非交易查询活跃的时间，把操作表里的数据往历史表里插一下，由于重建索引也用不了太久，一样半个小时左右。让两种表并存。还有另外一种策略，由于流水主要以时间做为排序对象，可以按照时间顺序，也就是ID自增长的顺序进行分库分表，就像试验的那样，100万左右条数据一张表，另外在做一张时间范围的索引表，如下：

CreateTimeIndexTable
IDTableNameCreateTimeStartCreateTimeEnd
1yun_cashflow_12018-10-2209:06:582018-10-2609:06:58
2yun_cashflow_22018-10-2609:06:582018-10-2909:06:58
3yun_cashflow_32018-11-1209:06:582018-11-2209:06:58
4yun_cashflow_42018-11-2209:06:582018-11-2609:06:58


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

select*fromyun_cashflowwheremoney<62anduserid=32andcreatetimebetween'2018-10-2709:06:58'and'2018-10-2809:06:59'


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

selectTableNamefromCreateTimeIndexTablewhereCreateTimeStart>'2018-10-2709:06:58'andCreateTimeEnd<'2018-10-2809:06:59'


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

select*fromyun_cashflow_2wheremoney<62anduserid=32andcreatetimebetween'2018-10-2709:06:58'and'2018-10-2809:06:59'


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

selectTableNamefromCreateTimeIndexTablewhereCreateTimeStart>'2018-10-2709:06:58'andCreateTimeEnd<'2018-11-1309:06:59'


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

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

根据小总结一和小总结三的特性，把关键的字段加上索引，用户，时间，这样保证查询的速度。

根据小总结四的特性，尽量限制查询结果的数量范围，比如，单个人查自己的交易明细，可以限制范围，比如查询时间范围不超过三个月，或半年，或一年。