Mysql的表的片段清理，Mysql片段清理

Mysql的表的片段清理，Mysql片段清理

最近在產生環境下的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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。