mysql大量数据更新的优化方案

时间：2024-04-15 21:18:02

1、业务需求
由于业务不同步的原因，导致用户管理侧oracle和139邮箱侧mysql的用户状态不一致，现在需要以用户管理侧为准，除却添加同步的程序之外，需要进行139邮箱一侧的数据的更新。
包括的内容是在139的插入不存在的用户，更新和用管侧不一样的属性。
初期的步骤，以csv格式获取139全量的用户以及状态，提交给用管的dba同事，进行对比同样以csv格式获取需要更新以及需要插入的用户和带有的属性。


2、环境介绍
2.1、mysql邮箱一侧和oracle的用管一侧网络并不互通，需要通过ftp服务器来互传文件
2.2、用户量巨大，库表按照了相关路由的方法分成了8*128张表，用管反馈的数据只是一个csv的文件，操作时需要进行进行路由的动作
2.3、反馈的信息，需要更改的行有5100w，需要插入的行有600w


3、失败的方案
3.1、按照一般的思维，由于存在路由策略，加上用户信息存在于csv文件，可以考虑使用对文件采用for循环获取每行的数据，路由到相关的表，然后进行更新操作
3.2、为了加快处理速度，考虑增加进程的数量，将文件分片成多个小的文件，对每个小的文件进行单独的处理
3.3、现象一：主机cpu使用率比较高，但是io使用相当的低（可能原因由于20个进程并行导致的，没太注意）
现象二：数据更新缓慢，整整一天的时间更新的数据才200w，这算下来得花一个月的时间才能完成任务了【黑线】


4、构思新的方案
反思原因，主机性能现象已经表明布置的几个任务被归为了cpu密集的业务，而这个更新动作明显是一个io密集型的事务才对，怎么会本末倒置了，检查实现的脚本，
豁然开朗，脚本为了获取路由，需要使用awk正则获取用户id，而正则表达式是一个cpu密集的命令，所以导致了当前cpu负载高的现象，而进程大量时间花在了正则表达式上面，所以数据库更新相当缓慢
所以要怎样进行改进呢？联想到insert的优化方案
由于时间主要花在了路由上面，加之每行数据都需要进行一次数据库的连接和最终的提交，所以导致了数据库更新缓慢的现象。新的方案应该需要解决的问题要提前处理掉路由过程以及多次连接提交的瓶颈。
为了解决多次提交可以建立临时表使用连接查询，csv文件也可以使用load data的方式导入数据库，不过要怎样判断路由呢，两种方案：
每个表对应一个更新表，总量就要128*8张，很不现实。
另一种方案是在临时表中添加数据库和表的字段，赋值可以通过存储过程来实现。
尝试用临时表的方案（以下方案由我老大进行了支援）


5、实施新的方案
5.1、创建临时表

CREATE TABLE `insert_mobilenumber_6836653` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`mobileusernumber` varchar(20) NOT NULL,
`CardType` int(2) DEFAULT NULL,
`Provcode` varchar(20) DEFAULT NULL,
`AreaCode` varchar(20) DEFAULT NULL,
`LastAction` int(4) DEFAULT NULL,
`Status` int(2) DEFAULT NULL,
`ReasonID` int(4) DEFAULT NULL,
`RegisterType` int(4) DEFAULT NULL,
`CreateTime` datetime DEFAULT NULL,
`ModifyTime` datetime DEFAULT NULL,
`RegisterTime` datetime DEFAULT NULL,
`FreezeTime` datetime DEFAULT NULL,
`UnFreezeTime` datetime DEFAULT NULL,
`ExpireTime` datetime DEFAULT NULL,
`dbid` int(11) DEFAULT NULL,
`tbid` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE `update_mobilenumber_51069865` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`mobileusernumber` varchar(20) DEFAULT NULL,
`Status` int(2) DEFAULT NULL,
`RegisterTime` datetime DEFAULT NULL,
`FreezeTime` datetime DEFAULT NULL,
`UnFreezeTime` datetime DEFAULT NULL,
`ExpireTime` datetime DEFAULT NULL,
`dbid` int(11) DEFAULT NULL,
`tbid` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　

5.2、LOAD导入：

mysql> load data infile "/home/mysql/itkeeper/20170424/replace.txt" into table insert_mobilenumber_6836653 fields terminated by \',\' 
-> 
-> (mobileusernumber,CardType,Provcode,AreaCode,LastAction,Status,ReasonID,RegisterType,CreateTime,ModifyTime,RegisterTime,FreezeTime,UnFreezeTime,ExpireTime);
Query OK, 6836653 rows affected, 65535 warnings (6 min 35.89 sec)
Records: 6836653 Deleted: 0 Skipped: 0 Warnings: 24782499

