MySQL Delete 后,如何快速释放磁盘空间

  一、起因:收到运维需求需要清理两张监控告警的日志表,数据删除之后,发现磁盘空间并未释放。

  二、分析:InnoDB 数据库在使用 delete 进行删除操作的时候,只会将已经删除的数据标记为删除,并没有把数据文件删除,因此并不会彻底的释放空间。这些被删除的数据会被保存在一个链接清单中,当有新数据写入的时候,MySQL 会重新利用这些已删除的空间进行再写入。

  三、解决:官方推荐可以使用 OPTIMIZE TABLE 命令来优化表,该命令会重新利用未使用的空间,并整理数据文件的碎片。

  语法如下:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

  注释:OPTIMIZE TABLE 将重新组织表数据和相关索引数据的物理存储空间,减少存储空间并提高I/O访问效率。对每个表所做的影响取决于该表所使用的存储引擎。该命令对视图无效。

  四、举例说明:

  1.查看优化前表占用空间大小:

root@dbs00 13:58:46:monitor$ ls -alth
total 7.1G
-rw-r-----  1 mysql mysql 5.1G Nov 21 13:59 job_status_trace_log.ibd
-rw-r-----  1 mysql mysql 1.2G Nov 21 13:58 job_execution_log.ibd

  2.使用OPTIMIZE 命令:

system@localhost 14:22:  [monitor]> optimize table job_execution_log;
+---------------------------+----------+----------+-------------------------------------------------------------------+
| Table                     | Op       | Msg_type | Msg_text                                                          |
+---------------------------+----------+----------+-------------------------------------------------------------------+
| monitor.job_execution_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| monitor.job_execution_log | optimize | status   | OK                                                                |
+---------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1.09 sec)

system@localhost 14:23:  [monitor]> optimize table job_status_trace_log;
+------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                        | Op       | Msg_type | Msg_text                                                          |
+------------------------------+----------+----------+-------------------------------------------------------------------+
| monitor.job_status_trace_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| monitor.job_status_trace_log | optimize | status   | OK                                                                |
+------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1.13 sec)

  3.查看优化之后的磁盘空间占用大小:

root@dpsvstadbs00 14:25:10:monitor$ ls -alth
total 868M
-rw-r-----  1 mysql mysql 368K Nov 21 14:26 job_exect_log.ibd
-rw-r-----  1 mysql mysql  17M Nov 21 14:26 job_trace_log.ibd

   4.可以使用SQL 语句查看表占用空间的大小(默认M为单位)

system@localhost 17:52:  [monitor]> select table_name,(data_length+index_length)/1048576,table_rows from information_schema.tables where t
able_schema='monitor' and table_name='job_status_trace_log';

+----------------------+------------------------------------+------------+ | table_name | (data_length+index_length)/1048576 | table_rows | +----------------------+------------------------------------+------------+ | job_trace_log | 9.0625 | 10401 | +----------------------+------------------------------------+------------+ 1 row in set (0.00 sec)

  补充:

  1、对于 InnoDB 存储引擎 MySQL,OPTIMIZE 命令,将会被映射为 ALTER TABLE  ... FORCE,并将重建表,更新索引统计信息,释放未使用的索引空间,这就意味着在一定程度上 OPTIMIZE  操作会造成一定的表阻塞(具体可以参加官网)。

  2、OPTIMIZE 操作会锁表,所以最好不要在高峰期使用。

    3、OPTIMIZE 操作相当于物理删除,一旦删除,恢复就很麻烦,所以最好使用逻辑删除,也不要经常使用,每月一次就够了

官网地址:https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

转载于:https://www.cnblogs.com/Camiluo/p/9996650.html

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值