mysql清理碎片

精选转载

紫暝1127 2013-10-11 18:38:22 博主文章分类：mysql

文章标签 空间 mysql 清理 file counter 文章分类 MySQL 数据库

出处：http://shanchao7932297.blog.163.com/blog/static/1363624201122811559288/

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

如果经常删除innodb table中的数据，或者经常插入/更新非固定长度的字段，
会导致表空间中产生大量的wasted space, 就像文件系统中的文件碎片一样。

能不能搞个碎片整理呢？
myisam表可以使用optimize table来进行整理。innodb table呢？

如果你把所有的table都放在同一个innodb data file中，那就只能导出数据再重新导入了。

如果使用了 innodb_file_per_table选项，就有更好的方法了：

在mysql 5.1.21之前的版本中， free space counter保存在 information_schema.tables表中的table_comment字段中。

SELECT table_schema,TABLE_NAME, table_comment FROM information_schema.TABLES
WHERE engine LIKE’InnoDB’AND table_comment RLIKE’InnoDB free: ([0-9]{6,}).*’;

通过这条语句，可以找到表空间中free space 大于 100M的表(其实应该是97.65M。 1000与1024的关系)。

ps: RLIKE就是REGEXP的意思。
关于mysql中的正则表达式可以继续阅读这里：

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

mysql 5.1.21以及之后的版本，这个数据被移动到了 data_free字段中。

SELECT table_schema,TABLE_NAME, data_free/1024/1024AS data_free_MB
FROM information_schema.TABLES
WHERE engine LIKE’InnoDB’AND data_free >100*1024*1024;

You can reclaim the lost space by rebuilding the table:

ALTERTABLE foo ENGINE=InnoDB;

InnoDB表并不支持 optimize table，如果你一定要optimize一个innodb table，

就会调用上面那条 alter table … engine=Innodb:

It will result in a read lock, but not a full table lock.
需要一个非全表的读锁。

如果表里面经常有更新或者删除操作，最好周期性的运行这条语句。
就像使用myisam表的时候，周期性地optimize table一样。

For MyISAM tables, OPTIMIZE TABLE works as follows:

If the table has deleted or split rows, repair the table.

If the index pages are not sorted, sort them.

If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics

and free unused space in the clustered index. Beginning with MySQL 5.1.27, this is displayed in the output of