什么？還在用delete刪除數(shù)據(jù)《死磕MySQL系列 九》

別再用delete刪除數(shù)據(jù)

系列文章

一、表空間

二、數(shù)據(jù)刪除流程

三、實(shí)踐全表刪除表文件大小不改變

四、如何正確的減少磁盤文件

五、實(shí)踐是檢驗(yàn)認(rèn)識(shí)是否具有真理性的唯一標(biāo)準(zhǔn)

六、開發(fā)建議

七、總結(jié)

系列文章

五、如何選擇普通索引和唯一索引《死磕MySQL系列 五》

六、五分鐘，讓你明白MySQL是怎么選擇索引《死磕MySQL系列 六》

七、字符串可以這樣加索引，你知嗎？《死磕MySQL系列 七》

八、無(wú)法復(fù)現(xiàn)的“慢”SQL《死磕MySQL系列 八》

參與了好幾個(gè)項(xiàng)目開發(fā)，每個(gè)項(xiàng)目隨著業(yè)務(wù)量的增大，MySQL數(shù)據(jù)日益劇增，例如其中一個(gè)項(xiàng)目中得用戶足跡表，那是非常的瘋狂，只怪我大意了，沒(méi)有閃。

這篇文章我會(huì)從delete對(duì)性能的影響，以及如何以正確的姿勢(shì)來(lái)刪除數(shù)據(jù)。

在MySQL中Innodb存儲(chǔ)引擎的表存在兩部分，一部分是表結(jié)構(gòu)，另一部分是表數(shù)據(jù)。

在MySQL8.0之前/var/lib/mysql下都會(huì)存在.frm文件，在MySQL8.0之后就不存在了。這是因?yàn)镸ySQL8.0中已經(jīng)允許把表結(jié)構(gòu)定義放到數(shù)據(jù)字典中了，是用參數(shù)innodb_file_per_table來(lái)決定的。

一、表空間

表空間分為幾種，系統(tǒng)表空間、用戶表空間、undo空間。

系統(tǒng)表空間：MySQL內(nèi)部的數(shù)據(jù)字典，如information_schema庫(kù)下的數(shù)據(jù)。

用戶表空間：自己建立的表結(jié)構(gòu)數(shù)據(jù)

undo空間：存儲(chǔ)Undo信息，用于快速回滾。

MySQL8.0之前表結(jié)構(gòu)是在系統(tǒng)表空間存儲(chǔ)的，在MySQL5.6.6后可以使用參數(shù)innodb_file_per_table來(lái)控制。

設(shè)置為off時(shí)，表數(shù)據(jù)是放在系統(tǒng)表空間中，也就是MySQL的數(shù)據(jù)字典放在一起。

設(shè)置為on時(shí)，innodb存儲(chǔ)引擎的表數(shù)據(jù)存儲(chǔ)在.idb文件中。

你知道表定義存儲(chǔ)在哪里嗎？

來(lái)到死磕MySQL系列的專用數(shù)據(jù)庫(kù)kaka，新建一張表evt_sms。

猜一下創(chuàng)建的evt_sms表結(jié)構(gòu)定義存儲(chǔ)在哪里呢？

在information_schema庫(kù)里邊的TABLES中，執(zhí)行查詢SELECT TABLE_NAME,TABLE_COMMENT FROM TABLES WHERE TABLE_TYPE='BASE TABLE';

我們自定義的表類型是TABLE_TYPE。

說(shuō)了這么是為了解釋如果把innodb_file_per_table設(shè)置為off，則表數(shù)據(jù)也會(huì)存放在這里。

問(wèn)題：如果數(shù)據(jù)存在放共享表空間中，表刪除了，空間會(huì)刪除嗎？

答案是不會(huì)的。

參數(shù)innodb_file_per_table設(shè)置為on數(shù)據(jù)存儲(chǔ)在哪里呢？

一般情況下是在var/lib/mysql中，會(huì)看到你創(chuàng)建的數(shù)據(jù)庫(kù)，進(jìn)入到數(shù)據(jù)庫(kù)中就能看到一張表對(duì)應(yīng)一個(gè)ibd文件。

數(shù)據(jù)就是存儲(chǔ)在這里。

結(jié)論

在項(xiàng)目開始階段，切記將innodb_file_per_table設(shè)置為on，這是正確的做法。

二、數(shù)據(jù)刪除流程

現(xiàn)在你應(yīng)該知道Innodb存儲(chǔ)引擎用的是B+樹數(shù)據(jù)結(jié)構(gòu)，如下圖。

