为什么MySQL 删除表数据 磁盘空间还一直被占用

目录
  • 1、Mysql数据结构
  • 2、表文件大小未更改和mysql设计有关
  • 3、那怎么才能让表大小变小
  • 4、Online DDL
  • 5、总结

最近有个上位机获取下位机上报数据的项目，由于上报频率比较频繁且数据量大，导致数据增长过快，磁盘占用多。

为了节约成本，定期进行数据备份，并通过delete删除表记录。

明明已经执行了delete，可表文件的大小却没减小，令人费解

项目中使用Mysql作为数据库，对于表来说，一般为表结构和表数据。表结构占用空间都是比较小的，一般都是表数据占用的空间。

当我们使用 delete删除数据时，确实删除了表中的数据记录，但查看表文件大小却没什么变化。

1、Mysql数据结构

凡是使用过mysql，对B+树肯定是有所耳闻的，MySQL InnoDB 中采用了 B+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。因此在删除数据时，会有两种情况：

  • 删除数据页中的某些记录
  • 删除整个数据页的内容

2、表文件大小未更改和mysql设计有关

比如想要删除 R4 这条记录：

InnoDB 直接将 R4 这条记录标记为删除，称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时，就会复用该位置。

由此可见，磁盘文件的大小并不会减少。

通用删除整页数据也将记录标记删除，数据就复用用该位置，与删除默写记录不同的是，删除整页记录，当后来插入的数据不在原来的范围时，都可以复用位置，而如果只是删除默写记录，是需要插入数据符合删除记录位置的时候才能复用。

因此，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。

3、那怎么才能让表大小变小

DELETE只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用OPTIMIZE TABLE来回收未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE 表名;

注意：OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

另外，也可以执行通过ALTER TABLE重建表

ALTER TABLE 表名 ENGINE=INNODB

有人会问OPTIMIZE TABLE和ALTER TABLE有什么区别？

alter table t engine = InnoDB（也就是recreate），而 optimize table t 等于 recreate+analyze

4、Online DDL

最后，再说一下Online DDL，dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于“Waiting for meta data lock”状态的时候。因此在 5.6 版本后引入了 Online DDL。

Online DDL推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。Online方式与前两种方式相比，不仅可以读，还可以支持写操作。

执行online DDL语句的时候，使用ALGORITHM和LOCK关键字，这两个关键字在我们的DDL语句的最后面，用逗号隔开即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM选项

  • INPLACE：替换：直接在原表上面执行DDL的操作。
  • COPY：复制：使用一种临时表的方式，克隆出一个临时表，在临时表上执行DDL，然后再把数据导入到临时表中，在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间，表不允许DML的操作。
  • DEFAULT：默认方式，有MySQL自己选择，优先使用INPLACE的方式。
  • LOCK选项
  • SHARE：共享锁，执行DDL的表可以读，但是不可以写。
  • NONE：没有任何限制，执行DDL的表可读可写。
  • EXCLUSIVE：排它锁，执行DDL的表不可以读，也不可以写。
  • DEFAULT：默认值，也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为

DEFAULT，那就是交给MySQL子句去觉得锁还是不锁表。不建议使用，如果你确定你的DDL语句不会锁表，你可以不指定lock或者指定它的值为default，否则建议指定它的锁类型。
执行DDL操作时，ALGORITHM选项可以不指定，这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT，也是同样的效果。如果指定了ALGORITHM选项，但不支持的话，会直接报错。

OPTIMIZE TABLE 和 ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL，但依旧建议在业务访问量低的时候使用

5、总结

delete 删除数据时，其实对应的数据行并不是真正的删除，仅仅是将其标记成可复用的状态，所以表空间不会变小。

可以重建表的方式，快速将delete数据后的表变小（OPTIMIZE TABLE 或ALTER TABLE），在 5.6 版本后，创建表已经支持 Online 的操作，但最好是在业务低峰时使用

