More  

小編的世界 優質文選 資料

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


2021年1月12日 - 資料小編  
   

計算機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特性實現無效數據自動清理。

  大家在看