数据库中利用limit批量删除数据的操作教程

执行计划：

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)