MySQL删除数据,表文件大小依然没变的原因_Mysql

来源:脚本之家  责任编辑:小易  

这是因为删除操作后在数据62616964757a686964616fe59b9ee7ad9431333365633838文件中留下碎片所致。DELETE只是将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间。另外实际操作过程中还发现这个问题还存在两种情况。(1)当DELETE后面跟条件的时候,则就会出现这个问题。如:delete from table_name where 条件删除数据后,数据表占用的空间大小不会变。(2)不跟条件直接delete的时候。如:delete from table_name清除了数据,同时数据表的空间也会变为0。这就存在了一个问题,在网站的实际运行过程中。经常会存在这样的附带条件删除数据的操作行为。天长日久,这不就在数据库中浪费了很多的空间吗。这个时候我们该使用 OPTIMIZE TABLE 指令对表进行优化了。如何使用 OPTIMIZE 以及在什么时候该使用 OPTIMIZE 指令呢? 命令语法:OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...最简单的:optimize table phpernote_article; 如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间,并整理数据文件的碎片。 在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。 OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。 注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。因此,这个操作一定要在网站访问量较少的时间段进行。TRUNCATE其语法结构为:TRUNCATE [TABLE] tbl_name这里简单的给出个示例,我想删除 friends 表中所有的记录,可以使用如下语句:truncate table friends;delete的效果有点像将mysql表中所有记录一条一条删除到删完,而truncate相当于保留mysql表的结构,重新创建了这个表,所有的状态都相当于新表,这样空间就减下来了。好了,当然对于我们网站不可能使用truncate table来清除了,因这样之后所有数据都丢失了,这样肯定是不合理的清除了,我们必须使用delete来删除,然后再来修复优化表了哦www.zgxue.com防采集请勿采集本网。

对于运行很长时间的数据库来说,往往会出现表占用存储空间过大的问题,可是将许多没用的表删除之后,表文件的大小并没有改变,想解决这个问题,就需要了解 InnoDB 如何回收表空间的。

是全表删除吗?可以用truncate table tbname; 或者你delete from tbname 之后, 执行optimize table tbname 来降低水位线 ----------tbname=表名

对于一张表来说,占用空间重要分为两部分,表结构和表数据。通常来说,表结构定义占用的空间很小。所以空间的问题主要和表数据有关。

1、对于你的第一个问题,数据到哪里去了,我不知道你的数据库文件的大小问什么没有变(有可能只是导入了部分数据),但是只要是查询一下数据库,就能知道数据到底有没有被正确导入。 2、mysql也可以像mssql一样把数据文件复制出来,等数据文件发生

在 MySQL 8.0 前,表结构存储在以 .frm 为后缀的文件里。在 8.0,允许将表结构定义在系统数据表中。

MYSQL里面没有限制文件大小的语句,但是事实上你的文件是有大小限制的--受操作系统的限制,比如32位操作系统单个文件有2G大小的限制。 你无法插入数据,要看具体的情况,除了上面说的文件大小超过2G,主要有一下两种可能: 一是插入的数据在唯

关于表数据的存放

试试中间表?把要保存的数据取出来,清空原表数据,再把中间表数据放回去

可以将表数据存在共享表空间,或者单独的文件中,通过 innodb_file_per_table 来控制。 如果为 OFF ,表示存在系统共享表空间中,和数据字典一起 如果为 ON,每个 InnoDB 表结构存储在 .idb 为后缀的文件中

SQL数据库中物理删除与逻辑删除区别如下:物理删除记录,即是会将数据库中的数据记录直接清除(也可以说是磁盘上的删除),会释放出物理空间,也将不能再从数据库中搜索到删去的数据记录;逻辑删除记录,不会直接删除数据库中的数据,仅是通过某

在 5.6.6 以后,默认值为 ON.

建议将该参数设置为 ON,这样在不需要时,通过 drop table 命令,系统就会直接删除该文件。

但在共享表空间中,即使表删掉,空间也不会回收。

truncate = drop + create

数据删除流程

但有时使用 delete删除数据时,仅仅删除的是某些行,但这可能就会出现表空间没有被回收的情况。

我们知道,MySQL InnoDB 中采用了 B+ 树作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。

