13数据库表空间回收

13数据库表空间回收

问题，我的数据库占用空间太大，把一个最大的表删掉了一半的数据，怎么表的文件大小还是没有变？

一个innodb表包含表结构定义和数据，在mysql8.0之前，表结构都是以frm文件里，而8.0后已经允许把表结构定义放在系统数据表中

参数innodb_file_per_table

表数据既可以放在共享表空间里，也可以是单独的文件，这个由参数控制

--参数为off的时，表的数据放在共享表空间汇总ibdata1之类的

--参数为on，每个innodb表数据存储在一个以tablename.idb的文件里从mysql 5.6.6开始，默认值是on

建议不论使用mysql的那个版本，都将这个值设置为on，一个表单独存为一个文件更容易管理，在drop table的时候，系统会直接删除这个文件，如果放在共享表空间中，即使表删除了，空间也是不会收回的。

在重新收缩的过程中，页会按90%满的比例来重新整理页数据（10%留给update使用）。

删除数据流程

Mysql在删除数据，innodb引擎只会把删除行的记录标记为删除，如果之后在插入之间的记录，可能会复用这个位置，但是磁盘大小并不会缩小，

在innodb中数据是按页存储的，那么如果我们删掉一个数据页上的所有记录会怎么样？结果就是整个数据页就可以被复用了。

但是，数据页的复用跟记录的复用是不同的。

记录的复用，只限于符合范围条件的数据，比如3行数据，200,500,700，删除了500的行，插入为400的行就可以复用，插入800的行就不能复用。

而当整个页从B+树里面摘掉以后，可以复用到任何位置，如果将page a上所有的记录删除以后，page a会被标记为可复用，这时候插入记录需要新也的时候，page a是可以复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据联合到其中一个页上，另外一个数据页就被标记为可复用。

如果用delete删除整个表，那所有的数据页都是可以复用的，但是在磁盘上，文件不会变小。

实际上，不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的，但如果数据是随机插入的，可能造成索引的数据页分裂。

另外更新索引上的值，可以理解为删除一个旧值，再插入一个新值，这也会造成空洞。

也就是说，表经过大量增删改操作，都是可能存在空洞的，如果把这些空洞去掉，就能达到收缩表空间的目的，重建表，就可以到达

重建表

可以把表a按照主键id顺序导入b表，然后rename b to a

可以使用alter table a engine=innodb来重建表。在mysql 5.5版本之前，整个ddl过程中，表a不能更新，这个ddl不是online的。

在mysql 5.6开始引入online ddl，对这个操作流程做了优化，ddl流程

?

1 2 3 4 5

--1 建立一个临时文件，扫描表a主键的所有数据页 --2 用数据页中表a的记录生成B+树，存储到临时文件中 --3 生成临时文件的过程中，将所有对a的操作记录在一个日志文件中(row_log) --4 临时文件生成后，将日志文件中的操作应用到临时中，得到一个逻辑数据上与表a相同的数据文件 --5 用临时文件替换表a的数据文件。

在alter语句启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化为读锁了。为了实现online,MDL读锁不会堵塞dml操作。MDL锁禁止其他线程对这个表进行DDL操作。

而对于一个大表来说，online ddl最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受dml操作，所以，相对于整个ddl过程来说，锁的时间非常短，对业务来说，可以认为是online的。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件，对于很大的表来说，这个操作是很消耗io和cpu资源的，因此，如果是线上服务，需要很小心地控制时间，如果想要比较安全的操作的话，推荐使用gh-ost，pt工具来做。

Online和inplace

区分online和inplace，在数据导出来存放的位置叫做tmp_table，这个临时表在server创建，如果这个临时文件是在inndob内部创建出来的，整个ddl过程在innodb内部完成，对于server层来说，没有把数据移到临时表，这就是inplace操作。

怎么区别是在server层还是innodb内部进行临时文件操作的呢？最直观的判断就是看命令执行后有影响的行数，在innodb层创建临时文件操作，影响的行数就是0，或者在slow_log中，Rows_examined为0.

如果有一个1 TB的表，磁盘空间为1.2TB，能不能做一个inplace的DDL呢？

答案是不能的，因为，tmp_file也是要占用临时空间的。

重建表的语言alter table t engine=innodb,隐含意思

alter table t engine=innodb,ALGORITHM=inplace;

跟inplace对应的就是拷贝的方式

alter table t engine=innodb,ALGORITHM=copy;

当使用ALGORITHM=copy的时候，表示的是强制拷贝表

Inplace和online并不是一个意思。

比如要给innodb表的一个字段加全文索引

alter table t add FULLTEXT(field_name);

这个过程是inplace的，但是会阻塞dml操作，是非online的，DML操作会加一个MDL读锁。

--DDL过程如果是online的，就一定是inplace的

--反过来未必，也就是说inplace的ddl，有可能不是online的。

使用optimize table\analyze table\alter table三种方式重建表的区别

--从mysql5.6开始，alter table t engine=INNODB(也就是recreate)默认online

--analyze table并不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程加了MDL读锁

--optimize table t等于recreate + analyze

DML操作会加一个MDL读锁。Online ddl的时候，dml操作的记录放在row_log里，这时进行select的数据是在旧表进行，在ddl还没有完成之前，应该查到ddl之前的结果。