MySQL数据库表空间回收的解决

目录
  • 1. MySQL表空间回收
  • 2. MySQL表空间设置
  • 3. MySQL删除数据流程
  • 4. MySQL数据页空洞问题

1. MySQL表空间回收

我们经常会发现一个问题,就是把表数据删除以后发现,数据文件大小并没有变化,这就是标题中所说的MySQL数据库表空间回收问题。

这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论。一个InnoDB表包含两部分,即:表结构定义和数据。在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里。而MySQL 8.0版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。

2. MySQL表空间设置

MySQL表空间设置是通过参数innodb_file_per_table,表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的:

  • 这个参数设置为OFF表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
  • 这个参数设置为ON表示的是,每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

从MySQL 5.6.6版本开始,它的默认值就是ON了。这里建议你不论使用MySQL的哪个版本,都将这个值设置为ON。因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过drop table命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。
所以将innodb_file_per_table i 设置为 设ON,是推荐做法,我们接下来的讨论都是基于这个 ,设置展开的。

我们在删除整个表的时候,可以使用drop table命令回收表空间。但是,我们遇到的更多的删除数据的场景是删除某些行,这时就遇到了我们文章开头的问题:表中的数据被删除了,但是表空间却没有被回收。

检查是否开启独立表空间

show variables like '%per_table%';

设置独立表空间
innodb_file_per_table 可通过SET GLOBAL动态的修改为ON或OFF,也可以在my.cnf中做永久性修改,在my.cnf中修改后生效的话需要重启mysqld服务。
1.innodb_file_per_table在my.cnf中[mysqld]下设置,开启方法

innodb_file_per_table=1

关闭独享表空间
innodb_file_per_table=0关闭独立的表空间

show variables like '%per_table%';

如果启用了innodb_file_per_talbe参数,需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他数据如:回滚信息、插入缓冲索引页、系统事物信息、二次写缓冲(Double write buffer)等还是放在原来的共享表空间内。同时说明了一个问题:即使启用了innodb_file_per_table参数共享表空间还是会不断的增加其大小的。
注意:动态修改后仅对后续操作生效,如原来为共享表空间,动态修改为独立表空间后仅新建的表为独立表空间。

想要将共享表空间转化为独立表空间有两种方法:
1.先逻辑备份,然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后将逻辑备份导入即可。
2.修改配置文件my.cnf中的参数innodb_file_per_table参数为1,重启服务后将需要修改的所有innodb表都执行一遍:alter table table_name engine=innodb;
使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间

3. MySQL删除数据流程

我们先再来看一下InnoDB中一个索引的示意图。

如果删除某条记录,则该记录空间可以复用,比如我们要删掉R4这个记录,InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时,可能会复用这个位置。但是,磁盘文件的大小并不会缩小。

如果删除整个数据页,则整个数据页就可以被复用。但是,数据页的复用跟记录的复用是不同的。 数记录的复用,只限于符合范围条件的数据。比如上面的这个例子,R4这条记录被删除后,如果插入一个ID是400的行,可以直接复用这个空间。但如果插入的是一个ID是800的行,就不能复用这个位置了。

而当整个页从B+树里面摘掉以后,可以复用到任何位置。以图1为例,如果将数据页page A上的所有记录删除以后,page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候,page A是可以被复用的。如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。

进一步地,如果我们用delete命令把整个表的数据删除呢?结果就是,所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小。所以delete命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也就是说,通过delete命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。

4. MySQL数据页空洞问题

实际上,不止是删除数据会造成空洞,插入数据也会。如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。
假设图1中page A已经满了,这时我要再插入一行数据,会怎样呢?

可以看到,由于page A满了,再插入一个ID是550的数据时,就不得不再申请一个新的页面page B来保存数据了。页分裂完成后,page A的末尾就留下了空洞(注意:实际上,可能不止1个记录的位置是空洞)。

