带percona的Drop列不会缩小表的大小

Question

我在Aurora MySQL 5.7有一张桌子。表的分区很少，有800米行，重量为2tb。最近，我使用percona删除了几个专栏。令人惊讶的是，表的大小并没有改变(看看information_schema.tables )。

percona进行更改的方式是在原始表上使用带有触发器的新表__new。它使用相同的DDL创建一个空的新表，执行我们希望的更改，并将所有内容都复制到带有触发器的新表中，以保持其最新。一旦数据被同步- percona重命名表并删除旧表。因此，表是从头构建的(没有锁定)。

然而，在运行alter table optimize partition之后，我看到它的大小缩小到250 to。有人知道我做错了什么吗？

pt命令：

pt-online-schema-change --user $MYSQL_DBA_USER --password $MYSQL_DBA_PASS --host $MYSQL_WRITER D=db,t=table_data --alter "drop column a1, drop column a2"  --execute --max-load Threads_running=18446744073709551606 --critical-load Threads_running=18446744073709551606 --recursion-method=none

优化命令：

MySQL [(db)]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';
+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  610884663 |        1847.7273712158203 |         202.40484619140625 |            0.0322265625 |           3247 |
+------------+---------------------------+----------------------------+-------------------------+----------------+
1 row in set (0.00 sec)


MySQL [db]> ALTER TABLE table_data OPTIMIZE PARTITION p20210601;
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                                                    |
+---------------+----------+----------+---------------------------------------------------------------------------------------------+
| db.table_data | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| db.table_data | optimize | status   | OK                                                                                          |
+------------------------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (5 hours 39 min 40.95 sec)

MySQL [db]>
MySQL [db]> select table_rows,data_length/power(1024,3), index_length/power(1024,3),DATA_FREE/power(1024,3),AVG_ROW_LENGTH  from information_schema.tables where table_name='table_data';


+------------+---------------------------+----------------------------+-------------------------+----------------+
| table_rows | data_length/power(1024,3) | index_length/power(1024,3) | DATA_FREE/power(1024,3) | AVG_ROW_LENGTH |
+------------+---------------------------+----------------------------+-------------------------+----------------+
|  736965899 |        104.25639343261719 |         155.98052978515625 |            0.0244140625 |            151 |
+------------+---------------------------+----------------------------+-------------------------+----------------+

Answer 1

由于许多原因，MySQL在许多情况下不向操作系统释放空闲空间。(常见的线程: MySQL在空间上选择速度。)

在这种情况下，有两个选择(尽管您可能没有意识到这些细节)。

• 默认的ALTER ... DROP COLUMN ...运行速度很快，只需更改表定义，而不必担心列占用的空间。
• ALTER ... ALGORITHM=COPY ... DROP COLUMN ...将表复制到上面，并对索引进行更新。这是缓慢的，但确实收回了自由空间。但它要慢得多，尤其是对于一张大桌子来说。OPTIMIZE TABLE实际上是作为“副本”的一部分完成的。

你被经典的电脑选择所困扰--速度和空间。

分区很少有用；您确定它提供了什么好处吗？

OPTIMIZE PARTITION中有一个"bug“--它重新构建每个分区，而不仅仅是您指定的分区。(要重建单个分区，请使用REORGANIZE。)