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

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;

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>

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

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>

#/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;

#/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;