数据库设计，最佳实践，需要建议
我有这个数据库，有一个名为“徽章”和“主题”的表，我在“主题”中有一个名为“is_canceled”布尔值的标志字段，现在出于某种原因有人建议我在“徽章”中有一个名为“ cancel_subject_count”，它与他所说的性能原因之一具有相同的值
"SELECT SUM(is_canceled) as cancel_count FROM subjects WHERE is_canceled = 1 AND badge_id = ".$some_badge_id
，最好直接取该值，而不是先对其进行求和，
你们觉得怎么样？我应该有这个字段吗？为什么？
谢谢
i have this database, with a table called "badges" and "subjects", i have a flag field in "subject" called "is_canceled" bool value, now for some reason someone suggested me to have a field in "badges" called "canceled_subject_count", which has the same value as
"SELECT SUM(is_canceled) as cancel_count FROM subjects WHERE is_canceled = 1 AND badge_id = ".$some_badge_id
one of the reason he said is performance, it is better to take the value straight rather having it to sum first
what do you guys think? should I have this field? and why?
thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这称为非规范化。在初始数据库设计中这样做可能会为您节省以后的时间和精力，但很可能这是一个不成熟的优化。您最好不添加
canceled_subject_count字段，如果经过一些分析后，您在计算徽章的取消主题时发现测量到的性能瓶颈，那么您可以添加场地。This is called denormalization. Doing this for the initial database design might save you time and effort later on, but chances are that it's a premature optimization. You're probably better off not adding the
canceled_subject_countfield and if after some profiling, you find a measured performance bottleneck when counting cancelled subjects for a badge, then you can add the field.这实际上取决于软件的使用。这种优化在很大程度上取决于值更改的频率、有多少人正在查看它等。
如果它很少更改，则存储值并在发生更改时更新可能更明智。
如果总和不断变化，那么在每个查询中计算它会更聪明。
但除非您要满足大量请求，否则这笔费用几乎不会花费您任何费用，并且可能更容易保持数据完整性良好。
It really depends on the use of the software. This kind of optimization depends a lot on how often the value changes, how many people are viewing it, etc.
If it rarely changes, storing the value and updating when changes occur is probably smarter.
If the sum is changing constantly, it would be smarter just to calculate it with each query.
But unless you're serving to a massive amount of requests, this sum will cost you almost nothing and is probably easier to keep your data integrity in good shape.