Question

I have questions about reclaiming storage space after performing significant deletes. The deleted records are not freeing up the storage space. Do we have to perform optimize table tablename query everytime to free up the deleted storage space ? Is there any configuration which will help us to free up the storage space?

我们使用非行表格

Optimize query : https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

Answer 1

参看<><>> > > > > > > > > >。

PARTITION BY RANGE(TO_DAYS(...))
Use a partition key of the datetime of the data;
Adjust the indexes.
Set up a script to DROP PARTITION and REORGANIZE PARTITION every hour.

Note: I suggest hourly partitions so that you have at most 25 hours worth of data in the table at a time.

在我的博客中进行更多讨论:>Partm>Partition。

DELETE leaves free space in the table; the free space will be reused by subsequent INSERTs. If you stick with the non-partition approach, I suggest you have a continuously running task that repeatedly does

DELETE ... 
    WHERE .. < NOW() - 24 HOUR
    LIMIT 1000`

and 该关键因素指数。更多见我的博客:。这还表明,如果你需要扫描<代码>,则是一种技术。缩略语......

<><>>> OPTIMIZE TABLE可能是一种“坏账”。它阻碍表格的使用。重新思考的空间很快将显现出来。我猜测,你目前的手法在一至二天之间积累了数据价值。

确保<><<><>>条码>innodb_file_per_table 载于1>>> <<<<<<<>>>>>>>>>上任何重大活动。 (这是合理的缺省,可能已经如此确定。)

我的建议将使磁盘空间保持在1.0至1.04天之间(24-25小时)。。连续部分删除可能或不一定需要<>OPTIMIZE;这取决于表格结构的细节。你们是否愿意分享? 我可能还有其他问题——例如酌情使用较小的“ints”。

友情链接