系列文章
六、五分鐘，讓你明白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
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。
”