mysql关联删除记录问题

steel_lei

有俩个表table1 , table2

talbe1   有俩个字段id,pid     有2千万条记录         同一pid对应多个id
table2   有一个字段pid         有100万条记录        pid唯一

想要删除表table1中记录，条件是table1.pid=table2.pid

大家给推荐一个效率高一点的方法，用delete表关联删除效率不是很高，感觉有点慢

[ 本帖最后由 steel_lei 于 2009-5-14 00:23 编辑 ]

ws00377531

我想到的是select建新表，但是好像也快不了多少

CREATE TABLE tmp AS  SELECT  * FROM table1  WHERE pid NOT IN (SELECT pid FROM table2)

steel_lei

原帖由 ws00377531 于 2009-5-14 09:29 发表
我想到的是select建新表，但是好像也快不了多少

CREATE TABLE tmp AS  SELECT  * FROM table1  WHERE pid NOT IN (SELECT pid FROM table2)

我在测试创建表,看一下create的效率,但后面没用not in,用not in会很慢的，用表关联会好很多

枫影谁用了

not in 更慢的了，关连要快。看索引了。

voxxu

改用left join吧
SELECT  * FROM table1 a left join table2 b on (a.pid=b.pid) where b.pid is null

枫影谁用了

原帖由 voxxu 于 2009-5-14 09:55 发表
改用left join吧
SELECT  * FROM table1 a left join table2 b on (a.pid=b.pid) where b.pid is null

这个好。

Coolriver

对于一个2000W的大表，一次走一个全表扫描确是很困难的。不过对于100W的表走一个全描扫也困难。

如果做这件事情又不想影响太大，就要分步来完成。

我给你一个思路你来参考一下。
首先衡量删除操作有多大。
selet count(*) from table1 a ,table2 b where a.pid=b.pid;
如果都有索引的话，这个操作还是挺快的。
如果操作的操作大于原表的40%，那么很有必要重建这个表了。

重建表的方法：就是按条件提取数据到一个新表，最后改名完成。这是一种方案。

另一种 删除方向。

上面通过比较觉的需要删除的量不是太大时，把需要删除的PID生到到另一个临时表中。
mysql DBname -e "select a.pid from table1 a ,table2 b where a.pid=b.pid">del_pid.txt;

sed -i '1d' del_pid.txt
awk '{print "delete from table1 where pid=",$1,";"}' del_pid.txt >del_pid.sql
mysql DBname<del_pid.sql

这样把SQL拆成多个SQL执行速度应该不会太慢了。

如果还是感觉不行，那就只能分段操作了。

枫影谁用了

这个很好,我们经常也这样做，用perl..

不过一般我是数据库做的事情让数据库做。

Coolriver

原帖由 枫影谁用了 于 2009-5-14 10:30 发表
这个很好,我们经常也这样做，用perl..

不过一般我是数据库做的事情让数据库做。

我现在很多工作拿到数据外面做了。
数据量大了，全压到DB上，都出不了结果了。：）

cenalulu

原帖由 Coolriver 于 2009-5-14 10:16 发表
对于一个2000W的大表，一次走一个全表扫描确是很困难的。不过对于100W的表走一个全描扫也困难。

如果做这件事情又不想影响太大，就要分步来完成。

我给你一个思路你来参考一下。
首先衡量删除操作有多大 ...

很不错~收藏了~但是把id放到临时文件里会有效率问题吗？跟建个临时表存id，哪个好一点？