性能文章>MySQL删除操作其实是假删除?>

MySQL删除操作其实是假删除?转载

4月前
258803

起因:Mysql大概1700W大表删除1000W左右数据,发现数据大小和索引大小并没有减少思考

因为近期在重构优化一个业务的时候 发现有一张表(send_log)数据量将近1700W 左右  占用数据大小17G,索引18G左右  而我们的核心应用在使用的时候 会去临时查询这张表 获取一些数据 先不管设计的合不合理吧,因为是维护  不出问题为第一要务 所以想到要物理删除一下表数据  计划把18年1000W左右数据给腾出空间  但运维执行删除操作后  发现情况没有那么理想  行数是少了  但表空间 大小 没降下去

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

 

Rows原来是1700W  已经delete删除了1000W左右  但dataLength indexLength都没变。。。

 

 

在 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 值为多少都可以被复用在这个页上。

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

因为你要知道,mysql 在底层是以数据页为单位来存储和读取数据的,每次向磁盘读一次数据就是读一个数据页,然而每访问一个数据页就对应一次磁盘 IO 操作,磁盘 IO 相对内存访问速度是相当慢的。

所以你想想,如果一个表上存在大量的数据空洞,原本只需一个数据页就保存的数据,由于被很多空洞占用了空间,不得不需要增加其他的数据页来保存数据,相应的,mysql 在查询相同数据的时候,就不得不增加磁盘 IO 操作,从而影响查询速度。

其实不仅仅是删除操作会造成数据空洞,插入和更新同样也会造成空洞,这里就不细说了,你知道就行。

因此,一个数据表在经过大量频繁的增删改之后,难免会产生数据空洞,浪费空间并影响查询效率,通常在生产环境中会直接表现为原本很快的查询会变得越来越慢。

对于这种情况,我们通常可以使用下面这个命令就能解决数据空洞问题。

  1. optimize table t 

这个命令的原理就是重建表,就是建立一个临时表 B,然后把表 A(存在数据空洞的表) 中的所有数据查询出来,接着把数据全部重新插入到临时表 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 + ****yze instead | 
  6. | test.t | optimize | status   | OK                                                                | 
  7. +--------+----------+----------+-------------------------------------------------------------------+ 
  8. 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 应该是定义表结构文件的大小了。

另外下面这个命令也可以实现重建表,可以达到跟上面一样的效果,而且推荐大家使用下面这个命令,大家可以试试。

  1. alter table t engine=InnoDB 

注意本文内容是基于 InnoDB 引擎,对于其他引擎可能存在一些差异。原创不易,如果文章对你有启发,就点个在看吧,有疑问也可以在下面留言交流,也可以与我私信交流,感谢支持。

作者:星巴克男孩

博客:https://www.cnblogs.com/StarbucksBoy/ 

文章来源:博客园

原文链接:https://www.cnblogs.com/StarbucksBoy/p/12511226.html

分类:标签:
请先登录,感受更多精彩内容
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
Prometheus时序数据库-数据的插入
前言在之前的文章里,笔者详细的阐述了Prometheus时序数据库在内存和磁盘中的存储结构。有了前面的铺垫,笔者就可以在本篇文章阐述下数据的插入过程。 监控数据的插入在这里,笔者并不会去讨论Promt
Prometheus时序数据库-数据的查询
前言在之前的博客里,笔者详细阐述了Prometheus数据的插入过程。但我们最常见的打交道的是数据的查询。Prometheus提供了强大的Promql来满足我们千变万化的查询需求。在这篇文章里面,笔者
记一次中间件导致的慢SQL排查过程
前言最近发现线上出现一个奇葩的问题,这问题让笔者定位了好长时间,期间排查问题的过程还是挺有意思的,正好博客也好久不更新了,就以此为素材写出了本篇文章。 Bug现场我们的分库分表中间件在经过一年的沉淀之
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL