什麼？還在用delete刪除數據《死磕MySQL系列 九》
系列文章
六、五分鐘，讓你明白MySQL是怎麼選擇索引《死磕MySQL系列 六》
七、字元串可以這樣加索引，你知嗎？《死磕MySQL系列 七》
參與了好幾個項目開發，每個項目隨著業務量的增大，MySQL數據日益劇增，例如其中一個項目中得用戶足跡表，那是非常的瘋狂，只怪我大意了，沒有閃。
這篇文章我會從delete對性能的影響，以及如何以正確的姿勢來刪除數據。
在MySQL中Innodb存儲引擎的表存在兩部分，一部分是表結構，另一部分是表數據。
在MySQL8.0之前/var/lib/mysql下都會存在.frm文件，在MySQL8.0之後就不存在了。這是因為MySQL8.0中已經允許把表結構定義放到數據字典中了，是用參數innodb_file_per_table來決定的。
一、表空間
表空間分為幾種，系統表空間、用戶表空間、undo空間。
系統表空間：MySQL內部的數據字典，如information_schema庫下的數據。
用戶表空間：自己建立的表結構數據
undo空間：存儲Undo資訊，用於快速回滾。
MySQL8.0之前表結構是在系統表空間存儲的，在MySQL5.6.6後可以使用參數innodb_file_per_table來控制。
設置為off時，表數據是放在系統表空間中，也就是MySQL的數據字典放在一起。
設置為on時，innodb存儲引擎的表數據存儲在.idb文件中。
你知道表定義存儲在哪裡嗎？
來到死磕MySQL系列的專用資料庫kaka，新建一張表evt_sms。
猜一下創建的evt_sms表結構定義存儲在哪裡呢？
在information_schema庫裡邊的TABLES中，執行查詢SELECT TABLE_NAME,TABLE_COMMENT FROM TABLES WHERE TABLE_TYPE='BASE TABLE';
我們自定義的表類型是TABLE_TYPE。
說了這麼是為了解釋如果把innodb_file_per_table設置為off，則表數據也會存放在這裡。
問題：如果數據存在放共享表空間中，表刪除了，空間會刪除嗎？
答案是不會的。
參數innodb_file_per_table設置為on數據存儲在哪裡呢？
一般情況下是在var/lib/mysql中，會看到你創建的資料庫，進入到資料庫中就能看到一張表對應一個ibd文件。
數據就是存儲在這裡。
結論
在項目開始階段，切記將innodb_file_per_table設置為on，這是正確的做法。
二、數據刪除流程
現在你應該知道Innodb存儲引擎用的是B+樹數據結構，如下圖。
如果現在刪了主鍵ID為4的這條記錄，Innodb引擎會把ID為4的這條記錄標記為刪除，如果之後再插入ID為4的記錄，可能會復用這個位置，但磁碟文件大小並不會縮小。
隱式欄位
這裡就牽扯到了mvcc中的一個知識點，MVCC實現原理是由倆個隱式欄位、undo日誌、Read view來實現的。
上文說的標記刪除就是隱式欄位中的delete flag，即記錄被更新或刪除，這裡的刪除並不代表真的刪除，而是將這條記錄的delete flag改為true。
在MVCC：聽說有人好奇我的底層實現這篇文章中也給大家留下了一個伏筆，資料庫的刪除是真的刪除嗎？
問題：刪了一個數據頁的所有數據會怎麼樣
跟單條數據是一樣的，整個數據頁都是可以復用的。
記錄的復用是僅限於符合範圍條件的數據，例如上文刪除的ID為4這條記錄，如果在插入ID為4就會復用。
這裡需要給大家再聊一個新的知識點頁合併，若相鄰的兩個數據頁利用率都很低，系統就會把這兩個數據頁合併到一個頁上，另一個數據頁就會標記為可復用。
問題：使用delete把整個表的數據都刪除了會怎麼樣
答案是，所有的數據頁都會標記為可復用，但是磁碟文件大小是不會改變的。
三、實踐全表刪除表文件大小不改變
經過添加數據後表數據已經達到近100W了，文件大小已經達到108M。
擴展
這裡大家應該能看見stopped，就是執行命令ctrl + z來的，作用是開始我們在MySQL窗口裡邊，但不想退出MySQL窗口查看MySQL表文件大小，然後就可以執行這個命令結束任務。
查看完後可以在執行fg返回到MySQL窗口。
問題：Linux如何把文件單位顯示為M
假設剛剛直接執行ll命令查看文件，那麼就需要手動計算文件大小，很不方便。
執行ll -h命令則可以直觀的看到文件大小。
刪除數據查看磁碟文件是否縮小
為了直觀看大文件大小變化，咔咔直接把表裡邊的數據全部刪了，再看文件大小，還是108M。文件大小是沒有變化的。
四、如何正確的減少磁碟文件
在第三小節中，我們演示了刪除了100W數據後文件大小是沒有改變的，也就是空洞問題影響的，接下來就解決這種問題。
問題：空洞是如何產生的？
到了這裡都應該知道空洞是因為大量的增刪改造成的。
解決思路
你可以新建一個evt_sms_copy表，然後根據主鍵ID遞增的順序，把數據從evt_sms讀入evt_sms1中。
這樣就可以達到因為空洞造成的磁碟文件大小無法收縮問題。
問題：為什麼能解決呢？
因為evt_sms_copy是一張新的表，並且數據是以主鍵ID遞增的，索引是緊促的，數據頁利用率已經達到了最高峰狀態，這樣就起到了磁碟文件無法收縮問題。
上乾貨
直接執行alter table evt_sms engine = Innodb 命令來達到磁碟文件收縮。
這裡需要跟大家聊一下不同版本處理不同。
在MySQL5.5之前，這個命令做的事情跟我們解決思路是一樣的，不同的是evt_sms_copy是不用自己創建的。
在執行命令期間如有新增數據的話，會造成數據丟失，因為在MySQL5.5之前版本的DDL不是Online的。因此不能有數據的改動。
現在MySQL都已經更新到8版本了，如果你是新項目就直接用8版本，不要在用5.6以前的老版本了，咔咔在18年開始就已經在使用MySQL8.0版本了。
在鎖那一期文章中跟大家聊了MySQL5.6在DDL操作做了優化，引入了Online DDL。
優化後的執行流程
-
建立臨時文件tmp_file，把表的B+樹存儲到臨時文件中。若此時有對錶的操作，則會記錄在row log文件中。 -
把數據從原表全部刷到臨時文件後，此時臨時文件的數據就跟原表的數據一致。 -
最後用臨時文件替換表A的數據文件。
Online DDL的由來
可以看到在收縮磁碟文件時有數據更新會記錄在row log中，意思就是在收縮磁碟空間時是可以對錶進行增刪改查的。
注意點
在進行磁碟文件收縮的過程中，都會全表掃描原數據和新增臨時文件，如果你的表非常大，會非常消耗IO和CPU。
因此，你要安全的做這個操作，可以使用開源的gh-ost來進行。
結論
當你想收縮因為大量增刪改查而導致表磁碟文件非常大時就可以執行alter table evt_sms engine=Innodb命令來達到收縮表空間的目的。
五、實踐是檢驗認識是否具有真理性的唯一標準
都應該知道實踐是檢驗認識是否具有真理性的唯一標準，那麼接下里就對本文提出的結論進行實際操作一下。
-
先執行 ctrl + z結束MySQL任務窗口 -
執行 ll -h查看此時表evt_sms磁碟文件大小為108M -
執行 fg返回到MySQL任務窗口 -
執行命令 alter table evt_sms engine=Innodb -
再執行 ctrl + z，執行ll -h查看磁碟文件大小已經到了128k。
上圖即是咔咔操作的全過程，得到的結論就是執行命令alter table ect_sms engine = Innodb可以收縮由於大量增刪改查的表引發的空洞問題。最終達到收縮表空間目的。
六、開發建議
刪除數據不要使用delete，而是使用軟刪除，做一個標記刪除即可。
這樣既不會出現空洞問題，也方便數據溯源。
每張表必備三個欄位create_time、update_time、delete_time。
七、總結
通過本期文章我們需要知道以下幾點。
-
通過大量增刪改查的表會出現空洞 -
幹掉空洞需要執行alter table evt_sms engine=Innodb來解決 -
使用delete刪除數據只會做一個標記處理，並不會真正刪除空間 -
本文所有的結論都基於innodb_file_per_table = on
「
堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的互聯網上能給你帶來一點幫助，我是咔咔，下期見。
」
