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---- 1 mysql mysql 140M Mar 26 22:12 /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---- 1 mysql mysql 140M Mar 26 23:56 /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---- 1 mysql mysql 48M Mar 27 00:01 /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, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `employees`.`salaries`...
Creating new table...
Created new table employees._salaries_new OK.
Altering new table...
Altered `employees`.`_salaries_new` OK.
2014-03-27T00:24:29 Creating triggers...
2014-03-27T00:24:29 Created triggers OK.
2014-03-27T00:24:29 Copying approximately 843830 rows...
2014-03-27T00:24:41 Copied rows OK.
2014-03-27T00:24:41 Swapping tables...
2014-03-27T00:24:41 Swapped original and new tables OK.
2014-03-27T00:24:41 Dropping old table...
2014-03-27T00:24:41 Dropped old table `employees`.`_salaries_old` OK.
2014-03-27T00:24:41 Dropping triggers...
2014-03-27T00:24:41 Dropped triggers OK.
Successfully altered `employees`.`salaries`.
[root@localhost ~]# ls -lh /data/mysql/employees/salaries.ibd 
-rw-rw---- 1 mysql mysql 48M Mar 27 00:24 /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