mysql - 减少 MySQL 中表的数据大小

Question

在其中一个表中，大约有 90 万条记录。当我们检查数据大小时，它大约是 800 MB。我们能否在不删除数据的情况下减小数据大小。

提前致谢。

---

CREATE TABLE snmptraps(
 SiteID int(11) NOT NULL default 1,
 SystemID int(11) NOT NULL,
 EnterpriseID varchar(255) default NULL,
 IPAddress varchar(255) default NULL,
 GenericTrap varchar(255) default NULL,
 SpecificTrap varchar(255) default NULL,
 TimestampGen varchar(255) default NULL,
 TimestampRec datetime default NULL,
 OID varchar(1024) default NULL,
 OIDValue varchar(1024) default NULL,
 TimePeriod TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
 PDUType int(11),
 Description varchar(255) default NULL,
);

Answer 1

尝试

optimize table your_table;

After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html