批量删除数据----limit

最新推荐文章于 2024-12-03 14:00:00 发布

huangliang0703

最新推荐文章于 2024-12-03 14:00:00 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/huangliang0703/article/details/81180008

mysql 专栏收录该内容

56 篇文章

订阅专栏

本文探讨了MySQL中批量删除数据的不同方法及其性能对比。通过使用LIMIT关键字分批删除和直接删除大量数据的方式，展示了如何减少数据库操作时间。实验结果显示，分批删除能够显著提高效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

执行计划：

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)