mysql刪除操作其實是假刪除問題
mysql刪除操作其實是假刪除
在 InnoDB 中，你的 delete 操作，并不會真的把數(shù)據(jù)刪除，mysql 實際上只是給刪除的數(shù)據(jù)打了個標記，標記為刪除，因此你使用 delete 刪除表中的數(shù)據(jù)，表文件在磁盤上所占空間不會變小，我們這里暫且稱之為假刪除。
上面這個是結(jié)論。
我們可以通過一個例子來驗證下
沿用前面文章中的例子吧，先創(chuàng)建一個存儲過程，插入 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;
#定義分割符號，mysql 默認分割符為分號;，這里定義為 //
#分隔符的作用主要是告訴mysql遇到下一個 // 符號即執(zhí)行上面這一整段sql語句
delimiter //
#創(chuàng)建一個存儲過程，并命名為 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 // #這里遇到//符號，即執(zhí)行上面一整段sql語句
delimiter ; #恢復mysql分隔符為;
call testData(); #調(diào)用存儲過程#下面這兩條命令可以查看表文件所占空間大小 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ù)被清空后，表所占空間大小并沒有變化，這就驗證了上面的結(jié)論，delete 操作并沒有真正刪除數(shù)據(jù)，表的空間并沒有被釋放。
這些被刪除的記錄行，只是被標記刪除，是可以被復用的，下次有符合條件的記錄是可以直接插入到這個被標記的位置的。
比如我們在 id 為 300-600 之間的記錄中刪除一條 id=500 的記錄，這條記錄就會被標記為刪除，等下一次如果有一條 id=400 的記錄要插入進來，那么就可以復用 id=500 被標記刪除的位置，這種情況叫行記錄復用。
還有一種情況是數(shù)據(jù)頁復用，就是指整個數(shù)據(jù)頁都被標記刪除了，于是這整個數(shù)據(jù)頁都可以被復用了，和行記錄復用不同的是，數(shù)據(jù)頁復用對要插入的數(shù)據(jù)幾乎沒有條件限制。
還以上面那個插入為例，假如要插入的記錄是 id=1000，那么就不能復用 id=500 這個位置了，但如果有一整個數(shù)據(jù)頁可復用的話，那么無論 id 值為多少都可以被復用在這個頁上。
這些被標記刪除的記錄，其實就是一個空洞，有種占著茅坑不拉屎的感覺，浪費空間不說，還會影響查詢效率。
因為你要知道，mysql 在底層是以數(shù)據(jù)頁為單位來存儲和讀取數(shù)據(jù)的，每次向磁盤讀一次數(shù)據(jù)就是讀一個數(shù)據(jù)頁，然而每訪問一個數(shù)據(jù)頁就對應一次磁盤 IO 操作，磁盤 IO 相對內(nèi)存訪問速度是相當慢的。
所以你想想，如果一個表上存在大量的數(shù)據(jù)空洞，原本只需一個數(shù)據(jù)頁就保存的數(shù)據(jù)，由于被很多空洞占用了空間，不得不需要增加其他的數(shù)據(jù)頁來保存數(shù)據(jù)，相應的，mysql 在查詢相同數(shù)據(jù)的時候，就不得不增加磁盤 IO 操作，從而影響查詢速度。
其實不僅僅是刪除操作會造成數(shù)據(jù)空洞，插入和更新同樣也會造成空洞，這里就不細說了，你知道就行。
因此，一個數(shù)據(jù)表在經(jīng)過大量頻繁的增刪改之后，難免會產(chǎn)生數(shù)據(jù)空洞，浪費空間并影響查詢效率，通常在生產(chǎn)環(huán)境中會直接表現(xiàn)為原本很快的查詢會變得越來越慢。
對于這種情況，我們通常可以使用下面這個命令就能解決數(shù)據(jù)空洞問題。
optimize?table?t
這個命令的原理就是重建表，就是建立一個臨時表 B，然后把表 A（存在數(shù)據(jù)空洞的表） 中的所有數(shù)據(jù)查詢出來，接著把數(shù)據(jù)全部重新插入到臨時表 B 中，最后再用臨時表 B 替換表 A 即可，這就是重建表的過程。
我們再來試驗一下。
看看效果
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了，說明表空間被釋放了，這個 0.02M 應該是定義表結(jié)構文件的大小了。
另外下面這個命令也可以實現(xiàn)重建表，可以達到跟上面一樣的效果，而且推薦大家使用下面這個命令，大家可以試試。
alter?table?t?engine=InnoDB
注意本文內(nèi)容是基于 InnoDB 引擎，對于其他引擎可能存在一些差異。
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
行級鎖加鎖規(guī)則比較復雜,不同的場景,加鎖的形式是不同的,本文主要介紹了深入理解MySQL中的行級鎖,具有一定的參考價值,感興趣的可以了解一下2024-01-01
分布式鎖開發(fā)中經(jīng)常使用,本文主要介紹了Mysql中分布式鎖的具體實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2024-01-01
Node.js下向MySQL數(shù)據(jù)庫插入批量數(shù)據(jù)的方法
這篇文章主要介紹了Node.js下向MySQL數(shù)據(jù)庫插入批量數(shù)據(jù)的實現(xiàn)方法，非常不錯，具有參考借鑒價值,需要的朋友可以參考下2017-10-10
我們知道Mysql并發(fā)事務會引起更新丟失問題,解決辦法是鎖,所以本文將對鎖（樂觀鎖、悲觀鎖）進行分析,這篇文章主要給大家介紹了關于MySQL悲觀鎖與樂觀鎖方案的相關資料,需要的朋友可以參考下2021-11-11
阿里云服務器安裝Mysql數(shù)據(jù)庫的詳細教程
這篇文章主要介紹了阿里云服務器安裝Mysql數(shù)據(jù)庫的詳細教程,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-11-11
下面小編就為大家?guī)硪黄猰ysql查詢結(jié)果輸出到文件的方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-01-01
這篇文章主要介紹了mysql之如何查找配置文件my.ini的位置問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-07-07
MYSQL中統(tǒng)計查詢結(jié)果總行數(shù)的便捷方法省去count(*)
查看手冊后發(fā)現(xiàn)SQL_CALC_FOUND_ROWS關鍵詞的作用是在查詢時統(tǒng)計滿足過濾條件后的結(jié)果的總數(shù)（不受 Limit 的限制）具體使用如下，感興趣的朋友可以學習下2013-07-07
這篇文章主要介紹了mysql全文模糊搜索MATCH AGAINST方法示例，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-11-11
2012-07-07