对于同一张表update带来的效率问题

需求：

存在一张表

 create table tmp_report 

 (

 ...

 tmp_domain varchar(20),

 report_status int

 ...

 );

 

 其中2列的值为：

 tmp_domain report_status

 a 3

 a 2

 a 1

 b 4

 希望update report_status 的值为3所对应 tmp_domain 值相同的 report_status 都改成3，即：

 tmp_domain report_status

 a 3

 a 3

 a 3

 b 4

 

 开始用以下sql报错：

 update tmp_report a set a.report_status = 3 where a.tmp_domain in 

 (select b.tmp_domain from tmp_report b where b.report_status =3);

-- ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

 

 

 更新：

 update 前先备份tmp_report表

 

 

方法一：

 root@localhost db1> update tmp_report a,tmp_report b set a.report_status = 3

    ->  where a.tmp_domain=b.tmp_domain and b.report_status=3;

Query OK, 2048 rows affected (11.32 sec)

Rows matched: 3072  Changed: 2048  Warnings: 0

-- 慢查询中的记录，扫描的行数大概是笛卡尔积

# Query_time: 11.318816  Lock_time: 0.000205 Rows_sent: 0  Rows_examined: 3146752

SET timestamp=1348214131;

update tmp_report a,tmp_report b set a.report_status = 3

 where a.tmp_domain=b.tmp_domain and b.report_status=3;

恢复备份表：

root@localhost db1>delete from tmp_report;

Query OK, 3072 rows affected (0.09 sec)

root@localhost db1>insert into tmp_report select * from tmp_reportbak;

Query OK, 3072 rows affected (0.12 sec)

Records: 3072  Duplicates: 0  Warnings: 0

方法二：

root@localhost db1> update tmp_report set report_status = 3 where tmp_domain in (select tmp_domain from (select tmp_domain from tmp_report where report_status =3) tmp);

Query OK, 2048 rows affected (0.15 sec)

Rows matched: 3072  Changed: 2048  Warnings: 0

对于大表的时候，这两种方法都很慢。分开语句会好很多~