如何回收MySql磁盘空间

Question

我在MySql服务器中有一个表，该表包含大约100万行。只是因为一个列表每天占用更多的磁盘空间。该列的数据类型为Mediumblob。表的大小约为90 GB。

在每次插入行之后，我会做一些处理，然后在不需要这个列之后进行一些处理。

因此，对于该列，如果在处理该行后将值设置为NULL，那么MySql是否为下一行插入使用了这个空空间？

MySql服务器详细信息

服务器版本: 5.7

引擎: InnoDB

托管：

编辑1：我从表中删除了90%的行，然后运行了优化表table_name，但是它只减少了4GB的磁盘空间，并且没有回收空闲的磁盘空间。

编辑2我甚至删除了我的数据库并创建了新的DB和表，但是MySql服务器仍然显示了80 DB的磁盘空间。MySQL服务器的所有数据库的大小

SELECT table_schema "database name",
         sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
         sum( data_free )/ 1024 / 1024 "free space in MB"
     FROM information_schema.TABLES
     GROUP BY table_schema; 

+--------------------+---------------------+------------------+
| database name      | database size in MB | free space in MB |
+--------------------+---------------------+------------------+
| information_schema |          0.15625000 |      80.00000000 |
| app_service        |         15.54687500 |       4.00000000 |
| mysql              |          6.76713467 |       2.00000000 |
| performance_schema |          0.00000000 |       0.00000000 |
| sys                |          0.01562500 |       0.00000000 |
+--------------------+---------------------+------------------+

谢谢

Answer 1

编辑:从下面的评论中可以看出，用户的二进制日志是罪魁祸首。在大量删除并假设MySQL实例使用基于行的复制之后，二进制日志将是大的，这是合理的。

答案很复杂。

您可以使用NULL而不是实值来节省空间。InnoDB只使用每行1位的列来指示该值为NULL (请参阅我以前对https://stackoverflow.com/a/230923/20860的答复)以获得详细信息。

但这只会在存储该行的页面中腾出空间。每个页面必须只存储同一表中的行。因此，如果您设置了一组NULL，那么您可以在该页面中留出空间，这可以用于后续插入该表的(只用于)。它不会对属于其他表的行使用空白。

而且它仍可能不会被重用到中间表的任何行，因为InnoDB按主键顺序存储行。给定表的页不必是连续的，但我猜页中的行可能是连续的。换句话说，您可能无法在页面中按主键随机顺序插入行。

我不太清楚这个细节，你必须阅读Jeremey对InnoDB存储的研究才能知道答案。以下是一段节选：

用户记录的实际磁盘格式将在以后的文章中描述，因为它相当复杂，需要进行冗长的解释。 用户记录按插入的顺序添加到页面正文中(并可能从先前删除的记录中占用现有的空闲空间)，并通过使用每个记录头中的“下一个记录”指针按键按升序进行单键链接。

仍然不太清楚行是否可以按顺序插入，并在页面上重用空间。

因此，您可能只会很糟糕地完成对页面的碎片化，而具有高主键值的新行无论如何也会添加到其他页面中。

如果不时使用OPTIMIZE TABLE，您可以更好地回收空间，这将有效地将整个表重写为新页。如果您将值更改为NULL，这可能会重新打包行，将更多的行放入每个页面。

删除不需要的行，然后优化表会更有效。这将消除整页，而不是让它们支离破碎。