MySQL-我应该反规范化吗? [英] mySQL - Should I denormalise?

问题描述

概述 (抱歉，它含糊不清-我认为，如果我详细介绍一下，将会使事情复杂化)

我有三个表，表一包含一个ID，表二包含其自己的ID和表一的ID，表三包含其自己的ID和表二的ID.

I have three tables, table one contains an id, table two contains its own id and table one's id and table three contains its own id and table two's id.

我花了很多时间思考，我认为对于表3来说，也要包含相关的表ID会更有效.

I have spent a lot of time pondering and I think it would be more efficient for table three to also contain the related table ones id.

-这将意味着我不必连接三个表，我只需查询三个表(对于将经常使用的查询)

-It will mean I will not have to join three tables, I can just query table three (for a query that will be used very often)

-通过仅锁定表3中包含表1中特定ID的行，这将使我能够更轻松地实现预订系统.

-It will allow me to implement a reservation system more easily by only locking rows within table three that contain a specific id from table one.

对于任何想了解更多有关数据库布局的人，有更多信息此处

For anyone who wants to know more about the database layout there is more info here

问题

去规范化有哪些不利条件?我看到有些人完全反对它，而另一些人则认为在正确的情况下它是一个有用的工具. id永远不会改变，因此除了不得不两次插入相同的数据并因此消耗掉额外的空间(因为它只是id，因此可以忽略不计)之外，我看不出有什么不利之处.

What are the disadvantaged to de-normalisation? I have seen some people who are completely against it and others who believe in the right situation it is a useful tool. The id's will never change so I do not really see any disadvantage other than having to insert the same data twice and thus the additional space it will consume (which as it is just id's will surely be negligible).

推荐答案

我的建议是遵循以下一般规则:默认情况下进行规范化，然后在确定何时可以解决性能问题时对规范化.

My advice is to follow this general rule: Normalise by default, then denormalise if and when you identify a performance problem which it will solve.

我找到了规范化的数据，并找到了处理它的代码，它们更容易维护，更合乎逻辑.我认为使用非规范化来提高性能不会有任何问题，但是我不会推测性地应用任何性能优化来导致可维护性降低，直到您确定它们是必需的为止.

I find normalised data, and code dealing with it, easier and more logical to maintain. I don't think there is any problem using denormalisation to improve performance, but I would not speculatively apply any performance optimisation which results in a decrease in maintainability until you are sure they are necessary.

这篇关于MySQL-我应该反规范化吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！