为什么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

2、通过执行du命令，进一步确认哪些目录占用空间最大。最后确认是4012实例组占用的磁盘空间最大。

du -lh --max-dept=1 /data1

3、登录赤兔管理平台，在“表空间分布”功能页面，找到哪些表占用的空间最大。由于磁盘使用量已经达97%且无法进行磁盘扩容，DBA及时通知开发单位对业务表进行核实并确认是否可以清理部分数据来确保业务稳定运行。

4、开发单位采用delete命令清理几张表数据，以及采用drop命令删除几张无用的日志表后，服务器磁盘空间没有得到释放。

5、实际上执行delete命令后，没有执行optimize table table_name立即释放空间。

6、为何drop 表也释放不出来空间，这个到底是什么情况呢？在表存储目录下，看到表数据文件自动增加了“.delayed_drop”后缀。

7、厂家反馈TDSQL使用file_slow_delete_rate参数来控制删除速度。TDSQL默认情况下的删除速度是每秒5M。这里为了尽快释放存储所以将每秒删除速度调50M。通过show variables where variable_name in("tdsql_delayed_drop","file_slow_delete_rate");

查看当前设置的每秒删除速度。

8、通过以下方式来修改每秒最大删除数据量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其实并不会真的把数据删除，mysql实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间(重用 → 覆盖)。

delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

2、drop命令

DROP 属于数据库 DDL定义语言 ，同Truncate一样，执行后立即生效，无法找回。drop table table_name 立刻释放磁盘空间，不管是 InnoDB 和 MyISAM; drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

3、truncate命令

TRUNCATE属于数据库 DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger。执行后立即生效，无法找回!

truncate table table_name 立刻释放磁盘空间，不管是 InnoDB和MYISAM。truncate table其实有点类似于drop table 然后create，只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度:

truncate 能够快速清空一个表，并且重置auto increment的值。