mysql 2个表数据同步 优化

[最后111]

有2个表user 和user_temp 。字段一样(open_id ,name , sex)。open_id是主键，两个表的数据都比较大。 user表200w ，user_temp表100w左右。
假设user表中有如下数据:
open_id name sex
001 张一 1
002 李二 0
003 张三 1
004 张四 1

user_temp表中有如下数据:
open_id name sex
001 张修改 0
002 李修改 1
005 王五 1
006 王六 0

现在将这2张表的数据进行合并。 合并规则为：
1) 将user表中open_id 为001 ，002的数据，update为user_temp中的数据。
2) 将user_temp表中open_id 为005 ，006的数据，insert到user表。
3) 将user表中open_id 为003 ，004的数据，delete。

想要达到的效果为：
user表中数据改为如下:
open_id name sex
001 张修改 0
002 李修改 1
005 王五 1
006 王六 0

用了如下2个方法:
方法一：
1)
update user u inner join user_temp as tmp on tmp.open_id = u.open_id
set u.name = tmp.name , u.sex = tmp.sex,
2)
insert into user (open_id, name, sex)
select open_id, name, sex from user_temp tmp
WHERE not EXISTS (SELECT 1 FROM user u where u.open_id = tmp.open_id)
3)
delete from user u
where not EXISTS ( select 1 from user_temp tmp where tmp.open_id = u.open_id )

方法二：
写存储过程，先把需要update的数据全部查询出来，然后一条一条修改
CREATE PROCEDURE `update`()
BEGIN
declare NAME varchar(1000);
declare OPEN_ID varchar(255);
declare SEX varchar(1);
declare stop int default 0;

declare user_cur cursor for( SELECT ft.name, ft.open_id, ft.sex,
from user_temp ft where EXISTS ( select 1 from user f where f.open_id = ft.open_id )
);

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
OPEN user_cur ;

FETCH user_cur INTO NAME , OPEN_ID, SEX;
WHILE ( stop is not null) DO
update user f set f.name = NAME , f.sex = SEX where f.open_id = OPEN_ID;
FETCH user_cur INTO NAME , OPEN_ID, SEX;
END WHILE;

CLOSE user_cur ;
END

后面还有2个存储过程略。。。。

以上2个方法都能实现功能。但是都存在问题，
使用 【方法一】 会造成长时间的锁表 (200w + 100w 数据join) 。
使用 【方法二】 执行速度太慢了， 执行完成需要几个小时。

求一个好的解决方案， 即 不会长时间锁表， 执行速度又不会太慢。

[benluobo]

按道理这种需求应该是一次性的 如果是定期执行的工作，需要考虑修改应用程序

[LongRui888]

第一种方法稍微好点，建议想办法优化执行速度，或者分批处理，不要一次更新所有的数据。 优化速度，可以考虑创建创建索引，不知道你的服务器性能如何，200w的和100w的表关联，然后update，速度应该是非常快的，不说1s种就能完成，但肯定也是10s多就能搞定的。

[iawen]

INSERT INTO t_user  SELECT * FROM t_user_tmp
ON DUPLICATE KEY UPDATE name=VALUES(name), sex=VALUES(sex);

INSERT INTO t_user_tmp  SELECT * FROM t_user
ON DUPLICATE KEY UPDATE name=VALUES(name), sex=VALUES(sex);

[iawen]

后一条语句可以优化为:

INSERT IGNORE INTO t_user_tmp  SELECT * FROM t_user;

[hdcopy2001]

1) 将user表中open_id 为001 ，002的数据，update为user_temp中的数据。 2) 将user_temp表中open_id 为005 ，006的数据，insert到user表。 3) 将user表中open_id 为003 ，004的数据，delete。 看上去，user_temp好像更新增量数据，增（56）删（34）改（12）。 规则2，与user表现有数据无关，直接insert即可。应该蛮快的，可以分批插入。 规则3，与user_temp表无关，直接delete即可。只有200W数据应该非常快的。 严重怀疑规则3打错了，应该是将user_temp表中open_id 为003 ，004的数据，delete。 那样的话， 用EXISTS判断后delete也是非常快的。 不明白提问原文为什么是 not EXISTS，这个是比较慢的。 就剩规则1了，可以考虑先将user_temp中规则2，3的数据都删了，或者将规则1的数据 创建到另一个表中。当然，即使不做这些，操作应该也一样很快的 第一 提问原文的SQL是错误的！ 第二 别忘记在USER和 user_temp的 OPEN_ID加索引。 第三 对当前应用，楼上iawen的SQL很好。 INSERT INTO t_user SELECT * FROM t_user_tmp ON DUPLICATE KEY UPDATE name=VALUES(name), sex=VALUES(sex); 其实已经处理了规则1,2。 第四 对于用A表的数据更新B表相应记录的标准SQL，不是两表inner join，是用的子查询， 具体我记不清了，应该用到 exists 和 rownum=1（limit 1），请自行百度下。

[hdcopy2001]

刚百度了下，发现用A表的数据更新B表相应记录的标准SQL，查到的排在前面的居然全是错误的。 好像应该是这样的。 update table1 a set a.col1 = (select col1 from table2 b where b.id = a.id limit 1) where exists (select 1 from table2 where table1.id =table2.id)