实体属性值模型 - 性能替代? [英] Entity attribute value model - Performance alternative?

问题描述

我使用 PHP 和 mySQL.

I work with PHP and mySQL.

我有一个页表和一个元表.看起来有点像这样.

I have a page table and a meta table. It looks a little bit like this.

页表

page_id | headline    | content
--------------------------
1       | My headline | My content
2       | Another one | Another text

元表

id | page_id | meta_key  | meta_value
------------------------------------
1  | 2       | seo_title | Hello world
2  | 2       | price     | 299

我读过这种类型的模型被称为 EAV.我还读到它对性能不利.

I've read that this type of model is called EAV. I also read that it is bad for performance.

我的元表是为连接到页面的任何类型的值制作的.这次我无法创建带有静态"列的表.

My meta table is made for any kind of value connected to a page. I can not created a table with "static" columns this time.

问题

• 对于 300 个页面，每页有 30 个元值，这有多糟糕?9000元表中的行.
• 是否有更好的动态"数据模型?

推荐答案

首先，有时这种模型使查询数据变得更加容易.几天前我问了一个问题这里和一些用户建议为什么我没有将模型更改为 1NF 形式以使查询数据更容易.只有当他们意识到我坚持这个设计时，他们才对这个问题提供了一些答案.关键是我很幸运只有 12 列要总结；否则，如果我的表包含 300 列，则可能没有用户费心为该问题编写查询.:-)

First, sometimes this model makes querying data much easier. I asked a question couple of days ago here and some users suggested why I didn't change my model to a 1NF form to make querying data easier. Only when they realized I was stuck with this design, they provided some answers to the question. The point is I was lucky enough to have only 12 columns to be summed up; otherwise, if my table contained 300 columns, perhaps no user bothered themselves to write a query for that problem. :-)

第二，由于数据库自然施加的一些限制，有时这种设计的实现更容易.如果您的 meta_key 值包含一些大于 30 个字符的冗长值，您要么必须缩短这些值并在某处进行映射，要么这可能是您唯一的选择.

Second, sometimes the implementation of this design is easier due to some limitations naturally imposed by databases. If your meta_key values contain some lengthy values larger than 30 characters, either you have to shorten the values and do the mapping somewhere or this would possibly be the only option you could have.

最后，性能很重要；这是真的.但是，另一方面，您可以应用某些技术来提高性能；例如通过创建适当的索引、分区表等.

Finally, performance is very important; that's true. But, on the other hand, there are certain techniques you could apply to improve the performance; such as by creating proper indexes, partitioning tables, and so on.

在这种情况下，表的大小非常小.因此，除非您的查询非常复杂，例如有大量计算和复杂的联接和聚合，并且如果应用程序对小时间部分不敏感，我想如果采用这种模型，您不会受到性能影响.

In this case, the table sizes are very small. So, unless your queries are very complicated such as having heavy calculations and complicated joins and aggregations, and if the application is not sensitive to small time fractions, I guess you wouldn't suffer from performance if adopted this model.

最后，如果您仍然过于关注性能，我建议您创建两个模型，用一些随机或真实数据填充它们，并分析计划成本以查看哪个模型更适合您的需求.

At the end, if you are still too much concerned about the performance, I would suggest create both models, populate them with some random or real data, and analyze the plan costs to see what model better suits your needs.

这篇关于实体属性值模型 - 性能替代?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！