大家好,又见面了,我是你们的朋友全栈君。
此文为极客时间MySQL实战45讲第13节的笔记
表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数 innodb_file_per_table
控制的:
一般情况下,表单独存放的时候,可以通过 drop table 语句直接删除,而如果放在共享表空间,及时删除了表也无法回收空间。
一般情况下,很少有直接连结构带数据直接删掉表的情况,更多时候是只删除一些数据,但是这样往往会遇到这样的问题:表中的数据被删除了,但是表空间却没有被回收。
实际上,这与 innodb 中数据都以 B+ 树的结构存储有关:
以上图为例,如果我们删除 R4 这条数据,则 innodb 会把 R4 这个位置标记为删除,之后如果插入一个在 300~600 的记录的时候,就会直接使用现在 R4 的位置。同理,如果我们清除这个 PageA,那么这个数据页都会被标记删除,等到下一个数据页加载的时候就可以直接使用这个空间。而当两个相邻的数据页上被删除了记录很多,也就是页的利用率都很低的时候,系统就会自动合并两个页的数据,并且标记其中一个为可复用。
值得一提的是,记录的空间被复用,必须限定位置,比如 R4 的空间被复用,就不能插入300 ~ 600之外的数据;而数据页的空间则可以被任意一页新加载的页复用。
综上所述,不难理解,我们使用 delete 删除的数据只是把这块空间标记为可复用,是一种逻辑上的删除,并没有实际减少磁盘空间的占用。
实际上,不止是删除数据会造成空洞,插入数据也会。
如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。
假如 PageA 已满,再插入一条550,PageA 就会不得不分离成两页,这就是页的分裂。当分裂完以后,PageA 就会留下一个空洞,新页 PageB 一样没有占满。
另外,更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。不难理解,这很有可能破坏了索引的有序性,也是会造成空洞的
也就是说,经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。
而重建表,就可以达到这样的目的。
为了去掉表中的空洞,我们可以新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据一行一行地从表 A 里读出来再插入到表 B 中。
由于表 B 是新建的表,所以表 A 主键索引上的空洞,在表 B 中就都不存在了。显然地,表 B 的主键索引更紧凑,数据页的利用率也更高。如果我们把表 B 作为临时表,数据从表 A 导入表 B 的操作完成后,用表 B 替换 A,从效果上看,就起到了收缩表 A 空间的作用。
可以使用 alter table A engine=InnoDB
命令来重建表。在 MySQL 5.5 版本之前,这个命令的执行流程跟我们前面描述的差不多,mysql 会自动完成全部操作。
但是这个 DDL 语句不是 online 的,也就说,如果过程中有新的数据往 A 表插入,有可能不会被读到并且一起转移到 B 表,导致最后丢失更新。因此,在 5.6 版本以后,引入的 online DDL 对这个流程做了优化。
新的流程如下:
因为过程中对表 A 的操作最后都会通过日志同步到临时文件,所以在整个 DDL 执行的过程不会影响对表 A 的增删改查。
我们可以注意到,5.6 之前是创建临时表,而 5.6 以后则是创建了临时文件。这两者的区别在于:临时表是创建在 server 层的,临时文件是创建在 innodb 内部的,整个 DDL 过程都在 innodb 内部完成。对于 server 层来说,后者没有把数据移到临时表,相当于一个原地操作,所以叫 inplace。
也就是说,alter table t engine=InnoDB
这个 DDL 实际上相当于:
alter table t engine=innodb,ALGORITHM=inplace;
相对于创建临时表:
alter table t engine=innodb,ALGORITHM=copy;
数据库表文件可以存在共享表空间里;也可以单独以 .ibd 文件存储。共享表空间中的表使用 drop table 无法清除。通过innodb_file_per_table
参数的 off/on 可以控制存放策略。mysql 5.6.6 以后默认为 on。
innodb 中的删除只是标记空间为可复用,没有实际删除数据。当非有序的插增删改影响了索引上的数据的有序性时,可能产生“空洞”降低空间利用率。当内存中相邻表空间利用率都很低的时候,可能引起页的合并,反之,过满或者无序插入会引发页分裂。
通过拷贝数据到临时表,再有序将数据插入原表,即重建表可以收缩空间。可以使用 alter table A engine=InnoDB
的 sql 实现。
5.5 之前版本拷贝过程中原表的正删改可能在重建后丢失,5.6 之后将对原表的正删改写入临时日志后再同步,实现了 online DDL。
5.6 之前重建操作为在server 层创建临时表,5.6 之后为在 innodb 内创建临时文件,他们的写法如下:
# 默认为inplace
alter table t engine=innodb(,ALGORITHM=inplace);
alter table t engine=innodb,ALGORITHM=copy;
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/170784.html原文链接:https://javaforall.cn