盲目更新还是在哪里更新？
我有一个表，其中保存有关游戏中城市的信息，您每回合可以建造一座建筑物，并用值“usedBuilding”记录。
每回合我都会运行一个脚本，将usedBuilding更改为0，问题是，以下两种方式中哪一种更快，使用哪种方式实际上重要吗？
UPDATE cities SET usedBuilding = 0;
UPDATE cities SET usedBuilding = 0 WHERE usedBuilding = 1;
I have a table that holds information about cities in a game, you can build one building each turn and this is recorded with the value "usedBuilding".
Each turn I will run a script that alters usedBuilding to 0, the question is, which of the following two ways is faster and does it actually matter which way is used?
UPDATE cities SET usedBuilding = 0;
UPDATE cities SET usedBuilding = 0 WHERE usedBuilding = 1;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
一般来说，第二种情况（使用 WHERE）子句会更快 - 因为它不会在未使用的行上导致触发器评估、事务日志记录、索引更新等。
有可能 - 根据 0/1 值的分布，更新所有行实际上可能比进行比较更快 - 但这是一个相当退化的情况。
由于大约 95% 的查询成本是 I/O，因此使用 WHERE 子句要么没有什么区别（因为该列没有索引，并且您正在执行表扫描），要么有很大的区别（如果该列已索引，或表分区等）。 无论哪种方式，都不会造成伤害。
我怀疑对于您正在谈论的数据量，您不会注意到执行计划或速度的差异 - 这充其量是学术性的，最坏的情况是过早优化。 因此，我建议选择对您的应用程序来说逻辑上有意义的任何内容。
In general, the 2nd case (with the WHERE) clause would be faster - as it won't cause trigger evaluation, transaction logging, index updating, etc. on the unused rows.
Potentially - depending on the distribution of 0/1 values, it could actually be faster to update all rows rather than doing the comparison - but that's a pretty degenerate case.
Since ~95% of your query costs are I/O, using the WHERE clause will either make no difference (since the column is not indexed, and you're doing a table scan) or a huge difference (if the column is indexed, or the table partitioned, etc.). Either way, it doesn't hurt.
I'd suspect that for the amount of data you're talking, you won't notice a difference in either execution plans or speed - which makes it academic at best, premature optimization at worst. So, I'd advise to go with whatever logically makes sense for your app.
如果usedBuilding已建立索引，则使用where子句会更快，因为它只会访问/更新usedBuilding为true的行。
如果没有索引，您无论如何都会进行全表扫描，因此不会产生太大（任何？）差异。
If usedBuilding is indexed, it will be quicker to use the where clause since it will only access/update rows where usedBuilding is true.
If it's not indexed, you'd be doing a full table scan anyway, so it wouldn't make much (any?) difference.
这些转变多久发生一次？ 您预计该表中有多少行？ 如果答案是“少于每秒一次”和“少于 10000”，则无需担心。
当然，除非你碰巧对此有某种学术兴趣。
How often are these turns happening? How many rows do you expect to have in this table? If the answers are 'less than once a second' and 'less than 10000', just stop worrying.
Unless if you happen to have some sort of academic interest in this, of course.
似乎需要较少数量的交易才能执行“UPDATE cars SETusedBuilding = 0;” 执行比更具体的查询。 我能想到反对这一点的主要原因是如果您的专栏中有多个州。 如果它只是一个布尔值那么就可以了，但是您可能需要花一些时间思考是否总是如此。
使用 WHERE 子句建立索引还可以提高执行计划的效率。
It seems like there would be a lower number of transactions to make the "UPDATE cities SET usedBuilding = 0;" execute than the more specific query. The main reason I can think of against this would be if you had more than one state to your column. If its merely a boolean then it would be fine, but you may want to spend some time thinking if that will always be the case.
Indexing could also cause the execution plan to be more efficient using the WHERE clause.
循环尝试这两种方法数千次并计时！
这可能取决于：该表中实际有多少记录，以及它们是否全部适合内存或必须分页到磁盘。 在运行更新之前有多少建筑物的值为 1（我猜这可能是 1）。
使用哪种方式并不重要，但最短的方式可能出问题的可能性最小。 你不编写的代码不可能有错误。
Try both ways in a loop a few thousand times and time them!
It probably depends on: how many records are actually in this table, and whether they all fit in memory or have to be paged to disk. How many buildings are at value 1 before you run the update (I'm guessing this might be 1).
It doesn't matter which way is used, but the shortest one's probably got the least that can go wrong with it. Code that you don't write can't have bugs.
获得明确答案的最佳方法是在不同场景下使用大量样本数据进行分析。
The best way to get a definitive answer would be to profile using a lot of sample data under differing scenarios.
索引根本不会帮助你，除非你有大约 2% 的usedBuilding = 1 值。
然而，这两种说法在逻辑上是不同的，并且可能意味着完全不同的事情。
但如果对于您的情况，它们是相同的，则使用不带 where 子句的那个。
indexing won't help you at all unless you have something like maybe 2% of the usedBuilding = 1 values.
however these 2 statements are logically different and can mean totally different things.
but if for your case they are the same then use the one without the where clause.
您到底有多少行？ 我怀疑对于一个小型网络游戏来说，你真的不在乎。
如果您要对“cities”表进行多次更新，那么如果可能的话，最好在一个 UPDATE 语句中完成所有这些操作。
对一行进行任何更改可能需要与写入整行一样多的 I/O（当然更新索引列也需要索引写入除外），因此您会因为进行多次命中大量行的 UPDATE 而失败。
但如果你有，比如说，<1000 行，你真的不在乎:)
How many rows exactly will you have? I suspect that for a smallish online game, you really don't care.
If you're doing several updates to the "cities" table, it might be a good idea to do them all in one UPDATE statement if possible.
Making any change to a row probably takes just as much I/O as writing the entire row (except of course updating indexed columns also requires index writes), so you lose out by making several UPDATEs which hit lots of rows.
But if you have, say, <1000 rows, you really don't care :)