MySQL删除数据，表⽂件⼤⼩依然没变的原因
对于运⾏很长时间的数据库来说，往往会出现表占⽤存储空间过⼤的问题，可是将许多没⽤的表删除之后，表⽂件的⼤⼩并没有改变，想解决这个问题，就需要了解 InnoDB 如何回收表空间的。
对于⼀张表来说，占⽤空间重要分为两部分，表结构和表数据。通常来说，表结构定义占⽤的空间很⼩。所以空间的问题主要和表数据有关。
在 MySQL 8.0 前，表结构存储在以 .frm 为后缀的⽂件⾥。在 8.0，允许将表结构定义在系统数据表中。
关于表数据的存放
可以将表数据存在共享表空间，或者单独的⽂件中，通过innodb_file_per_table 来控制。
如果为 OFF ，表⽰存在系统共享表空间中，和数据字典⼀起
如果为 ON，每个 InnoDB 表结构存储在 .idb 为后缀的⽂件中
在 5.6.6 以后，默认值为 ON.
建议将该参数设置为 ON，这样在不需要时，通过 drop table 命令，系统就会直接删除该⽂件。
但在共享表空间中，即使表删掉，空间也不会回收。
truncate = drop + create
数据删除流程
但有时使⽤delete删除数据时，仅仅删除的是某些⾏，但这可能就会出现表空间没有被回收的情况。
我们知道，MySQL InnoDB 中采⽤了 B+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。
在删除数据时，会有两种情况：
删除数据页中的某些记录
删除整个数据页的内容
⽐如想要删除 R4 这条记录：
InnoDB 直接将 R4 这条记录标记为删除，称为可复⽤的位置。如果之后要插⼊ ID 在 300 到 700 间的记录时，就会复⽤该位置。由此可见，磁盘⽂件的⼤⼩并不会减少。
⽽且记录的复⽤，只限于符合范围条件的数据。之后要插⼊ ID 为 800 的记录，R4 的位置就不能被复⽤了。
再⽐如要是删除了整个数据页的内容，假设删除 R3 R4 R5，为 Page A 数据页。
这时 InnoDB 就会将整个 Page A 标记为删除状态，之后整个数据都可以被复⽤，没有范围的限制。⽐如要插⼊ ID=50 的内容就可以直接复⽤。
并且如果两个相邻的数据页利⽤率都很⼩，就会把两个页中的数据合到其中⼀个页上，另⼀个页标记为可复⽤。
综上，⽆论是数据⾏的删除还是数据页的删除，都是将其标记为删除的状态，⽤于复⽤，所以⽂件并不会减⼩。对应到具体的操作就是使⽤ delete 命令.
⽽且，我们还可以发现，对于第⼀种删除记录的情况，由于复⽤时会有范围的限制，所以就会出现很多空隙的情况，⽐如删除R4，插⼊的却是 ID=800.
插⼊操作也会造成空隙
在插⼊数据时，如果数据按照索引递增顺序插⼊，索引的结构会是紧凑的。但如果是随机插⼊的，很可能造成索引数据页分裂。
⽐如给已满的 Page A 插⼊数据。
由于 Page A 满了，所以要申请 Page B，调整 Page A 的过程到 Page B，这也称为页分裂。
结束后 Page A 就有了空隙。
另外对于更新操作也是，先删除再插⼊，也会造成空隙。
进⽽对于⼤量进⾏增删改的表，都有可能存在空洞。如果把空洞去掉，⾃然空间就被释放了。
使⽤重建表
为了把表中的空隙去掉，这时就可以采⽤重新建⼀个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据依次插⼊到 B 表中。
由于是顺序插⼊，⾃然 B 表的空隙不存在，数据页的利⽤率也更⾼。之后⽤表 B 代替表 A，好像起到了收缩表 A 空间的作⽤。
mysql下载不了什么原因具体通过:
alter table A engine=InnoDB
在 5.5 版本后，该命令和上⾯提到的流程差不多，⽽且 MySQL 会⾃⼰完成数据，交换表名，删除旧表的操作。
但这就有⼀个问题，在 DDL 中，表 A 不能有更新，此时有数据写⼊表 A 的话，就会造成数据丢失。
在 5.6 版本后引⼊了 Online DDL。
Online DDL
Online DDL 在其基础上做了如下的更新：
重建表的过程如下：
1. 建⽴⼀个临时⽂件，扫描表 A 主键的所有数据页。
2. ⽤⽣成的数据页⽣成 B+ 树，存储到临时⽂件中。
3. ⽣成临时⽂件时，如果有对 A 的操作，将其记录在⽇志⽂件中，对应图中 state 2 的状态。
4. 临时⽂件⽣成后，将⽇志⽂件应⽤到临时⽂件中，得到与 A 表相同的数据⽂件，对应 state 3 状态。
5. ⽤临时⽂件替换 A 表的数据⽂件。
由于 row log ⽇志⽂件存在，可以在重建表⽰，对表 A 进⾏ DML 操作。
需要注意的是，在 alter 语句执⾏前，会先申请 MDL 写锁，但在拷贝数据前会退化成 MDL 读锁，从⽽⽀持 DML 操作。
⾄于为什么不⼤ MDL 去掉，是防⽌其他线程对这个表同时做 DDL 操作。
对于⼤表来说，该操作很耗 IO 和 CPU 资源，所以在线上操作时，要控制操作时间。如果为了保证安全，推荐使⽤ gh-ost 来迁移。
Online 和 inplace
⾸先说⼀下 inplace 和 copy 的区别：
在 Online DDL 中，表 A 重建后的数据放在 tmp_file 中，这个临时⽂件是在 InnoDB 内部创建出来的。整个 DDL 在 InnoDB 内部完成。进⽽对于 Server 层来说，并没有数据移动到临时表中，是⼀个 "原地" 操作，所以叫 "inplace" .
⽽在之前普通的 DDL 中，创建后的表 A 是在 tmp_table 是 Server 创建的，所以叫 "copy"
对应到语句其实就是：
-- alter table t engine=InnoDB 默认为下⾯
alter table t engine=innodb,ALGORITHM=inplace;
-- ⾛的就是 server 拷贝的过程
alter table t engine=innodb,ALGORITHM=copy;
需要注意的是 inplace 和 Online 并不是对应关系：
1. DDL 过程是 Online，则⼀定是 inplace
2. 如果是 inplace 的 DDL 不应当是 Online，如在 <= 8.0, 添加全⽂索引和空间索引就属于这种情况。
拓展
说⼀下 optimize，analyze，alter table 三种重建表之间的区别：
1. alter table t engine = InnoDB（也就是 recreate）默认的是 Oline DDL 过程。
2. analyze table t 不是重建表，仅仅是对表的索引信息做重新统计，没有修改数据，期间加 MDL 读锁。
3. optimize table t 等于上两步的操作。
在事务⾥⾯使⽤ alter table 默认会⾃动提交事务，保持事务⼀致性
如果有时，在重建某张表后，空间不仅没有变⼩，甚⾄还变⼤了⼀点点。这时因为，重建的这张表本⾝没有空隙，在 DDL 期间，刚好有⼀些 DML 执⾏，引⼊了⼀些新的空隙。
⽽且 InnoDB 不会把整张表填满，每个页留下 1/16 给后续的更新⽤，所以可能远离是紧凑的，但重建后变成的稍有空隙。
总结
现在我们知道，在使⽤ delete 删除数据时，其实对应的数据⾏并不是真正的删除，InnoDB 仅仅是将其标记成可复⽤的状态，所以表空间不会变⼩。
通常来说，在标记复⽤空间时分为两种，⼀种是仅将某些数据页中的位置标记为删除状态，但这样的位置只会在⼀定范围内使⽤，会出现空隙的情况。
另⼀种是将整个数据页标记成可复⽤的状态，这样的数据页没有限制，可直接复⽤。
为了解决这个问题，我们可以采⽤重建表的⽅式，其中在 5.6 版本后，创建表已经⽀持 Online 的操作，但最后是在业务低峰时使⽤
以上就是MySQL删除数据，表⽂件⼤⼩依然没变的原因的详细内容，更多关于MySQL表⽂件⼤⼩的资料请关注其它相关⽂章！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。