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

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

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

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

1. — 建立一张 现金流量表
3. DROP TABLE IF EXISTS `yun_cashflow`;
4. CREATE TABLE `yun_cashflow` (
5. `id` bigint(20) NOT NULL AUTO_INCREMENT,
6. `userid` int(11) DEFAULT NULL,
7. `type` int(11) DEFAULT NULL COMMENT ‘1、入账，2提现’,
8. `operatoruserid` int(11) DEFAULT NULL COMMENT ‘操作员ID’,
9. `withdrawdepositid` bigint(20) DEFAULT NULL COMMENT ‘提现ID’,
10. `money` double DEFAULT NULL COMMENT ‘钱数’,
11. `runid` bigint(20) DEFAULT NULL COMMENT ‘工单ID’,
12. `createtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
13. PRIMARY KEY (`id`)
14. ) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

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

 

2. — 循环插入
4. drop PROCEDURE test_insert;
5. DELIMITER;;
7. CREATE PROCEDURE test_insert()
9. begin
11. declare num int;
13. set num=0;
15. while num < 10000 do
17. insert into yun_cashflow(userid,type,operatoruserid,withdrawdepositid,money) values(FLOOR(7 + (RAND() * 6))+FLOOR(22 + (RAND() * 9)),1,FLOOR(97 + (RAND()
19. * 6))+FLOOR(2 + (RAND() * 9)),FLOOR(17 + (RAND() * 6))+FLOOR(2 + (RAND() * 9)),FLOOR(5 + (RAND() * 6))+FLOOR(2 + (RAND() * 9)));
21. set num=num+1;
23. end while;
25. END;;
28. call test_insert();

坑一：

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

1. INSERT INTO example
2. (example_id, name, value, other_value)
3. VALUES
4. (100, ‘Name 1’, ‘Value 1’, ‘Other 1’),
5. (101, ‘Name 2’, ‘Value 2’, ‘Other 2’),
6. (102, ‘Name 3’, ‘Value 3’, ‘Other 3’),
7. (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张表。

3. BEGIN
4. DECLARE `@i` int(11);
5. DECLARE `@createSql` VARCHAR(2560);
6. DECLARE `@createIndexSql1` VARCHAR(2560);
7. DECLARE `@createIndexSql2` VARCHAR(2560);
8. DECLARE `@createIndexSql3` VARCHAR(2560);
11. set `@i`=0;
12. WHILE `@i`< 100 DO
13. SET @createSql = CONCAT(‘CREATE TABLE IF NOT EXISTS yun_cashflow_’,`@i`,‘(
14. `id` bigint(20) NOT NULL AUTO_INCREMENT,
15. `userid` int(11) DEFAULT NULL,
16. `type` int(11) DEFAULT NULL ,
17. `operatoruserid` int(11) DEFAULT NULL ,
18. `withdrawdepositid` bigint(20) DEFAULT NULL ,
19. `money` double DEFAULT NULL ,
20. `runid` bigint(20) DEFAULT NULL ,
21. `createtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
22. PRIMARY KEY (`id`)
23. )’
24. );
25. prepare stmt from @createSql;
26. execute stmt;
28. — 创建索引
29. set @createIndexSql1 = CONCAT(‘create index `t_money` on yun_cashflow_’,`@i`,‘(`money`);’);
30. prepare stmt1 from @createIndexSql1;
31. execute stmt1;
32. set @createIndexSql2 = CONCAT(‘create index `t_userid` on yun_cashflow_’,`@i`,‘(`userid`);’);
33. prepare stmt2 from @createIndexSql2;
34. execute stmt2;
35. SET `@i`= `@i`+1;
36. END WHILE;
37. END

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

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

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

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

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

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

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

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

场景：银行交易流水记录的查询
1、根据小总结六的特性，操作表和历史查询表一定要时间可以分开，由于带索引的历史表，插入会很慢，所以要插入到操作表内，操作表和历史表的字段是一样的。

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

CreateTimeIndexTable

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

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百万的那张分出来的表，速度会很快的。

 

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

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