MySQL資料表磁碟重組

標籤：mysql   資料表   磁碟重組   

在MySQL中，我們經常會使用VARCHAR、TEXT、BLOB等可變長度的文本資料類型。不過，當我們使用這些資料類型之後，我們就不得不做一些額外的工作——MySQL資料表磁碟重組。

那麼，為什麼在使用這些資料類型之後，我們就要對MySQL定期進行磁碟重組呢？

現在，我們先來看一個具體的例子。在這裡，我們使用如下SQL語句在MySQL內建的TEST資料庫中建立名為DEMO的資料表並插入5條測試資料。

--建立DEMO表 id int unsigned, body text ) engine=myisam charset=utf8; --插入5條測試資料 INSERT INTO DEMO VALUES(1, ‘AAAAA‘); INSERT INTO DEMO VALUES(2, ‘BBBBB‘); INSERT INTO DEMO VALUES(3, ‘CCCCC‘); INSERT INTO DEMO VALUES(4, ‘DDDDD‘); INSERT INTO DEMO VALUES(5, ‘EEEEE‘);

然後我們以這5條測試資料為基礎，使用如下INSERT INTO語句重複執行多次進行複製性插入。

INSERT INTO DEMO SELECT id, body FROM DEMO;

使用INSERT INTO語句多次插入產生總共約262萬條資料

眾所周知，MySQL中MyISAM表的資料是以檔案形式儲存的，我們可以在MySQL儲存資料的檔案夾中找到資料庫test目錄下的demo.MYD檔案。此時，我們可以看到demo.MYD檔案的大小約為50MB。

demo.MYD檔案約為50MB

此時，假如我們需要刪除DEMO表中所有ID列小於3的資料(即1和2)，於是我們執行如下SQL語句：

DELETE FROM DEMO WHERE id < 3

此時，我們可以看到DEMO表中的資料量只有原來的3/5：

刪除後，只剩下157萬條記錄

DEMO表中的現有資料量只有原來的3/5，按理說，這個時候demo.MYD檔案的大小也應該只有原來的3/5左右。不過，我們再次查看demo.MYD檔案時，卻驚奇地發現該檔案的大小一點都沒有變！

刪除資料後，demo.MYD的檔案大小沒有變化

那麼就究竟是怎麼一回事呢？原來，在MySQL中，如果我們刪除了表中的大量資料，或者我們對含有可變長度文本資料類型(VARCHAR，TEXT或BLOB)的表進行了很多更改，不過被刪除的資料記錄仍然被保持在MySQL的連結清單中，因此資料存放區檔案的大小並不會隨著資料的刪除而減小。

當我們確定資料需要被清除掉時，那麼這些資料就已經成了無用的資料，但是按照MySQL的處理方式，這些資料仍然會佔用我們的磁碟空間，從而造成了極大的資源浪費。不僅如此，過大的資料檔案還會導致MySQL執行相關資料操作時需要耗費更多的效能和時間。因此，對MySQL的某些資料表進行磁碟重組是非常有必要的。

對MySQL進行磁碟重組的方法非常簡單，因為MySQL已經給我們提供了對應的SQL指令，這個SQL指令就是OPTIMIZE TABLE，其完整文法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2] ...

從上面的文法描述中，我們可以得知，OPTIMIZE TABLE可以一次性對多個表進行磁碟重組，只需要在OPTIMIZE TABLE後面接多個表名，並以英文逗號隔開即可。

此外，OPTIMIZE TABLE語句有兩個可選的關鍵字：LOCAL和NO_WRITE_TO_BINLOG。在預設情況下，OPTIMIZE TABLE語句將會被記錄到二進位日誌中，如果我們指定了LOCAL或NO_WRITE_TO_BINLOG關鍵字，則不會記錄。當然，一般情況下，我們也無需關注這兩個關鍵字。

現在，我們就使用OPTIMIZE TABLE語句對剛才的DEMO表進行磁碟重組。

對demo表進行磁碟重組

然後，我們再來查看demo.MYD檔案，此時我們就會發現demo.MYD檔案的大小已經減小到約為原來的3/5了。

磁碟重組後demo.MYD檔案的大小

備忘： 1.MySQL官方建議不要經常(每小時或每天)進行磁碟重組，一般根據實際情況，只需要每周或者每月整理一次即可。2.OPTIMIZE TABLE只對MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，並不是所有表都需要進行磁碟重組，一般只需要對包含上述可變長度的文本資料類型的表進行整理即可。3.在OPTIMIZE TABLE運行過程中，MySQL會鎖定表。4.預設情況下，直接對InnoDB引擎的資料表使用OPTIMIZE TABLE，可能會顯示“ Table does not support optimize, doing recreate + analyze instead”的提示資訊。這個時候，我們可以用mysqld --skip-new或者mysqld --safe-mode命令來重啟MySQL，以便於讓其他引擎支援OPTIMIZE TABLE。

本文出自 “XFICC” 部落格，請務必保留此出處http://xficc.blog.51cto.com/1189288/1570296

MySQL資料表磁碟重組