mysql刪除操作其實(shí)是假刪除問(wèn)題
mysql刪除操作其實(shí)是假刪除
在 InnoDB 中,你的 delete 操作,并不會(huì)真的把數(shù)據(jù)刪除,mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記,標(biāo)記為刪除,因此你使用 delete 刪除表中的數(shù)據(jù),表文件在磁盤(pán)上所占空間不會(huì)變小,我們這里暫且稱之為假刪除。
上面這個(gè)是結(jié)論。
我們可以通過(guò)一個(gè)例子來(lái)驗(yàn)證下
沿用前面文章中的例子吧,先創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,插入 10w 條數(shù)據(jù),然后看下這 10w 條數(shù)據(jù)占了多大的空間。
CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB;
#定義分割符號(hào),mysql 默認(rèn)分割符為分號(hào);,這里定義為 // #分隔符的作用主要是告訴mysql遇到下一個(gè) // 符號(hào)即執(zhí)行上面這一整段sql語(yǔ)句 delimiter // #創(chuàng)建一個(gè)存儲(chǔ)過(guò)程,并命名為 testData create procedure testData() #下面這段就是表示循環(huán)往表里插入10w條數(shù)據(jù) begin declare i int; set i=1; while(i<=100000)do insert into t values(i, i, i); set i=i+1; end while; end // #這里遇到//符號(hào),即執(zhí)行上面一整段sql語(yǔ)句 delimiter ; #恢復(fù)mysql分隔符為; call testData(); #調(diào)用存儲(chǔ)過(guò)程
#下面這兩條命令可以查看表文件所占空間大小 mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; +-------------------------------------------------+ | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | +-------------------------------------------------+ | 3.52M | +-------------------------------------------------+ 1 row in set (0.04 sec)
可以看到 10w 條數(shù)據(jù)在 mysql 中占用了 3.52M 大小的空間,那么我們執(zhí)行刪除命令 delete from t,再看看呢。
#先刪除表所有數(shù)據(jù),再重新查看表文件大小 mysql> delete from t; Query OK, 100000 rows affected (0.46 sec) mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; +-------------------------------------------------+ | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | +-------------------------------------------------+ | 3.52M | +-------------------------------------------------+ 1 row in set (0.00 sec)
從結(jié)果可以發(fā)現(xiàn)表數(shù)據(jù)被清空后,表所占空間大小并沒(méi)有變化,這就驗(yàn)證了上面的結(jié)論,delete 操作并沒(méi)有真正刪除數(shù)據(jù),表的空間并沒(méi)有被釋放。
這些被刪除的記錄行,只是被標(biāo)記刪除,是可以被復(fù)用的,下次有符合條件的記錄是可以直接插入到這個(gè)被標(biāo)記的位置的。
比如我們?cè)?id 為 300-600 之間的記錄中刪除一條 id=500 的記錄,這條記錄就會(huì)被標(biāo)記為刪除,等下一次如果有一條 id=400 的記錄要插入進(jìn)來(lái),那么就可以復(fù)用 id=500 被標(biāo)記刪除的位置,這種情況叫行記錄復(fù)用。
還有一種情況是數(shù)據(jù)頁(yè)復(fù)用,就是指整個(gè)數(shù)據(jù)頁(yè)都被標(biāo)記刪除了,于是這整個(gè)數(shù)據(jù)頁(yè)都可以被復(fù)用了,和行記錄復(fù)用不同的是,數(shù)據(jù)頁(yè)復(fù)用對(duì)要插入的數(shù)據(jù)幾乎沒(méi)有條件限制。
還以上面那個(gè)插入為例,假如要插入的記錄是 id=1000,那么就不能復(fù)用 id=500 這個(gè)位置了,但如果有一整個(gè)數(shù)據(jù)頁(yè)可復(fù)用的話,那么無(wú)論 id 值為多少都可以被復(fù)用在這個(gè)頁(yè)上。
這些被標(biāo)記刪除的記錄,其實(shí)就是一個(gè)空洞,有種占著茅坑不拉屎的感覺(jué),浪費(fèi)空間不說(shuō),還會(huì)影響查詢效率。
因?yàn)槟阋溃琺ysql 在底層是以數(shù)據(jù)頁(yè)為單位來(lái)存儲(chǔ)和讀取數(shù)據(jù)的,每次向磁盤(pán)讀一次數(shù)據(jù)就是讀一個(gè)數(shù)據(jù)頁(yè),然而每訪問(wèn)一個(gè)數(shù)據(jù)頁(yè)就對(duì)應(yīng)一次磁盤(pán) IO 操作,磁盤(pán) IO 相對(duì)內(nèi)存訪問(wèn)速度是相當(dāng)慢的。
所以你想想,如果一個(gè)表上存在大量的數(shù)據(jù)空洞,原本只需一個(gè)數(shù)據(jù)頁(yè)就保存的數(shù)據(jù),由于被很多空洞占用了空間,不得不需要增加其他的數(shù)據(jù)頁(yè)來(lái)保存數(shù)據(jù),相應(yīng)的,mysql 在查詢相同數(shù)據(jù)的時(shí)候,就不得不增加磁盤(pán) IO 操作,從而影響查詢速度。
其實(shí)不僅僅是刪除操作會(huì)造成數(shù)據(jù)空洞,插入和更新同樣也會(huì)造成空洞,這里就不細(xì)說(shuō)了,你知道就行。
因此,一個(gè)數(shù)據(jù)表在經(jīng)過(guò)大量頻繁的增刪改之后,難免會(huì)產(chǎn)生數(shù)據(jù)空洞,浪費(fèi)空間并影響查詢效率,通常在生產(chǎn)環(huán)境中會(huì)直接表現(xiàn)為原本很快的查詢會(huì)變得越來(lái)越慢。
對(duì)于這種情況,我們通常可以使用下面這個(gè)命令就能解決數(shù)據(jù)空洞問(wèn)題。
optimize?table?t
這個(gè)命令的原理就是重建表,就是建立一個(gè)臨時(shí)表 B,然后把表 A(存在數(shù)據(jù)空洞的表) 中的所有數(shù)據(jù)查詢出來(lái),接著把數(shù)據(jù)全部重新插入到臨時(shí)表 B 中,最后再用臨時(shí)表 B 替換表 A 即可,這就是重建表的過(guò)程。
我們?cè)賮?lái)試驗(yàn)一下。
看看效果
mysql> optimize table t; +--------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+-------------------------------------------------------------------+ | test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.t | optimize | status | OK | +--------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.39 sec) mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; +-------------------------------------------------+ | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | +-------------------------------------------------+ | 0.02M | +-------------------------------------------------+ 1 row in set (0.00 sec)
可以看到表文件大小已經(jīng)變成 0.02M了,說(shuō)明表空間被釋放了,這個(gè) 0.02M 應(yīng)該是定義表結(jié)構(gòu)文件的大小了。
另外下面這個(gè)命令也可以實(shí)現(xiàn)重建表,可以達(dá)到跟上面一樣的效果,而且推薦大家使用下面這個(gè)命令,大家可以試試。
alter?table?t?engine=InnoDB
注意本文內(nèi)容是基于 InnoDB 引擎,對(duì)于其他引擎可能存在一些差異。
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
MySql數(shù)據(jù)庫(kù)單表查詢與多表連接查詢效率對(duì)比
在遇到數(shù)據(jù)之間的聯(lián)系很復(fù)雜,建表就很糾結(jié),到底該怎么去處理這些復(fù)雜的數(shù)據(jù)呢,是單表查詢,然后在業(yè)務(wù)層去處理數(shù)據(jù)間的關(guān)系,還是直接通過(guò)多表連接查詢來(lái)處理數(shù)據(jù)關(guān)系呢2021-09-09MySQL中建表時(shí)可空(NULL)和非空(NOT NULL)的用法詳解
這篇文章主要介紹了MySQL中建表時(shí)可空(NULL)和非空(NOT NULL)的用法詳解,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07MYSQL開(kāi)啟遠(yuǎn)程訪問(wèn)權(quán)限的方法
在本篇文章里小編給大家整理的是關(guān)于MYSQL開(kāi)啟遠(yuǎn)程訪問(wèn)權(quán)限的方法,對(duì)此有興趣的朋友們可以跟著學(xué)習(xí)下。2020-02-02MySQL中導(dǎo)出用戶權(quán)限設(shè)置的腳本分享
這篇文章主要介紹了MySQL中導(dǎo)出用戶權(quán)限設(shè)置的腳本分享,本文通過(guò)導(dǎo)出mysql.user表中數(shù)據(jù)實(shí)現(xiàn)導(dǎo)出權(quán)限設(shè)置,需要的朋友可以參考下2014-10-10linux系統(tǒng)下實(shí)現(xiàn)mysql熱備份詳細(xì)步驟(mysql主從復(fù)制)
這篇文章主要介紹了linux系統(tǒng)下實(shí)現(xiàn)MySQL主從熱備份2013-12-12Linux下MySql 1036 錯(cuò)誤碼解決(1036: Table ''xxxx'' is read only)
我們?cè)谶M(jìn)行數(shù)據(jù)庫(kù)搬家的時(shí)候,經(jīng)常會(huì)遇到(1036: Table 'xxxx' is read only)的問(wèn)題,字面意思很明確,就是數(shù)據(jù)庫(kù)只有讀權(quán)限,無(wú)寫(xiě)權(quán)限,那么我們來(lái)分享下我的處理辦法2014-07-07什么情況下需要?jiǎng)?chuàng)建MySQL索引?
這篇文章主要介紹了什么情況下需要?jiǎng)?chuàng)建MySQL索引?本文同時(shí)介紹了哪些情況不適合創(chuàng)建MySQL索引,需要的朋友可以參考下2014-10-10