MySQL刪除操作其實(shí)是假刪除

2021-03-03 分類(lèi)：網(wǎng)站建設(shè)

在 InnoDB 中，你的 delete 操作，并不會(huì)真的把數(shù)據(jù)刪除，mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記，標(biāo)記為刪除，因此你使用 delete 刪除表中的數(shù)據(jù)，表文件在磁盤(pán)上所占空間不會(huì)變小，我們這里暫且稱(chēng)之為假刪除。

上面這個(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ù)用的話(huà)，那么無(wú)論 id 值為多少都可以被復(fù)用在這個(gè)頁(yè)上。

這些被標(biāo)記刪除的記錄，其實(shí)就是一個(gè)空洞，有種占著茅坑不拉屎的感覺(jué)，浪費(fèi)空間不說(shuō)，還會(huì)影響查詢(xún)效率。

因?yàn)槟阋溃琺ysql 在底層是以數(shù)據(jù)頁(yè)為單位來(lái)存儲(chǔ)和讀取數(shù)據(jù)的，每次向磁盤(pán)讀一次數(shù)據(jù)就是讀一個(gè)數(shù)據(jù)頁(yè)，然而每訪(fǎng)問(wèn)一個(gè)數(shù)據(jù)頁(yè)就對(duì)應(yīng)一次磁盤(pán) IO 操作，磁盤(pán) IO 相對(duì)內(nèi)存訪(fǎng)問(wèn)速度是相當(dāng)慢的。

所以你想想，如果一個(gè)表上存在大量的數(shù)據(jù)空洞，原本只需一個(gè)數(shù)據(jù)頁(yè)就保存的數(shù)據(jù)，由于被很多空洞占用了空間，不得不需要增加其他的數(shù)據(jù)頁(yè)來(lái)保存數(shù)據(jù)，相應(yīng)的，mysql 在查詢(xún)相同數(shù)據(jù)的時(shí)候，就不得不增加磁盤(pán) IO 操作，從而影響查詢(xún)速度。

其實(shí)不僅僅是刪除操作會(huì)造成數(shù)據(jù)空洞，插入和更新同樣也會(huì)造成空洞，這里就不細(xì)說(shuō)了，你知道就行。

因此，一個(gè)數(shù)據(jù)表在經(jīng)過(guò)大量頻繁的增刪改之后，難免會(huì)產(chǎn)生數(shù)據(jù)空洞，浪費(fèi)空間并影響查詢(xún)效率，通常在生產(chǎn)環(huán)境中會(huì)直接表現(xiàn)為原本很快的查詢(xún)會(huì)變得越來(lái)越慢。

對(duì)于這種情況，我們通?？梢允褂孟旅孢@個(gè)命令就能解決數(shù)據(jù)空洞問(wèn)題。

optimize table t

這個(gè)命令的原理就是重建表，就是建立一個(gè)臨時(shí)表 B，然后把表 A(存在數(shù)據(jù)空洞的表) 中的所有數(shù)據(jù)查詢(xún)出來(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ì)于其他引擎可能存在一些差異。原創(chuàng)不易，如果文章對(duì)你有啟發(fā)，就點(diǎn)個(gè)在看吧，有疑問(wèn)也可以在下面留言交流，也可以與我私信交流，感謝支持。

文章名稱(chēng)：MySQL刪除操作其實(shí)是假刪除
分享URL：http://www.jbt999.com/news1/104001.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、自適應(yīng)網(wǎng)站、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站設(shè)計(jì)公司、定制開(kāi)發(fā)、域名注冊(cè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：[email protected]。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容