批量删除数据----limit
程序员文章站
2022-06-03 07:57:20
...
执行计划:
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)