这是我参与2022首次更文挑战的第25天，活动详情查看：2022首次更文挑战
InnoDB表
InnoDB表包含两部分：表结构定义和数据
表结构定义占用空间很小，主要讨论表数据
mySQL 8.0以前 - 表结构存在.frm后缀文件中
MySQL 8.0 - 表结构定义放在系统数据表中
为什么简单地删除表数据达不到表空间回收的效果
表数据的存放位置
参数 innodb_file_per_table
- 参数 OFF - 表数据在系统共享表空间 - 跟数据字典放在一起
- 参数 ON【5.6.6版本后 默认】 - 每个InnoDB 表数据存储在一个以.ibd为后缀的文件中
一个表单独存储为一个文件更容易管理 - 不需要这个表时，drop table 删除该文件 - 回收表空间 -> 共享表空间中即使表删掉了空间也不会回收
开始讨论表中的数据被删除了，表空间没有被回收的情况
数据删除流程
InnoDB的数据是按页存储的
数据页的复用和记录的复用并不相同
记录的复用只限于符合范围条件的数据
数据页的复用是可以复用到任何位置
如果相邻的两个数据页利用率都很小 - 系统会把这两个页上的数据合到其中一个页上，另一个数据页就被标记为可复用
造成数据空洞的情况
- delete命令 - 整个表的数据删除 - 数据页被标记为可复用 - 磁盘上文件大小不变 -- delete命令不能回收表空间
- 数据随机插入 - 索引的数据页分裂
- 更新索引上的值 - 相当于删除一个旧值，再插入一个新值
大量增删改的表，都可能存在空洞 - 去掉空洞就能收缩表空间 --> 重建表
重建表
新建一个与表A结构相同的表B - alter table A engine=InnoDB
整个DDL过程中，表A中不能有更新 - DDL不是online的
MySQL5.6引入online DDL
Online DDL重建表的流程
日志文件记录和重放操作 - 重建表过程中，允许对表A做增删改操作
- 建立一个临时文件 - 扫描表A主键的所有数据页；
- 数据页中表A的记录生成B+树 - 存储到临时文件中；
- 生成临时文件的过程中 - 将所有对A的操作记录在一个日志文件（row log)中
- 临时文件生成后 - 将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件
- 临时文件替换表A的数据文件
临时文件 - 存数据页 - 日志文件 - 存操作 - 应用到临时文件 - 替换表A
由于日志文件记录和重放操作 - 重建表的过程中，允许对表A做增删改操作
DDL - 在alter语句启动的时候 - 需要获取MDL写锁 - 在真正拷贝数据之前退化成读锁了
- 为了实现Online - MDL读锁不会阻塞增删改操作
- 不解锁是为了保护自己 - 禁止其他线程对这个表同时做DDL
大表 -- Online DDL 最耗时过程 - 拷贝数据到临时表的过程 -- 执行期间可以接受增删改操作 -- 相对于整个DDL过程来说 - 锁的时间非常短 - 对业务来说 - 可以认为是Online的
上述重建方法都会扫描原表数据和构建临时文件 - 大表来说 很消耗IO和CPU资源
线上服务 - 小心地控制操作时间
比较安全的操作 -- Github开源的gh-ost
Online和inplace
表数据导出来的存放位置 - tmp_table - 临时表 - Server层创建
表重建出来的数据存放位置 - tmp_file - 临时文件 - InnoDB在内部创建出来的 - 整个DDL过程在InnoDB内部完成 - 对server层 - 没有把数据挪动到临时表 - “原地”操作 - inplace
重建表 - alter table t engine=InnoDB = alter table t engine=InnoDB,ALGORITHM=inplace;
如果ALGORITHM=copy表示的是强制拷贝表
inplace和Online的异同
- DDL过程是Online -> 一定是inplace的
- inplace的DDL -> 可能不是Online的
optimize table、analyze table、alter table三种方式重建表的区别
- analyze table t - 不是重建表 - 对表的索引信息做重新统计 - 没有修改数据 - 过程中加了MDL读锁
- optimize table t等于recreate + analyze
