mysql海量資料條件刪除

標籤：des   style   blog   ar   color   sp   for   on   檔案   

1. 問題描述：現在存在兩個表，具體表結構及記錄數如下所示：

mysql> desc user_mapping;+------------+------------------+------+-----+---------+-------+| Field      | Type             | Null | Key | Default | Extra |+------------+------------------+------+-----+---------+-------+| open_id    | varchar(64)      | NO   | PRI | NULL    |       || platform   | tinyint(4)       | NO   | PRI | NULL    |       || serverid   | int(10) unsigned | NO   | PRI | 0       |       || uid        | int(10) unsigned | NO   |     | NULL    |       || updatetime | int(11)          | YES  |     | NULL    |       || lastlogin  | int(11)          | YES  |     | NULL    |       || via        | varchar(128)     | YES  |     | NULL    |       |+------------+------------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> select count(*) from user_mapping;+----------+| count(*) |+----------+| 12579610 |+----------+1 row in set (2.49 sec)mysql> desc uid1202;+-----------------+----------------------+------+-----+---------+-------+| Field           | Type                 | Null | Key | Default | Extra |+-----------------+----------------------+------+-----+---------+-------+| uid             | int(10) unsigned     | NO   |     | NULL    |       || last_login_time | int(10) unsigned     | YES  |     | NULL    |       || accCharge       | int(10) unsigned     | YES  |     | 0       |       || level           | smallint(5) unsigned | YES  |     | NULL    |       |+-----------------+----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> select count(*) from uid1202;+----------+| count(*) |+----------+|  9887299 |+----------+1 row in set (3.25 sec)

 

目的是將user_mapping表中的uid在uid1202表中存在的記錄刪除，mysql處理大資料時，多表串連會直接將伺服器單核卡死，而且還不知道猴年馬月才能處理完。

解決方案：

step1. 將user_mapping表和uid1202表dump到內網，建新庫匯入

step2. 去掉內網user_mapping表中的主鍵

alter table user_mapping drop primary key;

 

step3. 刪除內網user_mapping表中uid重複的值刪除保留一條

mysql -uusername -ppassword -e"select platform,uid from user_mapping group by uid having count(*) > 1 ;" > uid_double.txtawk ‘{print "delete from user_mapping where platform="$1" and uid="$2";"}‘ uid_double.txt  > del_double.sqlmysql -uusername -ppassword user_del < del_double.sql

 

step4. 修改user_mapping表，重建立立以uid為主鍵

alter table user_mapping add primary key(uid);

 

step4. 構造查詢user_mapping表中uid在uid1202表中的語句

mysql -uusername -ppassword user_del -e"select uid from uid1202" > uid.txtawk ‘{print "select open_id,platform,serverid from user_mapping where uid="$1"; "}‘ uid.txt > del_uid.sql

 

step5. 構造刪除user_mapping表中以open_id,platform,serverid為條件的語句

mysql -uroot -p1234 user_del < del_uid.sql > del_usermapping.txt
sed -i  ‘/open_id/d‘ del_usermapping.txt #刪除奇數行table頭awk ‘{print "delete from user_mapping where open_id=\""$1"\" and platform="$2" and uid="$3"  ;"}‘ del_usermapping.txt > del_usermapping.txt 

 

step6. 分解查詢語句到多個sql檔案，在外網同時運行

#!/bin/bashfor i in $(seq 1 8)do    cat del_usermapping.sql | head -n 1300000 > del_usermapping_$i.sql    sed -i ‘1,1300000d‘ del_usermapping.sql    cat del_usermapping_$i.sql | wc -l donefor i in $(seq 1 8)do    mysql -uroot -p1234 user_del < del_usermapping_$i.sql &done

 

mysql海量資料條件刪除