场景:发现mysql数据库中的一个数据库的某张记录日志的表都2G多了,用mysql客户端打开时,都报out of memory.
而且版本更新迭代快,测试平台的日志保存一个月内的足够用了,没有必要保存太长时间。
鉴于此,用命令行的方式执行一条delete命令来删除该表中的数据,只保留1个月左右的日志。
DELETE FROM test_logdetail WHERE logtime BETWEEN '2018-01-01 00:00:00' AND '2019-04-30 00:00:00'--删除test_logdetail表中2018年1月1日到2019年4月30日的数据
本以为,执行该语句后,占用的空间会降下来,可是查了下,发现,占用的空间并没有减少。
原来,当DELETE后面跟条件的时候,则就会出现这个问题,delete from table_name where 条件删除数据后,数据表占用的空间大小不会变。 如果已经删除了表数据的很大一部分,或者有很多变化和变长表行(VARCHAR表,VARBINARY、BLOB或文本列)进行了更改,因为删除操作后在数据文件中留下碎片所致。DELETE只是将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间,可以使用OPTIMIZE TABLE来回收未使用的空间,并整理数据文件的碎片。
注意: OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
于是,继续执行:
然后再去查看数据库,发现该表所占用的空间变小了:
备注:
删除数据库表一般来说有三种方法:
1、当你不再需要该表时, 用 drop; drop 是直接将表格删除,无法找回;
2、当你仍要保留该表,但要删除所有记录时, 用 truncate; truncate删除后不记录mysql日志,因此不可以rollback,更不可以恢复数据;
3、当你要删除部分记录或者有可能会后悔的话, 用 delete。 可以与where连用,删除特定行;
鉴于我们需要保留差不多1个月的日志记录,drop和truncate都不合适,只能使用delete来删除数据。