MySQL Massive data condition removal

1. Description of the problem: there are now two tables, the specific table structure and the number of records are as follows:

Mysql>desc user_mapping;+------------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+------------------+------+-----+---------+-------+| open_id | varchar -)      | NO | PRI |       NULL | || Platform | tinyint4)       | NO | PRI |       NULL | || ServerID |int(Ten) unsigned | NO | PRI |0| || UID |int(Ten) unsigned |     NO | |       NULL | || UpdateTime |int( One)          |     YES | |       NULL | || Lastlogin |int( One)          |     YES | |       NULL | || Via | varchar -)     |     YES | |       NULL | |+------------+------------------+------+-----+---------+-------+7RowsinchSet (0.00sec) MySQL>SelectCOUNT (*) from user_mapping;+----------+| COUNT (*) |+----------+|12579610|+----------+1RowinchSet (2.49sec) MySQL>desc uid1202;+-----------------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+----------------------+------+-----+---------+-------+| UID |int(Ten) unsigned |     NO | |       NULL | || Last_login_time |int(Ten) unsigned |     YES | |       NULL | || Acccharge |int(Ten) unsigned |     YES | |0| || Level | smallint5) unsigned |     YES | |       NULL | |+-----------------+----------------------+------+-----+---------+-------+4RowsinchSet (0.00sec) MySQL>SelectCOUNT (*) from uid1202;+----------+| COUNT (*) |+----------+|9887299|+----------+1RowinchSet (3.25Sec

The purpose is to remove the UID from the user_mapping table in the uid1202 table, and when MySQL handles big data, the multi-table connection will simply kill the server single core, and it is not known that the SE years can be processed.

Solution:

Step1. Dump the user_mapping and uid1202 tables into the intranet, and build a new library to import

Step2. Remove the primary key from the Intranet user_mapping table

Alter Table Drop Primary key;

Step3. Delete the value of the UID duplicate in the Intranet user_mapping table Delete Keep one

Mysql-uusername-ppassword-e"select Platform,uid from user_mapping GROUP by UID have count (*) > 1; " > uid_double.txtawk'{print" delete from user_mapping where platform= "$ 1 "and uid=" "$"; "} ' uid_double.txt  >-uusername-ppassword user_del < Del_double.sql

Step4. Modify the User_mapping table to re-establish the UID as the primary key

Alter Table Add Primary Key (UID);

Step4. Constructs a statement that queries the UID in the uid1202 table in the User_mapping table

Mysql-uusername-ppassword user_del-e"select uid from uid1202" > uid.txt  awk'{print ' Select Open_id,platform,serverid from user_mapping where uid= "$"; "}' uid.txt > Del_uid.sql

Step5. Construct DELETE statement with Open_id,platform,serverid condition in user_mapping table

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=\ "" "\" and platform= "$" and uid= "$"  ; "} '

Step6. Decomposition of query statements to multiple SQL files, running at the same time in the external network

#!/bin/Bash forIinch$(seq 1 8) Do    CatDel_usermapping.sql |Head-N1300000>Del_usermapping_$i.sqlsed-I.'1,1300000d'Del_usermapping.sqlCatDel_usermapping_$i.sql |WC-L Done forIinch$(seq 1 8) DoMySQL-uroot-p1234 User_del < Del_usermapping_$i.sql & Done

MySQL Massive data condition removal