首先明确一个概念,innodb表包含两部分,表结构定义和数据,Mysql8.0以前表结构定义存放在.frm为后缀的文件里,而Mysql8.0版本以后允许表结构定义放到系统数据表中,因为表结构定义占用的空间很小,因此我今天主要说是表数据。
日常开发中,当我们删除一个表的的数据的时候,发现表空间大小并不会变小,我们要知道为什么会发生这样神奇的事。
参数innodb_file_per_table
表数据可以存在共享表空间里,也可以在单独的文件中,这个行为由参数innodb_file_per_table控制,这个参数为OFF的时候,说明表的数据存放在系统共享表空间,也就是跟数据字典放在一起,如果这个参数是ON的时候,说明表的数据放到单独的文件中,Mysql5.6.6以后默认就是ON。
无论使用哪个版本我们都建议把这个值设置成ON,单独放到一个文件,方便管理,当我们不需要这个表的时候,通过drop table 就可以直接删除这个文件。如果放到系统共享表空间中,即使删除掉了,空间也就是不会回收的。
Mysql数据删除流程
假如我删除500这个数据,innodb只会把这个记录标记为删除,如果之后要插入400的时候,就会直接复用这个位置,但是磁盘的文件并不变小,当然我如果删除了在跟个pageA的数据,当然也是被复用的,但是数据页的复用和记录的复用是不一样的。记录的复用,只限于符合条件的数据,正如上面的例子,但是如果插入一个800的数据,就不能复用这个位置了,而数据页可以复用任何位置,如上图为例,把pageA的数据全部删除,PageA页就会被标记为可复用,这个时候插入一条id=50的时候,要创建新的数据页的时候,就会复用pageA。
现在我们知道delete 命令只会把记录的位置或数据页标记为可复用,但是磁盘的文件大小不会变小,也就是说delete并不会回收表空间,这个可以进行复用,而没有使用的空间,看起来就像空洞,实际上不止删除会造成空洞,插入数据也会。如果数据按照索引递增顺序插入,那么索引是紧凑,但是如果数据是随机的,就可能存在数据页的分裂,如下图
我们看到当先插入一个数据的时候,此时要申请一个数据页pageB,来保存数据了,页分裂完成后,PageA尾部就留下了空洞,另外更新索引的值,可以理解为删除一个数据,新增一条数据,不难理解这样也是会导致空洞的。
总之,经过大量的增删改的表都可能存在空洞,所以,如果能把这些空洞去掉,就能达到收缩表空间的目的,而重建表,就可以达到这样的目的。
Mysql重建表去掉空洞
试想一下,如果我们需要去掉这些空洞,如何做呢,当然你可以建立一个和A表一样表B,把数据A的数据复制到表B,
由于表B是新建的表,所以表B不存在空洞,显然表B的主键索引更紧凑,数据页利用率也高,如果我们建立一个临时表B,把数据从表A导入表B的操作完成后,用表B替换A,效果上就起到了收缩表A的作用。
我们可以使用下面命令重建表,在mysql5.5版本之前,这个命令的执行流程跟我们前面描述的差不多,区别就是这个临时表B不需要手动创建,MySql会自动完成转存数据,就换表明,删除旧表的操作
显然上图中最花时间的步骤是往临时表插入数据的过程,如果整个过程中,有新的数据写入是不被允许的,这个就会造成数据丢失,表A不会有数据的更新,这个DDL不是Online的。
但是在mysql5.6版本开始引入Online DDL,这个操作流程做了优化,如下面流程:建立一个临时表,扫描表A主键的所有数据页
用数据页中表A记录生成B+树,存储到临时文件中,
生成临时文件的过程,将所有对表A的操作应用到日志文件中
临时文件生成后,将日志文件应用到临时文件中,得到一个逻辑数据和表A相同的数据文件
用临时文件替换表A的数据文件
可以看到上图和之前的不同之处在于,由于日志文件激励和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表A增删改,这就是Online DDL的来源。
之前我们介绍过在执行DDL的时候会先拿到MDL的写锁,但是这个写锁在真正拷贝数据之前就会退化成读锁,因为这样才能实现Online DDL,不会阻塞增删改的操作。
但是为什么不直接接触锁呢,那是因为防止其他线程对这个表进行DDL操作。
而对于一个达标来说,Online DDL最耗时的过程就是拷贝数据,这个步骤执行期间可以增删改,所以相对于真个DDL锁的时间非常短,对业务来说,可以容忍,当然对于大表的重建表,这个操作是很消耗IO和CPU资源的,生产环境要谨慎执行。
Online 和inplace
我们看到第一张状态的图是把表A的数据导出来放到temp_table,这个是临时表,在server层建立的,但是,第二章状态的图是把表A的数据放到临时文件temo_file,这个临时文件是innodb的内部创建出来的,整个DDL过程都在InnoDB内部完成的,对于server层来说,没有把数据挪到临时表,是一个原地操作,这就是inplace名称的来源,但是我们发现在重建表的过程中,inplace和online好像是一个意思,其实他们是不一样的,比如如果我要在innodb中给一个字段加全文多音,写法是alter table t add fulltext(file_name)
这个过程是inplace的,但是会阻塞增删改操作,是非Online的,
他们的关系如下:DDL的过程如果是Online,就一定是inplace的
反过来未必,也就是inplace的DDL,有可能不是online,截止到Mysql8.0,添加全文索引和空间索引就属于这种情况,
这个说明一个重建表的区别mysql5.6版本开始alter table t engin=innodb(recreate),默认就是第二张状态图描述的
analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁
aptimize table t =recreate+analyze
YUKX优科学习网
本文内容及图片来源于网上摘录或读者投稿