学生时期欠的债, 步入社会迟早是要还的 不知道大家有没有遇到过这样一个问题,就是数据库占用空间太大,把一些表删除后,表文件大小并没有改变?
学习这些之前,我先去查阅了一些资料。InnoDB 和 MyISAM 的数据文件都是什么样子的?
-
InnoDB
-
MyISAM
-
MyISAM 表是独立于操作系统的,这说明可以轻松的将其从windows 服务器移植到 Linux服务器。每当我们建立一个MyISAM 引擎的表时,就会在本地磁盘上建立三个文件,文件名就是表名。demo.frm 存储表定义; demo.MYD (MYData) 存储数据;demo.MYI (MYIndex) 存储索引
为什么有人说InnoDB文件是存储在一个文件中,也有人说存储在多个文件中。这到底哪一个是正确的?
其实表数据是可以存在共享表空间里,也可以是多个单独的文件。这个行为是由参数 innodb_file_per_table 控制的:
1、这个参数设置为OFF表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起。
2、这个参数设置为ON表示的是,每个InnoDB表数据存储在一个以.ibd为后缀的文件中。
从MySQL5.6开始,默认值改为了ON。这里不管使用的是哪个版本,这个参数最好设置为ON。因为,一个表单独存储为一个文件更容易管理,而且在不需要这个表的时候,通过drop table 命令, 系统可以直接删除这个文件。而如果放在共享表空间中,即使表删除了,空间也是不会回收的。
数据空洞
通过drop table 删除整表,可以达到回收表空间的目的。但是在业务场景上,我们几乎是不可能删除整个表的,而是删除某些行比较多。这个时候,空间该如何进行回收呢?要知道这些是如何回收的,需要知道是如何删除一个数据的。先来看下InnoDB中一个索引的示意图。(B+ 树索引示意图)
InnoDB 里的数据都是使用 B+ 树进行组织存储的。假设 R4 是我们要删除的记录,InnoDB只会把这个记录标记为删除。如果之后插入一个 300 和 600 之间的记录时,会直接复用这个位置。也是为了方便维护索引,但是磁盘文件并不会缩小。
我们知道 InnoDB 是按照进行存储的,如果删除整个页会怎么样?整个页都是可以被复用的。
复用数据页 和 复用记录是不同的:
复用记录,只限于符合范围条件的数据。比如上面的例子,R4删除后,如果插入一个400是可以被复用的,如果是800就不可以被复用了。
复用数据页的话,是可以被复用到任何位置的。还用上面的图片举例,如果将数据页 page A 上的所有记录删除,page A会被标记为可复用。
现在知道了 delete 命令只是将数据 或者 数据页标记为可复用, 而不是真正的删除,所以磁盘文件大小是不会改变的。
这些可复用的,而且没有被使用的空间,除了删除的情况下会出现,还有什么操作会出现呢?
插入操作也是会造成空洞现象的出现。如果数据是按照索引递增顺序插入的,这个时候索引是紧凑的。但是如果数据是随机插入的,这个时候可能会造成数据页的分裂。因为什么呢?我们知道innodb存储是按照页进行存储的,每页数据是有固定的大小。如果说这个时候,数据页满了,需要插入数据的话,这个时候就需要重新申请一个数据页,为了保证数据的均衡,会把所有的数据在进行一次分配,使得两个数据页的数据量不会差的太多,但是这个时候会出现很多“可复用”的空间。
举例:
比如说现在有一个数据页A,A中只能放下三个数据,分别是300,500, 600;这个时候我们需要插入一个数据为530的记录,这个时候,数据页A中已经无法进行存放了,需要重新申请一个数据页B。这个时候为了数据的均衡,此时新插入的530和600被放到数据页B中,此时数据页A中就出现了一个空洞。但是真实的环境中,可能不止1个记录的位置是空洞。
更新操作其实就是删除一个旧的值,在插入一个新的值,也是会造成空洞的。只要经过大量的增删改的表,都有可能存在着空洞。怎么才能将这些空洞去掉,节省表空间呢?
Online DDL
alert table A engine=InnoDB
这个命令就是用来重建表的。他的具体流程是怎么样的呢?在MySQL 5.5之前他的流程是不支持 online 的,看下流程图:
1、新建一个和表A相同的结构的表B
2、将表A中数据一行行的读出来插入到表B中
3、数据从表A到入表B操作完成后
4、用表B替换表A,达到收缩表A空间的作用
在这个过程中,表A 是不能进行更新操作的。在这个过程中如果有写入表A的操作,就会造成数据的丢失。所以这个过程不是online的。但是耗时最多的时间就是往临时表中写数据的过程,这个过程往往是不能接受的。
但是在MySQL 5.6版本开始引入了Online DDL,对整个操作流程做了优化。优化之后重建表的流程就是这样的:
1、建立一个临时文件,扫描表A的所有数据页
2、用数据页表A的数据生成B+树,存在临时文件中
3、生成临时文件的过程中,将所有对表A的操作记录在日志中,也就是row_log
4、临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件
5、用临时文件替换表A的数据文件
由于日志文件记录了所有的操作和重放操作的存在,就是Online DDL。
学习过程中疑问点
在这期间alert语句不是需要获取MDL写锁的吗?这个时候怎么能叫online ddl 呢?
在 alert 语句启动的时候需要获取MDL写锁,但是在写锁真正拷贝数据的时候就会退化为MDL读锁。
为什么要退化为MDL读锁?为什么不直接加MDL读锁,省去退化的操作呢?
退化为MDL 读锁是为了实现 Online DDL,MDL读锁不会阻塞增删改操作。
不能省去MDL写锁的原因是为了保护自己。为什么说是为了保护自己呢?
因为在做一些准备工作未完成之前,主表不允许做任何修改操作或读取,之后降级为读锁是允许其他线程进行DML操作,因为这个时候log文件已经准备就绪,他们的DML操作都会写入到 row_log 文件中。