MySQL系列(六)——空间回收

这篇旨在说明为什么简单地删除表数据达不到表空间回收的效果,然后再介绍正确回收空间的方法。

我的新书《LangChain编程从入门到实践》 已经开售!推荐正在学习AI应用开发的朋友购买阅读!
LangChain编程从入门到实践

InnoDB的物理存储

  1. InnoDB表的组成:表结构(frm)+数据(ibd)
    • MySQL 8.0开始,允许将表结构定义(占用空间很小)放在系统数据表中
  2. 控制参数innodb_file_per_table
    • ON:每个InnoDB表数据存储在一个以.ibd为后缀的文件中,更容易管理,DROP TABLE会直接删除这个文件
    • OFF:InnoDB表数据存储在共享表空间, DROP TABLE空间也是不会回收的
1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

文件空洞

空洞:可以被复用但没有被使用的空间,经过大量增删改的表,都会存在空洞

数据删除流程

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

插入

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

更新

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

重建表

逻辑过程:

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

重建命令

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

Online DDL,重建表的流程:

  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)
1
2
3
4
5
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层

1
2
3
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 tALTER + ANALYZE

问题思考

假设现在有人碰到了一个“想要收缩表空间,结果适得其反”的情况,看上去是这样的:
一个表t文件大小为1TB;
对这个表执行 alter table t engine=InnoDB;
发现执行完成后,空间不仅没变小,还稍微大了一点儿,比如变成了1.01TB。
可能是什么原因呢 ?

在重建表的时候,InnoDB不会把整张表占满,每个页留了1/16给后续的更新用。
将表t重建一次,插入一部分数据,但是插入的这些数据本来用掉了一部分的预留空间;这种情况下,再重建一次表t,就可能会出现问题中的现象。

MySQL系列(六)——空间回收

https://liduos.com/mysql-reclaim-space.html

作者

莫尔索

发布于

2020-10-14

更新于

2024-05-24

许可协议

评论