Mysql 大批量删除数据

参考微信公众号《Java自学之路》

在业务场景要求高的数据库中，对于单条删除或者更新的操作，在delete和update后面加上limit1是个好习惯。我在工作中看到有同事这样写

为什么这样做？

比如在执行删除中，如果第一条就命中了删除行，如果Sql中有limit1；这时候就直接return了，否则还会执行完全表扫描才return。如果表的数据量大，会做很多无用功。

那么我们讨论下如果删除大数据量的sql，能否用limit达到意向不到的效果。

delete from t where sex = 1 limit = 100;

或许你没有用过，在一般场景下，我们对delete后是否需要加limit的问题很陌生，也不知道有多大区别，今天一起学习下。

如果我们清空表数据，建议直接使用truncate，效率上truncate远高于delete，在另一篇文章讲mysql的，可以得知，我们truncate不走事务，不会锁表，也不会产生大量日志写入日志文件，我们访问log执行日志可以发现每次delete都有记录。truncate table table_name 会立刻释放磁盘空间，并重置auto_increment的值，delete 删除不释放磁盘空间，insert会覆盖之前的数据上，因为我们创建表的时候有一个创建版本号。

以下只讨论delete场景，首先我们看delete命令的参数信息

delete [low_priority] [quick] [ignore] from tbl_name
[where ...]
[order by ...]
[limit row_count]

delete后面是可以跟limit关键词的，但仅支持单个参数，用于告诉服务器在控制命令被返回到客户端前被删除的行的最大值。

如果要用order by 必须要和 limit 联用，否则被优化掉。

讨论这条sql

delete from t where sex = 1;

如果加上limit ，会有哪些改变？

  • 降低写错sql的代价，即使我们删除错误，只会删除我们limit后面那个数字，limit 10 ，最多丢失10条数据，当然可以通过binlog日志恢复数据，这里面设计到版本号，有兴趣的可以自己了解。
  • 避免了长事务，delete执行时会加锁，无论你有没有加锁，默认涉及到的行加写锁和Gap锁，所有相关的行都会被锁定，如果数据量大直接导致其他连接无法访问该表数据，导致业务无法使用。
  • delete数据量大的时候，如果不加limilt，容易cpu打满，导致越删越慢。

以上三点的背景是，sex是有索引的，如果sex字段没索引，就会扫描主键索引，即使只有一条数据，也会锁表。

对于delete limit 的使用，MySQL大佬丁奇有一道题：

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

肉山：

第一个方案，一次占用锁的时间比较长，可能导致其他客户端一致等待资源。
第二个方案，分多次占用锁，串行化执行，不占有锁的间隙，其他客户端可以工作，每次执行不同片段的数据，我理解为分段锁concurrentHashmap
第三个方案。自己制造锁竞争，加剧并发。可能锁住同一记录导致死锁的可能性增大。

总结：
我们从这个讨论或者这个博文可知，在删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，可以缩小加锁范围，值得我们去研究