mysql - 如何从已截断的 Mysql 5.6 分区中清除数据？

Question

我有一个表正在由不同的应用程序写入。为了检查该表中的行数，我实现了基于 DAY_OF_YEAR 的分区方案，产生 52 个分区，一年中的每周一个。截断旧分区的计划作业每周运行一次。

在过去的运行之后，p27、p28 和 p29 中的所有行都已被清除。但是，当我尝试使用以下查询查看与分区行数相对应的信息时:

select * from INFORMATION_SCHEMA.partitions where table_schema = 'myDB';

我得到了以下结果:

TABLE_NAME          PARTITION_NAME      PARTITION_ORDINAL_POSITION  PARTITION_METHOD    PARTITION_EXPRESSION    PARTITION_DESCRIPTION   TABLE_ROWS  AVG_ROW_LENGTH  DATA_LENGTH     INDEX_LENGTH    DATA_FREE   
outbound_messages   p27                 27                          RANGE               DAYOFYEAR(created_at)   190                     0           0               4574101504      2393751552      7194279936  
outbound_messages   p28                 28                          RANGE               DAYOFYEAR(created_at)   197                     0           0               3436199936      3680010240      14167310336 
outbound_messages   p29                 29                          RANGE               DAYOFYEAR(created_at)   204                     0           0               509624320       6084018176      45427458048 
outbound_messages   p30                 30                          RANGE               DAYOFYEAR(created_at)   211                     36995867    1713            63385387008     29497769984     7340032     

尽管 p27、p28 和 p29 中的行计数为零，但“data_length”参数不为零。为了查看这些无行分区对整个表中数据大小的贡献有多大，我运行了以下查询来获取大小贡献(以 MB 为单位):

select ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) from information_schema.partitions where table_schema = 'myDB' and (partition_name = 'p27' or partition_name = 'p28' or partition_name = 'p29')

结果为 19719.8 MB。

为什么会发生这种情况？如何清除分区中的所有数据？

Answer 1

一年大约有 365 天，而不是 52 天。所以我对你的划分方式感到困惑。最好显示使用SHOW CREATE TABLE，至少包括前几个和最后几个分区。

DELETEing 不会释放操作系统空间。

您需要innodb_file_per_table = ON和DROP PARTITION，而不是DELETE。

如果您使用过“范围”的天数，则通过 DAYOFYEAR() 进行分区的效果会很差——它将检查所有 52 个分区。

要高效处理时间序列，请参阅 http://mysql.rjweb.org/doc.php/partitionmaint