关于MySQL中删除操作的注意事项

关于MySQL中删除满足子查询结果数据的操作

关键问题:MySQL不允许在子查询中使用要删除的表

错误示范:

DELETE
FROM
post_activity_user
WHERE
id IN (
SELECT
	a.id
FROM
	post_activity_user a
LEFT JOIN post_activity_info b ON a.activity_id = b.id
WHERE
	b.enterprise_id = 10000000
AND b.activity_type = 3
)

正确处理:

DELETE
FROM
	post_activity_user
WHERE
	post_activity_user.id IN (
		SELECT
			*
		FROM
			(
				SELECT
					a.id
				FROM
					post_activity_user a
				LEFT JOIN post_activity_info b ON a.activity_id = b.id
				WHERE
					b.enterprise_id = 10000000
				AND b.activity_type = 3
			) ee
	)

DELETE tb_a from table_a as tb_a INNER JOIN (SELECT * from table_a where name like ‘123%’) as tb_b on tb_b.id = tb_a.id; 

delete后加 limit提高效率

写在前面，如果是清空表数据建议直接用truncate，效率上truncate远高于delete，应为truncate不走事务，不会锁表，也不会生产大量日志写入日志文件；truncate table table_name 后立刻释放磁盘空间，并重置auto_increment的值。delete删除不释放磁盘空间，但后续insert会覆盖在之前删除的数据上。详细了解请跳转另一篇博文《delete、truncate、drop的区别有哪些，该如何选择》

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

delete limit语法如下：

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

delete [low_priority] [quick] [ignore] from 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 的只有一条记录，也会锁表。

参考：

关于MySQL中删除满足子查询结果数据的操作：https://www.cnblogs.com/wing7319/p/10458765.html

delete后加 limit是个好习惯么：https://blog.csdn.net/qq_39390545/article/details/107519747