mysql怎么把表中的信息全部刪完（面試官mysql表刪除一半數(shù)據(jù)）

發(fā)布時(shí)間：2024-01-24閱讀(13)

導(dǎo)讀這期面試官提的問題是：MySQL表刪除一半數(shù)據(jù)，表空間是否會(huì)變小？為什么？我：你這么問，肯定是不會(huì)？但是我不知道為什么（理直氣壯.jpg）國際慣例先上思維導(dǎo)....

這期面試官提的問題是：

MySQL 表刪除一半數(shù)據(jù)，表空間是否會(huì)變小？為什么？
我：
你這么問，肯定是不會(huì)？但是我不知道為什么（理直氣壯.jpg）
國際慣例先上思維導(dǎo)圖：
遇到這種問題先做一波實(shí)驗(yàn)，我的思路驗(yàn)證下是否會(huì)刪除。聲明：此次實(shí)驗(yàn)采用的 MySQL 版本是 5.7，引擎是 InnDB
往期精彩
MySQL 查詢語句是怎么執(zhí)行的？
MySQL 索引
MySQL 日志
MySQL 事務(wù)與 MVCC
MySQL 的鎖機(jī)制
MySQL 字符串怎么設(shè)計(jì)索引？
面試官：數(shù)據(jù)庫自增 ID 用完了會(huì)咋樣？
面試官：order by 怎么優(yōu)化？
面試官：count (*) 怎么優(yōu)化？
面試官：explain 應(yīng)該關(guān)注哪些指標(biāo)？
01 做個(gè)實(shí)驗(yàn)
首先整一張表結(jié)構(gòu)：訂單表 order，主鍵是 id，另外還有一個(gè)索引 index_city 用 city 字段建索引。
CREATETABLE`order`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT主鍵,`user_code`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT用戶編號(hào),`goods_name`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULLCOMMENT商品名稱,`order_date`timestamp(0)NULLDEFAULTCURRENT_TIMESTAMPCOMMENT下單時(shí)間,`city`varchar(64)CHARACTERSETutf8COLLATEutf8_general_ciNULLDEFAULTNULLCOMMENT下單城市,`order_num`int(10)NOTNULLCOMMENT訂單號(hào)數(shù)量,PRIMARYKEY(`id`)USINGBTREE,INDEX`city_index`(`city`)USINGBTREE)ENGINE=innodbAUTO_INCREMENT=2000002CHARACTERSET=utf8COLLATE=utf8_general_ciCOMMENT=商品訂單表ROW_FORMAT=Compact;
1.1 插入數(shù)據(jù)
造點(diǎn)數(shù)據(jù)，為了效果。我直接造 200W 條數(shù)據(jù)，然后直接 delete 刪掉一半。
//第一步：創(chuàng)建函數(shù)delimiter//DROPPROCEDUREIFEXISTSproc_buildata;CREATEPROCEDUREproc_buildata(INloop_timesINT)BEGINDECLAREvarINTDEFAULT0;WHILEvar<loop_timesDOSETvar=var 1;INSERTINTO`order`(`id`,`user_code`,`goods_name`,`order_date`,`city`,`order_num`)VALUES(var,var 1,有線耳機(jī),2021-06-2016:46:00,杭州,1);ENDWHILE;END//delimiter;//第二步：調(diào)用上面生成的函數(shù)，即可插入數(shù)據(jù)CALLproc_buildata(2000000);
插入完成，耗時(shí)賊久。建議批量插入：
插入完成，到 MySQL 查看文件大小對應(yīng)文件大小（下圖中的 .idb 文件）
200W 數(shù)據(jù)大概是 184M 左右的大小：
1.1.1 一些小知識(shí)
1、一個(gè) InnoDB 表包含表結(jié)構(gòu)定義和數(shù)據(jù)兩部分，在 MySQL 8.0 版本以前，表結(jié)構(gòu)是存在于 .frm 為后綴的文件里。而 MySQL 8.0 版本，則已經(jīng)允許把表結(jié)構(gòu)定義放在系統(tǒng)數(shù)據(jù)表中了
2、表數(shù)據(jù)既可以存在共享表空間里，也可以是單獨(dú)的文件。由參數(shù) InnoDB_file_per_table 控制。MySQL 5.6.6 版本之后，默認(rèn)是 ON，也即每個(gè) InnoDB 表數(shù)據(jù)以及索引存儲(chǔ)在一個(gè)以 .ibd 為后綴的文件中。
3、為方便管理建議你設(shè)置為 ON，因?yàn)楫?dāng)你不需要這個(gè)表時(shí)，通過 drop table 命令，系統(tǒng)直接刪除這個(gè)文件。而如果放在共享表空間中，即使表刪掉了，空間也是不會(huì)回收的。
4、由于表結(jié)構(gòu)文件一半很小，本文討論的表空間是指表數(shù)據(jù)文件 .ibd 的變化。
1.2 刪除數(shù)據(jù)
批量刪除其中的 100W 的數(shù)據(jù)，此時(shí)的總數(shù)據(jù)量：
再次查看 order.ibd 文件的大小，還是 184M。也就是說 MySQL 表刪除一半數(shù)據(jù)之后，表空間并沒有隨之減小，好特么奇怪呀。
這是為啥呢？這就得說說 MySQL 刪除數(shù)據(jù)的流程了
02 刪除數(shù)據(jù)流程
還記得我之前講的索引原理么？不清楚的朋友們，請看以下這篇文章，看看 InnDB 索引是怎么組織數(shù)據(jù)的。不然你是看不懂下面的過程的。
MySQL 索引原理
InnoDB 里的數(shù)據(jù)都是用 B 樹的結(jié)構(gòu)組織的，假設(shè)現(xiàn)在我們表里的數(shù)據(jù)長這樣：
我刪除 id = 10 的這行數(shù)據(jù)，MySQL 實(shí)際上只是把這行數(shù)據(jù)標(biāo)記為已刪除，并不會(huì)回收表空間，而是給后來的數(shù)據(jù)復(fù)用。
那怎么復(fù)用呢？總得有規(guī)則吧？如果這時(shí)客戶端申請插入的是 id 在 (8，18) 范圍內(nèi)的數(shù)據(jù)，此時(shí) id = 10 的位置就會(huì)被復(fù)用。比如我插入 id=11 的記錄就會(huì)復(fù)用 id=10 的空間。但如果插入的是 id = 20 的數(shù)據(jù)就沒法復(fù)用這個(gè)空間了。
2.1 整頁刪除
InnoDB 的數(shù)據(jù)是按頁存儲(chǔ)的，如果刪掉了一個(gè)數(shù)據(jù)頁上的所有記錄，會(huì)怎么樣？那就是這個(gè)頁的所有數(shù)據(jù)都能被復(fù)用。
但是數(shù)據(jù)頁的復(fù)用跟記錄的復(fù)用是不同，記錄的復(fù)用有限定范圍，而數(shù)據(jù)頁的復(fù)用并沒有限制。舉例：如果我現(xiàn)在把 P2 整頁數(shù)據(jù)刪除，那么限制我要插入 id = 50 的數(shù)據(jù)也是可以被復(fù)用，當(dāng)然這時(shí)候 P2 頁的范圍就不再是 id (8，19) 了。
2.2 什么是數(shù)據(jù) "空洞"？
如果相鄰的兩個(gè)數(shù)據(jù)頁利用率都很小，MySQL 會(huì)把這兩個(gè)頁的數(shù)據(jù)合到其中一個(gè)頁，另外一個(gè)被標(biāo)記為可復(fù)用。
當(dāng)然，如果用 delete 刪除整個(gè)表數(shù)據(jù)的結(jié)果就是：所有的數(shù)據(jù)頁都會(huì)被標(biāo)記為可復(fù)用。但是磁盤上，文件不會(huì)變小。
所以，delete 命令其實(shí)只是把記錄的位置，或者數(shù)據(jù)頁標(biāo)記為了可復(fù)用，但磁盤文件的大小是不會(huì)變的。也就是說，通過 delete 命令是不能回收表空間的。
這些可以復(fù)用，而沒有被使用的空間，被稱為空洞。
03 新增數(shù)據(jù)
不止是刪除數(shù)據(jù)會(huì)造成空洞，插入數(shù)據(jù)也會(huì)
如果數(shù)據(jù)是隨機(jī)插入，非主鍵自增的，就可能造成索引的數(shù)據(jù)頁分裂。
下圖中，假設(shè)數(shù)據(jù)頁 P2 已滿，這時(shí)再插入 id=16 的記錄，就需要申請一個(gè)新的 P3 頁來存儲(chǔ)數(shù)據(jù)。等到頁分裂完成后，P2 的末尾就留下了空洞（PS：實(shí)際上，可能不止 1 個(gè)記錄的位置是空洞）。
但是如果數(shù)據(jù)是按照索引遞增順序插入的，索引就是緊湊的，就不會(huì)有頁分裂這回事。這也是為什么數(shù)據(jù)庫要設(shè)置自增 ID 的主要原因
04 修改數(shù)據(jù)
不僅是插入數(shù)據(jù)，更新數(shù)據(jù)也會(huì)造成空洞。很多人可能不理解這個(gè)過程，更新數(shù)據(jù)主鍵都沒變怎么會(huì)造成數(shù)據(jù)空洞呢？實(shí)際上更新索引上的值，可以理解為刪除一個(gè)舊的值，再插入一個(gè)新值。
比如，我把 id = 10 的城市從北京改成東京，就會(huì)造成空洞。
你可能會(huì)說不對啊，上圖中 id 都沒變怎么會(huì)數(shù)據(jù)空洞呢？實(shí)際上文章開頭就說了，city 這個(gè)字段是二級(jí)索引，索引 index_city 的值從北京變成南京，北京的索引數(shù)據(jù)會(huì)標(biāo)記為刪除，然后重新建立南京的索引數(shù)據(jù)，一刪一增的過程就產(chǎn)生了空洞。
總結(jié)一句：更新過程中如果有索引更新了，就會(huì)造成數(shù)據(jù)空洞。也就是二級(jí)索引樹更新造成的數(shù)據(jù)空洞
05 重建表，回收空間
從上面的結(jié)論你也知道了，大量的增刪改確實(shí)會(huì)造成空洞的。如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。而重建表就能做到。具體怎么做呢？
那 order 表舉例，可以新建一個(gè)臨時(shí)表 order_tmp，它的表數(shù)據(jù)結(jié)構(gòu)與 order 完全相同。然后按 id 從小到大的順序把數(shù)據(jù)從 order 表讀出來插入到 order_tmp 表。
此時(shí)，由于 order_tmp 并沒有數(shù)據(jù)空洞，所以它的主鍵索引更緊湊，數(shù)據(jù)頁利用率更高。等到遷移完成，可以用 order_tmp 表替代 order 表，從而收縮 order 表的空間。
以上描述的一系列操作，是不是覺得超級(jí)麻煩？貼心的 MySQL 在 5.5 版本之前，提供了以下命令來重建表，回收空間。
altertableorderengine=InnoDB
執(zhí)行它，臨時(shí)表 order_tmp 不需要你自己創(chuàng)建，MySQL 會(huì)自動(dòng)完成轉(zhuǎn)存數(shù)據(jù)、交換表名、刪除舊表的操作。
我畫個(gè)流程圖，幫助大家理解下：
看到這里你可能覺得完美解決了空洞問題，其實(shí)不然，這個(gè)方案最大的缺點(diǎn)就是：表重構(gòu)過程中，往臨時(shí)表插入數(shù)據(jù)是很耗時(shí)的；如果有新的數(shù)據(jù)寫入 order 時(shí)，不會(huì)被遷移，會(huì)造成數(shù)據(jù)丟失。
5.2 Online DDL
那咋辦呢？MySQL 5.6 版本開始引入的 Online DDL，解決了這個(gè)問題。引入了 Online DDL 之后，重建表的流程是這樣的：
建立一個(gè)臨時(shí)文件，掃描表 order 主鍵的所有數(shù)據(jù)頁；
用數(shù)據(jù)頁中表 order 的記錄生成 B 樹，存儲(chǔ)到臨時(shí)文件中；
生成臨時(shí)文件的過程中，將所有對 order 的操作記錄在一個(gè)日志文件（row log）中，對應(yīng)的是圖中 state2 的狀態(tài)；
臨時(shí)文件生成后，將日志文件中的操作應(yīng)用到臨時(shí)文件，得到一個(gè)邏輯數(shù)據(jù)上與表 order 相同的數(shù)據(jù)文件，對應(yīng)的就是圖中 state3 的狀態(tài)；
用臨時(shí)文件替換表 order 的數(shù)據(jù)文件。
上圖，方便你們理解：
由于日志文件記錄和重放操作這個(gè)功能的存在，這個(gè)方案在重建表的過程中，允許對表 A 做增刪改操作。
06 總結(jié)
這篇文章我們聊了 MySQL 中大量的增刪改都有可能造成數(shù)據(jù)空洞、數(shù)據(jù)庫中收縮表空間的方法。其中 delete 命令是不會(huì)回收表空間的，還要通過 alter table 命令重建表，才能達(dá)到表文件變小的目的。
這個(gè)命令在 5.6 版本以及之后可以考慮在業(yè)務(wù)低峰期使用的，但在 5.5 及之前的版本，這個(gè)命令是會(huì)阻塞 DML 的，建議你慎重。
另外，重建表都會(huì)掃描原表數(shù)據(jù)和構(gòu)建臨時(shí)文件。對于大表來說，這個(gè)操作是很消耗 IO 和 CPU 的。因此，如果是線上服務(wù)你要很小心地控制操作時(shí)間。如果想要比較安全的操作的話，推薦使用 GitHub 開源的 gh-ost 來做。
6.1 參考
https://time.geekbang.org/column/article/73479
https://mp.weixin.qq.com/s/B0frdGgUciYckRNfRkcRvw
原文鏈接：https://mp.weixin.qq.com/s/7LIyobRjrIpGJ3J6BMEcxA