数据库表数据空洞处理

数据空洞

当对一条数据执行delete操作时，MySQL将数据删除后，并未将数据占用的空间返还给操作系统，而是将当前空间标记为"可复用"，当有新的数据插入时，则不会重新申请空间，而是插入到"可复用"空间中，这种"可复用"空间，称之为数据空洞。

MySQL官方文档对此的解释如下：

After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

数据空洞的好处是尽可能的复用表空间结构，带来的问题也是显而易见，当删除数据后，表空间并未及时的释放，当长时间没有新的数据填充，会造成空间浪费的情况。

查看数据库表使用情况

//show table status是有缓存的，执行前最好先执行以下语句
ANALYZE TABLE 表名;
//update_time列表示表数据最后修改时间
show table status;

查看数据库表数据空洞大小

SELECT table_schema AS '数据库',
　　　　TABLE_NAME AS '表名',
　　　　table_rows AS '记录数',
　　　　truncate(data_length/1024/1024, 2) AS '数据容量(MB)',
　　　　truncate(data_free/1024/1024, 2) AS '数据空洞(MB)',
　　　　truncate(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables ORDER BY data_length DESC,index_length DESC;

回收空洞

对于myisam可以直接使用 optimize table table_name, 当是InnoDB引擎时，会报“Table does not support optimize, doing recreate + analyze instead”。

InnoDB需要使用以下命令。

alter table 表名 engine ='innodb';