如果現(xiàn)在刪了主鍵ID為4的這條記錄，Innodb引擎會(huì)把ID為4的這條記錄標(biāo)記為刪除，如果之后再插入ID為4的記錄，可能會(huì)復(fù)用這個(gè)位置，但磁盤文件大小并不會(huì)縮小。

隱式字段

這里就牽扯到了mvcc中的一個(gè)知識(shí)點(diǎn)，MVCC實(shí)現(xiàn)原理是由倆個(gè)隱式字段、undo日志、Read view來(lái)實(shí)現(xiàn)的。

上文說(shuō)的標(biāo)記刪除就是隱式字段中的delete flag，即記錄被更新或刪除，這里的刪除并不代表真的刪除，而是將這條記錄的delete flag改為true。

在MVCC：聽說(shuō)有人好奇我的底層實(shí)現(xiàn)這篇文章中也給大家留下了一個(gè)伏筆，數(shù)據(jù)庫(kù)的刪除是真的刪除嗎？

問(wèn)題：刪了一個(gè)數(shù)據(jù)頁(yè)的所有數(shù)據(jù)會(huì)怎么樣

跟單條數(shù)據(jù)是一樣的，整個(gè)數(shù)據(jù)頁(yè)都是可以復(fù)用的。

記錄的復(fù)用是僅限于符合范圍條件的數(shù)據(jù)，例如上文刪除的ID為4這條記錄，如果在插入ID為4就會(huì)復(fù)用。

這里需要給大家再聊一個(gè)新的知識(shí)點(diǎn)頁(yè)合并，若相鄰的兩個(gè)數(shù)據(jù)頁(yè)利用率都很低，系統(tǒng)就會(huì)把這兩個(gè)數(shù)據(jù)頁(yè)合并到一個(gè)頁(yè)上，另一個(gè)數(shù)據(jù)頁(yè)就會(huì)標(biāo)記為可復(fù)用。

問(wèn)題：使用delete把整個(gè)表的數(shù)據(jù)都刪除了會(huì)怎么樣

答案是，所有的數(shù)據(jù)頁(yè)都會(huì)標(biāo)記為可復(fù)用，但是磁盤文件大小是不會(huì)改變的。

三、實(shí)踐全表刪除表文件大小不改變

經(jīng)過(guò)添加數(shù)據(jù)后表數(shù)據(jù)已經(jīng)達(dá)到近100W了，文件大小已經(jīng)達(dá)到108M。

擴(kuò)展

這里大家應(yīng)該能看見stopped，就是執(zhí)行命令ctrl + z來(lái)的，作用是開始我們?cè)贛ySQL窗口里邊，但不想退出MySQL窗口查看MySQL表文件大小，然后就可以執(zhí)行這個(gè)命令結(jié)束任務(wù)。

查看完后可以在執(zhí)行fg返回到MySQL窗口。

問(wèn)題：Linux如何把文件單位顯示為M

假設(shè)剛剛直接執(zhí)行l(wèi)l命令查看文件，那么就需要手動(dòng)計(jì)算文件大小，很不方便。

執(zhí)行l(wèi)l -h命令則可以直觀的看到文件大小。

刪除數(shù)據(jù)查看磁盤文件是否縮小

為了直觀看大文件大小變化，咔咔直接把表里邊的數(shù)據(jù)全部刪了，再看文件大小，還是108M。文件大小是沒(méi)有變化的。

四、如何正確的減少磁盤文件

在第三小節(jié)中，我們演示了刪除了100W數(shù)據(jù)后文件大小是沒(méi)有改變的，也就是空洞問(wèn)題影響的，接下來(lái)就解決這種問(wèn)題。

問(wèn)題：空洞是如何產(chǎn)生的？

到了這里都應(yīng)該知道空洞是因?yàn)榇罅康脑鰟h改造成的。

解決思路

你可以新建一個(gè)evt_sms_copy表，然后根據(jù)主鍵ID遞增的順序，把數(shù)據(jù)從evt_sms讀入evt_sms1中。

這樣就可以達(dá)到因?yàn)榭斩丛斐傻拇疟P文件大小無(wú)法收縮問(wèn)題。

問(wèn)題：為什么能解決呢？

因?yàn)閑vt_sms_copy是一張新的表，并且數(shù)據(jù)是以主鍵ID遞增的，索引是緊促的，數(shù)據(jù)頁(yè)利用率已經(jīng)達(dá)到了最高峰狀態(tài)，這樣就起到了磁盤文件無(wú)法收縮問(wèn)題。

上干貨

