mysql大量資料更新的最佳化方案

標籤：index   logs   不一致   3.2   相關   cte   into   done   原因   

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，而Regex是一個cpu密集的命令，所以導致了當前cpu負載高的現象，而進程大量時間花在了Regex上面，所以資料庫更新相當緩慢
所以要怎樣進行改進呢？聯想到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: 24782499ysql> 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: 99206015mysql> mysql>

io果然也上來了

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %utilsdb 0.00 7654.00 6.00 1098.00 248.00 70072.00 63.70 2.46 2.23 0.54 59.50sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda 0.00 83.50 0.00 70.50 0.00 1232.00 17.48 0.03 0.40 0.03 0.20sdd 0.00 8124.00 6.50 1096.00 272.00 73800.00 67.19 2.44 2.22 0.56 61.65sde 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()begindeclarev 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()begindeclarev 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: 0mysql>

5.4、進行更新，4個進程，每個進程二個庫，並發執行，5100萬，大約的接近小時左右更新完成。
----此步，如有針對庫同步的，比如要同步cnmn_db_001-8，則採用下面方法，會導致資料同步丟失，正常步驟，是先use cnmn_db_001，再進行進行更新。

#/bin/bashfor i in `seq 1 2` ; dofor 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.logdone;done;

5.5、插入資料處理，4個進程，每個進程二個庫，並發執行。

#/bin/bashfor i in `seq 1 2` ; dofor 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;

mysql大量資料更新的最佳化方案