myisam:optimize table table_name

innodb:alter table table.name engine="innodb’

(資料圖)

1. 問題描述在使用mysql的時候有時候，可能會發(fā)現(xiàn)盡管一張表刪除了許多數(shù)據(jù)，但是這張表表的數(shù)據(jù)文件和索引文件卻奇怪的沒有變小。這是因為mysql在刪除數(shù)據(jù)（特別是有Text和BLOB）的時候，會留下許多的數(shù)據(jù)空洞/碎片，這些空洞會占據(jù)原來數(shù)據(jù)的空間，所以文件的大小沒有改變。這些空洞在以后插入數(shù)據(jù)的時候可能會被再度利用起來，當(dāng)然也有可能一直存在。這種空洞不僅額外增加了存儲代價，同時也因為數(shù)據(jù)碎片化降低了表的掃描效率。

2. 使用場景如果您已經(jīng)刪除了表的一大部分，或者如果您已經(jīng)對含有可變長度行的表（含有VARCHAR, BLOB或TEXT列的表）進(jìn)行了很多更改，則應(yīng)使用OPTIMIZE TABLE。被刪除的記錄被保持在鏈接清單中，后續(xù)的INSERT操作會重新使用舊的記錄位置。您可以使用OPTIMIZE TABLE來重新利用未使用的空間，并整理數(shù)據(jù)文件的碎片。

當(dāng)您的庫中刪除了大量的數(shù)據(jù)后，您可能會發(fā)現(xiàn)數(shù)據(jù)文件尺寸并沒有減小。這是因為刪除操作后在數(shù)據(jù)文件中留下碎片所致。

在多數(shù)的設(shè)置中，您根本不需要運行OPTIMIZE TABLE。即使您對可變長度的行進(jìn)行了大量的更新，您也不需要經(jīng)常運行，每周一次或每月一次即可，只對特定的表運行。

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

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

對于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，這會重建表。重建操作能更新索引統(tǒng)計數(shù)據(jù)并釋放成簇索引中的未使用的空間。

注意：在OPTIMIZE TABLE運行過程中，MySQL會鎖定表。

對于myisam可以直接使用 optimize table table_name, 當(dāng)是InnoDB引擎時，會報“Table does not support optimize, doing recreate + analyze instead”，一般情況下，由myisam轉(zhuǎn)成innodb，會用alter table table.name engine="innodb’進(jìn)行轉(zhuǎn)換，優(yōu)化也可以用這個。所以當(dāng)是InnoDB引擎時我們就用alter table table.name engine="innodb’來代替optimize做優(yōu)化就可以。

查看前后效果可以使用show table status命令,例如show table status from [database] like ‘[table_name]’;返回結(jié)果中的data_free即為空洞所占據(jù)的存儲空間。

總結(jié)1.MySQL官方建議不要經(jīng)常(每小時或每天)進(jìn)行碎片整理，一般根據(jù)實際情況，只需要每周或者每月整理一次即可。2.OPTIMIZE TABLE只對MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，并不是所有表都需要進(jìn)行碎片整理，一般只需要對包含上述可變長度的文本數(shù)據(jù)類型的表進(jìn)行整理即可。3.在OPTIMIZE TABLE運行過程中，MySQL會鎖定表。4.默認(rèn)情況下，直接對InnoDB引擎的數(shù)據(jù)表使用OPTIMIZE TABLE，可能會顯示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。這個時候，我們可以用mysqld --skip-new或者mysqld --safe-mode命令來重啟MySQL，以便于讓其他引擎支持OPTIMIZE TABLE。

關(guān)鍵詞：