数据库挑战：如何有效地更新100万条记录？(Database challenge: How to Update 1 million records efficiently?)

 我正在努力更新我的海量数据库，但由于这个大的更新/比较，我的wamp / Heidisql一直在崩溃。 
 
 我有两个数据库表：主表“member_all”（包含300万条记录）和子表：“mobile_results”（包含9,000条记录）。 表的数据库结构如下所示： 
 
 主表（“member_all”） 
 
id int(11),
name varchar(255),
phoneWork varchar(255),
phoneMobile  varchar(255),
phoneMobileNetwork varchar(255)
 
 表中的数据如下所示： 
 
id name      phoneWork      phoneMobile   phoneMobileNetwork 
1  bill      061090999990   0789867676    Null
3  billsaasa 06109094399990 076689867676  Null
 
 子表:(“mobile_results”） 
 
id int(11) autoincrement,
phoneMobile varchar(255),
phoneMobileNetwork  varchar(255)
 
 mobile_results中的数据如下所示： 
 
id     phoneMobile  phoneMobileNetwork
8789   0789867676   Orange     
238789 076689867676 O2
 
 9,000手机号码的所有移动网络数据都存储在“mobile_results”中，但是当我尝试比较这两个表时，我卡住了，我的wamp / Heidi sql崩溃了吗？ 
 
 我的问题是： 
 
 如何有效地使用“mobile_results”中的“phoneMobileNetwork”值填充“member_all”？ 
 
 以下是我尝试的查询： 
 
 查询1 
 
 我使用限制来划分我的查询。这很慢，并且还需要1周的时间来比较来自mobile_results的9,000条记录。 
 
update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results limit 1,10);
 
 查询2 
 
update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results where id <10);
 
 同样不适合大量记录。 
 
 请帮助我如何有效地一次更新记录我的“member_all”表。 
 
 在这方面，我将不胜感激。 

I am struggling to update my massive database but my wamp/Heidisql keeps crashing due to this large update/comparisons.
 
I have two database tables: main table "member_all" (contains 3 million records) and child table:"mobile_results" (contains 9,000 records). The database structure of tables look like this:
 
Main Table ("member_all")
 
id int(11),
name varchar(255),
phoneWork varchar(255),
phoneMobile  varchar(255),
phoneMobileNetwork varchar(255)
 
Data in table looks like this:
 
id name      phoneWork      phoneMobile   phoneMobileNetwork 
1  bill      061090999990   0789867676    Null
3  billsaasa 06109094399990 076689867676  Null
 
Child Table : ("mobile_results")
 
id int(11) autoincrement,
phoneMobile varchar(255),
phoneMobileNetwork  varchar(255)
 
Data in mobile_results looks like this:
 
id     phoneMobile  phoneMobileNetwork
8789   0789867676   Orange     
238789 076689867676 O2
 
All my mobile network data for 9,000 mobile number is stored in "mobile_results" but when i try to compare both these table ,i get stuck and my wamp/Heidi sql crashes?
 
My question is :
 
How can i populate "member_all" with "phoneMobileNetwork" values from "mobile_results" efficiently?
 
Here are the queries i have tried:
 
Query 1
 
i divided my query using limit .This is slow and would also take 1 week to compare 9,000 records from mobile_results.
 
update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results limit 1,10);
 
Query 2
 
update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results where id <10);
 
Same not good for large number of records.
 
PLEASE help me how can i update records my "member_all" table efficiently in one go.
 
I would appreciate you help in this regard.

原文：