在删除数据时,会有两种情况: 删除数据页中的某些记录 删除整个数据页的内容

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

InnoDB 直接将 R4 这条记录标记为删除,称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时,就会复用该位置。由此可见,磁盘文件的大小并不会减少。

而且记录的复用,只限于符合范围条件的数据。之后要插入 ID 为 800 的记录,R4 的位置就不能被复用了。

再比如要是删除了整个数据页的内容,假设删除 R3 R4 R5,为 Page A 数据页。

这时 InnoDB 就会将整个 Page A 标记为删除状态,之后整个数据都可以被复用,没有范围的限制。比如要插入 ID=50 的内容就可以直接复用。

并且如果两个相邻的数据页利用率都很小,就会把两个页中的数据合到其中一个页上,另一个页标记为可复用。

综上,无论是数据行的删除还是数据页的删除,都是将其标记为删除的状态,用于复用,所以文件并不会减小。对应到具体的操作就是使用 delete 命令.

而且,我们还可以发现,对于第一种删除记录的情况,由于复用时会有范围的限制,所以就会出现很多空隙的情况,比如删除 R4,插入的却是 ID=800.

插入操作也会造成空隙

在插入数据时,如果数据按照索引递增顺序插入,索引的结构会是紧凑的。但如果是随机插入的,很可能造成索引数据页分裂。

比如给已满的 Page A 插入数据。

由于 Page A 满了,所以要申请 Page B,调整 Page A 的过程到 Page B,这也称为页分裂。

结束后 Page A 就有了空隙。

另外对于更新操作也是,先删除再插入,也会造成空隙。

进而对于大量进行增删改的表,都有可能存在空洞。如果把空洞去掉,自然空间就被释放了。

使用重建表

为了把表中的空隙去掉,这时就可以采用重新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据依次插入到 B 表中。

由于是顺序插入,自然 B 表的空隙不存在,数据页的利用率也更高。之后用表 B 代替表 A,好像起到了收缩表 A 空间的作用。

具体通过:

alter table A engine=InnoDB

在 5.5 版本后,该命令和上面提到的流程差不多,而且 MySQL 会自己完成数据,交换表名,删除旧表的操作。

但这就有一个问题,在 DDL 中,表 A 不能有更新,此时有数据写入表 A 的话,就会造成数据丢失。

在 5.6 版本后引入了 Online DDL。

Online DDL

Online DDL 在其基础上做了如下的更新:

重建表的过程如下:

    建立一个临时文件,扫描表 A 主键的所有数据页。 用生成的数据页生成 B+ 树,存储到临时文件中。 生成临时文件时,如果有对 A 的操作,将其记录在日志文件中,对应图中 state 2 的状态。 临时文件生成后,将日志文件应用到临时文件中,得到与 A 表相同的数据文件,对应 state 3 状态。 用临时文件替换 A 表的数据文件。

由于 row log 日志文件存在,可以在重建表示,对表 A 进行 DML 操作。

需要注意的是,在 alter 语句执行前,会先申请 MDL 写锁,但在拷贝数据前会退化成 MDL 读锁,从而支持 DML 操作。

至于为什么不大 MDL 去掉,是防止其他线程对这个表同时做 DDL 操作。

对于大表来说,该操作很耗 IO 和 CPU 资源,所以在线上操作时,要控制操作时间。如果为了保证安全,推荐使用 gh-ost 来迁移。

Online 和 inplace

首先说一下 inplace 和 copy 的区别:

在 Online DDL 中,表 A 重建后的数据放在 tmp_file 中,这个临时文件是在 InnoDB 内部创建出来的。整个 DDL 在 InnoDB 内部完成。进而对于 Server 层来说,并没有数据移动到临时表中,是一个 "原地" 操作,所以叫 "inplace" .

而在之前普通的 DDL 中,创建后的表 A 是在 tmp_table 是 Server 创建的,所以叫 "copy"

对应到语句其实就是:

-- alter table t engine=InnoDB 默认为下面alter table t engine=innodb,ALGORITHM=inplace;-- 走的就是 server 拷贝的过程alter table t engine=innodb,ALGORITHM=copy;

