MySQL碎片整理小節-實例演示 - ⎝⎛CodingNote.cc ⎞⎠

MySQL碎片整理小節–實例演示

2022 年 3 月 31 日
筆記
JAVA, MySQL, 大二學習的MySQL, 調優

MYSQL之磁盤碎片整理

清澈，細流涓涓的愛

數據庫引擎以InnoDB為主

1.磁盤碎片是什麼

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

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

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

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

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

InnoDB後台線程：//www.cnblogs.com/abclife/p/5062008.html

解釋磁盤碎片的英文博客：//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鏈接://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。

參考博客：//www.cnblogs.com/kerrycode/p/10943122.html –寫的很好，就是排版不舒服

//www.cnblogs.com/lovebing/p/7463532.html –寫的不錯

Tags: JAVA MySQL 大二學習的MySQL 調優