（我的）SQL 性能：更新一个字段与许多不必要的字段
我正在处理一个表单，其中包含许多字段供正在编辑现有记录的用户使用。用户可能只更改了一个字段，我通常会执行更新查询来设置所有字段的值，即使其中大多数字段没有更改。我可以进行某种跟踪以查看哪些字段实际上已更改，并且只更新少数已更改的字段。更新记录中的所有字段与仅更新已更改的字段之间是否存在性能差异？还有其他理由选择这两种方法吗？霰弹枪的方法很简单...
i'm processing a form that has a lot of fields for a user who is editing an existing record. the user may have only changed one field, and i would typically do an update query that sets the values of all the fields, even though most of them don't change. i could do some sort of tracking to see which fields have actually changed, and only update the few that did. is there a performance difference between updating all fields in a record vs only the one that changed? are there other reasons to go with either method? the shotgun method is pretty easy...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我想说这取决于以下因素：
已处理
相对于申请
数据更改
如果您正在传输大量数据和/或连接是远程的，那么您应该做一些测试，看看是否可以通过跟踪更改来提高性能。否则，您可能会发现一条记录被操纵的假设可以忽略不计。
I would say that it depends upon the following:
processed
relative to the application
for data change
If you are transferring large amounts of data and/or the connection is remote then you should do some tests to see if you can improve performance by tracking changes. Otherwise, you will probably find that it is negligible presuming that one record is being manipulated.
我想说选择霰弹枪，但这实际上取决于很多因素以及你对数据库的使用。
您必须考虑到 UPDATE 不仅必须将新字段（甚至是未更改的字段）存储到表中，而且还必须：
在所有情况下但是，您可以实施简单的方法并测试任何性能问题以确保安全。
I'd say go for the shotgun but it really depends on many things and the use you have on the db.
You have to take into account that the
UPDATEwill have to not only store the new (even the unchanged ones) fields into the table but also:In all cases however, you can implement the easy way and test for any performance issues to be safe.
如果您谈论的是合理数量的数据（例如 1kb+），那么优化可能是值得的。如果此语句/表被多个用户频繁运行/更新（每秒几次？），则可能值得优化。
您应该已经拥有原始数据的副本，因此弄清楚发生了什么变化并不是一个大问题，并且更改更新语句以仅适应已更改的字段也不是一个大问题。
因此，这可能不是一个坏主意，但除非您希望节省带宽，或者觉得需要提高性能，否则可能没有必要。
If you are talking about a reasonable amount of data (e.g. 1kb+) then optimizing could be worthwhile. If this statement/table is being run/updated frequently (several times a second?), by multiple users, etc it may be worth optimizing.
You should already have a copy the original data, so figuring out what has changed is not a big problem, and neither is changing the update statement to accommodate only the changed fields.
So it may not be a bad idea, but unless you are looking to save bandwidth, or feel you need to improve performance, it's probably not necessary.
仅更新单个字段实际上可能会降低性能。如果您使用准备好的语句，则数据库已经编译了用于更新所有字段的查询计划。如果您开始更新随机字段，那么每次更新时都必须解析查询，从而失去准备好的语句的好处。我不确定这对 MySQL 会有多大影响，但我知道这对 SQL Server 和 Oracle 可能很重要。
You might actually lose performance by only updating single fields. If you're using prepared statements, then the database has already compiled the query plan for updating all fields. If you start updating random fields, then you'll have to parse the query every time you update, losing the benefit of the prepared statements. I'm not sure how much effect this would have in MySQL, but I know it can be significant with SQL Server and Oracle.
当然，更新多个字段会产生一些开销，但与维护有关字段更改的状态的成本相比，开销很小。如果这是 1 个表，我不会担心根据表单的状态有选择地更新列。
此外，查看像 Hibernate 这样的数据库框架来抽象其中一些细节可能会很有用。
There would of course be some overhead in updating multiple fields, but the overhead will be minor in comparison to the cost of maintaining state about what fields changed. If this is 1 table, I would not worry about selectively updating columns based on the state of your form.
Also, it may be useful to look at a database framework like Hibernate to abstract some of these details.
我会选择霰弹枪方法，它很容易理解和实施。性能影响应该可以忽略不计。
I would go for the shotgun method ad it is easy to understand and implement. The performance hit should be neglectable.