系列文章

五、如何选择普通索引和唯一索引《死磕MySQL系列 五》

六、五分钟，让你明白MySQL是怎么选择索引《死磕MySQL系列 六》

七、字符串可以这样加索引，你知吗？《死磕MySQL系列 七》

八、无法复现的“慢”SQL《死磕MySQL系列 八》

参与了好几个项目开发，每个项目随着业务量的增大，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

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

文章来源: blog.csdn.net，作者：咔咔-，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/fangkang7/article/details/121153360