MySQL-13：数据库表回收

妖狐艹你老母 2022-12-25 01:48 110阅读 0赞

#### 12.1表结构 ####

一个InnoDB表包含**表结构定义**和**数据**。

**表结构定义：** 在MySQL 8.0 版本之前，表结构是存储在以.frm为后缀的文件里，在MySQL 8.0 之后，表结构定义在系统数据表中。

**表数据：** 表数据即可以存在系统共享表空间中，也可以是单独文件。这个行为由innodb\_file\_per\_table参数控制，当参数为**on**时，表数据存在以.ibd为后缀的文件中；当参数为**off**时，表数据存在系统共享空间。

在MySQL 5.6.6版本之后，参数默认值为**on**。以下的讨论基于**innodb\_filr\_per\_table = ON**。

#### 12.2数据表删除流程 ####

我们先回忆一下，以索引构建的树是怎样的？差不多是以下情况，现在假设我们要来删除ID=300的数据。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

**ID = 300数据删除流程：**

1.  根据索引找到ID = 300的数据行。
2.  将找到的数据行标记为删除，完事。

数据行标记为删除之后又拿来干嘛呢？复用啊，当我们插入一个ID=400，我们就将数据插入到刚刚ID = 300的数据位置。

**如果我们删除掉一个数据页上面的所有数据，数据页又会怎样呢？**

数据页也会拿来达到复用的目的，不过它可以复用到任意位置，没有顺序上面的限制，不想上面数据行，当插入的数据300<ID<500才会复用那一行，例如插入数据ID=700就不能复用那一行。

还有一种情况：如果相邻数据页的利用率较低，系统就会把两个数据页上的数据合并到一个数据页中，另一个数据页标记为可复用。

**总结：** 也就是执行delete命令会导致数据“空洞”，某行/某页数据标记为删除，但实际的磁盘空间却没有释放，文件大小没有改变。

实际上，不仅删除数据会造成数据空洞，**插入数据**也会。如果插入数据是按照索引递增插入还好，但是如果数据是随机插入的呢？，那就有可能造成索引的数据分裂，看下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0TEU7sZx-1606671165923)(D:\\计算机笔记\\mysql\\images\\25.jpg)\]

Page A 已满，不得已要申请Page B，页分裂完成后，导致Page A的末尾产生数据空洞。另外，在索引上更新值，可以理解为先删除一个旧值，再插入一个新值，因为插入的新值不一定会在原有位置上，这些也会造成数据空洞。

#### 12.3 重建表 ####

想要修复那些数据空洞，我们就要重建表，让索引变得更加紧凑。

重建表的过程很简单，我们可以自己稍稍脑补一下：

1.  新建一个临时表B
2.  将待重建表A的数据按主键递增的方式复制到表B
3.  用表B来替换表A

这样子，表的重建就算是好了，我们可以用`alter table tableName engine = InnoDB;`命令来重建表。在MySQL 5.5版本之前，流程就是我们上面所想的那样，执行命令后，MySQL 会帮我们完成创建表B，复制数据，替换表等一系列操作。

但是，这样子会存在个问题：重建过程中写入到表A中的数据可能会丢失。

MySQL 5.6版本之后，对操作流程进行优化，实现online操作：

1.  新建临时表B
2.  扫描表A的所有数据页，将表A数据复制到表B
3.  数据复制过程中，生成日志文件row log来记录复制过程中的对表A数据的写入事件。
4.  将row log中记录的操作时间应用到表B
5.  表B替换表A

这个有个点：重建表的语句属于DDL，在开始它会获得表级锁MDL写锁，但在复制数据过程中，它会退化为MDL读锁，不会影响数据的增删改查，要不然还怎么online呢。

**练习问题：**

1.  数据表个结构是怎么样？版本更迭有没有什么变化？
2.  数据删除流程？
3.  数据空洞又是怎么一回事？有几种情况会触发？
4.  重建表的命令？重建表流程在版本更迭之间的变化？
5.  online DDL和inplace的概念？

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/3410382cb23c4fae80cc18dcab218377.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/ecf88b7258304f298401dc7b7c672531.png