参与了好几个项目开发，每个项目随着业务量的增大，MySQL 数据日益剧增，例如其中一个项目中得用户足迹表，那是非常的疯狂，只怪我大意了，没有闪。

这篇文章我会从 delete 对性能的影响，以及如何以正确的姿势来删除数据。

在 MySQL 中 Innodb 存储引擎的表存在两部分，一部分是表结构，另一部分是表数据。

在 MySQL8.0 之前/var/lib/mysql下都会存在.frm 文件，在 MySQL8.0 之后就不存在了。这是因为 MySQL8.0 中已经允许把表结构定义放到数据字典中了，是用参数 innodb_file_per_table 来决定的。

一、表空间

表空间分为几种，系统表空间、用户表空间、undo 空间。

系统表空间：MySQL 内部的数据字典，如 information_schema 库下的数据。

用户表空间：自己建立的表结构数据

undo 空间：存储 Undo 信息，用于快速回滚。

MySQL8.0 之前表结构是在系统表空间存储的，在 MySQL5.6.6 后可以使用参数 innodb_file_per_table 来控制。

设置为 off 时，表数据是放在系统表空间中，也就是 MySQL 的数据字典放在一起。

设置为 on 时，innodb 存储引擎的表数据存储在.idb 文件中。

你知道表定义存储在哪里吗？

来到死磕 MySQL 系列的专用数据库 kaka，新建一张表 evt_sms。

猜一下创建的 evt_sms 表结构定义存储在哪里呢？

在 information_schema 库里边的 TABLES 中，执行查询SELECT TABLE_NAME,TABLE_COMMENT FROM TABLES WHERE TABLE_TYPE='BASE TABLE';

我们自定义的表类型是TABLE_TYPE。

说了这么是为了解释如果把innodb_file_per_table设置为 off，则表数据也会存放在这里。

问题：如果数据存在放共享表空间中，表删除了，空间会删除吗？

答案是不会的。

参数 innodb_file_per_table 设置为 on 数据存储在哪里呢？

一般情况下是在var/lib/mysql中，会看到你创建的数据库，进入到数据库中就能看到一张表对应一个 ibd 文件。

数据就是存储在这里。

结论

在项目开始阶段，切记将 innodb_file_per_table 设置为 on，这是正确的做法。

二、数据删除流程

现在你应该知道 Innodb 存储引擎用的是 B+树数据结构，如下图。

如果现在删了主键 ID 为 4 的这条记录，Innodb 引擎会把 ID 为 4 的这条记录标记为删除，如果之后再插入 ID 为 4 的记录，可能会复用这个位置，但磁盘文件大小并不会缩小。

隐式字段

这里就牵扯到了 mvcc 中的一个知识点，MVCC 实现原理是由俩个隐式字段、undo 日志、Read view 来实现的。

上文说的标记删除就是隐式字段中的 delete flag，即记录被更新或删除，这里的删除并不代表真的删除，而是将这条记录的 delete flag 改为 true。

在MVCC：听说有人好奇我的底层实现这篇文章中也给大家留下了一个伏笔，数据库的删除是真的删除吗？

问题：删了一个数据页的所有数据会怎么样

跟单条数据是一样的，整个数据页都是可以复用的。

记录的复用是仅限于符合范围条件的数据，例如上文删除的 ID 为 4 这条记录，如果在插入 ID 为 4 就会复用。

这里需要给大家再聊一个新的知识点页合并，若相邻的两个数据页利用率都很低，系统就会把这两个数据页合并到一个页上，另一个数据页就会标记为可复用。

问题：使用 delete 把整个表的数据都删除了会怎么样

答案是，所有的数据页都会标记为可复用，但是磁盘文件大小是不会改变的。

三、实践全表删除表文件大小不改变

经过添加数据后表数据已经达到近 100W 了，文件大小已经达到 108M。

扩展

这里大家应该能看见stopped，就是执行命令ctrl + z来的，作用是开始我们在 MySQL 窗口里边，但不想退出 MySQL 窗口查看 MySQL 表文件大小，然后就可以执行这个命令结束任务。

查看完后可以在执行fg返回到 MySQL 窗口。

