數(shù)據(jù)庫引擎以InnoDB為主

1.磁盤碎片是什么

?InnoDB表的數(shù)據(jù)存儲在頁中，每個頁可以存放多條記錄，這些記錄以樹形結(jié)構(gòu)組織，這棵樹稱為B+樹。

?聚簇索引的葉子結(jié)點包含行中所有字段的值，輔助索引的葉子結(jié)點包含索引列和主鍵列。

?在InnoDB中，刪除一些行，這些行只是被標(biāo)記已刪除，而不會立即刪除，個人認(rèn)為和電腦的清除磁盤相同，之后可以通過覆蓋舊數(shù)據(jù)實現(xiàn)刪除，InnDB的Purge線程會異步的清理這些沒用的索引鍵和行。但是依然不會把這些釋放出來的空間還給操作系統(tǒng)重新使用，因此會導(dǎo)致頁面中存在很多空洞，如果表結(jié)構(gòu)中包含動態(tài)長度字段，這些空間甚至無法被InnoDB重新用來存儲新的行。

?另外嚴(yán)重的問題是刪除數(shù)據(jù)會導(dǎo)致頁page中出現(xiàn)空白空間，大量隨機的Delete操作必然會在數(shù)據(jù)文件中造成不連續(xù)的空白空間，當(dāng)插入數(shù)據(jù)時，這些空白空間則會被利用起來，造成了數(shù)據(jù)的物理存儲順序和邏輯的排序順序不同，這就是數(shù)據(jù)碎片。

-- 查看全局變量Purge
show variables like 'innodb_purge_threads';

InnoDB后臺線程：http://www.dhdzp.com/article/243211.htm

解釋磁盤碎片的英文博客：https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/

上面這是個大能的博客，寫的pretty good！

2.實驗

我們首先創(chuàng)建一個具有一百條數(shù)據(jù)的表來進(jìn)行實驗：

delimiter //
create procedure insertt()
begin
	declare i int DEFAULT 0;
	while i<1000000 do
		insert into temp values(null,'a',1);
		set i:=i+1;
	end while;
end;
//
delimiter ;
drop PROCEDURE insertt;
-- 嘗試插入一百萬條數(shù)據(jù)
 
call insertt()
 
-- mysql版本5.7.36
> OK
> 時間: 838.706s

創(chuàng)建后的磁盤存儲大?。?/p>

?DB：information_scheme中存放我們表的信息，通過下列命令來查看我們的磁盤碎片最大的前五名

-- 別人的博客中copy的，我的豬腦寫不出來
SELECT CONCAT(table_schema, '.', table_name) as 'TABLE', 
       ENGINE, CONCAT(ROUND(table_rows / 1000000, 2), 'M')  ROWS, 
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, 
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') IDX, 
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL SIZE', 
       ROUND(index_length / data_length, 2)  IDXFRAC, CONCAT(ROUND(( data_free / 1024 / 1024),2), 'MB') AS data_free 
FROM information_schema.TABLES  
ORDER BY data_length + index_length desc LIMIT 5;

result:

我們可以看到data_free，我們最高的free空間只有6MB

innodb_ruby工具可以直接在linux系統(tǒng)下運行查看.Ibd文件的結(jié)構(gòu)，將B+tree以及磁盤使用暴露出來，但是我不會用，這里帶上他的github鏈接:https://github.com/akopytov/sysbench

下面我們執(zhí)行刪除操作：

-- 刪除前五十萬條數(shù)據(jù)
delete from temp order by id LIMIT 500000

文件大?。?/p>

刪除后，磁盤文件的大小并沒有變化，因為刪除產(chǎn)生了磁盤碎片，空白page殘留在文件中，被刪除的數(shù)據(jù)記錄仍然被保持在MySQL的鏈接清單中，因此數(shù)據(jù)存儲文件的大小并不會隨著數(shù)據(jù)的刪除而減小，我們再次使用上述操作查看data_free。

可以看到temp表的data_free增長了。

3.對于碎片回收操作

對MySQL進(jìn)行碎片整理有兩種方法：

OPTIMIZE TABLE

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

ALTER

ALTER TABLE table_name ENGINE = Innodb

OPTIMIZE可以同時對多個表格進(jìn)行碎片整理，OPTIMIZE語句有兩個可選的關(guān)鍵字：LOCAL和NO_WRITE_TO_BINLOG，默認(rèn)是每次碎片整理都會被記錄到BINlog二進(jìn)制日志中去，如果帶了關(guān)鍵字，就不會被記錄到日志中去。

ALTER看起來是執(zhí)行了一次空操作，重新設(shè)置了一遍數(shù)據(jù)庫引擎，同時會進(jìn)行碎片整理。

兩種操作在一定程度是等價的。

使用optimize結(jié)果

磁盤文件小了一半左右

alter不再演示

4.后記

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

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

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

參考博客：http://www.dhdzp.com/article/223238.htm --寫的很好，就是排版不舒服

http://www.dhdzp.com/article/243217.htm --寫的不錯

補充：如何優(yōu)化磁盤空間

1.優(yōu)化前必看注意事項

①.優(yōu)化表空間時，會造成鎖表

數(shù)據(jù)量越大的表，優(yōu)化耗時越長，百萬條數(shù)據(jù)大約耗時30s(約25000-30000行/秒,此數(shù)據(jù)根據(jù)機器磁盤性能會有差異)。所以，在磁盤優(yōu)化時，所有的增刪操作將受限，請選擇一個業(yè)務(wù)空檔期執(zhí)行。

②.間隔多久需要優(yōu)化一次磁盤碎片？

Mysql官方不建議頻繁進(jìn)行碎片整理，比如每天都整理磁盤?？捎^測一次優(yōu)化后，能撐多久才會產(chǎn)生比較大的碎片文件，然后根據(jù)這個周期，定制一個定期優(yōu)化碎片的任務(wù)。

如：每周或每月凌晨3點定時清理碎片。

2.如何優(yōu)化磁盤空間

判斷你的數(shù)據(jù)表的引擎是什么

①.如果引擎是MyISAM,則可以通過下面SQL優(yōu)化。

-- 優(yōu)化表空間
optimize table tableName;

注：如果引擎是InnoDB，執(zhí)行此SQL會提示：Table does not support optimize, doing recreate + analyze instead②.如果引擎是InnoDB,通過下列SQL可以代替optimize table xxx

-- 將表改為InnoDB數(shù)據(jù)庫引擎
ALTER TABLE tableName ENGINE=InnoDB;

-- 分析表，查看表狀態(tài)
ANALYZE TABLE tableName;

即通過重新將數(shù)據(jù)庫引擎設(shè)置為InnoDB的方式，來優(yōu)化磁盤空間。

到此這篇關(guān)于MySQL磁盤碎片整理的文章就介紹到這了,更多相關(guān)MySQL碎片整理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！