Mysql面试题_表空间05


文章目录

  • Mysql面试题_表空间05
  • 1.Innodb_file_per_table 参数有什么作用?
  • 2.Mysql数据删除流程是怎样的?
  • 3.哪些情况会导致Mysql的数据空洞?
  • 4.重建表的执行步骤是什么?
  • 5.MySQL 5.6 版本开始引入的 Online DDL,重建表的流程是怎样的?
  • 6.重建表的三种方式是什么?有什么区别?
  • 7.inplace 跟 Online 的异同?
  • 8.什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大?


1.Innodb_file_per_table 参数有什么作用?

该参数是用于控制InnoDB表数据存储位置的参数。

  • Innodb_file_per_table=off: 表示表数据存放在系统共享表空间,和数据字典存放在一起。
  • Innodb_file_per_table=on: 表示每个innodb表数据存储在一个以.ibd为后缀的文件中。

从 MySQL 5.6.6 版本开始,innodb_file_per_table的默认值就是 ON 了。


2.Mysql数据删除流程是怎样的?

InnoDB里的数据都是用B+树结构组织的。

对于需要删除的记录,Mysql采用标记清除和可复用的状态来记录;并不会清除磁盘文件上的表空间。

  • 对于单条记录,会被标记为删除;但会保留该位置,等待下一个满足条件的记录写入;
  • 对于单个数据页,整页会被标记为删除;提供给需要使用的新数据页复用
  • 对于delete整表而言;会将该表的所有数据页标记为删除;这些被标记清除的空间可以被复用。
  • 记录的复用,只限于符合范围条件的数据。为了维护B+树叶子节点数据的有序性。
  • 数据页的复用,可以理解为一张全新的数据页,当需要新页的时候,可以服用。
  • 如果使用delete命令删除整张表的数据,会导致所有的数据页都会被标记为可复用,但磁盘文件的大小并不会变小。

3.哪些情况会导致Mysql的数据空洞?

什么是数据“空洞”:可以复用,而没有被使用的空间,看起来就像是“空洞”。

经过大量增删改的表,都是可能存在空洞的。

  • 删除数据会造成数据空洞:delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的
  • 随机插入数据会造成索引的数据页分裂,造成数据空洞
  • 更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。

4.重建表的执行步骤是什么?

设有表 A,需要做空间收缩,为了把表中存在的空洞去掉,你可以怎么做呢?

  • 新建一张与表A结构相同的表B,然后按照主键ID递增的顺序,把A表中的数据一一插入到表B中;
  • 当数据从表A导入到表B的操作完成后,用表B替换表A的过程。

可以使用 alter table A engine=InnoDB 命令来重建表。

重建表的好处:可以将原表A上的主键索引空洞释放掉,起到收缩表空间的作用。


5.MySQL 5.6 版本开始引入的 Online DDL,重建表的流程是怎样的?

  • 建立一个临时文件,扫描表 A 主键的所有数据页;
  • 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
  • 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是图中 state2 的状态;
  • 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中 state3 的状态;
  • 用临时文件替换表 A 的数据文件。

6.重建表的三种方式是什么?有什么区别?

  • 从 MySQL 5.6 版本开始,alter table t engine = InnoDB(也就是 recreate)
  • analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
  • optimize table t 等于 recreate+analyze。

在重建表的时候,InnoDB 不会把整张表占满,每个页留了 1/16 给后续的更新用。也就是说,其实重建表之后不是“最”紧凑的。


7.inplace 跟 Online 的异同?

重建表的这个语句 alter table t engine=InnoDB,其实隐含的意思是:

alter table t engine=innodb,ALGORITHM=inplace;

跟 inplace 对应的就是拷贝表的方式了,用法是

alter table t engine=innodb,ALGORITHM=copy;

当使用 ALGORITHM=copy 的时候,表示的是强制拷贝表。

临时表插入数据时,不允许表 A 有增删改操作,否则会造成数据丢失。所以表数据 copy 的方式不是 online 的。
而 inplace 的方式,在构建临时文件时,允许表 A 有增删改操作,期间新的增删改操作会记录到另外的日志文件,表 A 数据页的所有数据复制完成后,再应用日志文件(自己理解:应用日志文件时,不允许对表 A 增删改操作,即非 online 的)。整体操作是 online 的。
切换表名或临时文件时,需要获取 MDL 写锁。
inplace 表示在 innodb 引擎完成所有操作,对 server 层是透明的。inplace 操作不一定是 online 的,如 MySQL 8.0 的添加全文索引和空间索引。而 online 的一定是 inplace 的。


8.什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大?

  • 就是这个表,本身就已经没有空洞的了,比如说刚刚做过一次重建表操作。
  • 在 DDL 期间,如果刚好有外部的 DML 在执行,这期间可能会引入一些新的空洞。
  • 在重建表的时候,InnoDB 不会把整张表占满,每个页留了 1/16 给后续的更新用。也就是说,其实重建表之后不是“最”紧凑的。