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

1.磁盤(pán)碎片是什么

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

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

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

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

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

InnoDB后臺(tái)線程：http://chabaoo.cn/article/243211.htm

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

上面這是個(gè)大能的博客，寫(xiě)的pretty good！

2.實(shí)驗(yàn)

我們首先創(chuàng)建一個(gè)具有一百條數(shù)據(jù)的表來(lái)進(jìn)行實(shí)驗(yà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;
-- 嘗試插入一百萬(wàn)條數(shù)據(jù)
 
call insertt()
 
-- mysql版本5.7.36
> OK
> 時(shí)間: 838.706s

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

?DB：information_scheme中存放我們表的信息，通過(guò)下列命令來(lái)查看我們的磁盤(pán)碎片最大的前五名

-- 別人的博客中copy的，我的豬腦寫(xiě)不出來(lái)
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)下運(yùn)行查看.Ibd文件的結(jié)構(gòu)，將B+tree以及磁盤(pán)使用暴露出來(lái)，但是我不會(huì)用，這里帶上他的github鏈接:https://github.com/akopytov/sysbench

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

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

文件大?。?/p>

刪除后，磁盤(pán)文件的大小并沒(méi)有變化，因?yàn)閯h除產(chǎn)生了磁盤(pán)碎片，空白page殘留在文件中，被刪除的數(shù)據(jù)記錄仍然被保持在MySQL的鏈接清單中，因此數(shù)據(jù)存儲(chǔ)文件的大小并不會(huì)隨著數(shù)據(jù)的刪除而減小，我們?cè)俅问褂蒙鲜霾僮鞑榭磀ata_free。

可以看到temp表的data_free增長(zhǎng)了。

3.對(duì)于碎片回收操作

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

OPTIMIZE TABLE

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

ALTER

ALTER TABLE table_name ENGINE = Innodb

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

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

兩種操作在一定程度是等價(jià)的。

使用optimize結(jié)果

磁盤(pán)文件小了一半左右

alter不再演示

4.后記

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。

參考博客：http://chabaoo.cn/article/223238.htm --寫(xiě)的很好，就是排版不舒服

http://chabaoo.cn/article/243217.htm --寫(xiě)的不錯(cuò)

補(bǔ)充：如何優(yōu)化磁盤(pán)空間

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

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

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

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

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

如：每周或每月凌晨3點(diǎn)定時(shí)清理碎片。

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

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

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

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

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

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

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

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

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

最新評(píng)論