加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程 > 正文

MySQL删除操作其实是假删除

发布时间:2019-05-30 01:50:24 所属栏目:编程 来源:谭小谭啊
导读:在 InnoDB 中,你的 delete 操作,并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记,标记为删除,因此你使用 delete 删除表中的数据,表文件在磁盘上所占空间不会变小,我们这里暂且称之为假删除。 上面这个是结论,我们可以通过一个例子来
副标题[/!--empirenews.page--]

MySQL删除操作其实是假删除

在 InnoDB 中,你的 delete 操作,并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记,标记为删除,因此你使用 delete 删除表中的数据,表文件在磁盘上所占空间不会变小,我们这里暂且称之为假删除。

上面这个是结论,我们可以通过一个例子来验证下。

沿用前面文章中的例子吧,先创建一个存储过程,插入 10w 条数据,然后看下这 10w 条数据占了多大的空间。

  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遇到下一个 // 符号即执行上面这一整段sql语句 
  3. delimiter // 
  4.  
  5. #创建一个存储过程,并命名为 testData 
  6. create procedure testData()  
  7.  
  8. #下面这段就是表示循环往表里插入10w条数据 
  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 //  #这里遇到//符号,即执行上面一整段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 条数据在 mysql 中占用了 3.52M 大小的空间,那么我们执行删除命令 delete from t,再看看呢。

  1. #先删除表所有数据,再重新查看表文件大小 
  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) 

从结果可以发现表数据被清空后,表所占空间大小并没有变化,这就验证了上面的结论,delete 操作并没有真正删除数据,表的空间并没有被释放。

这些被删除的记录行,只是被标记删除,是可以被复用的,下次有符合条件的记录是可以直接插入到这个被标记的位置的。

比如我们在 id 为 300-600 之间的记录中删除一条 id=500 的记录,这条记录就会被标记为删除,等下一次如果有一条 id=400 的记录要插入进来,那么就可以复用 id=500 被标记删除的位置,这种情况叫行记录复用。

还有一种情况是数据页复用,就是指整个数据页都被标记删除了,于是这整个数据页都可以被复用了,和行记录复用不同的是,数据页复用对要插入的数据几乎没有条件限制。

还以上面那个插入为例,假如要插入的记录是 id=1000,那么就不能复用 id=500 这个位置了,但如果有一整个数据页可复用的话,那么无论 id 值为多少都可以被复用在这个页上。

这些被标记删除的记录,其实就是一个空洞,有种占着茅坑不拉屎的感觉,浪费空间不说,还会影响查询效率。

(编辑:核心网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

热点阅读