MySQL碎片整理小節--例項演示

寫不完作業還要玩發表於2022-03-31
MySql

MYSQL之磁碟碎片整理

清澈，細流涓涓的愛

資料庫引擎以InnoDB為主

1.磁碟碎片是什麼

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

img

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

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

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

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

InnoDB後臺執行緒：https://www.cnblogs.com/abclife/p/5062008.html

解釋磁碟碎片的英文部落格：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.cnblogs.com/kerrycode/p/10943122.html --寫的很好，就是排版不舒服

https://www.cnblogs.com/lovebing/p/7463532.html --寫的不錯

相關文章