MySQL的 data_free，表碎片整理

最新推荐文章于 2024-07-29 04:06:11 发布

雅冰石

最新推荐文章于 2024-07-29 04:06:11 发布

阅读量1.8w

点赞数 2

分类专栏： 2 MySql 学习 # MySql 性能调优

2 MySql 学习同时被 2 个专栏收录

332 篇文章 5 订阅

订阅专栏

MySql 性能调优

47 篇文章 4 订阅

订阅专栏

在MySQL中，我们经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。不过，当我们使用这些数据类型之后，我们就不得不做一些额外的工作——MySQL数据表碎片整理。
每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

1.或者查看某个表所占空间，以及碎片大小。

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002';
或者

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='db002' and data_free !=0;

--注意:如果是共享表空间的话， data_free 表示共享表空间的大小而非数据的大小。如果是独享表空间才是该表的剩余空间。

如：我的表空间文件一共才136G，下面的几个表的data_free都是120多G:

mysql> select table_schema,table_name,data_free,data_length,index_length FROM information_schema.TABLES where data_free !=0;
+--------------+------------------------------+--------------+-------------+--------------+
| table_schema | table_name | data_free | data_length | index_length |
+--------------+------------------------------+--------------+-------------+--------------+
| eip_partner | TASK_AUDIT_PARAM | 124129378304 | 16384 | 0 |
| eip_partner | TASK_AUDIT_TOTAL | 124129378304 | 16384 | 0 |
| eip_partner | TASK_PENDING_LOG | 124129378304 | 16384 | 0 |
| eip_partner | TASK_READING_LOG | 124129378304 | 16384 | 0 |
2、整理表的碎片
optimize table aaa_safe,aaa_user,t_platform_user,t_user;

提示该表不支持 optimize,但是下边有显示OK.其实已经执行成功了。5.6.X的版本，其实已经支持Innodb了。
整理完毕

针对以前版本：
大致意思是说innodb的数据库不支持optimize，可以用
ALTER TABLE table.name ENGINE='InnoDB';
该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的，但是在进行操作时还是先进行备份为好

也可以在启动数据库的时候指定--skip-new或者--safe-mode选项来支持optimize功能

[root@wg ~]# service mysql start --skip-new

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

1. 如果表已经删除或分解了行，则修复表。

2. 如果未对索引页进行分类，则进行分类。

3. 如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。

对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

备注：
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2. OPTIMIZE TABLE 只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在 OPTIMIZE TABLE 运行过程中，MySQL会锁定表。

4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

--本篇文章转自：http://blog.itpub.net/29893219/viewspace-1871911/，http://www.linuxidc.com/Linux/2013-01/77872.htm