共计 6945 个字符，预计需要花费 18 分钟才能阅读完成。
本篇内容主要讲解“MySQL 亿级数据数据库优化的方法是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 亿级数据数据库优化的方法是什么”吧!
对 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`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;
然后开始造 1 个亿的数据进去。
— 循环插入
drop PROCEDURE test_insert;
DELIMITER;;
CREATE PROCEDURE test_insert()
begin
declare num int;
set num=0;
while num 10000 do
insert 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 num=num+1;
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 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;
— 创建索引
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`= `@i`+1;
END WHILE;
END
表建完了，库里的效果是酱样的。
是不是很酷，这表分的，绝了，满库全是表。那还得往每张表里整 100 万的数据。这部分代码就不写了，可以参考前面的改，相信能把文章看到这的都是懂行的人，也是对这方面有一腚追求的人。
坑二：我高估了我的计算机的并行计算能力，当我启用 100 个线程同时玩我自己电脑的数据库连接的时候，到后期给我反馈的结果是这样的。
说白了，连接满了，超时，数据库都不给我返回值了，所以这种实验，不找 100 台机器，也别可一台机器去霍霍，因为如果能快，那个 1 个亿的大表，返回的也不会慢。这时候拼的就是计算能力了, 都在一台机器上去做实验，会让你怀疑人生的。
那咋办，这地方我就假装返回都是 1000 毫秒，也就 1 秒，然后每个线程都在 1 秒的时候都给我返回值，这个值我写死，可以看看多线程分布式统计 count 的效果。
最后总体耗时，就是最后那个返回时间最长的线程返回的时间，所以理论上 100 个线程同时启动，应该在 1 秒完成，但线程这玩意有快有慢，所以 1 秒多一点，也是可以接受的。如果碰上都是机器性能好的时候，所有数据库返回都在 1 秒以内，那么也就是 1 秒了。
这个多线程编程可以试试类似 Java 的 countDownLatch/AKKA 将异步多线程结果同步返回。
最后是在数据库数据量比较大的时候，通过 MySQL 以上的特性，进行不同场景应用的思考。
场景：银行交易流水记录的查询
根据小总结六的特性，操作表和历史查询表一定要时间可以分开，由于带索引的历史表，插入会很慢，所以要插入到操作表内，操作表和历史表的字段是一样的。
根据小总结二特性，然后固定某个时间点，比如半夜 12 点，或者固定日期，或者选择非交易查询活跃的时间，把操作表里的数据往历史表里插一下，由于重建索引也用不了太久，一样半个小时左右。让两种表并存。还有另外一种策略，由于流水主要以时间做为排序对象，可以按照时间顺序，也就是 ID 自增长的顺序进行分库分表，就像试验的那样，100 万左右条数据一张表，另外在做一张时间范围的索引表，如下：
CreateTimeIndexTable
ID TableName CreateTimeStart CreateTimeEnd
1 yun_cashflow_1 2018-10-22 09:06:58 2018-10-26 09:06:58
2 yun_cashflow_2 2018-10-26 09:06:58 2018-10-29 09:06:58
3 yun_cashflow_3 2018-11-12 09:06:58 2018-11-22 09:06:58
4 yun_cashflow_4 2018-11-22 09:06:58 2018-11-26 09:06:58
当遇见这样语句需求的时候：
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 百万的那张分出来的表，速度会很快的。
根据小总结一和小总结三的特性，把关键的字段加上索引，用户，时间，这样保证查询的速度。
根据小总结四的特性，尽量限制查询结果的数量范围，比如，单个人查自己的交易明细，可以限制范围，比如查询时间范围不超过三个月，或半年，或一年。
到此，相信大家对“MySQL 亿级数据数据库优化的方法是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
