MySQL：为什么DELETE比INSERT占用更多的CPU？ [英] MySQL: Why is DELETE more CPU intensive than INSERT?

问题描述

我目前正在大学里学习性能评估课程，我们现在正在做一个作业，在PHP和MySQL数据库服务器上测试CPU使用率。我们使用httperf创建自定义流量，并使用vmstat跟踪服务器负载。我们正在为PHP服务器运行3000个连接，分别用于INSERT和DELETE。

I'm currently taking the course "Performance Evaluation" at university, and we're now doing an assignment where we are testing the CPU usage on a PHP and MySQL-database server. We use httperf to create custom traffic, and vmstat to track the server load. We are running 3000 connections to the PHP-server, for both INSERT and DELETE (run separately).

数字显示DELETE操作比INSERT操作占用更多的CPU资源。 —我只是想知道为什么？

Numbers show that the DELETE operation is a lot more CPU intensive than INSERT — and I'm just wondering why?

我最初以为INSERT需要更多的CPU使用率，因为需要重新创建索引，需要将数据写入磁盘，等等。但是，显然我错了，我想知道是否有人可以告诉我这样做的技术原因。

I initially thought INSERT required more CPU usage, as indexes would need to be recreated, data needed to be written to disk, etc. But obviously I'm wrong, and I'm wondering if anyone can tell me the technical reason for this.

推荐答案

在至少对于InnoDB（我希望他们对此有所帮助）而言，即使没有外键，您的操作也更多。插入大致是这样的：

At least with InnoDB (and I hope they have you on this), you have more operations even with no foreign keys. An insert is roughly this:

1. 插入行


2. 在二进制日志缓冲区中标记


3. 标记提交

删除操作如下：

1. 标记行已删除（与插入相同的命中-页面被重写）


2. 在二进制日志缓冲区中标记


3. 标记为已提交


4. 实际上去掉了该行（与插入时一样命中-重写了页面）


5. 清除线程轨迹

1. Mark row removed (taking the same hit as an insertion -- page is rewritten)
2. Mark in binary log buffer
3. Mark committed
4. Actually go remove the row, (taking the same hit as an insertion -- page is rewritten)
5. Purge thread tracks deletions in binary log buffer too.

为此，您要完成两倍于删除而不是插入的工作。删除操作需要执行两次写入操作，因为对于以后的所有版本，必须将其标记为已删除，但只有在没有事务可看到时才将其删除。因为InnoDB仅将完整的块写入磁盘，所以对块的修改代价是恒定的。

For that, you've got twice the work going on to delete rather than insert. A delete requires those two writes because it must be marked as removed for all versions going forward, but can only be removed when no transactions remain which see it. Because InnoDB only writes full blocks, to the disk, the modification penalty for a block is constant.

这篇关于MySQL：为什么DELETE比INSERT占用更多的CPU？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！