mysql频繁insert和delete,MySQL：为什么DELETE比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.

解决方案

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:

Insert row

Mark in binary log buffer

Mark commit

Deletions do the following:

Mark row removed (taking the same hit as an insertion -- page is rewritten)

Mark in binary log buffer

Mark committed

Actually go remove the row, (taking the same hit as an insertion -- page is rewritten)

Purge thread tracks deletions in binary log buffer too.

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.