在MySQL中，我們經(jīng)常會使用VARCHAR、TEXT、BLOB等可變長度的文本數(shù)據(jù)類型。不過，當(dāng)我們使用這些數(shù)據(jù)類型之后，我們就不得不做一些額外的工作——MySQL數(shù)據(jù)表碎片整理。

那么，為什么在使用這些數(shù)據(jù)類型之后，我們就要對MySQL定期進(jìn)行碎片整理呢？

現(xiàn)在，我們先來看一個具體的例子。在這里，我們使用如下SQL語句在MySQL自帶的TEST數(shù)據(jù)庫中創(chuàng)建名為DEMO的數(shù)據(jù)表并插入5條測試數(shù)據(jù)。

--創(chuàng)建DEMO表
CREATE TABLE DEMO(
id int	unsigned,
body text
) engine=myisam charset=utf8;
												
 
--插入5條測試數(shù)據(jù)
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條測試數(shù)據(jù)為基礎(chǔ)，使用如下INSERT INTO語句重復(fù)執(zhí)行多次進(jìn)行復(fù)制性插入。

INSERT INTO DEMO SELECT id, body FROM DEMO;

使用INSERT INTO語句多次插入產(chǎn)生總共約262萬條數(shù)據(jù)

眾所周知，MySQL中MyISAM表的數(shù)據(jù)是以文件形式存儲的，我們可以在MySQL存儲數(shù)據(jù)的文件夾中找到數(shù)據(jù)庫test目錄下的demo.MYD文件。此時，我們可以看到demo.MYD文件的大小約為50MB。

demo.MYD文件約為50MB

此時，假如我們需要刪除DEMO表中所有ID列小于3的數(shù)據(jù)(即1和2)，于是我們執(zhí)行如下SQL語句：

DELETE FROM DEMO WHERE id < 3

此時，我們可以看到DEMO表中的數(shù)據(jù)量只有原來的3/5：

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

DEMO表中的現(xiàn)有數(shù)據(jù)量只有原來的3/5，按理說，這個時候demo.MYD文件的大小也應(yīng)該只有原來的3/5左右。不過，我們再次查看demo.MYD文件時，卻驚奇地發(fā)現(xiàn)該文件的大小一點(diǎn)都沒有變！

刪除數(shù)據(jù)后，demo.MYD的文件大小沒有變化

那么就究竟是怎么一回事呢？原來，在MySQL中，如果我們刪除了表中的大量數(shù)據(jù)，或者我們對含有可變長度文本數(shù)據(jù)類型(VARCHAR，TEXT或BLOB)的表進(jìn)行了很多更改，不過被刪除的數(shù)據(jù)記錄仍然被保持在MySQL的鏈接清單中，因此數(shù)據(jù)存儲文件的大小并不會隨著數(shù)據(jù)的刪除而減小。

當(dāng)我們確定數(shù)據(jù)需要被清除掉時，那么這些數(shù)據(jù)就已經(jīng)成了無用的數(shù)據(jù)，但是按照MySQL的處理方式，這些數(shù)據(jù)仍然會占用我們的磁盤空間，從而造成了極大的資源浪費(fèi)。不僅如此，過大的數(shù)據(jù)文件還會導(dǎo)致MySQL執(zhí)行相關(guān)數(shù)據(jù)操作時需要耗費(fèi)更多的性能和時間。因此，對MySQL的某些數(shù)據(jù)表進(jìn)行碎片整理是非常有必要的。

對MySQL進(jìn)行碎片整理的方法非常簡單，因?yàn)镸ySQL已經(jīng)給我們提供了對應(yīng)的SQL指令，這個SQL指令就是OPTIMIZE TABLE，其完整語法如下：

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

從上面的語法描述中，我們可以得知，OPTIMIZE TABLE可以一次性對多個表進(jìn)行碎片整理，只需要在OPTIMIZE TABLE后面接多個表名，并以英文逗號隔開即可。

此外，OPTIMIZE TABLE語句有兩個可選的關(guān)鍵字：LOCAL和NO_WRITE_TO_BINLOG。在默認(rèn)情況下，OPTIMIZE TABLE語句將會被記錄到二進(jìn)制日志中，如果我們指定了LOCAL或NO_WRITE_TO_BINLOG關(guān)鍵字，則不會記錄。當(dāng)然，一般情況下，我們也無需關(guān)注這兩個關(guān)鍵字。

現(xiàn)在，我們就使用OPTIMIZE TABLE語句對剛才的DEMO表進(jìn)行碎片整理。

對demo表進(jìn)行碎片整理

然后，我們再來查看demo.MYD文件，此時我們就會發(fā)現(xiàn)demo.MYD文件的大小已經(jīng)減小到約為原來的3/5了。

碎片整理后demo.MYD文件的大小

備注：

1.MySQL官方建議不要經(jīng)常(每小時或每天)進(jìn)行碎片整理，一般根據(jù)實(shí)際情況，只需要每周或者每月整理一次即可。

2.OPTIMIZE TABLE只對MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，并不是所有表都需要進(jìn)行碎片整理，一般只需要對包含上述可變長度的文本數(shù)據(jù)類型的表進(jìn)行整理即可。

3.在OPTIMIZE TABLE運(yùn)行過程中，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。

總結(jié)

到此這篇關(guān)于MYSQL優(yōu)化之?dāng)?shù)據(jù)表碎片整理的文章就介紹到這了,更多相關(guān)MYSQL數(shù)據(jù)表碎片整理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論