mysql最快合并相同的表_千万级别mysql合并表快速去重

mysql> create index ind_temp_c123 on temp(c1,c2,c3);

Query OK, 40000004 rows affected (3 min 43.87 sec)

Records: 40000004  Duplicates: 0  Warnings: 0

查看执行计划

mysql> explain select c1,c2,c3,max(c4) from temp FORCE INDEX (ind_temp_c123) group by c1,c2,c3 ;

+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------+

| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows     | Extra |

+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------+

|  1 | SIMPLE      | temp  | index | NULL          | ind_temp_c123 | 71      | NULL | 40000004 |       |

+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------+

1 row in set (0.05 sec)

mysql> insert into c2kw select c1,c2,c3,max(c4) from temp FORCE INDEX (ind_temp_c123) group by c1,c2,c3 ;

Query OK, 20000004 rows affected (2 min 0.85 sec)

Records: 20000004  Duplicates: 0  Warnings: 0