InnoDB 表包含两部分,即:表结构定义和数据。
在 MySQL 8.0 版本以前,表结构是存在以 .fm 为后缀的文件里。而 MySQL 8.0 版本,已经允许把表结构定义放在系统数据表中了。因为表结构占用空间很小,所以我们主要来讨论表数据。
参数 inoodb_file_pre_table
这个参数可以控制表数据是存在共享表空间里,还是单独文件。
- 这个参数设为 OFF 表示,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
- 这个参数设为 ON 表示,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始,它的默认值是 ON。无论用什么版本,都建议设置为 ON,更容易管理。而且当不用这张表时,可以用 drop table
这个命令,直接删掉这个文件。如果在共享表空间中,即使执行这个命令,空间也不会回收。
数据的删除流程
InnoDB 里的数据都是用 B+ 树的结构组织的。
当我们要删掉 R4 这个记录,InnoDB 会把这个记录标记为删除。如果以后要插入一个 ID 在 300 和 600 之间的记录时,就可以复用这个位置。但是,文件大小不会变。
那如果我们删掉一整个数据页上的记录呢?答案是,整个数据页就可以复用了。
但是,数据页的复用和记录的复用是不同的。
-
记录的复用必须是符合范围条件的复用,就想上面删除掉 R4 后,如果想插入一个 ID 为 800 的数据,就不能复用这个位置。
-
数据页的复用就可以是任意位置。如果上图中 pageA 上的记录被删除后,pageA 就会被标记为可复用。如果这时要插入一条 ID 为 50 的记录,pageA 是可以复用的。
如果相邻的两个数据页利用率都很小,系统会把这两个页的数据合并到一个页上面,另一个页就会被标记为可复用。
如果用 delete 命令把整个表的数据删除呢?答案是,所有的数据页都会被标记为可复用。但是磁盘上的文件不会变小。
所以,delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,也就是说,delete 命令不能回收表空间的。这些被标记的空间,看起来就像“空洞”。
不止是删除数据会产生空洞,插入数据也会。
如果数据按照索引的递增顺序添加的,那么索引时紧凑的。但是数据是随机插入的,就可能造成索引的数据页分裂。
以下是插入数据导致页分裂。
注意:实际上,当分裂完后,可能不止一个空洞。
另外,更新索引上的值,可以理解为,删除一个旧值,再插入一个新值。这也会造成空洞。
如果能把这些空洞去掉,就能达到收缩表空间的目的。重建表就可以达到这个目的。
重建表
当表 A 有很多空洞,我们可以按照表 A 的结构新建一个表 B,然后把 A 的数据全部复制到 B 上,再用表 B 代替表 A 。从效果上看,就达到了收缩表 A 的目的。
我们可以使用 alter table A engine=InnoDB
命令来重建表。在 MySQL 5.5 之前,这个命令的过程,和上面讲的差不多,都是由 MySQL 自动完成的。
显然,最花时间的就是复制表数据了,而且这个过程表 A 不能有更新。也就是,这个 DDL 不是 Online 的。
而 MySQL 5.6 版本开始做了优化,引入了 Online DDL。
这个时候的操作见如下:
- 建立一个临时文件,扫描表 A 的主键所有数据页;
- 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
- 在生成临时文件的过程中,将对 A 的所有操作记录在日志文件(row log)中,对应的是图中 state2 的状态
- 临时文件生成后,将日志文件中操作应用到临时文件,得到一个逻辑数据上与表 A 相同的临时数据文件(state3)
- 用临时文件代替表 A 的数据文件。
在 Online DDL 中,允许对表 A 做增删改查操作。
DDL 之前是要拿 MDL 的写锁的,这样还能叫 Online DDL 吗?
确实,上图的流程中,alter语句在启动的时候需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢?为了实现Online,MDL读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做DDL。
而对于一个大表来说,Online DDL最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个DDL过程来说,锁的时间非常短。对业务来说,就可以认为是Online的。
需要补充说明的是,上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗IO和CPU资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用GitHub开源的gh-ost来做。
Online 和 inplace
在最初的 DDL 重建表中,把数据导出来存放的位置叫 tmp_table。这是个临时表,是在 server 层创建的。
在 Online DDL 中,重建出来的数据是放在“tmp_file”里的,这个是在 InnoDB 内部创建出来的。整个过程也是在 InnoDB 内部完成的。对于 server 来说,没有移动数据,是个“原地操作”,这就是“inplace”的来源。
从 MySQL 5.6 版本开始,alter table t engine=InnoDB
这个命令默认的就是 Online DDL 的流程了。
Online DDL 是可以考虑在业务的低峰期使用的,而 MySQL 5.5 版本之前,这个命令会阻塞 DML 的。