文章目录
MySQL学习笔记-如何有效的回收表空间
1.笔记图
2.InnoDB 表说明
- 表结构:
- 在
MySQL 8.0
版本以前,表结构是存在以.frm
为后缀的文件里 MySQL 8.0
版本,则已经允许把表结构定义放在系统数据表中了- 表数据:
- 表数据既可以存在共享表空间里,也可以是单独的文件
- 参数:innodb_file_per_table
- 这个参数设置为
OFF
表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起 - 这个参数设置为
ON
表示的是,每个InnoDB
表数据存储在一个以.ibd
为后缀的文件中 - 从
MySQL 5.6.6
版本开始,它的默认值就是ON
了
- 这个参数设置为
3.数据库表的空间回收
- 表空间回收前提:
innodb_file_per_table
参数设置为ON
,因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过drop table
命令,系统就会直接删除这个文件- 而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的
- 表空间回收遇到的问题:
- 表中的数据被删除了,但是表空间却没有被回收
4.数据删除流程
InnoDB
里的数据都是用B+
树的结构组织的,数据是按页存储的- 删除记录:假设一个数据
ID=500
位于ID = 300
和ID=600
之间,我们要删掉ID=500
这个记录,InnoDB
引擎只会把ID=500
这个记录标记为删除,如果之后要再插入一个ID
在300
和600
之间的记录时,可能会复用这个位置,但是,磁盘文件的大小并不会缩小 - 删除数据页:如果我们删掉了一个数据页上的所有记录,整个数据页就可以被复用了
- 数据页的复用跟记录的复用是不同的
- 记录的复用:只限于符合范围条件的数据,例如上面描述提到的删除 ID = 500 这条记录,之后插入 ID = 800的就不能
- 数据页的复用
- 当整个页从
B+
树里面摘掉以后,可以复用到任何位置 - 如果将数据页
page A
上的所有记录删除以后,page A
会被标记为可复用。这时候如果要插入一条ID=50
的记录需要使用新页的时候,page A
是可以被复用的 - 如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用
- 如果我们用
delete
命令把整个表的数据删除呢?结果就是,所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小
5.数据 “空洞”
- 删除数据:
- 通过
delete
命令是不能回收表空间的,磁盘文件的大小是不会变的 - 被删除的记录或者数据页可以被复用,而没有被使用的空间,看起来就像是
空洞
- 插入数据:
- 如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。
page A
已经满了,这时我要再插入一行数据,就会申请一个新的页面page B
来保存数据了,页分裂完成后,page A
的末尾就留下了空洞(注意:实际上,可能不止1
个记录的位置是空洞)- 更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。不难理解,这也是会造成空洞的
6.如何去掉表数据 “空洞”
- 重建表:
- 可以新建一个与表
A
结构相同的表B
,然后按照主键ID
递增的顺序,把数据一行一行地从表A
里读出来再插入到表B
中 - 由于表
B
是新建的表,所以表A
主键索引上的空洞,在表B
中就都不存在了 - 如果我们把表
B
作为临时表,数据从表A
导入表B
的操作完成后,用表B
替换A
,从效果上看,就起到了收缩表A
空间的作用 - alter table A engine=InnoDB:
- 在
MySQL 5.5
版本之前,这个命令的执行流程跟上面描述的差不多,区别只是这个临时表B
不需要你自己创建,MySQL
会自动完成转存数据、交换表名、删除旧表的操作。 MySQL 5.6
版本开始引入的Online DDL
- 建立一个临时文件,扫描表
A
主键的所有数据页 - 用数据页中表
A
的记录生成B+
树,存储到临时文件中 - 生成临时文件的过程中,将所有对
A
的操作记录在一个日志文件(row log)
中 - 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表
A
相同的数据文件 - 用临时文件替换表
A
的数据文件
- 建立一个临时文件,扫描表
7.Online DDL
alter
语句在启动的时候需要获取MDL
写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了- 为什么要退化呢?:为了实现
Online
,MDL
读锁不会阻塞增删改操作 - 为什么不干脆直接解锁呢?:
- 为了保护自己,禁止其他线程对这个表同时做
DDL
- 对于一个大表来说,
Online DDL
最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个DDL
过程来说,锁的时间非常短。对业务来说,就可以认为是Online
的 - 上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗
IO
和CPU
资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用GitHub
开源的gh-ost
来做 - Online 和 inplace:
- 整个
DDL
过程都在InnoDB
内部完成,对于server
层来说,没有把数据挪动到临时表,是一个原地
操作,这就是inplace
名称的来源 DDL
过程如果是Online
的,就一定是inplace
的- 反过来未必,也就是说
inplace
的DDL
,有可能不是Online
的。截止到MySQL 8.0
,添加全文索引(FULLTEXT index)
和空间索引(SPATIAL index)
就属于这种情况
8.表操作命令说明
- alter table:从
MySQL 5.6
版本开始,alter table t engine = InnoDB (recreate)
,默认的就是上上述流程了 - analyze table:对表的索引信息做重新统计,没有修改数据,这个过程中加了
MDL
读锁 - optimize table:等于
recreate+analyze
9.课后问题
- 问题描述
假设现在有人碰到了一个"想要收缩表空间,结果适得其反"的情况,看上去是这样的:
1.一个表 t 文件大小为 1TB;
2.对这个表执行 alter table t engine=InnoDB;
3.发现执行完成后,空间不仅没变小,还稍微大了一点儿,比如变成了 1.01TB。
你觉得可能是什么原因呢 ?
- 参考答案
这个表,本身就已经没有空洞的了,比如说刚刚做过一次重建表操作。在DDL
期间,如果刚好有外部的DML
在执行,这期间可能会引入一些新的空洞,完成之后新生成的表空间就可能会变大
扫码关注