MySQL使用delete删除数据的若干疑问 - 高飞网
161 人阅读

MySQL使用delete删除数据的若干疑问

2016-02-18 09:40:55

    在生产环境上,有一个表,记录微信消息推送。发现表大小已经到5.8G了,库总容量是6.4G,占比近91%。非常惊人。

    表结构如下:

CREATE TABLE `weixin_push_log_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `openid` char(50) DEFAULT NULL,
  `job_kind` char(1) DEFAULT '1',
  `job_id` int(11) unsigned DEFAULT NULL,
  `modifiedtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP
  PRIMARY KEY (`wspl_id`),
  UNIQUE KEY `logIndex` (`openid`,`job_kind`,`job_id`)
);

在测试环境,使用存储过程生成10000000条数据:

create procedure pro10()
begin
declare i int;
set i=0;
while i<10000000 do
    insert into weixin_sub_push_log_test(job_id) values(i);
    set i=i+1;
end while;
end;

测试删除10万条数据,执行时间为0.6ms;删除100万条数据,4.6ms。

在生产环境中,执行删除 38025572条数据,执行时间: 2347.681s,近40分钟。

执行完成后,数据只剩下百万条了。但数据体积却由原来的5.8G升为7.9G了。看下面的文章和该案例类似:

http://my.oschina.net/u/241670/blog/298319

http://blog.sina.com.cn/s/blog_3dbab2840100c13p.html

在使用mysql的时候有时候,可能会发现尽管一张表删除了许多数据,但是这张表表的数据文件和索引文件却奇怪的没有变小。这是因为mysql在删除数据(特别是有Text和BLOB)的时候,会留下许多的数据空洞,这些空洞会占据原来数据的空间,所以文件的大小没有改变。这些空洞在以后插入数据的时候可能会被再度利用起来,当然也有可能一直存在。这种空洞不仅额外增加了存储代价,同时也因为数据碎片化降低了表的扫描效率。

      mysql提供了解决这一个问题的方法:optimize table table_name 命令。该命令会对表进行碎片整理,去除空洞。查看前后效果可以使用show table status命令,例如show table status from [database] like '[table_name]';返回结果中的data_free即为空洞所占据的存储空间。当执行完optimize table命令后,该字段的值将为0。

下面查看一下空洞大小:

show table status from c like 'weixin_sub_push_log';

结果:


有6G的空洞

疑问:
1、上面的文章解释了为什么表没有变小,但没有解释表为什么变大。
2、奇怪的事情再次发生,第二天早上来观察表时,表变为1.6G了。


还没有评论!
54.225.9.188