新闻资讯

新闻资讯 媒体报道

delete后加 limit是个好习惯么?

编辑:005     时间:2020-11-11

在业务场景要求高的数据库中,对于单条删除和更新操作,在delete和update后面加limit 1绝对是个好习惯。

比如,在删除执行中,第一条就命中了删除行,如果SQL中有limit 1;这时就return了,否则还会执行完全表扫描才return。效率不言而喻。

那么,在日常执行delete时,我们是否需要养成加 limit 的习惯呢?是不是一个好习惯呢?

在日常的SQL编写中,你写delete语句时是否用到过以下SQL?

delete from t where sex = 1 limit 100

你或许没有用过,在一般场景下,我们对 delete 后是否需要加 limit 的问题很陌生,也不知有多大区别,今天带你来了解一下,记得mark!

写在前面,如果是清空表数据建议直接用truncate,效率上truncate远高于delete,应为truncate不走事务,不会锁表,也不会生产大量日志写入日志文件;

truncate table table_name 后立刻释放磁盘空间,并重置auto_increment的值。delete删除不释放磁盘空间,但后续insert会覆盖在之前删除的数据上。

下面只讨论delete场景,首先,delete后面是支持limit关键字的,但仅支持单个参数,也就是[limit row_count],用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。

delete limit语法如下:

(值得注意的是,当需要用到order by排序时,必须order by + limit联用,否则order by 就会被优化器优化掉,被认为无意义。)

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

加limit的的优点:

以下面的这条SQL为例:

delete from t where sex = 1
  • 1. 降低写错SQL的代价,就算删错了,比如limit 500,那也就丢了500条数据,并不致命,通过binlog也可以很快恢复数据。

  • 2. 避免了长事务,delete执行时MySQL会将所有涉及的行加写锁和Gap锁(间隙锁),所有DML语句执行相关行会被锁住,如果删除数量大,会直接影响相关业务无法使用。

  • 3. delete数据量大时,不加limit容易把cpu打满,导致越删越慢。

针对上述第二点,前提是sex上加了索引,大家都知道,加锁都是基于索引的,如果sex字段没索引,就会扫描到主键索引上,那么就算sex = 1 的只有一条记录,也会锁表。

怎么删除表的前 10000 行。比较多的朋友都选择了第二种方式,即:在一个连接中循环执行 20 次 delete from T limit 500。确实是这样的,第二种方式是相对较好的。

第一种方式(即:直接执行 delete from T limit 10000)里面,单个语句占用时间长,锁的时间也比较长;而且大事务还会导致主从延迟。

第三种方式(即:在 20 个连接中同时执行 delete from T limit 500),会人为造成锁冲突。

这个例子对我们实践的指导意义就是,在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数,让操作更安全,还可以减小加锁的范围。所以,在 delete 后加 limit 是个值得养成的好习惯。

本内容属于网络转载,文中涉及图片等内容如有侵权,请联系编辑删除

郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。

回复列表

相关推荐