当前位置:主页 > 澳门码今晚开什么 >

什么?还在用delete删除数据《死磕MySQL系列 九

发布日期:2022-01-27 02:58   来源:未知   阅读:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。返回搜狐,查看更多语音搜索等创新还没有成为购物搜索的主流