一、抛砖引玉
开发过程中,我们难免存在大表数据,把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?
二、正文开始
🏁:1.想要知道为什么没有变首先我们要明确MySQL的Innodb引擎中,表的结构和数据是存放在哪里的?
- 表结构:MySQL8.0以前表的结构数据存放在以.frm 为后缀的文件里,以后允许把表结构定义放在系统数据表中了。
- 表数据:表数据的存放是通过参数 innodb_file_per_table控制的,OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
PS:5.6.6以后默认是ON,推荐做法就是ON
🏁:2.明白了表数据的存储,具体的删除流程是怎么样的?
当我们要删除(delete)Page中一条数据时(如:R4),这条数据只会标记为已删除,因为如果之后插入数据再用到这个区间的数据,此时被复用。这也是为什么大小不会缩小了。
PS:如果数据页被删除了,也同样会被复用。区别就在于,数据行的复用是区间内的,数据页的复用是任意数据都可。
⚠️:如果相邻(相邻应该是指B+树上的逻辑相邻,而不是表空间文件的物理相邻)的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。
问题2总结就是:delete 命令把整个表就意味着所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小。
🏁:3.我们知道了原理后,深入想一下,这样删除或者插入都会造成表“空洞”,如何把这些空洞去掉达到收缩表的目的呢?
如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。
重建表。即:通过使用 alter table A engine=InnoDB 命令实现转存数据、交换表名、删除旧表的操作。
⚠️:时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表 A 的话,就会造成数据丢失。
🏁:3.1.重建表原理(MySQL如何做到重建过程数据不丢失)
MySQL 5.6 版本开始引入的 Online DDL。
重建表的流程:
建立一个临时文件,扫描表 A 主键的所有数据页。(State1)
用数据页中表 A 的记录生成 B+ 树,存储到临时文件中。生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中。(State2)
临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件。(State3 )
用临时文件替换表 A 的数据文件。( State4 )
上述过程中,alter 语句在启动的时候需要获取 MDL 写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。为了实现 Online,MDL 读锁不会阻塞增删改操作。
思考下,这里为啥不直接解锁呢?
👋:简化后的Online DDL
Online DDL的过程是这样的:
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
⚠️⚠️⚠️:不要以为知道了这个语句就可以放飞自我了!!!
线上对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。因此,如果是线上服务,你要很小心地控制操作时间,安全的操作的话使用 GitHub 开源的 gh-ost 来做。
🏁:4.除了Online还有一个姊妹操作Inplace
- 相同点是他们过程一毛一样
- 区别是什么?Online过程导出来的存放位置叫作 tmp_table。这是一个临时表,是在 server 层创建的。
Inplace数据是放在“tmp_file”里的,这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说,没有把数据挪动到临时表,是一个“原地”操作,这就是“inplace”名称的来源。
因此我们用的这个语句:alter table t engine=InnoDB 等价于 alter table t engine=innodb,ALGORITHM=inplace;
Online就是ALGORITHM=copy
🏁:4.1.alter table t add FULLTEXT(field_name);这个是Online还是Inplace的?
这个过程是 inplace 的,但会阻塞增删改操作,是非 Online 的。
因为,截止到 MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 就属于非Online这种情况
三、课后题
一个表 t 文件大小为 1TB;对这个表执行 alter table t engine=InnoDB;发现执行完成后,空间不仅没变小,还稍微大了一点儿,比如变成了 1.01TB。你觉得可能是什么原因呢 ?
本身就已经没有空洞的了,比如说刚刚做过一次重建表操作。在 DDL 期间,如果刚好有外部的 DML 在执行,这期间可能会引入一些新的空洞。@飞翔 提到了一个更深刻的机制,是我们在文章中没说的。在重建表的时候,InnoDB 不会把整张表占满,每个页留了 1/16 给后续的更新用。也就是说,其实重建表之后不是“最”紧凑的。假如是这么一个过程:将表 t 重建一次;插入一部分数据,但是插入的这些数据,用掉了一部分的预留空间;这种情况下,再重建一次表 t,就可能会出现问题中的现象。(个人没太看懂的地方)
四、评论区
🏁:5.使用 optimize table、analyze table 和 alter table 这三种方式重建表的区别。从 MySQL 5.6 版本开始,
- alter table t engine = InnoDB(也就是 recreate)默认的就是上面图 4 的流程了;
- analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
- optimize table t 等于 recreate+analyze。
🏁:6.Truncate 可以理解为drop+create;
页分裂是发生在索引;
不影响增删改,就是 Online;相对 Server层没有新建临时表,就是 inplace
🏁:7.如果将 alter 操作显式的放到事务里 ,事务不提交 , 另一个事务查询的时候会查询到alter 操作后的表结构 , 比如新增了一个字段。这个是什么原因 ?
alter table 语句会默认提交前面的事务(隐式提交),然后自己独立执行,因为要保持事务一致性。