MySQL亿级数据数据库优化方案测试-银行交易流水记录的查询

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

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;           

（编辑：济源站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!