另外,更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。不难理解,这也是会造成空洞的。也就是说,经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。而重建表,就可以达到这样的目的。

MySQL空洞问题解决方案
重建表,试想一下,如果你现在有一个表A,需要做空间收缩,为了把表中存在的空洞去掉,你可以怎么做呢?

你可以新建一个与表A结构相同的表B,然后按照主键ID递增的顺序,把数据一行一行地从表A里读出来再插入到表B中。由于表B是新建的表,所以表A主键索引上的空洞,在表B中就都不存在了。显然地,表B的主键索引更紧凑,数据页的利用率也更高。如果我们把表B作为临时表,数据从表A导入表B的操作完成后,用表B替换A,从效果上看,就起到了收缩表A空间的作用。

这里,你可以使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前,这个命令的执行流程跟我们前面描述的差不多,区别只是这个临时表B不需要你自己创建,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

显然,花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表A的话,就会造成数据丢失。因此,在整个DDL过程中,表A中不能有更新。也就是说,这个DDL不是Online的。

而在MySQL 5.6 M 版本开始引入的 版 Online DDL O ,对这个操作流程做了优化。
我给你简单描述一下引入了Online DDL之后,重建表的流程:

  • 建立一个临时文件,扫描表A主键的所有数据页;
  • 用数据页中表A的记录生成B+树,存储到临时文件中;
  • 生成临时文件的过程中,将所有对A的操作记录在一个日志文件rowlog中,对应的是图中state2的状态;
  • 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应的就是图中state3的状态;
  • 用临时文件替换表A的数据文件。

可以看到,与图3过程的不同之处在于,由于日志文件记录和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表A做增删改操作。这也就是Online DDL名字的来源。

确实,图4的流程中,alter语句在启动的时候需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。

为什么要退化呢?为了实现Online,MDL读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做DDL。而对于一个大表来说,Online DDL最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个DDL过程来说,锁的时间非常短。对业务来说,就可以认为是Online的。

需要补充说明的是,上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗IO和CPU资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用GitHub开源的gh-ost来做。