问题：Linux 如何把文件单位显示为 M

假设刚刚直接执行 ll 命令查看文件，那么就需要手动计算文件大小，很不方便。

执行 ll -h 命令则可以直观的看到文件大小。

删除数据查看磁盘文件是否缩小

为了直观看大文件大小变化，咔咔直接把表里边的数据全部删了，再看文件大小，还是 108M。文件大小是没有变化的。

四、如何正确的减少磁盘文件

在第三小节中，我们演示了删除了 100W 数据后文件大小是没有改变的，也就是空洞问题影响的，接下来就解决这种问题。

问题：空洞是如何产生的？

到了这里都应该知道空洞是因为大量的增删改造成的。

解决思路

你可以新建一个 evt_sms_copy 表，然后根据主键 ID 递增的顺序，把数据从 evt_sms 读入 evt_sms1 中。

这样就可以达到因为空洞造成的磁盘文件大小无法收缩问题。

问题：为什么能解决呢？

因为 evt_sms_copy 是一张新的表，并且数据是以主键 ID 递增的，索引是紧促的，数据页利用率已经达到了最高峰状态，这样就起到了磁盘文件无法收缩问题。

上干货

直接执行 alter table evt_sms engine = Innodb 命令来达到磁盘文件收缩。

这里需要跟大家聊一下不同版本处理不同。

在 MySQL5.5 之前，这个命令做的事情跟我们解决思路是一样的，不同的是 evt_sms_copy 是不用自己创建的。

在执行命令期间如有新增数据的话，会造成数据丢失，因为在 MySQL5.5 之前版本的 DDL 不是 Online 的。因此不能有数据的改动。

现在 MySQL 都已经更新到 8 版本了，如果你是新项目就直接用 8 版本，不要在用 5.6 以前的老版本了，咔咔在 18 年开始就已经在使用 MySQL8.0 版本了。

在锁那一期文章中跟大家聊了 MySQL5.6 在 DDL 操作做了优化，引入了 Online DDL。

优化后的执行流程

• 建立临时文件 tmp_file，把表的 B+树存储到临时文件中。若此时有对表的操作，则会记录在 row log 文件中。

• 把数据从原表全部刷到临时文件后，此时临时文件的数据就跟原表的数据一致。

• 最后用临时文件替换表 A 的数据文件。

Online DDL 的由来

可以看到在收缩磁盘文件时有数据更新会记录在 row log 中，意思就是在收缩磁盘空间时是可以对表进行增删改查的。

注意点

在进行磁盘文件收缩的过程中，都会全表扫描原数据和新增临时文件，如果你的表非常大，会非常消耗 IO 和 CPU。

因此，你要安全的做这个操作，可以使用开源的 gh-ost 来进行。

结论

当你想收缩因为大量增删改查而导致表磁盘文件非常大时就可以执行alter table evt_sms engine=Innodb命令来达到收缩表空间的目的。

五、实践是检验认识是否具有真理性的唯一标准

都应该知道实践是检验认识是否具有真理性的唯一标准，那么接下里就对本文提出的结论进行实际操作一下。

• 先执行ctrl + z结束 MySQL 任务窗口

• 执行ll -h查看此时表 evt_sms 磁盘文件大小为 108M

• 执行fg返回到 MySQL 任务窗口

• 执行命令alter table evt_sms engine=Innodb
  

• 再执行ctrl + z，执行ll -h查看磁盘文件大小已经到了 128k。

上图即是咔咔操作的全过程，得到的结论就是执行命令alter table ect_sms engine = Innodb可以收缩由于大量增删改查的表引发的空洞问题。最终达到收缩表空间目的。

六、开发建议

删除数据不要使用 delete，而是使用软删除，做一个标记删除即可。

这样既不会出现空洞问题，也方便数据溯源。

每张表必备三个字段 create_time、update_time、delete_time。

七、总结

通过本期文章我们需要知道以下几点。

• 通过大量增删改查的表会出现空洞

• 干掉空洞需要执行 alter table evt_sms engine=Innodb 来解决

• 使用 delete 删除数据只会做一个标记处理，并不会真正删除空间

• 本文所有的结论都基于 innodb_file_per_table = on

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。