直接執(zhí)行alter table evt_sms engine = Innodb 命令來(lái)達(dá)到磁盤文件收縮。

這里需要跟大家聊一下不同版本處理不同。

在MySQL5.5之前，這個(gè)命令做的事情跟我們解決思路是一樣的，不同的是evt_sms_copy是不用自己創(chuàng)建的。

在執(zhí)行命令期間如有新增數(shù)據(jù)的話，會(huì)造成數(shù)據(jù)丟失，因?yàn)樵贛ySQL5.5之前版本的DDL不是Online的。因此不能有數(shù)據(jù)的改動(dòng)。

現(xiàn)在MySQL都已經(jīng)更新到8版本了，如果你是新項(xiàng)目就直接用8版本，不要在用5.6以前的老版本了，咔咔在18年開始就已經(jīng)在使用MySQL8.0版本了。

在鎖那一期文章中跟大家聊了MySQL5.6在DDL操作做了優(yōu)化，引入了Online DDL。

優(yōu)化后的執(zhí)行流程

建立臨時(shí)文件tmp_file，把表的B+樹存儲(chǔ)到臨時(shí)文件中。若此時(shí)有對(duì)表的操作，則會(huì)記錄在row log文件中。

把數(shù)據(jù)從原表全部刷到臨時(shí)文件后，此時(shí)臨時(shí)文件的數(shù)據(jù)就跟原表的數(shù)據(jù)一致。

最后用臨時(shí)文件替換表A的數(shù)據(jù)文件。

Online DDL的由來(lái)

可以看到在收縮磁盤文件時(shí)有數(shù)據(jù)更新會(huì)記錄在row log中，意思就是在收縮磁盤空間時(shí)是可以對(duì)表進(jìn)行增刪改查的。

注意點(diǎn)

在進(jìn)行磁盤文件收縮的過(guò)程中，都會(huì)全表掃描原數(shù)據(jù)和新增臨時(shí)文件，如果你的表非常大，會(huì)非常消耗IO和CPU。

因此，你要安全的做這個(gè)操作，可以使用開源的gh-ost來(lái)進(jìn)行。

結(jié)論

當(dāng)你想收縮因?yàn)榇罅吭鰟h改查而導(dǎo)致表磁盤文件非常大時(shí)就可以執(zhí)行alter table evt_sms engine=Innodb命令來(lái)達(dá)到收縮表空間的目的。

五、實(shí)踐是檢驗(yàn)認(rèn)識(shí)是否具有真理性的唯一標(biāo)準(zhǔn)

都應(yīng)該知道實(shí)踐是檢驗(yàn)認(rèn)識(shí)是否具有真理性的唯一標(biāo)準(zhǔn)，那么接下里就對(duì)本文提出的結(jié)論進(jìn)行實(shí)際操作一下。

先執(zhí)行ctrl + z結(jié)束MySQL任務(wù)窗口

執(zhí)行l(wèi)l -h查看此時(shí)表evt_sms磁盤文件大小為108M

執(zhí)行fg返回到MySQL任務(wù)窗口

執(zhí)行命令alter table evt_sms engine=Innodb

再執(zhí)行ctrl + z，執(zhí)行l(wèi)l -h查看磁盤文件大小已經(jīng)到了128k。

上圖即是咔咔操作的全過(guò)程，得到的結(jié)論就是執(zhí)行命令alter table ect_sms engine = Innodb可以收縮由于大量增刪改查的表引發(fā)的空洞問(wèn)題。最終達(dá)到收縮表空間目的。

六、開發(fā)建議

刪除數(shù)據(jù)不要使用delete，而是使用軟刪除，做一個(gè)標(biāo)記刪除即可。

這樣既不會(huì)出現(xiàn)空洞問(wèn)題，也方便數(shù)據(jù)溯源。

每張表必備三個(gè)字段create_time、update_time、delete_time。

七、總結(jié)

通過(guò)本期文章我們需要知道以下幾點(diǎn)。

通過(guò)大量增刪改查的表會(huì)出現(xiàn)空洞

干掉空洞需要執(zhí)行alter table evt_sms engine=Innodb來(lái)解決

使用delete刪除數(shù)據(jù)只會(huì)做一個(gè)標(biāo)記處理，并不會(huì)真正刪除空間

本文所有的結(jié)論都基于innodb_file_per_table = on

堅(jiān)持學(xué)習(xí)、堅(jiān)持寫作、堅(jiān)持分享是咔咔從業(yè)以來(lái)所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來(lái)一點(diǎn)幫助，我是咔咔，下期見。

MySQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。