MYSQL優化之資料表碎片整理詳解

在MySQL中，我們經常會使用VARCHAR、TEXT、BLOB等可變長度的文字資料型別。不過，當我們使用這些資料型別之後，我們就不得不做一些額外的工作——MySQL資料表碎片整理。

那麼，為什麼在使用這些資料型別之後，我們就要對MySQL定期進行碎片整理呢？

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

--建立DEMO表
CREATE TABLE 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。

總結

到此這篇關於MYSQL優化之資料表碎片整理的文章就介紹到這了,更多相關MYSQL資料表碎片整理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！