MySQL:合并几个大表并添加值，优化

Question

我有几个大表，其中的字段如下:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
| fid1  | varchar(10) | NO   | MUL | NULL    |       |
| fid2  | varchar(10) | NO   |     | NULL    |       |
| cnt   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

我在 {fid1,fid2,cnt} 上有索引，每个表都有超过 20000000 行。

我想将这些表合并为一个表，如果 fid1 和 fid2 匹配，那么新的 cnt 值将是这些表中 cnt 的总和。

我尝试了merge and add values from two tables中建议的方式, 得到类似的东西

SELECT COALESCE(A.fid1, B.fid1) AS fid1, COALESCE(A.fid2, B.fid2) AS fid2, (COALESCE(A.cnt,0)+COALESCE(B.cnt,0))
FROM test1 A LEFT JOIN test2 B ON a.fid1 = b.fid1 AND A.fid2 = B.fid2
UNION
SELECT COALESCE(A.fid1, B.fid1) AS fid1, COALESCE(A.fid2, B.fid2) AS fid2, (COALESCE(A.cnt,0)+COALESCE(B.cnt,0))
FROM test1 A RIGHT JOIN test2 B ON a.fid1 = b.fid1 AND A.fid2 = B.fid2

但是，由于我有几个表，而且它们都非常大，所以这种使用 UNION 的方法非常耗时。有没有其他方法可以有效地实现它或优化它？

谢谢!

Answer 1

它可以通过非常高效和简单的方式实现。

第一步:

Alter table test1 
  add constraint fid1_fid2_unique UNIQUE (fid1,fid2);

第 2 步:

insert into test1 
   select  test2.fid1,test2.fid2,test2.cnt  from test2
 on duplicate key update test1.cnt=test1.cnt+test2.cnt ;

同样可以处理多个表。

您可以通过以下链接验证结果: http://sqlfiddle.com/#!9/07c6b/1