MySQL -- 空间回收

我就是我 2023-02-27 10:46 21阅读 0赞

## InnoDB的物理存储 ##

1.  InnoDB表的组成：表结构（frm）+数据（ibd）
    
    1.  MySQL 8.0开始，允许将表结构定义（占用空间很小）放在系统数据表中
2.  控制参数innodb\_file\_per\_table
    
    1.  ON：每个InnoDB表数据存储在一个以.ibd为后缀的文件中，推荐。更容易管理，**DROP TABLE会直接删除这个文件**
    2.  OFF：InnoDB表数据存储在共享表空间，DROP TABLE，空间也是不会回收的  
        `SHOW VARIABLES LIKE '%innodb_file_per_table%';`来查看innodb\_file\_per\_table的参数值。

## 文件空洞 ##

空洞 ：经过大量增删改的表，都会存在空洞

### 删除 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]

1.  如果删掉R4，InnoDB只会将R4标记为删除，如果再插入300~600的记录时，可能会复用这个位置，但磁盘文件不会缩小。**记录的复用，仅限于符合范围条件的数据**
2.  如果删除了一个数据页上的所有记录，那么整个数据页都可以被复用的
    
    1.  当整个页从B+树里摘除后，可以被复用到任何位置
    2.  如果将page A上的所有记录删除后，page A会被标记为可复用
    3.  当插入ID=50的记录时，需要申请新页时page A可以被复用
3.  如果相邻的两个数据页利用率都很小。系统会把这两个数据页上的数据合并到其中一个页上，另一个数据页就会被标记为可复用
4.  如果通过DELETE命令删除整个表，那么所有的数据页都会被标记为可复用，但磁盘上的文件同样不会变小
5.  TRUNCATE = DROP + CREATE

### 插入 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]

1.  如果数据是随机插入的，就有可能造成索引的数据页分裂
2.  page A已满，如果再插入ID=550的数据，就必须申请一个新的页面page B来保存数据，导致页分裂，留下了空洞

### 更新 ###

更新索引上的值，等同于先逻辑删除旧值后再插入新值，同样也会造成空洞

### 重建表 ###

#### 逻辑过程 ####

1.  新建一个与表A结构相同的表B
2.  按照主键递增的顺序，把表A中的数据一行一行读出，然后再插入表B，**表B的主键索引更紧凑，数据页的利用率也更高**
3.  表B作为临时表，数据从表A导入到表B，然后用表B替换A

#### 重建命令 ####

`ALTER TABLE A ENGINE=InnoDB;`  
ALTER TABLE默认会提交前面的事务

##### mysql5.5以前 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70]

1.  与上述的逻辑过程类似，MySQL自动完成转存数据，交换表名和删除旧表等操作
2.  时间消耗最多的是往临时表（Server层）插入数据的过程，在这个过程中，如果新数据要写入表A，就会造成数据丢失
3.  因此整个DDL过程中，表A是不能执行DML的，即不是Online的
4.  MySQL 5.6引入Online DDL

##### mysql5.6以后 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 1]

1.  建立一个临时文件（InnoDB内部），扫描表A主键的所有数据页
2.  用数据页中表A的记录生成B+树，存储到临时文件
3.  state 2（日志）：生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中
4.  state 3（重放）：临时文件生成后，将日志文件的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件
5.  用最新的临时文件替换表A的数据文件

##### MDL锁 #####

1.  ALTER语句在启动时需要获取MDL写锁，但会在真正拷贝数据之前退化为MDL读锁。**MDL读锁不会阻塞其他线程对这个表的DML，同时又能阻塞其他线程对这个表的DDL**
2.  对一个大表来说，Online DDL最耗时的过程是拷贝数据到临时表的过程，期间是可以接受DML。**相对于整个DDL过程来说，锁的时间非常短，对业务来说，可以认为是Online**

##### 性能消耗 #####

1.  重建表会扫描原表数据和构建临时文件（或临时表）
2.  对于大表来说，重建表会非常消耗IO和CPU资源
3.  推荐工具：gh-ost

#### Online + Inplace ####

1.  tmp\_table是一个临时表，在Server层创建的
2.  tmp\_file是临时文件，在InnoDB内部创建的，整个DDL过程都在InnoDB内部完成。对于Server层来说，并没有把数据挪动到临时表，是个原地操作（Inplace）
3.  DDL过程如果是Online的，那一定是Inplace的，反之不成立。`ALTER TABLE t ADD FULLTEXT(field_name);`是Inplace的，但会阻塞DML（非Online）

ALTER TABLE A ENGINE=InnoDB;
    等同于
    mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE;
    Query OK, 0 rows affected (0.68 sec)
    Records: 0  Duplicates: 0  Warnings: 0

与Inplace对应的是Copy，强制拷贝表到Server层

mysql> ALTER TABLE t ENGINE=InnoDB, ALGORITHM=COPY;
    Query OK, 100000 rows affected (1.46 sec)
    Records: 100000  Duplicates: 0  Warnings: 0

#### ALTER + ANALYZE + OPTIMIZE ####

1.  ALTER TABLE t ENGINE=InnoDB：重建表
2.  ANALYZE TABLE t：触发表索引信息的重新采样统计
3.  OPTIMIZE TABLE t：ALTER + ANALYZE

PS:参考资料 《MySQL实战45讲》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/43e10947b4a54404ae3cb7cf30cdccc4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20230209/1de1fe410b2440feac7446dba89f6289.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70]: /images/20230209/a607f4b910ba4fceb24d23ce27dd7072.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 1]: /images/20230209/32ce30d7da9f4de8ab571fe955efc15c.png