MySQL刪除操作其實是假刪除

2021-03-03    分類： 網站建設

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

上面這個是結論，我們可以通過一個例子來驗證下。

沿用前面文章中的例子吧，先創(chuàng)建一個存儲過程，插入 10w 條數(shù)據(jù)，然后看下這 10w 條數(shù)據(jù)占了多大的空間。

  1. CREATE TABLE `t` ( 
  2.   `id` int(11) NOT NULL, 
  3.   `a` int(11) DEFAULT NULL, 
  4.   `b` int(11) DEFAULT NULL, 
  5.   PRIMARY KEY (`id`), 
  6.   KEY `a` (`a`), 
  7.   KEY `b` (`b`) 
  8. ) ENGINE=InnoDB;  
  1. #定義分割符號，mysql 默認分割符為分號;，這里定義為 // 
  2. #分隔符的作用主要是告訴mysql遇到下一個 // 符號即執(zhí)行上面這一整段sql語句 
  3. delimiter // 
  4.  
  5. #創(chuàng)建一個存儲過程，并命名為 testData 
  6. create procedure testData()  
  7.  
  8. #下面這段就是表示循環(huán)往表里插入10w條數(shù)據(jù) 
  9. begin 
  10.   declare i int; 
  11.   set i=1; 
  12.   while(i<=100000)do 
  13.     insert into t values(i, i, i); 
  14.     set i=i+1; 
  15.   end while; 
  16. end //  #這里遇到//符號，即執(zhí)行上面一整段sql語句 
  17.  
  18. delimiter ; #恢復mysql分隔符為; 
  19.  
  20. call testData(); #調用存儲過程  
  1. #下面這兩條命令可以查看表文件所占空間大小 
  2. mysql> use information_schema; 
  3. Reading table information for completion of table and column names 
  4. You can turn off this feature to get a quicker startup with -A 
  5.  
  6. Database changed 
  7. mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; 
  8. +-------------------------------------------------+ 
  9. | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | 
  10. +-------------------------------------------------+ 
  11. | 3.52M                                           | 
  12. +-------------------------------------------------+ 
  13. 1 row in set (0.04 sec) 

可以看到 10w 條數(shù)據(jù)在 mysql 中占用了 3.52M 大小的空間，那么我們執(zhí)行刪除命令 delete from t，再看看呢。

  1. #先刪除表所有數(shù)據(jù)，再重新查看表文件大小 
  2. mysql> delete from t; 
  3. Query OK, 100000 rows affected (0.46 sec) 
  4.  
  5. mysql> use information_schema; 
  6. Reading table information for completion of table and column names 
  7. You can turn off this feature to get a quicker startup with -A 
  8.  
  9. Database changed 
  10. mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; 
  11. +-------------------------------------------------+ 
  12. | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | 
  13. +-------------------------------------------------+ 
  14. | 3.52M                                           | 
  15. +-------------------------------------------------+ 
  16. 1 row in set (0.00 sec) 

從結果可以發(fā)現(xiàn)表數(shù)據(jù)被清空后，表所占空間大小并沒有變化，這就驗證了上面的結論，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 相對內存訪問速度是相當慢的。

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

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

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

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

  1. optimize table t 

這個命令的原理就是重建表，就是建立一個臨時表 B，然后把表 A(存在數(shù)據(jù)空洞的表) 中的所有數(shù)據(jù)查詢出來，接著把數(shù)據(jù)全部重新插入到臨時表 B 中，最后再用臨時表 B 替換表 A 即可，這就是重建表的過程。

我們再來試驗一下，看看效果。

  1. mysql> optimize table t; 
  2. +--------+----------+----------+-------------------------------------------------------------------+ 
  3. | Table  | Op       | Msg_type | Msg_text                                                          | 
  4. +--------+----------+----------+-------------------------------------------------------------------+ 
  5. | test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead | 
  6. | test.t | optimize | status   | OK                                                                | 
  7. +--------+----------+----------+-------------------------------------------------------------------+ 
  8. 2 rows in set (0.39 sec) 
  9.  
  10. mysql> use information_schema; 
  11. Reading table information for completion of table and column names 
  12. You can turn off this feature to get a quicker startup with -A 
  13.  
  14. Database changed 
  15. mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t'; 
  16. +-------------------------------------------------+ 
  17. | concat(round(sum(DATA_LENGTH/1024/1024),2),'M') | 
  18. +-------------------------------------------------+ 
  19. | 0.02M                                           | 
  20. +-------------------------------------------------+ 
  21. 1 row in set (0.00 sec) 

可以看到表文件大小已經變成 0.02M了，說明表空間被釋放了，這個 0.02M 應該是定義表結構文件的大小了。

另外下面這個命令也可以實現(xiàn)重建表，可以達到跟上面一樣的效果，而且推薦大家使用下面這個命令，大家可以試試。

  1. alter table t engine=InnoDB 

注意本文內容是基于 InnoDB 引擎，對于其他引擎可能存在一些差異。原創(chuàng)不易，如果文章對你有啟發(fā)，就點個在看吧，有疑問也可以在下面留言交流，也可以與我私信交流，感謝支持。

網站名稱：MySQL刪除操作其實是假刪除
當前地址：http://muchs.cn/news/104001.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站收錄、外貿網站建設、網站排名、ChatGPT、App開發(fā)、響應式網站

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)

網站建設網站維護公司