使用相关子查询
truncate t_target;
insert into t_target
select distinct t1.* from t_source t1 where item_id in
(select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);
这个语句很长时间都出不来结果,只看一下执行计划吧。
mysql> explain select distinct t1.* from t_source t1 where item_id in
-> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 1.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
2 rows in set, 3 warnings (0.00 sec)
主查询和相关子查询都是全表扫描,一共要扫描100万*100万数据行,难怪出不来结果。
---------------------