数据库规范化
我正在构建一个查询，当我构建它时，我意识到如果某些表包含冗余字段，那么编写会更容易；它会节省一些连接。然而，这样做意味着数据库模型不会完全规范化。
我的目标是表现；非规范化数据库会影响性能吗？我正在使用 SQL Server。
谢谢。
I'm building a query and as I'm building it, I'm realizing that it'd be easier to write if some of the tables contained redundant fields; it'd save a few joins. However, doing so would mean that the database model would not be totally normalized.
I'm aiming for performance; will having a denormalized database impede performance? I'm using SQL Server.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我不知道你的具体实现是什么，但它通常有助于拥有冗余索引引用，但本身没有冗余字段。
例如，假设您有三个表：tbl_building、tbl_room 和 tbl_equipment。 （一个设备属于一个房间，房间属于一个buildng）
tbl_building有一个buildingID，tbl_room有一个roomID和对buildingID的引用。如果您的 tbl_equipment 引用了 roomID 和buildingID，那么即使您可以从 roomID 推断出buildingID，它也会为您节省一次连接。
现在，例如，如果您在 tbl_building 上有buildingSize 字段并将该buildingSize 字段复制到tbl_room 和tbl_equipment 中，那就不好了。
I don't know exactly what your implementation is, but it normally helps to have redundant index references, but not redundant fields per se.
For example, say you have three tables: tbl_building, tbl_room, and tbl_equipment. (An equipment belongs to a room, which belongs to a buildng)
tbl_building has a buildingID, tbl_room has a roomID and a reference to buildingID. It would save you a join if your tbl_equipment had a reference to both roomID and buildingID, even though you could infer the buildingID from the roomID.
Now, it would not be good if, for example, you have the field buildingSize on tbl_building and copy that buildingSize field to tbl_room and tbl_equipment.
在这种情况下，我经常发现最好的选择是创建一个索引视图，它是规范化表的非规范化版本。这将使您能够轻松查询数据，同时不会造成维护噩梦。
需要注意的几点：
joins
插入/更新/删除功能
这里有一篇文章介绍了索引视图的一些优点。
回答你的问题；采用非规范化结构通常会提高性能，但会造成维护噩梦。
In this type of situation I often find your best option is to create an indexed view that is a denormalized version of your normalized tables. This will allow you to easily query data while not creating a maintenance nightmare.
A few things to note:
joins
Insert/Update/Delete functions
Here is an article that goes over some of the benefits of Indexed Views.
In answer to your question; having a denormalized structure will often improve performance but it will create a maintenance nightmare.
一旦您知道连接会导致性能问题，并且升级硬件不是一个选择，那么要么是非规范化的时候，要么是处理某些用例（多个用户获取相同的数据，例如主页一个站点）开始缓存。
Once you know for a fact that the joins are causing performance issues, and upgrading the hardware isn't an option, then it's either time to denormalize or if dealing with certain use cases (multiple users getting the same data e.g. for a home page of a site) start caching.
要回答您的问题“非标准化数据库会影响性能吗？”，答案是“这取决于情况”。标准化是一个约束。它不会提高数据库性能，除非您的访问模式使得查询中的大量数据被忽略（您的结果集较小）。但非标准化可以提高有很多连接（有更大的结果集）的性能。
To answer your question, "will having a non-normalized database impede performance?", the answer is "it depends". Normalization is a constraint. It won't improve database performance, unless you access patterns are such that a lot of data is ignored in your queries (you have smaller result sets). But non-normalization can improve performance where you have many joins (you have bigger result sets).
标准化并不决定性能。规范化涉及正确性和防止某些数据完整性问题。
正常形式的数据库确实还有助于减少设计偏差（有偏差的模式意味着一个设计比其他模式更适合某些类型的查询）。从这个意义上说，它应该为数据库优化器提供完成其工作的最佳机会。非规范化意味着添加冗余，在许多情况下，这还意味着相同信息需要更多存储 - 可能会影响性能。
Normalization does not determine performance. Normalization is about correctness and preventing certain data integrity problems.
A database in Normal Form does also help reduce design bias (a biased schema means one designed to suit some types of query better than others). In that sense it should give the best chance for the database optimiser to do its work. Denormalization means adding redundancy and in many cases that also means more storage is required for the same information - potentially impacting performance.
当您遇到问题（可能是性能问题）时，通常会在规范化之后发生非规范化。
你不需要预先设计它：我几乎可以保证你的假设将是错误的，并且处理以意想不到的方式使用的非规范化模式将是一个痛苦的世界。
例如，数据修改异常
而且，也许我误解了过去十五年的情况，但是数据库引擎不是为了有效地连接表而设计的吗？
Denormalisation typically happens after normalisation when you have a issue, perhaps with performance.
You don't design it in up front: I can pretty much guarantee that your assumptions will be wrong and it'll be a world of pain to deal with a denormalued schema that is used in unexpected ways.
For instance, Data modification anomalies
And, perhaps I've misunderstood this last decade and a half, but aren't database engines designed to JOIN tables efficiently?
规范化的基本目的是减少表中数据的冗余，从而减少存储浪费和不一致性。就性能而言，这取决于数据库的设计方式。如果冗余太多，则检查和搜索对于关系中的某个元素，会增加搜索时间并降低效率。另一方面，如果冗余较少，那么对性能不会有太大影响。但拥有规范化模式总是更好。
The basic purpose of normalization is to reduce redundancy of data in your tables which reduces storage wastage and inconsistency.As far as the performance is concerned,it depends on the way your database is designed.If there are too many redundancy, then checking and searching for an element in a relation will increase the search time and reduce the efficiency.On the other hand,if there is less redundancy then there won't be much effect on performance.But it is always better to have a normalized schema .