資料庫引擎以InnoDB為主
1。磁碟碎片是什麼
InnoDB表的資料儲存在頁中，每個頁可以存放多條記錄，這些記錄以樹形結構組織，這棵樹稱為B+樹。
聚簇索引的葉子結點包含行中所有欄位的值，輔助索引的葉子結點包含索引列和主鍵列。
在InnoDB中，刪除一些行，這些行只是被標記
已刪除
，而不會立即刪除，
個人認為和電腦的清除磁碟相同，之後可以透過覆蓋舊資料實現刪除
，InnDB的Purge執行緒會非同步的清理這些沒用的索引鍵和行。但是依然不會把這些釋放出來的空間還給作業系統重新使用，因此會導致頁面中存在很多空洞，如果表結構中包含動態長度欄位，這些空間甚至無法被InnoDB重新用來儲存新的行。
另外嚴重的問題是刪除資料會導致頁page中出現空白空間，大量隨機的Delete操作必然會在資料檔案中造成不連續的空白空間，當插入資料時，這些空白空間則會被利用起來，
造成了資料的物理儲存順序和邏輯的排序順序不同
，這就是資料碎片。
複製程式碼
—— 檢視全域性變數Purgeshow 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/oldoldcoder/p/16084412。html
