mysql 临时表增加主键_MySQL之重建表

//MySQL之重建表//

    在MySQL中，如果我们对大表频繁进行insert和delete操作，那么时间一长，这个表中会出现很多"空洞"，也就是表碎片。

碎片产生的原因是insert随机值作为主键id，会产生很多数据页分裂操作；而delete掉一些排列有序的主键值，这些被delete的空间不会直接释放，而是仅仅进行delete的标记，这些空间如果不能被利用，那就会变成"空洞"。

      在这种情况下，往往需要对表进行重建，从而释放这些空余的空间，让数据变得"紧凑些"，如下：

 这个重建表的过程，在MySQL5.5之前，它的执行逻辑是下面这样的：

1、假设原表是A，新建一个表table B，和表A的表结构保持一致

2、按照主键顺序，将表A的数据一行一行的读出来，插入到表B里面

3、交换表A和表B的名称

当我们执行alter table A engine=Innodb的时候，就会执行我们上述描述的三个动作。

在MySQL5.6及以后的版本里面，引入了Online DDL的方法，Online DDL的引入，使得上面的过程有了一点点不同，当执行alter table A engine=Innodb的时候，MySQL的内部动作如下：

1、建立临时文件file A，扫描表A主键的所有数据页，注意，这里不是临时表，而是临时文件；

2、用数据页中表A的记录生成B+树，然后存储到临时文件中去

3、在生成临时文件的过程中，使用另一个log文件，将对表A的所有DML操作，记录到log文件中，改文件大小由参数控制：

mysql> show variables like "%online%";+----------------------------------+-----------+| Variable_name                    | Value     |+----------------------------------+-----------+| innodb_online_alter_log_max_size | 134217728 |+----------------------------------+-----------+1 row in set, 1 warning (0.01 sec)

4、临时文件file A生成之后，将log中对表A的操作应用到临时文件中，

5、用临时文件替换表A的数据文件

这个重建的过程中，允许对表A做增删改查的操作，所以称之为Online DDL。

这里需要注意一点，因为Online DDL需要在DDL的过程中支持表的增删改查操作，所以Online DDL一开始会获取MDL写锁，但是在数据文件开始拷贝之前，就退化成MDL读锁了。

现在我们来说下第一点里面强调的临时文件file A.

在MySQL5.5之前，我们使用临时表作为重建的中间介质，在MySQL5.6之后，我们使用临时文件作为重建的中间介质，这里说说这个临时表和临时文件的区别。

临时表是创建在server层面的，临时文件是创建在innodb层面的，所以Online DDL的整个过程都是在Innodb内部完成的，这种方法也称之为"inplace",相对应的，需要借助server层面临时表的过程，称之为"Copy"。

我们在阅读Online DDL一节的官方文档的时候，看到的copy和inplace也就是这个意思。详情可参见官方文档：

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

几个小问题：

1、假设我们有一个1TB的表，磁盘只有1.2TB，那么还可以做inplace的DDL呢？

答案：不可以，因为inplace方案中的临时文件也要占用一定的空间。

2、inplace方案进行的表重建操作，都是Online DDL么？

答案：不是，有些操作是inplace的，也就是在innodb层面进行的，但是却不是online的，例如增加全文索引的操作，这个操作是inplace的，但是会阻塞增删改查操作，因此不是Online DDL，只能说：Online DDL一定是inplace的，但是inplace方案进行的操作，不一定是Online的。

3、加入某个表的大小是1TB，进行alter table A engine=Innodb之后，表的空间没有缩小，反而增大了一点，这是为什么？

答案：可能是因为表之前刚刚进行过一次alter table的操作，而且表上面的并发增删改比较多，在进行alter table的过程中，这些操作都写进了log中，从而导致表的实际大小增加。

有帮助的话还希望点下再看哈