到此这篇关于MySQL数据库表空间回收的解决的文章就介绍到这了,更多相关MySQL 表空间回收内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql在线回收undo表空间实战记录

    1 Mysql5.6 1.1 相关参数 MySQL 5.6增加了参数innodb_undo_directory.innodb_undo_logs和innodb_undo_tablespaces这3个参数,可以把undo log从ibdata1移出来单独存放. innodb_undo_directory:指定单独存放undo表空间的目录,默认为.(即datadir),可以设置相对路径或者绝对路径.该参数实例初始化之后虽然不可直接改动,但是可以通过先停库,修改配置文件,然后移动undo表空间文件的方

  • 浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • MySQL数据库表空间回收的解决

    目录 1. MySQL表空间回收 2. MySQL表空间设置 3. MySQL删除数据流程 4. MySQL数据页空洞问题 1. MySQL表空间回收 我们经常会发现一个问题,就是把表数据删除以后发现,数据文件大小并没有变化,这就是标题中所说的MySQL数据库表空间回收问题. 这里,我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论.一个InnoDB表包含两部分,即:表结构定义和数据.在MySQL 8.0版本以前,表结构是存在以.frm为后缀的文件里.而MySQL 8.0版本,则已经允

  • MYSQL数据库表结构优化方法详解

    本文实例讲述了MYSQL数据库表结构优化方法.分享给大家供大家参考,具体如下: 选择合适的数据类型 1.使用可以存下你的数据的最小的数据类型 2.使用简单的数据类型.Int要比varchar类型在mysql处理上简单 3.尽可能的使用not null定义字段 4.尽量少用text类型,非用不可时最好考虑分表 使用int来存储日期时间,利用FROM_UNIXTIME()[将int类型时间戳转换成日期时间格式],UNIX_TIMESTAMP()[将日期时间格式转换成int类型]两个函数进行转换 使用

  • Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法

    本文实例讲述了Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法.分享给大家供大家参考,具体如下: #!/usr/bin/env python # -*- coding:utf-8 -*- """ Purpose: 生成日汇总对账文件 Created: 2015/4/27 Modified:2015/5/1 @author: guoyJoe """ #导入模块 import MySQLdb import time impor

  • 88秒插入1000万条数据到MySQL数据库表的操作方法

    我用到的数据库为,mysql数据库5.7版本的 首先自己准备好数据库表 其实我在插入1000万条数据的时候遇到了一些问题,现在先来解决他们,一开始我插入100万条数据时候报错,控制台的信息如下: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4232009 > 4194304). You can change this value on the server by setting the max_allo

  • MySQL InnoDB表空间加密示例详解

    前言 从 MySQL5.7.11开始,MySQL对InnoDB支持存储在单独表空间中的表的数据加密 .此功能为物理表空间数据文件提供静态加密.该加密是在引擎内部数据页级别的加密手段,在数据页写入文件系统时加密,加密用的是AES算法,而其解密是在从文件读到内存中时进行. 1 配置加密插件 1.1 修改配置文件 在mysql配置文件[mysqld]x项中添加如下内容 plugin_dir=/usr/local/mysql5.7/lib/mysql/plugin # 插件路径,根据实际情况修改 ear

  • MySQL的表空间是什么

    今天我要跟你分享的话题是:"大家常说的表空间到底是什么?究竟什么又是数据表?" 这其实是一个概念性的知识点,当作拓展知识.涉及到的概念大家了解一下就好,涉及的参数,留个印象就好. 一.什么是表? 但凡是用过MySQL都知道,直观上看,MySQL的数据都存在数据表中. 比如一条Update SQL: update user set username = '白日梦' where id = 999; 它将user这张数据表中id为1的记录的username列修改成了'白日梦' 这里的user

  • MySQL数据库表被锁、解锁以及删除事务详解

    目录 背景 故障追踪 解决方案 第一步:查看表使用 第二步:查看进程 第三步:查看当前运行的所有事务 第四步:查看当前出现的锁 第五步:查询锁等待的对应关系 第六步:kill掉事务 MySQL的锁 MySQL锁表场景 Waiting for table metadata lock 场景一:长事务运行,阻塞DDL,继而阻塞所有同表的后续操作. 场景二:为提交事务,阻塞DDL,继而阻塞所有同表的后续操作. 场景三:显式事务失败操作获得锁,未释放 小结 总结 背景 在程序员的职业生涯中,总会遇到数据库

  • 利用Python批量导出mysql数据库表结构的操作实例

    目录 前言 解决方法 1. mysql 数据库 表信息查询 2.连接数据库代码 3.数据查询处理代码 3.0 配置信息 3.1查询数据库表 3.2 查询对应表结构 3.3 pandas进行数据保存导出excel 补充:python脚本快速生成mysql数据库结构文档 总结 前言 最近在公司售前售后同事遇到一些奇怪的需求找到我,需要提供公司一些项目数据库所有表的结构信息(字段名.类型.长度.是否主键.***.备注),虽然不是本职工作,但是作为python技能的拥有者看到这种需求还是觉得很容易的,但

  • MySQL数据库表的合并与分区实现介绍

    目录 创建数据表 数据库表合并 数据库表分区 创建数据表 创建数据表的,使用字符串应该遵循的原则 从速度方面考虑,要选择固定的列,可以使用CHAR类型 要节省空间,使用动态的列,可以使用VARCHAR类型 要将列中的内容限制为一种选择,可以使用ENUM类型 允许在一列中有多个条目,可以使用SET类型 如果要搜索的内容不区分大小写,可以使用TEXT类型 如果要搜索的内容区分大小写,可以使用BLOB类型 创建数据表其实就是在已经创建好的数据库中建立新表. 数据表属于数据库,在创建数据表之前,应该使用

随机推荐