思考MySQL的性能和亿级数据的处理方式，分库分表怎么做，在哪些场景下它更合适吗？比如银行交易记录的查询限制有点大，实际实验过程，下面是实验过程中做的一些操作，踩过的一些坑，我觉得坑对读者很有用。第一：创建现金流量表。交易历史是各个金融系统中使用率最高、历史数据量最大的数据类型。现金流量表的数据检索可以按时间范围、个体、金额进行检索。--创建现金流表DROPTABLEIFEXISTS`yun_cashflow`;CREATETABLE`yun_cashflow`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`userid`int(11)DEFAULTNULL,`type`int(11)DEFAULTNULLCOMMENT'1,entry,2Withdraw',`operatoruserid`int(11)DEFAULTNULLCOMMENT'operatorID',`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()begindeclarenumint;setnum=0;whilenum<10000doinsertintoyun_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)));setnumnum=num+1;endwhile;END;;调用test_insert();坑一：存储过程建立后，发现插入数据特别慢，一天一夜插入不到100万条数据，平均每秒40-60条数据。中间停了几次，本来以为是随机函数的问题，结果变成了常数，但是效果是一样的，还是很慢。当时我对这个MySQL数据库很悲观。毕竟oracle用惯了，插入速度确实很快，但是很吃力。不负众望，事实证明可以使用另一种写入数据的方式来创建数据，速度非常快，而且是编码的。插入示例（example_id、名称、值、other_value）VALUES（100、'Name1'、'Value1'、'Other1'）、（101、'Name2'、'Value2'、'Other2'）、（102、'Name3','Value3','Other3'),(103,'Name4','Value4','Other4');就是在循环中创建很多这种格式的数据，用VALUES隔开，然后写入数据，我用Excel创建了10000条数据，按照语句格式粘贴出来，就变成了10000条每个周期的数据，从而在短时间内创建了1亿条数据。selectcount(*)fromyun_cashflow还是很好奇，8个字段1亿条数据到底占用了多少空间，通过下面语句找到数据的路径。showglobalvariableslike"%datadir%";通过查看文件，它是7.78GB。好像字段不多，数据量大的话，问题不大。其实作为架构师，在估算机器配置中硬盘的冗余度时，这是最简单、最直接、最粗暴的转换思路。就这样，表建好了，开始各种实验。首先，让我们看看条件是什么。呵呵，Outofmemory，看来这个query真的在内存中，内存冒烟了。内存里好像放了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：在没有索引的情况下，不管是单查询还是联合查询，结果都是1分钟多，2分钟不到。好吧，那就试试加个索引，看看会发生什么样的奇迹。给用户加索引ALTERTABLEyun_cashflowADDINDEXindex_userid(userid)`给金额加索引ALTERTABLEyun_cashflowADDINDEXindex_money(money)给时间加索引ALTERTABLEyun_cashflowADDINDEXindex_createtime(createtime)总结2：平均创建一个索引的时间约为1400秒，约23分钟。索引都建好了，开始前条件查询看看效果。1、时间范围查询select*fromyun_cashflowwherecreatetimebetween'2018-10-2309:06:58'and'2018-10-2309:06:59'2。userquery和money联合查询3.userquery,money,time三者条件联合查询select*fromyun_cashflowwheremoney<62anduserid=32andcreatetimebetween'2018-10-2209:06:58'and'2018-10-2309:06:59'总结3：建立索引后，这个级联查询，速度基本上很快，数据量不大的时候，基本不会超过一秒。由于timerange返回的是56条数据，数据量比较小，所以速度快可能和这个有关，那么实验下数据多的条件下效果会怎样。首先尝试添加索引和金额条件的效果。2500万条数据，返回时间为11.460秒。添加一个条件，用户数比较多的userid=21返回1000万条以上的数据，找一个用户数比较少的userid=34返回4000多条记录用时6秒，耗时不到1第二。总结4：条件返回的数据统计越多，速度就会越慢。如果超过1000万就慢的离谱，1秒左右100万的量就可以了。那。...........我们程序员都知道，我们做数据的时候，都会用到分页。分页一般使用LIMIT，比如每页10行，第二页为LIMIT10,10。你要试试分页后在哪些页面会有什么样的效果？限速为1000限速为100万时，限速为1000万。速度很小。总结5：LIMIT参数1、参数2当参数1（启动索引）增大时，速度会越来越慢。如果要求1秒左右返回时的速度是100万条数据，太大会比较慢，即如果每页有10条，当你到第100000页时，就变成了越来越慢。如果达到30万页，可能达不到一般系统3秒的要求。数据库已经建好索引了，那会不会影响我插入数据的速度呢？试一下，插入100条数据，将近5秒，平均每秒插入20条数据。总结6：也就是说，如果按照这个速度插入，一旦并发量大了，操作会很慢。因此，在有索引的情况下插入数据时，要么索引失败，要么插入速度极慢。分库分表的思想，一个大表返回那么多数据很慢，那我把它变成几个表，然后每个表count(*)，我统计累加，合计就是查询所有数据的结果条目数，然后是页数。我会先计算这个页面在哪个库，哪个表，如果不能从那个表读取，就完了。按照前面的总结，返回100万条数据需要1秒，所以把100万条数据放在一张表里，1亿条数据放100张表。开始声明`@i`int(11);声明`@createSql`VARCHAR(2560);声明`@createIndexSql1`VARCHAR(2560);声明`@createIndexSql2`VARCHAR(2560);声明`@createIndexSql3`VARCHAR(2560);set`@i`=0;WHILE`@i`<100DOSET@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_TIMESTAMPONUPDIMATECURRENT_PRY(`))');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`);');preparemt2from@createIndexSql2;executestmt2;SET`@i`=`@i`+1;ENDWHILE;END表建好后，Curry的效果是一样的。是不是很酷，这表很好，绝对的，图书馆里全是表。然后你要给每个表添加100万条数据。这部分代码就不写了，大家可以参考之前的改动，相信能看懂文章的都是有知识的人，也是对这方面有很深追求的人。坑二：高估了自己电脑的并行计算能力。当我开启100个线程同时玩自己电脑的数据库连接时，后来反馈给我的结果是这样的。说白了，当连接满了超时了，数据库是不会给我返回值的，所以这种实验，不要找100台机器，也不要用一台机器来凑热闹，因为如果能快，亿大表，回本不会慢。这时候，一切都与计算能力有关。在一台机器上做实验会让你怀疑人生。那怎么办呢，这里我假设return是1000毫秒，也就是1秒，然后每个线程1秒返回一个值给我。我会把这个值写死。可以看看多线程分布式统计。计数的作用。***整体耗时是返回时间最长的线程的返回时间，所以理论上100个线程同时启动，应该1秒完成，但是线程有快有慢，所以需要超过1秒一点点也是可以接受的。如果机器性能好的时候所有的数据库返回都在1秒以内，那么就是1秒。对于这种多线程编程，可以尝试类似Java的countDownLatch/AKKA，将异步多线程结果同步返回。***是在数据库数据量比较大的情况下，通过MySQL及以上的特性来思考不同场景的应用。场景：查询银行交易记录根据第六小结的特点，操作表和历史查询表必须按时间分开。由于有索引的历史表，插入会很慢，所以插入操作表，操作表和历史表历史表的字段是一样的。根据小总结的两个特点，然后固定某个时间点，比如半夜12点，或者固定日期，或者选择非交易查询活跃的时间，插入操作表中的数据进入历史表，因为重建索引不会用的太久，像半个小时左右。让两个表共存。还有另一种策略。由于流主要是按时间排序的，所以可以按照时间的先后顺序，即ID自增的顺序，分为数据库和表。就像实验一样，一张表大概有100万条数据，另外我在创建一个时间范围的索引表，如下：CreateTimeIndexTableIDTableNameCreateTimeStartCreateTimeEnd1yun_cashflow_12018-10-2209:06:582018-10-2609:06:582yun_cashflow_22018-10-2609:06:582018-10-2909:13210909:06:583fash2yun:06:582018-11-2209:06:584yun_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-2709语句的查询中：06:58'和'2018-10-2809:06:59'像这样，分两遍可以查到结果。但是查询结果可能有多个，比如selectTableNamefromCreateTimeIndexTablewhereCreateTimeStart>'2018-10-2709:06:58'andCreateTimeEnd<'2018-11-1309:06:59'yun_cashflow_2,和yun_cashflow_3，这时候需要把两张表的结果全部查询并合并。相信对于程序员来说合并两张表的结果集并不难，这里就不多解释了。这样做的主要好处是每次重建索引时，不需要重建整个亿大表，而只重建最近被拆分的一百万表，速度会非常快。3、根据总结1和总结3的特点，对用户、时间等关键字段添加索引，保证查询速度。4、根据第四小结的特点，尽量限制查询结果的数量。例如，如果一个人检查自己的交易明细，则可以限制范围。例如，查询时间范围不能超过三个月、半年、一年。
