mysql刪除操作其實是假刪除問題怎麼解決
- WBOY轉載
- 2023-05-30 13:22:39878瀏覽
mysql刪除操作其實是假刪除
在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刪除操作其實是假刪除問題怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！