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

Question

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

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

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

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

Answer 1

影响：

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大表数据归档的几种方法介绍

Answer 2

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

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

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

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

Answer 3

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

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

Answer 4

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