MySQL：为什么 DELETE 比 INSERT 更消耗 CPU 资源？
我目前正在大学学习“性能评估”课程，我们现在正在做一项作业，测试 PHP 和 MySQL 数据库服务器上的 CPU 使用情况。我们使用 httperf 创建自定义流量，并使用 vmstat 跟踪服务器负载。我们正在运行 3000 个与 PHP 服务器的连接，用于插入和删除（单独运行）。
数字显示 DELETE 操作比 INSERT 消耗更多的 CPU 资源 — 我只是想知道为什么？
我最初认为 INSERT 需要更多的 CPU 使用率，因为需要重新创建索引，需要将数据写入磁盘等。但显然我错了，我想知道是否有人可以告诉我这样做的技术原因。
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).
Numbers show that the DELETE operation is a lot more CPU intensive than INSERT — and I'm just wondering why?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
至少对于 InnoDB（我希望他们能让你了解这一点），即使没有外键，你也可以进行更多操作。插入大致是这样的：
删除执行以下操作：
为此，删除工作要比插入工作多两倍。删除需要这两次写入，因为必须将其标记为对于以后的所有版本已删除，但只有在没有看到它的事务剩余时才能删除。由于 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:
Deletions do the following:
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.
DELETE 还需要将数据写入磁盘，重新计算索引，此外还需要一组逻辑比较来查找您首先尝试删除的记录。
DELETE also requires data to be written to disk, plus recalculation of indexes, and in addition, a set of logical comparisons to find the record(s) you are trying to delete in the first place.
删除需要的逻辑比你想象的要多；多少取决于模式的结构。
在几乎所有情况下，删除记录时，服务器必须检查该记录是否有任何依赖项作为外键引用。简而言之，就是对系统表进行查询，查找具有指向该表的外键引用的表定义，然后在每个表中选择引用要删除的记录的记录。无论服务器是否执行级联删除或只是返回错误，您都将计算时间增加了几个数量级。
自平衡的内部数据结构也必须重新组织，并且索引必须更新以删除索引树的任何现在为空的分支，但这些在插入操作中将有对应的分支。
Delete requires more logic than you think; how much so depends on the structure of the schema.
In almost all cases, when deleting a record, the server must check for any dependencies upon that record as a foreign key reference. That, in a nutshell, is a query of the system tables looking for table definitions with a foreign key ref to this table, then a select of each of those tables for records referencing the record to be deleted. Right there you've increased the computational time by a couple orders of magnitude, regardless of whether the server does cascading deletes or just throws back an error.
Self-balancing internal data structures would also have to be reorganized, and indexes would have to be updated to remove any now-empty branches of the index trees, but these would have counterparts in the Insert operations.