听说MySQL频繁的删除数据会影响性能,请问原理是什么? 能影响到什么程度?

码强
  • 26

听说MySQL频繁的删除数据会影响性能,请问原理是什么? 能影响到什么程度?

我有一个2千万数据的表A,最近想拆分一下.

  1. 将一部分数据从表A复制到表B,同时从表A中删掉该部分数据.
  2. 今后每日从表A中搬运一部分数据到表B

之前听说这种高频度的删除操作极其影响MySQL的性能, 真的吗?请问原理是什么?能影响到什么程度? 怎么避开?

回复
阅读 7.4k
4 个回答
abul
  • 1.6k
✓ 已被采纳

影响:

  1. 产生大量碎片,影响磁盘IO;
  2. 另外会影响索引的基数Cardinality值,从而导致关联sql时使用不当的索引;
  3. 如果数据库拓扑中有做主从同步,一次性delete大量数据,会出现主从同步延迟

首先要了解下,对mysql进行删除数据操作,磁盘空间并不会立即被回收,这里的空间包括数据和索引空间,但是可能被后续的insert利用,也可能不会,就形成碎片。

怎么测试删除数据mysql没有立即回收空间呢?
很简单,首先创建一个innodb表tb,往里插入大量数据(比如10w条),这个时候看下tb的数据文件tb.ibd的大小,记录下来;此时再把tb表数据删除(delete from tb),然后再看下tb.ibd的大小,会发现没有变化,也就是没被回收!

当然更关注的是怎么解决。
问题1、2都可以通过执行OPTIMIZE TABLE 表名来优化表,重新组织表数据和关联索引数据的物理存储。(执行完再去看看.ibd的大小是不是变小了)
主从同步延迟的问题则是按照上面说的,分批间隔几分钟删除,把大事务化成小事务去执行。

最后再说下,这种迁移数据到另外一张表,然后删除大表数据,叫做MySQL归档,随便搜下就有,给个传送门MySQL大表数据归档的几种方法介绍

mysql读取数据是以页为基本单位的。

删除数据会导致页需要合并。

另外不是尾插的数据可能导致页的分裂。

页的频繁变化会导致影响性能。

频繁删除会导致pageCleaner线程一直在处理脏页刷新,导致服务器负载高

正常应该在流量少的时候分批间隔几分钟删除,间隔时间根据实际情况调整

删数据的影响:
1.造成数据块碎片,高水位,浪费空间;
2.索引维护的代价;
3.大事务造成主从延迟;
4.没有合适索引或删除数据量大,持锁范围大或时间长;
5.占用IO资源

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
宣传栏