到此这篇关于为什么MySQL 删除表数据 磁盘空间还一直被占用的文章就介绍到这了,更多相关MySQL 删除表数据 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql存储引擎MyISAM的常见问题（表损坏、无法访问、磁盘空间不足）

    Mysql存储引擎MyISAM的常见问题（表损坏、无法访问、磁盘空间不足）

    本文为大家分享了解决Mysql存储引擎MyISAM常见问题的方法,供大家参考,具体内容如下 一.处理MyISAM存储引擎的表损坏 在使用MySQL,可能会遇到过MyISAM存储引擎的表损坏的情况.如以下情况: .frm被锁定不能修改 找不到.myi文件(索引文件) 意外结束记录 文件被毁坏 从表处理器得到错误nnn 解决办法1: 使用MySQL自带的myisamchk工具进行修复 打开bin目录,可以看到该工具 命令如下 myisamchk -r tablename r代表recover 或 m

  • Mysql InnoDB删除数据后释放磁盘空间的方法

    Innodb数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致InnoDB数据库文件不断增长. 如果在创建数据库的时候设置innodb_file_per_table=1,这样InnoDB会对每个表创建一个数据文件,然后只需要运行OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间. 运行OPTIMIZE TABLE 表名后,虽然最后会报Table does not support optimize, doing recreate + analyze in

  • MySQL之导出整个及单个表数据的操作

    导出单个表 mysqldump -u user -p dbname tablename > db.sql 导入单个表 mysql>drop tablename; mysql -u user -p dbname < db.sql 导出整个表 mysqldump --opt -d dbname -u dbuser -p > db.sql mysqldump dbname -udbuser -p > db_dt.sql 补充知识:mysql导入导出sql文件:导出整个数据库,导出一

  • 解决MySQL数据库意外崩溃导致表数据文件损坏无法启动的问题

    问题故障: MySQL数据库意外崩溃,一直无法启动数据库. 报错日志: 启动报错:service mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL. ERROR! The server quit without updating PID file (/www/wdlinux/mysql/var/iZ2358oz5deZ.pid). 数据库error日志: 200719 22:07:43 I

  • lnmp下如何关闭Mysql日志保护磁盘空间

    LNMP一键包安装的Mysql默认开启Mysql日志,如果网站对数据库读写较为频繁的话可能会产生大量日志,并占用磁盘空间,还有可能导致VPS的硬盘闲置空间消耗满导致数据库无法开启. 本教程的路径适用于LNMP一键安装包环境,其它环境请根据实际情况操作. 若需要关闭,请执行以下操作: 一.修改文件配置 修改/etc/my.cnf,找到以下字符 复制代码 代码如下: log-bin=mysql-bin binlog_format=mixedbash 注释这两行,如下: 复制代码 代码如下: #log

  • 几个缩减MySQL以节省磁盘空间的建议

    我们在工作中时常会遇到一些客户的TPS\QPS都不太高,但磁盘占用非常大,一旦单实例空间太大,像内存.网络.CPU以及备份都将增加相应的开销.可能仅仅是由于空间不满足使得我们不得不进行扩容,下面的方法提供给大家参考.有则改之无则加勉. 1.表结构设计上 1) 字符集是否遵循了最小化原则?(能用latin的就不用gbk.能用gbk的就不用utf8) 2) 索引上是否有滥用?(根本不使用的字段建索引.不适合建索引的字段建索引.重复建索引或者不能很好的利用前缀索引等) 3) 冗余字段是否太多?(各表中

  • MySQL 利用frm文件和ibd文件恢复表数据

    frm文件和ibd文件简介 在MySQL中,如果我们使用了默认的存储引擎innodb创建一张表,那么在文件夹下面就会出现表名.frm和表名.ibd两个文件,如果我们使用的是Myisam存储引擎,那么就会出现三个文件,这里我们给出例子: [root@ /data/yeyz]#ll total 580 -rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 a.frm -rw-rw---- 1 mysql mysql 0 Apr 3 17:44 a.MYD -rw-rw-

  • ubuntu下磁盘空间不足导致mysql无法启动的解决方法

    前言 最近在数据库的一张表添加两个字段,后来提示什么磁盘空间不足什么什么的,后来数据库就断开连接了,之后就一直连接不上去后来,最后经过思考终于解决了这个问题,这一经历下来真是心惊胆战,本文作为记录一下磁盘空间不足导致的 mysql 无法启动的解决办法. 方法如下 操作系统:ubuntu,磁盘空间不足导致的 mysql 无法启动,会造成如下问题: root@iZ28z558vv0Z:/etc/mysql# mysql -u root -p Enter password: ERROR 2002 (H

  • MySQL Delete 删数据后磁盘空间未释放的原因

    MySQL Delete 删数据后磁盘空间未释放的原因

    问题描述 在 MySQL 中使用 delete 语句删除数据之后,监控视图中可用的磁盘空间没有增加,磁盘使用率没有下降等等. 解决方案 delete 不释放空间是 MySQL 自身机制的原因,需要重建表才可以释放磁盘空间,可以参考的操作: 执行 optimize table ${table_name}. 如果是 InnoDB 的表,执行 alter table ${table_name} engine = innodb. 需要注意以下两个问题: 这两个命令都会重建表,尽量不要在磁盘空间紧张(>9

  • 为什么MySQL 删除表数据 磁盘空间还一直被占用

    为什么MySQL 删除表数据 磁盘空间还一直被占用

    目录 1.Mysql数据结构 2.表文件大小未更改和mysql设计有关 3.那怎么才能让表大小变小 4.Online DDL 5.总结 最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多. 为了节约成本,定期进行数据备份,并通过delete删除表记录. 明明已经执行了delete,可表文件的大小却没减小,令人费解 项目中使用Mysql作为数据库,对于表来说,一般为表结构和表数据.表结构占用空间都是比较小的,一般都是表数据占用的空间. 当我们使用

  • MySQL删除表数据的方法

    在MySQL中有两种方法可以删除数据,一种是DELETE语句,另一种是TRUNCATE TABLE语句.DELETE语句可以通过WHERE对要删除的记录进行选择.而使用TRUNCATE TABLE将删除表中的所有记录.因此,DELETE语句更灵活. 如果要清空表中的所有记录,可以使用下面的两种方法:       DELETE FROM table1       TRUNCATE TABLE table1 其中第二条记录中的TABLE是可选的. 如果要删除表中的部分记录,只能使用DELETE语句.

  • MySQL删除表数据与MySQL清空表命令的3种方法浅析

    目录 一.MySQL清空表数据命令:truncate 二.MySQL删除表命令:drop 三.MySQL清空数据表内容的语法:delete 补充:MySQL删除表操作delete.truncate.drop的区别 总结 一.MySQL清空表数据命令:truncate SQL语法: truncate table 表名 注意: 不能与where一起使用. truncate删除数据后是不可以rollback的. truncate删除数据后会重置Identity(标识列.自增字段),相当于自增列会被置为

  • mysql删除表数据如何恢复

    mysql删除表数据如何恢复

    目录 实验模拟 恢复实战 常用参数选项解释 不常用选项 实验模拟 该实验必须是mysql开启了binlog的情况. binlog日志,即binary log,是二进制日志文件.它有两个作用, 一是增量备份,即只备份新增的内容: 二是用于主从复制等,即主节点维护了一个binlog日志文件,从节点从binlog中同步数据. 我们可以通过binlog日志恢复数据 查看mysql是否开启binlog语句 mysql> show variables like 'log_bin'; +-----------

  • MySQL删除表的时候忽略外键约束的简单实现

    删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心.但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情:另外在测试的时候,也有需要重新创建数据库的所有表.当然很多自动化工具也可以做这样的事情. 删除表的时候有时会遇到这样的错误消息: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 这是因为你尝试删除的表中的

  • mysql清空表数据的两种方式和区别解析

    在MySQL中删除数据有两种方式: truncate(截短)属于粗暴型的清空 delete属于精细化的删除 删除操作 如果你需要清空表里的所有数据,下面两种均可: delete from tablename; truncate table tablename; 而如果你只是删除一部分数据,就只能使用delete: delete from tablename where case1 and case2; 区别 在精细化的删除部分数据时,只能使用delete. 而清空所有表数据时,两者均可,此时这两

  • 为什么说MySQL单表数据不要超过500万行

    今天,探讨一个有趣的话题:MySQL 单表数据达到多少时才需要考虑分库分表?有人说 2000 万行,也有人说 500 万行.那么,你觉得这个数值多少才合适呢? 曾经在中国互联网技术圈广为流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降.事实上,这个传闻据说最早起源于百度.具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来.然后又据说百度的工程师流动到业界的其它公司,

  • MySQL删除表的三种方式(小结)

    drop table drop 是直接删除表信息,速度最快,但是无法找回数据 例如删除 user 表: drop table user; truncate (table) truncate 是删除表数据,不删除表的结构,速度排第二,但不能与where一起使用 例如删除 user 表: truncate table user; delete from delete 是删除表中的数据,不删除表结构,速度最慢,但可以与where连用,可以删除指定的行 例如删除user表的所有数据 delete fro

  • Mysql删除重复数据并且只保留一条(附实例!)

    (1)以这张表为例: CREATE TABLE `test` ( `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '注解id', `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字', PRIMARY KEY (`id`) USING BTREE ) ENGI

  • Mysql 删除重复数据保留一条有效数据(最新推荐)

    目录 Mysql 删除重复数据保留一条有效数据 一.Mysql 删除重复数据,保留一条有效数据 二.Mysql 删除重复数据(多个字段分组) 三.Mysql 查询出可以删除的重复数据 补充:mysql删除重复记录并且只保留一条 MySql如何删除所有多余的重复数据 需要处理的数据,如: Mysql 删除重复数据保留一条有效数据 一.Mysql 删除重复数据,保留一条有效数据 DELETE FROM SZ_Building WHERE id NOT IN ( SELECT t.min_id FRO

随机推荐