需要注意的是 inplace 和 Online 并不是对应关系:

    DDL 过程是 Online,则一定是 inplace 如果是 inplace 的 DDL 不应当是 Online,如在 <= 8.0, 添加全文索引和空间索引就属于这种情况。

拓展

说一下 optimize,analyze,alter table 三种重建表之间的区别:

    alter table t engine = InnoDB(也就是 recreate)默认的是 Oline DDL 过程。 analyze table t 不是重建表,仅仅是对表的索引信息做重新统计,没有修改数据,期间加 MDL 读锁。 optimize table t 等于上两步的操作。

在事务里面使用 alter table 默认会自动提交事务,保持事务一致性

如果有时,在重建某张表后,空间不仅没有变小,甚至还变大了一点点。这时因为,重建的这张表本身没有空隙,在 DDL 期间,刚好有一些 DML 执行,引入了一些新的空隙。

而且 InnoDB 不会把整张表填满,每个页留下 1/16 给后续的更新用,所以可能远离是紧凑的,但重建后变成的稍有空隙。

总结

现在我们知道,在使用 delete 删除数据时,其实对应的数据行并不是真正的删除,InnoDB 仅仅是将其标记成可复用的状态,所以表空间不会变小。

通常来说,在标记复用空间时分为两种,一种是仅将某些数据页中的位置标记为删除状态,但这样的位置只会在一定范围内使用,会出现空隙的情况。

另一种是将整个数据页标记成可复用的状态,这样的数据页没有限制,可直接复用。

为了解决这个问题,我们可以采用重建表的方式,其中在 5.6 版本后,创建表已经支持 Online 的操作,但最后是在业务低峰时使用

以上就是MySQL删除数据,表文件大小依然没变的原因的详细内容,更多关于MySQL表文件大小的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:MySQL 快速删除大量数据(千万级别)的几种实践方案详解MySQL 处理重复数据的方法(防止、删除)MySQL对数据库操作(创建、选择、删除)MySQL使用mysqldump+binlog完整恢复被删除的数据库原理解析mysql数据库常见基本操作实例分析【创建、查看、修改及删除数据库】Linux实现定时备份MySQL数据库并删除30天前的备份文件linux定时备份MySQL数据库并删除以前的备份文件(推荐)MySQL数据误删除的快速解决方法(MySQL闪回工具)mysql定时删除过期数据记录的简单方法Mysql的Binlog数据恢复:不小心删除数据库详解MySQL实现快速删除所有表而不删除数据库的方法MySQL删除数据库的两种方法

