为什么在MySQL中不推荐使用DELETE命令删除数据

一、问题背景

运维团队接到来自腾讯赤兔管理平台的警报，发现TDSQL分布式数据库集群的某个存储节点磁盘使用率已达到96%，超出了最大磁盘利用率（max_df_usage）的告警阈值。DBA团队迅速介入进行问题分析，快速定位到几张与业务相关的日志表。之后，开发团队试图通过执行DELETE和DROP命令来清理这些日志数据，但却发现磁盘空间并没有如预期那样释放。

[TDSQL]TDSQL_ARM,目录:/dev/mapper/datavg1-datalv1;最大磁盘利用率大于85%产生告警，后续60分钟内屏蔽告警;异常策略:>阈值(85),当前值:96

二、处置过程

  1. 根据告警信息中的IP地址，找到相应的服务器并检查其磁盘占用情况。
[root@a-db06 ~]# df -h
Filesystem                   Size  Used Avail Use% Mounted on
/dev/mapper/datavg1-datalv1   32T   31T   10T  97% /data1
  1. 使用du命令进一步确认哪些目录占用的空间最大。最终确认是4012实例组占用的磁盘空间最多。
du -lh --max-depth=1 /data1

图片

  1. 登录赤兔管理平台，利用“表空间分布”功能，查看哪些表占用了最多的空间。由于当前磁盘使用率已达97%，且无法进行扩容，DBA迅速通知开发团队确认是否可以删除部分数据，以确保业务的稳定性。

图片

  1. 开发团队使用DELETE命令清理部分表数据，并通过DROP命令删除一些无用的日志表后，发现服务器的磁盘空间并未得到释放。

  2. 实际上，在执行DELETE命令后，没有立即通过OPTIMIZE TABLE table_name释放空间。

  3. 那么，在DROP表的情况下，空间为何也未释放呢？在表的存储目录中，发现表数据文件自动增加了“.delayed_drop”后缀。

图片

  1. 厂家反馈TDSQL使用file_slow_delete_rate参数来控制删除速度。TDSQL默认删除速度为每秒5MB。为了更快速释放存储，将删除速度调整至每秒50MB。可以通过以下命令查看当前的每秒删除速度设置：

图片

  1. 通过以下方式，修改每秒最大删除数据量的file_slow_delete_rate参数。
[root@a-db07~]# cd /data/tdsql_run/4012/percona-5.7.17/install/
[root@a-db07install]# ./jmysql.sh 4012/data1/tdengine/data/4012/prod/mysql.sock/data/tdsql_run/4012/percona-5.7.17
mysql> set global file_slow_delete_rate=50;

三、数据删除方式

常用的三种删除方式：通过DELETE、TRUNCATE、DROP关键字进行数据删除。这三种方式各有不同的适用场景。

  • 执行速度: DROP > TRUNCATE >> DELETE

1. DELETE命令

DELETE属于数据库的DML（数据操作语言）。在InnoDB存储引擎中，DELETE并不会真正删除数据，而是将被删除的数据标记为已删除。执行DELETE后，表文件在磁盘上所占用的空间不会减少，存储空间不会被释放，但可以在下一次插入时重用这部分空间。为了释放磁盘空间，必须在执行DELETE操作后，使用OPTIMIZE TABLE table_name。

2. DROP命令

DROP是数据库的DDL（数据定义语言），与TRUNCATE类似，执行后立即生效并无法恢复。执行DROP TABLE table_name会立即释放磁盘空间，无论是InnoDB还是MyISAM。DROP语句会删除表的结构、约束、触发器和索引，但依赖于该表的存储过程和函数将保留，但状态会变为无效。

3. TRUNCATE命令

TRUNCATE也是DDL操作，不会走事务，原数据不会放到回滚段中，操作不触发触发器。执行后立即生效且无法恢复。执行TRUNCATE TABLE table_name会释放磁盘空间，不管是InnoDB还是MyISAM。TRUNCATE可以视为先DROP表后CREATE表的过程，且此过程经过优化，因此速度几乎接近DROP TABLE的速度。TRUNCATE能快速清空表，并重置自增计数器。