MySQL性能是DELETE还是UPDATE? [英] MySQL performance DELETE or UPDATE?

问题描述

我有一个MyISAM表，其中有10到7列以上.向其中添加数据时，我必须在末尾更新约10行.删除它们然后插入新记录是否更快，或者更新这些行是否更快?应该更新的数据不属于索引的一部分.那索引/数据碎片呢?

I have a MyISAM table with more than 10^7 rows. When adding data to it, I have to update ~10 rows at the end. Is it faster to delete them and then insert the new ones, or is it faster to update those rows? Data that should be updated is not part of the index. What about index/data fragmentation

推荐答案

UPDATE快得多.

UPDATE时，表记录仅被新数据重写.

When you UPDATE, the table records are just being rewritten with new data.

DELETE时，应该更新索引(请记住，您删除了整个行，​​不仅是您需要修改的列)而且数据块也可能被移动(如果达到了PCTFREE限制)

When you DELETE, the indexes should be updated (remember, you delete the whole row, not only the columns you need to modify) and datablocks may be moved (if you hit the PCTFREE limit)

所有这些必须在INSERT上再次完成.

And all this must be done again on INSERT.

这就是为什么您应该始终使用

That's why you should always use

INSERT ... ON DUPLICATE KEY UPDATE

而不是REPLACE.

前一个是发生键冲突时的UPDATE操作，而后一个是DELETE/INSERT.

The former one is an UPDATE operation in case of a key violation, while the latter one is DELETE / INSERT.

这篇关于MySQL性能是DELETE还是UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！