你创建数据库的时候给了数据库大小的初始值,当你数据量没达到初始值的时候,数据库文件大小是不会变化的本回答被提问者和网友采纳,碎片整理啊:ANALYZE TABLE,MySQL 8.0.16 已经发布,它像往常2113一样增强了组复5261制 Group Replication 功能。这篇文章介绍了 MySQL 8.0.16 为 Group Replication 带来的4102新功能:Message fragmentation(信息碎片化)。1653背景Group Replication 目前使用 XCom(一种组通信引擎),特点:原子性,组员状态检测等。每个成员的组复制插件先将信息转发到本地 XCom,再由 XCom 最终以相同的顺序将信息传递给每个组成员的 Group Replication 插件。XCom 由单线程实现。当一些成员广播信息过大时,XCom 线程必须花费更多的时间来处理那个大信息。如果成员的 XCom 线程忙于处理大信息的时间过长,它可能会去查看其他成员的 XCom 实例。例如,忙碌的成员失效。如果是这样,该组可以从该组中驱逐忙碌的成员。MySQL 8.0.13 新增  group_replication_member_expel_timeout  系统变量,您可以通过它来调整将成员从组中驱逐的时间。例如,怀疑成员失败,但成员实际上忙于处理大信息,给成员足够的时间来完成处理。在这种情况下,是否为成员增加驱逐超时的设置是一种权衡。有可能等了很久,该成员实际真的失效了。Message fragmentation(信息碎片化)MySQL 8.0.16 的 Group Replication 插件新增用来处理大信息的功能:信息碎片化。简而言之,您可以为成员的广播信息指定最大值。超过最大值的信息将分段为较小的块传播。您可以使用  group_replication_communication_max_message_size  系统变量指定允许的信息最大值(默认值为10 MiB)。示例让我们用一个例子来解释新功能。图1显示了当绿色成员向组广播信息时,新功能是如何处理的。图1 对传出信息进行分段1. 如果信息大小超过用户允许的最大值(group_replication_communication_max_message_size),则该成员会将信息分段为不超过最大值的块。2. 该成员将每个块广播到该组,即将每个块单独转发到XCom。XCom 最终将这些块提供给组成员。下面三张图展示出了中间绿色成员发送大信息时工作的新特征。图2a 重新组合传入的信息:第一个片段3. 成员得出结论,传入的信息实际上是一个更大信息的片段。4. 成员缓冲传入的片段,因为他们认为片段是仍然不完整的信息的一部分。(片段包含必要的元数据以达到这个结论。)图2b 重新组合传入的信息:第二个片段5. 见上面的第3步。6. 见上面的第4步。图2c 重新组合传入的信息:最后一个片段7. 成员得出结论,传入的信息实际上是一个更大信息的片段。8. 成员得出结论,传入的片段是最后一个缺失的块,重新组合原始信息,然后对其进行处理,传输完毕。结论MySQL 8.0.16 已经发布后,组复制现在可以确保组内交换的信息大小不超过用户定义的阈值。这可以防止组内误判而驱逐成员内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql 数据库对服务器端光标的限制
  • mysql事务回滚的2个问题分析
  • mysql里的found_row()与row_count()的解释及用法
  • 聊一聊myisam和innodb的区别
  • 详细分析mysql主从复制
  • mysql关联两张表时的编码问题及解决办法
  • mysql 8.0.19 winx64.zip安装教程
  • win10安装mysql5.7.18winx64 启动服务器失败并且没有错误提示
  • mysql密码忘了怎么办?mysql重置root密码方法
  • mysql5.7.13.zip安装教程(windows)
  • mysql数据库delete删除将近80%的记录后数据库的文...
  • 如何解决mysql delete表数据后,表空间大小不变的...
  • mysql删除数据的效率和表本身大小有关系吗
  • mysql删除表数据后文件还在吗
  • mysql delete数据以后,表占用的磁盘大小没有改变...
  • mysql用source命令恢复一个4G大的sql文件时,为何d...
  • mysql数据文件大小问题
  • mysql删除没主键的表数据问题
  • MySQL删除数据什么情况下会释放空间
  • 如何解决mysql delete表数据后,表空间大小不变的问题
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql 快速删除大量数据(千万级别)的几种实践方案详解mysql 处理重复数据的方法(防止、删除)mysql对数据库操作(创建、选择、删除)mysql使用mysqldump+binlog完整恢复被删除的数据库原理解析mysql数据库常见基本操作实例分析【创建、查看、修改及删除数据库】linux实现定时备份mysql数据库并删除30天前的备份文件linux定时备份mysql数据库并删除以前的备份文件(推荐)mysql数据误删除的快速解决方法(mysql闪回工具)mysql定时删除过期数据记录的简单方法mysql的binlog数据恢复:不小心删除数据库详解mysql实现快速删除所有表而不删除数据库的方法mysql删除数据库的两种方法mysql 数据库对服务器端光标的限制mysql事务回滚的2个问题分析mysql里的found_row()与row_count()的解释及用法聊一聊myisam和innodb的区别详细分析mysql主从复制mysql关联两张表时的编码问题及解决办法mysql 8.0.19 winx64.zip安装教程win10安装mysql5.7.18winx64 启动服务器失败并且没有错误提示mysql密码忘了怎么办?mysql重置root密码方法mysql5.7.13.zip安装教程(windows)mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解mysql如何将表结构导出到excelmysql优化全攻略-相关数据库命令mysql为什么要避免大事务以及大事务解决的mysql 取字段值逗号第一个数据的查询语句mysql sql语句分析与查询优化详解mysql where 条件语句介绍和运算符小结分享mysql命令大全解压版mysql中文乱码问题解决方案10个mysql性能调优的方法mysql循环语句之while循环测试
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved