首页 » MySQL » 回收MySQL表空间

回收MySQL表空间

　
暂无评论　1,404次浏览

说明：
对于innodb独立表空间来说，数据delete后，其占用的表空间是没有被回收。可以通过很多种方法来释放表空间。
以下方法均是假定innodb_file_per_table已经开启，每个innodb引擎的表都单独存放。至于innodb_file_per_table=off的情况，请查看附件。

具体：
方法一、使用常规optimize回收
OPTIMIZE TABLE会通过创建新的空表，然后把旧表数据row by row（一行一行地)拷贝到新表去。在这个过程中新的.ibd表空间会被创建并且磁盘空间会被重建。具体还搞不太懂
1.1 查看innodb表的记录数及文件大小

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1200096 |
+----------+

[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 72M 10-08 17:39 t.ibd

1.2 删除80万条记录

mysql> delete from t limit 800000

1.3 查看delete后，innodb表后的记录数及文件大小，看到看到文件大小不变

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|   400096 |
+----------+

[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 72M 10-08 17:41 t.ibd

1.4 查看optimize后，innodb表后的记录数及文件大小

mysql> optimize table t;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| employees.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| employees.t | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+

[mysql@even employees]$ ls -alh t.ibd
-rw-rw---- 1 mysql dba 29M 10-08 17:43 t.ibd

1.5 optimize table缺陷

mysql> show processlist;
+----+------+-----------+-----------+---------+------+-------------------+------------------+
| Id | User | Host      | db        | Command | Time | State             | Info             |
+----+------+-----------+-----------+---------+------+-------------------+------------------+
|  5 | root | localhost | NULL      | Query   |    0 | NULL              | show processlist |
|  6 | root | localhost | employees | Query   |   64 | copy to tmp table | optimize table t |
+----+------+-----------+-----------+---------+------+-------------------+------------------+

mysqladmin debug结果如下
Thread database.table_name          Locked/Waiting        Lock_type

6       employees.t                 Locked - read         Read lock without concurrent inserts

可以看到当用optimize重建innodb表时，innodb表被Read lock(读锁)，因为当你有大表时性能恐怕会深受影响。

方法二、alter table TableName engine=innodb
alter table ….engine=innodb是可以整理碎片，回收部分表空间的。听说在数据量小或者buffer pool 比较小的时候（小于30G）是很不错的。

方法三、使用percona的pt-online-schema-change，避免加锁，类似方法二，通过alter table回收表空间

$ pt-online-schema-change -uroot -poracle --alter "ENGINE=InnoDB" D=employees,t=t --execute

percona这款工具本身是用来进行非阻塞的online ddl的，但由于只有alter table …语句才能回收表空间，那可以采用该工具的原理：创建一张临时表，以触发器来保证与原表的数据一致，最后renmame替换掉；用过这款工具的朋友可能会有疑问，“online ddl”最后的操作时 drop old table；drop trigger；这样的操作；但可以采用–no-drop-old-table,让其不会删除旧表，等有其他时间的时候，采用脚本形式批量删除记录，最后在drop掉剩余的“小表”；这样就避免了hang 住系统；

执行pt工具时有可能发生的类似错误：
Cannot chunk the original table There is no good index and the table is oversized
这是因为被作用的表需要含有主键或者唯一索引，这或许也能成为这款工具的小bug吧

附录：
How to reclaim space in InnoDB when innodb_file_per_table is ON
RDS MySql支持online ddl

原文链接：回收MySQL表空间，转载请注明来源！

赞 0