表数据存储位置由哪个参数控制

  1. innodb_file_per_table
  2. ON：每个InnoDB表数据存储在一个以.ibd为后缀的文件中
  3. OFF：表的数据放在系统共享表空间，也是和数据字典放在一起

MySQL5.6.6开始，这个参数的默认值就为ON。

为何建议将innodb_file_per_table设置为ON？

一个表单独存储为一个文件更好管理，当我们不需要这个表的时候，直接Drop table命令，系统就会直接删除这个文件；而如果放在系统共享表空间，那么即使Drop table把表删除了空间也不会回收的。
实际场景中，我们遇到的更多的还是删除部分表记录的情况。

但是我们又会遇到表的数据删除了，但是占用的空间并没有减小，这是为什么？

删除数据的流程

Innodb内部数据的存储都是按照B+树的结构组织的，每一行记录都占用一个位置，如下图表数据存储(数据页空洞) - 图1
当我们把R4删除的时候，只是R4这个数据被删除了，但是这个位置所占用的空间并没有释放，当下次插入一条记录比如400的时候，会直接复用这个位置。
又知道MySQL数据的存储是按照页存储的，如果整个数据页的数据都被删除了会怎么样， 事实是整个数据页都可以被复用。但是整个数据页的复用和记录的复用是不同的，记录的复用，例如上图，如果插入数据800，那么就不能复用R4，但是如果是整个数据页的复用，如PageA ，如果插入数据50，那么也是可以的。
因此，delete命令只是将表记录的位置或者数据页标记为”可复用“，但实际上表占用的空间不会减少 ，这些可以复用，但是又没有使用的空间，看起来就像是”空洞“ 。

插入也会产生数据空洞？

当一个数据页pageA满了的时候，再插入的数据就不得不申请一个新的数据页pageB，而有可能pageA的末尾就可能留下了空洞。
更新索引上的值也会造成空洞，因为更新索引的值就相当于先删除这个值，再插入。
如果能够把这些空洞去掉，就能达到收缩空间的目的

如何去掉空洞？

重建表

使用alert table A engine=InnoDB
这个命令的执行流程是什么？

Mysql5.5之前

首先创建一个临时表B，将表A的数据按照主键的顺序把数据插入到这个临时表B中，此时临时表B因为是新建的表，不存在数据空洞，然后将表B替换表A即可。
表数据存储(数据页空洞) - 图2

缺点：耗时最多的地方是往临时表B插入数据的过程，这个过程中不允许有数据的写操作，因为如果有写操作的话，可能会造成数据丢失，因此这个执行过程不是OnLine的。

MySQL5.6版本之后

增加了OnLine DDL，也就说，在重建表的过程中可以执行写操作具体的流程看下图表数据存储(数据页空洞) - 图3

  • 与之前的方式不同的是，没有临时表的概念，而是把表A的数据生成B+树存储到临时文件temp-file中，同时新增了一个row log来存储插入temp-file过程中数据库执行的操作，临时文件生成之后将操作日志应用到临时文件中，但是在应用操作日志的过程也不是OnLine的，得到与表A的数据文件相同的数据文件，然后替换表A的数据文件即可，这也是我们为什么建议把innodb_file_per_table设置为ON的原因

MySQL在执行DDL语句的时候，要先获得MDL写锁(表级锁)，但是这个写锁会在执行数据拷贝的时候退化为MDL读锁，是为了实现OnLine，但是为什么不直接把锁释放掉呢，是为了防止在此期间有DDL操作(DDL操作需要获得MDL写锁，与MDL读锁互斥，因此其他DDL操作会被阻塞)。
如果数据量很大的话，执行拷贝数据的过程是消耗IO和CPU资源的，推荐使用GitHub 开源的 gh-ost 来做。

OnLine和inplace

在Mysql5.5之前的那张重建表执行流程图中，会创建一张临时表，这张临时表是在Server层创建的，而不是InnoDB引擎创建的。
而MySQL5.6版本之后重建表的流程图中，会生成一个临时文件用来存储原表的数据，这个临时文件是InnoDB创建的，整个DDL过程都是在InnoDB引擎中完成的。对于Server层来说没有把数据挪动到临时表，是一个原地的操作，因此称这个过程为inplace。

如果有一个1TB的表，而磁盘空间有1.2TB，此时要重建表，那么是不能重建的，因为临时文件也要占用临时空间的。

alter table t engine=innodb 等同于
alter table t engine=innodb,ALGORITHM=inplace
和inplace相对的就是拷贝了
alter table t engine=innodb,ALGORITHM=copy


  • DDL过程如果是OnLine的，就一定是inplace的
  • DDL是inplace的时候，不一定是OnLine的，截至到8.0版本，全文索引和空间索引就属于这种。

    如何查看DDL操作是否为inplace

    执行完语句之后显示”共0行受影响”，表示为inplace操作。

问题：为什么执行完alter table t engine=innodb之后表的占用空间变大了？

答案：
1、可能表t中没有数据空洞，在执行alter过程中，数据库执行了DML语句，产生了数据空洞，导致占用空间变大。
2、InnoDB在重建表的时候，不会把整张表都沾满，而是每个页留出了1/16留给后续更新使用。当表重建完之后，插入一部分数据，可能这部分数据占用了这些空间，就会导致占用空间变大。