系列文章

五、如何抉择一般索引和惟一索引《死磕 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

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