關于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