MySQL刪除數據後空間沒變小

一、數據刪除的機制

在MySQL中，刪除數據一般是通過delete或truncate語句實現的。兩者的執行依據是不同的，導致在刪除之後空間佔用的變化也不同。

1. delete語句

delete語句是通過行級別刪除數據，刪除的行從磁碟中被完全刪除，但是磁碟空間會變成碎片。另外，如果一個表中有很多的刪除操作，那麼空間碎片會越來越多，導致查詢效率變慢。空間碎片的具體表現是磁碟上的空閑空間逐漸變小。

2. truncate語句

truncate語句是通過整體表重建，刪除數據之後，MySQL會將整個表的空間全部釋放掉，回到空表狀態。這種方式空間利用率高，速度也非常快。但是，如果表涉及到外鍵等約束，不能使用truncate語句，只能使用delete語句。

二、刪除數據後磁碟空間沒變小的原因

當我們使用delete或truncate語句刪除數據之後，我們期望的是磁碟空間能夠得到釋放，但實際情況是磁碟空間並沒有發生變化。這是由於MySQL的設計機制導致的。

1. Innodb存儲引擎

Innodb存儲引擎是一種高性能的存儲引擎，在刪除數據之後並不會立即在磁碟上刪除相關的數據文件，而是將這些文件標記為可復用的空間。這樣做的好處是可以提高刪除數據的效率，減少磁碟I/O次數。但是，這樣也是導致磁碟空間並沒有實時釋放的原因之一。

2. 磁碟空間再利用

MySQL認為，在將來可能還有數據需要寫入到磁碟中，為了避免頻繁的分配釋放磁碟空間，MySQL會將刪除的空間作為可再利用的空間保留下來。

三、如何釋放刪除後的磁碟空間

雖然MySQL不會自動釋放刪除後的磁碟空間，但是我們也可以手動進行釋放。

1. 重建表

使用truncate語句或者一次性刪除大量數據之後，可以嘗試重建整個表，這樣可以釋放磁碟空間。重建表的操作可以通過create table as select語句實現，該語句可以複製數據，同時重構表結構。

--將表重新命名，再進行create table操作
ALTER TABLE mytable RENAME TO old_mytable;
CREATE TABLE mytable AS SELECT * FROM old_mytable;
DROP TABLE old_mytable;

2. OPTIMIZE TABLE命令

除了重建表之外，MySQL還提供了一個optimize table命令，可以通過該命令來釋放刪除後空間。該命令會執行以下操作：

• 為每個索引重新構建B-tree，使其更加緊湊
• 將數據文件碎片整理成連續的塊
• 釋放沒有使用的空間

OPTIMIZE TABLE mytable;

3. 調整innodb_file_per_table參數

Innodb存儲引擎有一個配置參數innodb_file_per_table，設置該參數為ON之後，每個Innodb表使用一個單獨的表空間進行管理。這樣，在刪除數據之後，Innodb存儲引擎會自動回收相應的表空間，釋放磁碟空間。

--修改my.cnf文件
innodb_file_per_table = ON

四、小結

MySQL在刪除數據後，是通過標記刪除的方式來進行優化，所以，磁碟空間不會立即釋放。為了釋放刪除後的磁碟空間，我們可以使用重建表、optimize table命令、調整innodb_file_per_table參數等方式。需要注意的是，對於有刪除操作的表，我們應該盡量避免頻繁的刪除操作，以免導致空間碎片太多，影響查詢效率。