為什麼MySQL不建議delete刪除數據

計算機java編程

科技達人，優質創作者

罵歸罵，事情還是得解決，時候我分析原因發現，發現有些表的數據量增長很快，對應SQL掃描了很多無效數據，導致SQL慢了下來，通過確認之後，這些大表都是一些流水、記錄、日志類型數據，只需要保留1到3個月，此時需要對表做數據清理實現瘦身，一般都會想到用insert + delete的方式去清理。

這篇文章我會從InnoDB存儲空間分布，delete對性能的影響，以及優化建議方面解釋為什麼不建議delete刪除數據。

InnoDB存儲架構

從這張圖可以看到，InnoDB存儲結構主要包括兩部分：邏輯存儲結構和物理存儲結構。

邏輯上是由表空間tablespace —> 段segment或者inode —> 區Extent ——>數據頁Page構成，Innodb邏輯管理單位是segment，空間分配的最小單位是extent，每個segment都會從表空間FREE_PAGE中分配32個page，當這32個page不夠用時，會按照以下原則進行擴展：如果當前小於1個extent，則擴展到1個extent；當表空間小於32MB時，每次擴展一個extent；表空間大於32MB，每次擴展4個extent。

物理上主要由系統用戶數據文件，日志文件組成，數據文件主要存儲MySQL字典數據和用戶數據，日志文件記錄的是data page的變更記錄，用於MySQL Crash時的恢複。

Innodb表空間

InnoDB存儲包括三類表空間：系統表空間，用戶表空間，Undo表空間。

**系統表空間：**主要存儲MySQL內部的數據字典數據，如information_schema下的數據。

**用戶表空間：**當開啟innodb_file_per_table=1時，數據表從系統表空間獨立出來存儲在以table_name.ibd命令的數據文件中，結構信息存儲在table_name.frm文件中。

**Undo表空間：**存儲Undo信息，如快照一致讀和flashback都是利用undo信息。

從MySQL 8.0開始允許用戶自定義表空間，具體語法如下：

這樣的好處是可以做到數據的冷熱分離，分別用HDD和SSD來存儲，既能實現數據的高效訪問，又能節約成本，比如可以添加兩塊500G硬盤，經過創建卷組vg，劃分邏輯卷lv，創建數據目錄並mount相應的lv，假設劃分的兩個目錄分別是/hot_data 和 /cold_data。

這樣就可以將核心的業務表如用戶表，訂單表存儲在高性能SSD盤上，一些日志，流水表存儲在普通的HDD上，主要的操作步驟如下：

Inndob存儲分布

創建空表查看空間變化

設置參數innodb_file_per_table=1時，創建表時會自動創建一個segment，同時分配一個extent，包含32個data page的來存儲數據，這樣創建的空表默認大小就是96KB，extent使用完之後會申請64個連接頁，這樣對於一些小表，或者undo segment，可以在開始時申請較少的空間，節省磁盤容量的開銷。

插入數據後的空間變化

delete數據後的空間變化

MySQL內部不會真正刪除空間，而且做標記刪除，即將delflag:N修改為delflag:Y，commit之後會會被purge進入刪除鏈表，如果下一次insert更大的記錄，delete之後的空間不會被重用，如果插入的記錄小於等於delete的記錄空會被重用，這塊內容可以通過知數堂的innblock工具進行分析。

Innodb中的碎片

碎片的產生

我們知道數據存儲在文件系統上的，總是不能100%利用分配給它的物理空間，刪除數據會在頁面上留下一些”空洞”，或者隨機寫入（聚集索引非線性增加）會導致頁分裂，頁分裂導致頁面的利用空間少於50%，另外對表進行增刪改會引起對應的二級索引值的隨機的增刪改，也會導致索引結構中的數據頁面上留下一些"空洞"，雖然這些空洞有可能會被重複利用，但終究會導致部分物理空間未被使用，也就是碎片。

同時，即便是設置了填充因子為100%，Innodb也會主動留下page頁面1/16的空間作為預留使用（An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth）防止update帶來的行溢出。

其中data_free是分配了未使用的字節數，並不能說明完全是碎片空間。

碎片的回收

對於InnoDB的表，可以通過以下命令來回收碎片，釋放空間，這個是隨機讀IO操作，會比較耗時，也會阻塞表上正常的DML運行，同時需要占用額外更多的磁盤空間，對於RDS來說，可能會導致磁盤空間瞬間爆滿，實例瞬間被鎖定，應用無法做DML操作，所以禁止在線上環境去執行。

delete對SQL的影響

未刪除前的SQL執行情況

刪除後的SQL執行情況

結果統計分析

這也說明對普通的大表，想要通過delete數據來對表進行瘦身是不現實的，所以在任何時候不要用delete去刪除數據，應該使用優雅的標記刪除。

delete優化建議

控制業務賬號權限

對於一個大的系統來說，需要根據業務特點去拆分子系統，每個子系統可以看做是一個service，例如美團APP，上面有很多服務，核心的服務有用戶服務user-service，搜索服務search-service，商品product-service，位置服務location-service，價格服務price-service等。每個服務對應一個數據庫，為該數據庫創建單獨賬號，同時只授予DML權限且沒有delete權限，同時禁止跨庫訪問。

delete改為標記刪除

在MySQL數據庫建模規範中有4個公共字段，基本上每個表必須有的，同時在create_time列要創建索引，有兩方面的好處：

一些查詢業務場景都會有一個默認的時間段，比如7天或者一個月，都是通過create_time去過濾，走索引掃描更快。

一些核心的業務表需要以T +1的方式抽取數據倉庫中，比如每天晚上00:30抽取前一天的數據，都是通過create_time過濾的。

數據歸檔方式

通用數據歸檔方法

優化後的歸檔方式

這樣原表和歸檔表都是按月的分區表，只需要創建一個中間普通表，在業務低峰期做兩次分區交換，既可以刪除無效數據，又能回收空，而且沒有空間碎片，不會影響表上的索引及SQL的執行計劃。

總結

通過從InnoDB存儲空間分布，delete對性能的影響可以看到，delete物理刪除既不能釋放磁盤空間，而且會產生大量的碎片，導致索引頻繁分裂，影響SQL執行計劃的穩定性；

同時在碎片回收時，會耗用大量的CPU，磁盤空間，影響表上正常的DML操作。

在業務代碼層面，應該做邏輯標記刪除，避免物理刪除；為了實現數據歸檔需求，可以用采用MySQL分區表特性來實現，都是DDL操作，沒有碎片產生。

另外一個比較好的方案采用Clickhouse，對有生命周期的數據表可以使用Clickhouse存儲，利用其TTL特性實現無效數據自動清理。