频道栏目
首页 > 资讯 > MySQL > 正文

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

18-07-25        来源:[db:作者]  
收藏   我要投稿

执行计划:

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)
相关TAG标签
上一篇:MySQL数据库中的concat,concat_ws以及group_concat功能语法介绍
下一篇:关于wireshark过滤规则及使用方法介绍
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站