delete后空间无法释放
本帖最后由 fuhao009 于 2022-8-30 15:40 编辑Greatsql 8.0.25-16
# 删除数据后,文件大小没有缩减
(Tue Aug 30 15:25:32 2022)>delete from test01;
Query OK, 59251 rows affected (2.70 sec)
# 查看数据已经没有了
(Tue Aug 30 15:26:57 2022)>select count(1) from test01
-> ;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
# 查看表文件仍然是有数据的状态
# ll -h test01.ibd
-rw-r----- 1 mysql mysql 72M Aug 30 15:27 test01.ibd
# 事务积压状态
(Tue Aug 30 15:36:20 2022)>SELECT MEMBER_ID AS id, COUNT_TRANSACTIONS_IN_QUEUE AS trx_tobe_verified, COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE AS trx_tobe_applied, COUNT_TRANSACTIONS_CHECKED AS trx_chkd, COUNT_TRANSACTIONS_REMOTE_APPLIED AS trx_done, COUNT_TRANSACTIONS_LOCAL_PROPOSED AS proposed FROM performance_schema.replication_group_member_stats;
+--------------------------------------+-------------------+------------------+----------+----------+----------+
| id | trx_tobe_verified | trx_tobe_applied | trx_chkd | trx_done | proposed |
+--------------------------------------+-------------------+------------------+----------+----------+----------+
| 8eeedfb2-f5b5-11ec-bffb-fa160a219e3a | 0 | 0 |4685988 |4609338 | 76651 |
| a34484fb-f5b6-11ec-80a9-fa160a219e09 | 0 | 0 | 76553 | 76553 | 0 |
| c7acd552-f5b2-11ec-ba1e-fa160a219e07 | 0 | 0 |4686352 |4686355 | 0 |
+--------------------------------------+-------------------+------------------+----------+----------+----------+
3 rows in set (0.00 sec)
# 组状态
(Tue Aug 30 15:33:51 2022)>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST| MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 8eeedfb2-f5b5-11ec-bffb-fa160a219e3a | 10.33.158.58 | 3306 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | a34484fb-f5b6-11ec-80a9-fa160a219e09 | 10.33.158.9| 3306 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | c7acd552-f5b2-11ec-ba1e-fa160a219e07 | 10.33.158.7| 3306 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
delete from table这种方式删除数据后，不会自动回收磁盘空间，需要再手动回收。
如果是想删除所有数据，最好是直接执行truncate table。 yejr 发表于 2022-8-30 16:01
delete from table这种方式删除数据后，不会自动回收磁盘空间，需要再手动回收。
如果是想删除所有数据，最 ...
如果只是删除部分数据，想回收又不能重启mysql,有没有什么好的办法 # 碎片整理
ALTER TABLE test01 ENGINE = InnoDB;
# 分析表
analyze table test01;
解决
fuhao009 发表于 2022-8-30 16:38
# 碎片整理
ALTER TABLE test01 ENGINE = InnoDB;
# 分析表
执行 ALTER TABLE 就够了，analyze table不是必须的
页:
[1]