快捷導(dǎo)航

MYSQL優(yōu)化之?dāng)?shù)據(jù)表碎片整理詳解

更新時(shí)間：2022年04月02日 10:54:38 作者：劉俊濤的博客

在日常的數(shù)據(jù)庫(kù)維護(hù)中,在給客戶制定維護(hù)方案時(shí),都會(huì)有降低高水位此類的建議建議每年年終定期做數(shù)據(jù)整理,下面這篇文章主要給大家介紹了關(guān)于MYSQL優(yōu)化之?dāng)?shù)據(jù)表碎片整理的相關(guān)資料,需要的朋友可以參考下

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

INSERT INTO DEMO SELECT id, body FROM DEMO;

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

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

demo.MYD文件約為50MB

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

DELETE FROM DEMO WHERE id < 3

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

刪除后，只剩下157萬(wàn)條記錄

DEMO表中的現(xiàn)有數(shù)據(jù)量只有原來(lái)的3/5，按理說(shuō)，這個(gè)時(shí)候demo.MYD文件的大小也應(yīng)該只有原來(lái)的3/5左右。不過(guò)，我們?cè)俅尾榭磀emo.MYD文件時(shí)，卻驚奇地發(fā)現(xiàn)該文件的大小一點(diǎn)都沒(méi)有變！

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

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

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

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

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

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

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

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

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

然后，我們?cè)賮?lái)查看demo.MYD文件，此時(shí)我們就會(huì)發(fā)現(xiàn)demo.MYD文件的大小已經(jīng)減小到約為原來(lái)的3/5了。

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

備注：

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

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

3.在OPTIMIZE TABLE運(yùn)行過(guò)程中，MySQL會(huì)鎖定表。

4.默認(rèn)情況下，直接對(duì)InnoDB引擎的數(shù)據(jù)表使用OPTIMIZE TABLE，可能會(huì)顯示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。這個(gè)時(shí)候，我們可以用mysqld --skip-new或者mysqld --safe-mode命令來(lái)重啟MySQL，以便于讓其他引擎支持OPTIMIZE TABLE。

總結(jié)

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

您可能感興趣的文章: