MySQL磁碟碎片整理範例演示

2022-04-02 13:02:24

1.磁碟碎片是什麼

InnoDB表的資料儲存在頁中，每個頁可以存放多條記錄，這些記錄以樹形結構組織，這棵樹稱為B+樹。

聚簇索引的葉子結點包含行中所有欄位的值，輔助索引的葉子結點包含索引列和主鍵列。

在InnoDB中，刪除一些行，這些行只是被標記已刪除，而不會立即刪除，個人認為和電腦的清除磁碟相同，之後可以通過覆蓋舊資料實現刪除，InnDB的Purge執行緒會非同步的清理這些沒用的索引鍵和行。但是依然不會把這些釋放出來的空間還給作業系統重新使用，因此會導致頁面中存在很多空洞，如果表結構中包含動態長度欄位，這些空間甚至無法被InnoDB重新用來儲存新的行。

另外嚴重的問題是刪除資料會導致頁page中出現空白空間，大量隨機的Delete操作必然會在資料檔案中造成不連續的空白空間，當插入資料時，這些空白空間則會被利用起來，造成了資料的物理儲存順序和邏輯的排序順序不同，這就是資料碎片。

-- 檢視全域性變數Purge
show variables like 'innodb_purge_threads';

InnoDB後臺執行緒：https://www.jb51.net/article/243211.htm

解釋磁碟碎片的英文部落格：https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/

上面這是個大能的部落格，寫的pretty good！

2.實驗

我們首先建立一個具有一百條資料的表來進行實驗：

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;
-- 嘗試插入一百萬條資料
 
call insertt()
 
-- mysql版本5.7.36
> OK
> 時間: 838.706s

建立後的磁碟儲存大小：

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系統下執行檢視.Ibd檔案的結構，將B+tree以及磁碟使用暴露出來，但是我不會用，這裡帶上他的github連結:https://github.com/akopytov/sysbench

下面我們執行刪除操作：

-- 刪除前五十萬條資料
delete from temp order by id LIMIT 500000

檔案大小：

刪除後，磁碟檔案的大小並沒有變化，因為刪除產生了磁碟碎片，空白page殘留在檔案中，被刪除的資料記錄仍然被保持在MySQL的連結清單中，因此資料儲存檔案的大小並不會隨著資料的刪除而減小，我們再次使用上述操作檢視data_free。

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

3.對於碎片回收操作

對MySQL進行碎片整理有兩種方法：

OPTIMIZE TABLE

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

ALTER

ALTER TABLE table_name ENGINE = Innodb

OPTIMIZE可以同時對多個表格進行碎片整理，OPTIMIZE語句有兩個可選的關鍵字：LOCAL和NO_WRITE_TO_BINLOG，預設是每次碎片整理都會被記錄到BINlog二進位制紀錄檔中去，如果帶了關鍵字，就不會被記錄到紀錄檔中去。

ALTER看起來是執行了一次空操作，重新設定了一遍資料庫引擎，同時會進行碎片整理。

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

使用optimize結果

磁碟檔案小了一半左右

alter不再演示

4.後記

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。

參考部落格：https://www.jb51.net/article/223238.htm --寫的很好，就是排版不舒服

https://www.jb51.net/article/243217.htm --寫的不錯

補充：如何優化磁碟空間

1.優化前必看注意事項

①.優化表空間時，會造成鎖表

資料量越大的表，優化耗時越長，百萬條資料大約耗時30s(約25000-30000行/秒,此資料根據機器磁碟效能會有差異)。所以，在磁碟優化時，所有的增刪操作將受限，請選擇一個業務空檔期執行。

②.間隔多久需要優化一次磁碟碎片？

Mysql官方不建議頻繁進行碎片整理，比如每天都整理磁碟。可觀測一次優化後，能撐多久才會產生比較大的碎片檔案，然後根據這個週期，客製化一個定期優化碎片的任務。

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

2.如何優化磁碟空間

判斷你的資料表的引擎是什麼

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

-- 優化表空間
optimize table tableName;

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

-- 將表改為InnoDB資料庫引擎
ALTER TABLE tableName ENGINE=InnoDB;

-- 分析表，檢視表狀態
ANALYZE TABLE tableName;

即通過重新將資料庫引擎設定為InnoDB的方式，來優化磁碟空間。

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