执行计划:
mysql> explain delete from drp_pftzd where id in (select id from drp_pftzdbak) limit 1000\G *************************** 1. row *************************** id: 1 select_type: DELETE table: drp_pftzd partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 246100 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: drp_pftzdbak partitions: NULL type: unique_subquery possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: func rows: 1 filtered: 100.00 Extra: Using index 2 rows in set (0.00 sec) mysql> explain delete from drp_pftzd where id in (select id from drp_pftzdbak) and id>10000 and id<20000 limit 1000\G *************************** 1. row *************************** id: 1 select_type: DELETE table: drp_pftzd partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 24310 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: drp_pftzdbak partitions: NULL type: unique_subquery possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: func rows: 1 filtered: 100.00 Extra: Using index 2 rows in set (0.00 sec)
不分批和分批的时间对比:
mysql> delete from drp_pftzd where id in (select id from drp_pftzdbak) and id>10000 and id<20000 ; Query OK, 9940 rows affected (1.05 sec) mysql> rollback; Query OK, 0 rows affected (0.42 sec) mysql> delete from drp_pftzd where id in (select id from drp_pftzdbak) and id>10000 and id<20000 limit 5000; Query OK, 5000 rows affected (0.26 sec) mysql> delete from drp_pftzd where id in (select id from drp_pftzdbak) and id>10000 and id<20000 limit 5000; Query OK, 4940 rows affected (0.25 sec)