mysql 清理表碎片需要停止数据库吗_Mysql的表的碎片清理

最新推荐文章于 2022-05-11 16:00:23 发布

币圈沉浮

最新推荐文章于 2022-05-11 16:00:23 发布

阅读量139

点赞数

文章标签： mysql 清理表碎片需要停止数据库吗

本文链接：https://blog.csdn.net/weixin_35701203/article/details/114336040

版权

当MySQL数据库中表的碎片过多导致性能下降时，可以通过分析和重建表引擎来清理碎片。对于MyISAM表，使用`OPTIMIZE TABLE`；对于InnoDB表，若`innodb_file_per_table`启用，可通过`ALTER TABLE`重建引擎。在5.1.21之后，可以使用`data_free`列来识别需要清理的表。

摘要由CSDN通过智能技术生成

最近在生成环境下的mysql运行下降，有些sql执行也慢，首先检查下慢查询日志是否开启

show variables like ‘slow_query_log%’

在看慢日志设置的时间

show variables like ‘long_query_time%’;

可以看到开启了慢查询，find / -name slow-query.log查找慢日志

发现里面的sql是很慢但是都走了索引但是这些慢的sql都指向一两个表。所以想到可能是每次备份对这几个表的删除操作，但是没有进行碎片整理

进行下碎片整理，按表的引擎来处理

Myisam清理碎片

OPTIMIZE TABLE table_name、

InnoDB碎片清理

看到这段话

if you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation.

If you’re not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure.

But if you are using innodb_file_per_table, you can identify and reclaim this space!

Prior to 5.1.21, the free space counter is available from the table_comment column of information_schema.tables. Here is some SQL to identify tables with at least 100M (actually 97.65M) of free space:

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

Starting with 5.1.21, this was moved to the data_free column (a much more appropriate place):

SELECT table_schema, table_name, data_free/1024/1024 AS 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. The best way to do this is using ‘alter table’ without actually changing anything:

ALTER TABLE foo ENGINE=InnoDB;

This is what MySQL does behind the scenes if you run ‘optimize table’ on an InnoDB table. It will result in a read lock, but not a full table lock. How long it takes is completely dependent on the amount of data in the table (but not the size of the data file). If you have a table with a high volume of deletes or updates, you may want to run this monthly, or even weekly.

所以先进行分析，语句如下

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

返回了两个数据，正是那两张表得data_free大于100M，所以ALTER TABLE tablename ENGINE=InnoDB;

相当于重建表引擎了。再执行速度正常了。

这里要提下如果一个表的表数据大小和索引大小与实际的表数据不符也需要清理下表碎片

在这篇博客中有说http://blog.csdn.net/u011575570/article/details/48092469

结束.欢迎指出不当之处谢谢

参考：http://pengbotao.cn/mysql-suipian-youhua.html

Mysql的表的碎片清理

标签：mysql 表的碎片清理 innodb myisam

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://blog.csdn.net/u011575570/article/details/48092403