2021-03-03 分類: 網站建設
在 InnoDB 中,你的 delete 操作,并不會真的把數據刪除,mysql 實際上只是給刪除的數據打了個標記,標記為刪除,因此你使用 delete 刪除表中的數據,表文件在磁盤上所占空間不會變小,我們這里暫且稱之為假刪除。
上面這個是結論,我們可以通過一個例子來驗證下。
沿用前面文章中的例子吧,先創建一個存儲過程,插入 10w 條數據,然后看下這 10w 條數據占了多大的空間。
- 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遇到下一個 // 符號即執行上面這一整段sql語句
- delimiter //
- #創建一個存儲過程,并命名為 testData
- create procedure testData()
- #下面這段就是表示循環往表里插入10w條數據
- 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 // #這里遇到//符號,即執行上面一整段sql語句
- delimiter ; #恢復mysql分隔符為;
- call testData(); #調用存儲過程
- #下面這兩條命令可以查看表文件所占空間大小
- 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 條數據在 mysql 中占用了 3.52M 大小的空間,那么我們執行刪除命令 delete from t,再看看呢。
- #先刪除表所有數據,再重新查看表文件大小
- 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)
從結果可以發現表數據被清空后,表所占空間大小并沒有變化,這就驗證了上面的結論,delete 操作并沒有真正刪除數據,表的空間并沒有被釋放。
這些被刪除的記錄行,只是被標記刪除,是可以被復用的,下次有符合條件的記錄是可以直接插入到這個被標記的位置的。
比如我們在 id 為 300-600 之間的記錄中刪除一條 id=500 的記錄,這條記錄就會被標記為刪除,等下一次如果有一條 id=400 的記錄要插入進來,那么就可以復用 id=500 被標記刪除的位置,這種情況叫行記錄復用。
還有一種情況是數據頁復用,就是指整個數據頁都被標記刪除了,于是這整個數據頁都可以被復用了,和行記錄復用不同的是,數據頁復用對要插入的數據幾乎沒有條件限制。
還以上面那個插入為例,假如要插入的記錄是 id=1000,那么就不能復用 id=500 這個位置了,但如果有一整個數據頁可復用的話,那么無論 id 值為多少都可以被復用在這個頁上。
這些被標記刪除的記錄,其實就是一個空洞,有種占著茅坑不拉屎的感覺,浪費空間不說,還會影響查詢效率。
因為你要知道,mysql 在底層是以數據頁為單位來存儲和讀取數據的,每次向磁盤讀一次數據就是讀一個數據頁,然而每訪問一個數據頁就對應一次磁盤 IO 操作,磁盤 IO 相對內存訪問速度是相當慢的。
所以你想想,如果一個表上存在大量的數據空洞,原本只需一個數據頁就保存的數據,由于被很多空洞占用了空間,不得不需要增加其他的數據頁來保存數據,相應的,mysql 在查詢相同數據的時候,就不得不增加磁盤 IO 操作,從而影響查詢速度。
其實不僅僅是刪除操作會造成數據空洞,插入和更新同樣也會造成空洞,這里就不細說了,你知道就行。
因此,一個數據表在經過大量頻繁的增刪改之后,難免會產生數據空洞,浪費空間并影響查詢效率,通常在生產環境中會直接表現為原本很快的查詢會變得越來越慢。
對于這種情況,我們通常可以使用下面這個命令就能解決數據空洞問題。
- optimize table t
這個命令的原理就是重建表,就是建立一個臨時表 B,然后把表 A(存在數據空洞的表) 中的所有數據查詢出來,接著把數據全部重新插入到臨時表 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)
可以看到表文件大小已經變成 0.02M了,說明表空間被釋放了,這個 0.02M 應該是定義表結構文件的大小了。
另外下面這個命令也可以實現重建表,可以達到跟上面一樣的效果,而且推薦大家使用下面這個命令,大家可以試試。
- alter table t engine=InnoDB
注意本文內容是基于 InnoDB 引擎,對于其他引擎可能存在一些差異。原創不易,如果文章對你有啟發,就點個在看吧,有疑問也可以在下面留言交流,也可以與我私信交流,感謝支持。
文章名稱:MySQL刪除操作其實是假刪除
分享URL:http://m.newbst.com/news1/104001.html
成都網站建設公司_創新互聯,為您提供微信公眾號、自適應網站、品牌網站設計、網站設計公司、定制開發、域名注冊
聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創新互聯
猜你還喜歡下面的內容