InnoDB表回收空间

当innodb_file_per_table设置为OFF，那么所有数据将被存储在ibdata文件。如果删除一些数据和删除一些表则没有回收未使用的磁盘空间，除了导出表然后再导入的方法来回收表空间大小，除此之外没有任何其他的方法。
当innodb_file_per_table设置为ON时，每个表中存储的数据和索引在它自己的表空间文件。但是，共享表空间的ibdata1仍然在增长，为什么呢？因为ibdata1中依然有：

（1）data dictionary aka metadata of InnoDB tables

（2）change buffer

（3）doublewrite buffer

（4）undo logs

更为详细的介绍请各位童鞋阅读此文章
http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

当你删除表中一些记录时，他们只是在磁盘上标记为删除，但空间会由以后可以重复使用，当插入/更新多个行，但它永远不会被回收。
但是，如果设置innodb_file_per_table为ON，即使用独立表空间，那么可以通过在该表运行OPTIMIZE TABLE回收空间。 OPTIMIZE TABLE将创建一个新的相同的空表。然后，它会通过行数据复制一行从旧表到新的表。在这个过程中一个新的。 ibd表空间将被创建和空间将被回收。

具体的我们来看一个例子：

mysql> select count(*) from salaries;

+----------+

| count(*) |

+----------+

|  2844047 |

+----------+

1 row in set (0.00 sec)

mysql>

[root@localhost ~]# ls -lh /data/mysql/employees/salaries.ibd

-rw-rw----  mysql mysql 140M Mar  : /data/mysql/employees/salaries.ibd

[root@localhost ~]#

mysql> delete from salaries limit 2000000;

Query OK, 2000000 rows affected (37.50 sec)

mysql> select count(*) from salaries;

+----------+

| count(*) |

+----------+

|   844047 |

+----------+

1 row in set (0.13 sec)

mysql>

[root@localhost ~]# ls -lh /data/mysql/employees/salaries.ibd

-rw-rw----  mysql mysql 140M Mar  : /data/mysql/employees/salaries.ibd

[root@localhost ~]#

可以看见虽然删除200w记录后，但是ibd文件并没有减小，还是140M。

mysql> optimize table salaries;

+--------------------+----------+----------+-------------------------------------------------------------------+

| Table              | Op       | Msg_type | Msg_text                                                          |

+--------------------+----------+----------+-------------------------------------------------------------------+

| employees.salaries | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| employees.salaries | optimize | status   | OK                                                                |

+--------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (7.89 sec)

mysql>

[root@localhost ~]# ls -lh /data/mysql/employees/salaries.ibd

-rw-rw----  mysql mysql 48M Mar  : /data/mysql/employees/salaries.ibd

[root@localhost ~]#

在OPTIMIZE 优化表后，可以回收空间。正如你看到的，salaries.ibd文件的大小减小从140M减小到48M。
我想在这里提一提，在这个过程中该表将被锁定（Table locked for just Writes），当操作比较大的表时，这会影响性能，数据无法写入。所以，如果不想要锁定表，我们可以使用Percona的pt-online-schema-change。它可以改变表的存储引擎并且不会锁表。可以使用ALTER TABLE ENGINE=INNODB，这将重新创建表并回收空间。

mysql> select count(*) from  salaries;

+----------+

| count(*) |

+----------+

|  2844047 |

+----------+

1 row in set (0.00 sec)

mysql> delete from salaries limit 2000000;

Query OK, 2000000 rows affected (21.88 sec)

mysql> \! ls -lh /data/mysql/employees/salaries.ibd

-rw-rw---- 1 mysql mysql 140M Mar 27 00:21 /data/mysql/employees/salaries.ibd

接下来使用pt-online-schema-change回收空间：

[root@localhost ~]# pt-online-schema-change --alter "ENGINE=InnoDB" D=employees,t=salaries --execute

Operation, tries, wait:

  copy_rows, , 0.25

  create_triggers, ,

  drop_triggers, ,

  swap_tables, ,

  update_foreign_keys, ,

Altering `employees`.`salaries`...

Creating new table...

Created new table employees._salaries_new OK.

Altering new table...

Altered `employees`.`_salaries_new` OK.

--27T00:: Creating triggers...

--27T00:: Created triggers OK.

--27T00:: Copying approximately  rows...

--27T00:: Copied rows OK.

--27T00:: Swapping tables...

--27T00:: Swapped original and new tables OK.

--27T00:: Dropping old table...

--27T00:: Dropped old table `employees`.`_salaries_old` OK.

--27T00:: Dropping triggers...

--27T00:: Dropped triggers OK.

Successfully altered `employees`.`salaries`.

[root@localhost ~]# ls -lh /data/mysql/employees/salaries.ibd

-rw-rw----  mysql mysql 48M Mar  : /data/mysql/employees/salaries.ibd

[root@localhost ~]#

可以看见已经成功释放空间。从140M缩小到48M

使用该工具需要注意：

请确保有足够的空间运行pt-online-schema-change，因为它会创建一个包含原始表的大小大致相同的临时表。在主的操作会应用到从服务器！（假如你是主从环境）

参考资料：

http://www.mysqlperformanceblog.com/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

巴特西

InnoDB表回收空间

最新文章

热门文章