ysql> load data infile "/home/mysql/itkeeper/20170424/zhao_comm.txt" into table update_mobilenumber_51069865 fields terminated by \',\' 
-> 
-> (mobileusernumber,Status,RegisterTime,FreezeTime,UnFreezeTime,ExpireTime)
-> ;
Query OK, 51069865 rows affected, 65535 warnings (14 min 31.30 sec)
Records: 51069865 Deleted: 0 Skipped: 0 Warnings: 99206015

mysql> 
mysql>

io果然也上来了

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdb 0.00 7654.00 6.00 1098.00 248.00 70072.00 63.70 2.46 2.23 0.54 59.50
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda 0.00 83.50 0.00 70.50 0.00 1232.00 17.48 0.03 0.40 0.03 0.20
sdd 0.00 8124.00 6.50 1096.00 272.00 73800.00 67.19 2.44 2.22 0.56 61.65
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

5.3、更新DBID，TBID;

--利用ID自增特性：

CREATE PROCEDURE p_update_insert_mobilenumber()
begin
declare
v int default 0;
while v < 7000000 do 
update insert_mobilenumber_6836653 set dbid = mod(mobileusernumber,8) + 1, tbid = mod(mobileusernumber,127) + 1 where id >= v and id < v + 10000;
set v = v + 10000;
end while;
end;

CREATE PROCEDURE p_update_update_mobilenumber()
begin
declare
v int default 0;
while v < 52000000 do 
update update_mobilenumber_51069865 set dbid = mod(mobileusernumber,8) + 1, tbid = mod(mobileusernumber,127) + 1 where id >= v and id < v + 10000;
set v = v + 10000;
end while;
end;

 

mysql> call p_update_insert_mobilenumber ;
Query OK, 0 rows affected (8 min 26.29 sec)

mysql> 
mysql> 
mysql> call p_update_update_mobilenumber ;


Query OK, 0 rows affected (47 min 9.58 sec)

mysql>

mysql> create index idx_dbid_tbid on update_mobilenumber_51069865(dbid,tbid);
Query OK, 0 rows affected (7 min 6.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

5.4、进行更新，4个进程，每个进程二个库，并发执行，5100万，大约的接近小时左右更新完成。
----此步，如有针对库同步的，比如要同步cnmn_db_001-8，则采用下面方法，会导致数据同步丢失，正常步骤，是先use cnmn_db_001，再进行进行更新。

#/bin/bash

for i in `seq 1 2` ; 
do
for x in `seq 1 128` 
do 
BEGIN_TIME=`date +%Y%m%d%H%M%S`

mysql -u -e "UPDATE cnmn_db_00$i.mail_usernumber_info_$x a ,myefz.update_mobilenumber_51069865 b set a.Status=b.Status,a.FreezeTime=b.FreezeTime,a.UnFreezeTime=b.UnFreezeTime,a.ExpireTime=b.ExpireTime , a.ModifyTime=now() where b.dbid =$i and b.tbid=$x and b.mobileusernumber=a.usernumber;" ; 

END_TIME=`date +%Y%m%d%H%M%S`
TOTAL_TIME=` expr $END_TIME - $BEGIN_TIME `
echo "db_"$i".table_"$x" is OK|"$BEGIN_TIME"|"$END_TIME"|"$TOTAL_TIME >> result_log.log
done;
done;

5.5、插入数据处理，4个进程，每个进程二个库，并发执行。

#/bin/bash

for i in `seq 1 2` ; 
do

for x in `seq 1 128`;
do 
mysql -u -e "use cnmn_db_00\'$i\';insert into mail_usernumber_info_\'$x\' (usernumber,CardType,Provcode,AreaCode,LastAction,Status,ReasonID,RegisterType,CreateTime,ModifyTime,RegisterTime,FreezeTime,UnFreezeTime,ExpireTime) select mobileusernumber,CardType,Provcode,AreaCode,LastAction,Status,ReasonID,RegisterType,CreateTime,ModifyTime,RegisterTime,FreezeTime,UnFreezeTime,ExpireTime from myefz.insert_mobilenumber_6836653 where dbid =\'$i\' and tbid=\'$x \' and mobileusernumber not in (select usernumber from mail_usernumber_info_\'$x\');" 
done;
done;

相关文章