Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

13 | 为什么表数据删掉一半,表文件大小不变? #22

Open
git-zjx opened this issue Jul 25, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

commented Jul 25, 2019

参数 innodb_file_per_table
表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的:

  • 设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
  • 设置为 ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

建议设置为 ON,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的

数据删除

InnoDB 删除记录时,只会把记录标记为已删除,如果再插入符合条件的记录会复用,但是磁盘大小不会变
如果删除一个页上的所有记录,整个页就会被标记为已删除,可以被复用到任何位置。
如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用
如果删除整个表,所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小

数据插入

如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂
经过大量增删改的表,都是可能是存在空洞的

重建表

使用 alter table A engine=InnoDB 命令来重建表,MySQL 会自动完成转存数据、交换表名、删除旧表的操作

显然花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表 A 的话,就会造成数据丢失。因此,在整个 DDL 过程中,表 A 中不能有更新。也就是说,这个 DDL 不是 Online 的

Online DDL

MySQL 5.6 版本开始引入的 Online DDL,引入之后的创建流程:

2d1cfbbeb013b851a56390d38b5321f0

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

DDL 之前是要拿 MDL 写锁的,这样还能叫 Online DDL 吗?
alter 语句在启动的时候需要获取 MDL 写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢?为了实现 Online,MDL 读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做 DDL。

问题

  1. optimize table、analyze table 和 alter table 这三种方式重建表的区别?
    MySQL 5.6 版本开始,alter table t engine = InnoDB(也就是 recreate)默认是上图的流程;
    analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
    optimize table t 等于 recreate+analyze。
  2. 如果一个高配的机器,redo log 设置太小,会发生什么情况?
    系统不得不停止所有更新,去推进 checkpoint,磁盘压力很小,但是数据库出现间歇性的性能下跌
  3. Truncate 会释放表空间吗?
    Truncate 可以理解为drop+create
  4. 怎么判断是不是相对 Server 层没有新建临时表?
    看命令执行后影响的行数,没有新建临时表的话新建的行数是 0

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Jul 26, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
1 participant
You can’t perform that action at this time.