維護mysql 對錶空間的回收策略是隻擴充,不回收。對錶執行delete之後,表空間是不回收的,空閒的空間可以被insert 繼續使用，通常執行truncate,重新邏輯匯出,再匯入或者執行optimize table 三種都可以重新使用未使用的表空間。

本文介紹一下optimize table OPTIMIZE TABLE可以重新利用未使用的空間,並整理資料檔案的碎片。

1 演示回收空間

刪除之前:

[root@rac1 test]# ll tab.ibd

-rw-rw---- 1 mysql mysql 150994944 10-16 22:15 tab.ibd

對錶進行刪除操作：

root@rac1 : test 21:12:00> optimize table tab;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

刪除之後:

[root@rac1 test]# ll tab.ibd

-rw-rw---- 1 mysql mysql 98304 10-16 22:15 tab.ibd

顯然 ibd檔案經過optimize之後明顯的減小了。

2 使用範圍

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

對於MyISAM表,OPTIMIZE TABLE按如下方式操作：

1.如果表已經刪除或分解了行,則修復表。

2.如果未對索引頁進行分類,則進行分類。

3.如果表的統計資料沒有更新（並且透過對索引進行分類不能實現修復）,則進行更新。

對於BDB表,OPTIMIZE TABLE目前被對映到ANALYZE TABLE上。

對於InnoDB表,OPTIMIZE TABLE被對映到ALTER TABLE上,這會重建表。重建操作能更新索引統計資料並釋放成簇索引中的未使用的空間。

3 對於 M-S,M-M 架構的資料庫

如果不想把optimize table 寫入從資料庫,可以執行

OPTIMIZE NO_WRITE_TO_BINLOG TABLE

這樣命令就不會寫入binglog 並且不被從庫執行。

4 遇到的問題

如果在使用optimize的時候遇到如下錯誤：

root@rac1 : test 22:10:22> optimize table tab;

+----------+----------+----------+-------------------------------------------------------------------+

+----------+----------+----------+-------------------------------------------------------------------+

+----------+----------+----------+-------------------------------------------------------------------+

2 rows in set (29.88 sec)

使用--skip-new或--safe-mode選項可以啟動mysqld。透過啟動mysqld,您可以使OPTIMIZE TABLE對其它表型別起作用。

注意,在OPTIMIZE TABLE執行過程中,MySQL會鎖定表。

【MySQL】mysql optimize table

相關文章