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

阳光穿透心脏的1/2处 2024-03-23 22:05 46阅读 0赞

![8f4f8fcea527927b04cb1420c54dc32e.jpeg][]

本文主要介绍了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命令回收表空间**。但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

**检查是否开启独立表空间**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>show variables </code><code>like</code> <code>'%per_table%'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>innodb_file_per_table=1</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**关闭独享表空间**  
innodb\_file\_per\_table=0关闭独立的表空间

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>show variables </code><code>like</code> <code>'%per_table%'</code><code>;</code></p> </td> 
  </tr> 
 </tbody> 
</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中一个索引的示意图。

![727478e072b55a017d14993682f9e53b.jpeg][]

如果删除某条记录，则该记录空间可以复用，比如我们要删掉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已经满了，这时我要再插入一行数据，会怎样呢？

![588d0d971e375cb4e0179b2402c3411b.jpeg][]

可以看到，由于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会自动完成转存数据、交换表名、删除旧表的操作。

![38e3df09936a3a5d6f81918fb0ce5321.jpeg][]

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

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

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

![737fa7ec541ea9c9773c4e2c7134d650.jpeg][]

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

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

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

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

到此这篇关于MySQL数据库表空间回收的解决的文章就介绍到这了,希望可以帮到你

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[8f4f8fcea527927b04cb1420c54dc32e.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d121a29bf2e24226bb59bce5278415e8.png
[727478e072b55a017d14993682f9e53b.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a912e871e8904e0e8f10addbc7f48ab5.png
[588d0d971e375cb4e0179b2402c3411b.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/caaeb5e4add242a190851305c66c1992.png
[38e3df09936a3a5d6f81918fb0ce5321.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/54e06d5e26e64ca5871d0989598cd553.png
[737fa7ec541ea9c9773c4e2c7134d650.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/c76384adcb0743a29ebb2bb4eed9c15c.png
[Link 1]: https://www.weidianyuedu.com