为了避免一些复杂的查询而重复数据库中的字段是不好的设计实践吗？
如果您有一些包含您需要的所有信息的联接表，但它涉及一些复杂的联接等。在您选择的表中添加一个额外的字段来节省这些额外的查询会更好吗？
预先感谢
保罗
If you have a few join tables which contain all the information you need, but it involves some complex joins etc.etc. would it be better to stick an extra field in your table of choice to save yourself these extra queries ?
thanks in advance
Paul
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是和不是。
这是故意违背数据库规范化原则的，因此被称为“非规范化”。
它的缺点几乎是我们标准化的所有原因，最值得注意的是它引入了一种错误可能使数据库与其自身不一致的方式。因此，总的来说，这是糟糕的数据库设计。实际上，它还使数据库的更新变得更加复杂，因此更加昂贵（假设没有错误）。
不过，它可以为某些查询带来性能优势，这就是总是超时和微不足道之间的区别。因此，这是一种合理的、有时甚至是必要的优化。
我喜欢采取以下方法来解决这个问题：
仔细考虑其必要性。特别是，CTE 使我不再需要在某些情况下使用此技术，因为 CTE 可以完成过去需要多次表查找的操作（事实上，在我过去使用过的大多数情况下） 。有时，真正需要的只是彻底检查所涉及的指数。最后，值得尝试的是通过使用这种非规范化而优化的查询，而不是时不时地使用它；各种改变都可以消除对它的需求。
为相关数据创建一个单独的表，将其视为“查找”，不将其视为主要设计的一部分，向其他开发人员强调这是一种优化，而不仅仅是糟糕的数据库设计。如果错误导致上述那种不一致，这也使得重建整个系统变得更容易。
使用触发器来维护表，这样对其所依赖的表的更新将自动保持正确，而不必在更新时引入有关维护它的特殊规则（这些规则几乎肯定会被打破，或者至少会导致 使用触发器
Yes and no.
This is deliberately going against the principle of database normalisation, and is hence called "denormalisation".
Its bad points are pretty much all the reasons why we normalise, most notably that it introduces a way in which an error can make the database inconsistent with itself. In general therefore, it is bad DB design. In practice, it also makes updates to the database both more complicated and hence more expensive (and that's assuming no errors).
It can though give performance benefits to some queries that are the difference between always timing out, and being trivial. It is therefore a reasonable, and sometimes essential, optimisation.
I like to take the following approach to this:
Double think its necessity. In particular, CTEs have made some cases I would have resorted to this technique no longer necessary, as CTEs can do what used to require multiple table look-ups in just a couple (indeed, most cases where I resorted to it in the past). Sometimes also, all that's really needed is a thorough examination of the indices involved. Finally, it's worth trying the queries that have been optimised through the use of this denormalisation, without using it, every now and again; all sorts of changes can remove the need for it.
Create a separate table for the relevant data consider to be a "lookup" that is not considered part of the main design, highlighting to other developers that it is an optimisation rather than just bad DB design. This also makes it easier to rebuild the whole thing should an error cause the sort of inconsistency mentioned above.
Use triggers to maintain the table, so that updates to the tables it depends upon will automatically keep things correct, rather than having to introduce special rules about maintaining it when updating (those rules almost definitely will be broken, or at least lead to the code being less clear).
我实际上已经看到这种情况发生，尽管我怀疑这是否是一个好的设计实践，但它似乎有时可以提供一些良好的性能。特别是，我将其视为 Rails 中的计数器缓存，其中提供了一个新字段以保留计数。我想如果你有太多的条目，它确实可以产生更好的性能。
I've actually seen that happen and though i would be skeptic if it a good design practice, it seems that it can offer some good performance at times. In particular i've seen it as a counter cache in Rails, where a new field is presented in order to keep the count. I suppose that if you have too many entries, it can really produce some better performance.