如何为用户自定义字段设计数据库？

Question

如何为用户自定义字段设计数据库？

sqldatabasedatabase-designuser-defined-fields

159

我的需求如下：

需要能够动态添加任何数据类型的用户自定义字段
需要能够快速查询UDF
需要能够根据数据类型在UDF上进行计算
需要能够根据数据类型对UDF进行排序

其他信息：

我主要寻求性能优化
可能会有几百万个主记录附带UDF数据
上次检查时，我们当前的数据库中有超过5000万条UDF记录
大多数情况下，一个UDF只附加在一小部分主记录上，而不是全部主记录
UDF没有连接或用作键。它们只是用于查询或报告的数据。

选项：

创建一个大表，包括StringValue1、StringValue2... IntValue1、IntValue2等列。我不喜欢这个想法，但如果有人能告诉我这比其他想法更好，以及为什么更好，我会考虑它。
创建一个动态表，在需要时按需添加新列。我也不喜欢这个想法，因为我觉得除非你为每个列建立索引，否则性能会很慢。
创建一个包含UDFName、UDFDataType和Value的单一表。当添加新UDF时，生成一个视图，仅提取该数据并将其解析为指定类型。不符合解析标准的项返回NULL。
创建多个UDF表，每个表对应一个数据类型。因此，我们会有UDFStrings、UDFDates等表。可能会像#2一样自动生成视图以便在添加新字段时使用。
XML数据类型？我以前没有使用过，但见过它们的提及。不确定它们是否能够给我想要的结果，特别是在性能方面。
其他的办法？

- Rachel

8

马丁·福勒建议使用两个（可由用户更新的模式）或五个（带索引的 XML LOB）：http://martinfowler.com/bliki/UserDefinedField.html - Neil McGuigan

1

请参考StackOverflow上关于动态数据库模式的问题。 - FloverOwe

1

现在许多数据库都支持JSON字段......但索引/性能仍然是一个问题。例如，PostgreSQL（v13）支持在JSON上的GIN索引......但只支持等式运算符（不支持范围查询）。MongoDB似乎具有更好的支持通配符索引。 - collimarco

14个回答

23

我已经写过关于这个问题很多。最常见的解决方案是实体-属性-值反模式，与您在选项#3中描述的类似。像瘟疫一样避免使用这种设计。

当我需要真正动态的自定义字段时，我使用的解决方案是将它们存储在XML块中，这样我可以随时添加新字段。但为了使它更快速，还要创建附加表来搜索或按排序每个字段（不是每个字段都需要一个表，只需要每个可搜索字段的表）。这有时称为倒排索引设计。

您可以在此处阅读有关此解决方案的有趣文章：http://backchannel.org/blog/friendfeed-schemaless-mysql

或者你可以使用面向文档的数据库，其中每个文档都有自定义字段。我会选择Solr。

- Bill Karwin

1

你能解释一下为什么我应该避免选项#3吗？我看了一些你的例子，但它们并不完全符合我的需求。我只是想要一个存储额外数据的地方，而不是存储所有属性的地方。 - Rachel

2

首先，你会将哪个属性设置为NOT NULL？如何使一个属性成为唯一的而不是所有属性都唯一？这些问题还有很多。最终，你会编写应用程序代码来提供RDBMS已经为你提供的功能，甚至需要编写某种映射类来简单地插入逻辑实体记录并获取它。 - Bill Karwin

2

简短回答是“不要混淆数据和元数据。” 为fieldname或tablename创建varchar列，相当于将元数据标识符存储为数据字符串，这就是许多问题的起点。另请参见http://en.wikipedia.org/wiki/Inner-platform_effect - Bill Karwin

2

@Thomas：在倒排索引设计中，您可以使用标准模式解决方案来处理数据类型和约束，例如UNIQUE和FOREIGN KEY。但是，当您使用EAV时，这些解决方案根本不起作用。我同意倒排索引与EAV共享非关系型的特性，因为它支持每行不同的属性，但这是一种妥协。 - Bill Karwin

2

@thitami，多年来我学到的是，对于你的应用程序而言，任何解决方案都可能是正确的。即使EAV对于某些特定的应用程序来说可能是最不错的解决方案。在不了解查询之前，你无法选择优化策略。每种类型的优化会在牺牲其他查询的基础上改善某些查询。 - Bill Karwin

显示剩余17条评论

12

这听起来像是一个更适合使用非关系型解决方案（例如MongoDB或CouchDB）的问题。它们都允许动态模式扩展，同时可以保持您所寻求的元组完整性。

我同意Bill Karwin的观点，EAV模型不适用于您。在关系系统中使用名称-值对并不本质上不好，但仅当名称-值对构成完整的信息元组时才能有效运作。当使用它强制您在运行时动态重构表格时，所有事情开始变得困难。查询变成了一个需要维护的透视操作或者迫使您将元组重构推向对象层。

您不能确定空值或缺失值是有效条目还是缺乏条目，而不将模式规则嵌入到对象层中。您失去了有效管理模式的能力。100个字符的varchar类型是否适合“value”字段？200个字符？它应该是nvarchar吗？这可能是一个艰难的权衡，最终你必须对动态集合的人为限制。比如，“您只能拥有x个用户定义的字段，每个字段长度为y个字符”。

使用类似MongoDB或CouchDB的面向文档的解决方案，您在单个元组中维护与用户相关的所有属性。由于连接不是问题，生活很愉快，因为这两种方法都不擅长连接，尽管有人吹嘘。您的用户可以定义任意数量（或您允许的数量）的属性，在不到4MB的长度范围内进行管理。

如果您有需要ACID级别完整性的数据，则可以考虑将解决方案拆分，使高完整性数据存储在关系数据库中，动态数据存储在非关系型存储中。

- Data Monk

11

我很可能会创建以下结构的表：

varchar 名称
varchar 类型
decimal 数值
varchar 字符串值
date 日期值

当然，确切的类型取决于您的需求（当然还要取决于您使用的数据库管理系统）。您还可以将 NumberValue（decimal）字段用于 int 和布尔值。您可能还需要其他类型。

您需要一些链接到拥有该值的主记录的方式。最简单和最快速的方法可能是为每个主表创建一个用户字段表并添加一个简单的外键。这样，您就可以轻松快速地通过用户字段过滤主记录。

您可能希望具有某种元数据信息。因此，您最终会得到以下内容：

Table UdfMetaData

int id
varchar Name
varchar Type

Table MasterUdfValues

int Master_FK
int MetaData_FK
decimal NumberValue
varchar StringValue
date DateValue

不管您做什么，我都不建议动态更改表结构。这是一个维护噩梦。我也不建议使用 XML 结构，它们太慢了。

- Stefan Steinegger

我喜欢你的策略，也许会选择它，但在2017年，你会选择一些不同的东西吗？比如JSON。 - maztt

在我们的项目中，我们实现了自己的数据结构，可以序列化为类似于json的格式。它具有类型安全的接口，可以读写数据而无需转换，并且与编程语言集成良好。这真的很棒。它与数据库中所有这种“文档”的问题一样。很难查询特定值，并且不能轻松地引用“文档”之外的数据。根据使用情况，两者都不是问题。 - Stefan Steinegger

除此之外，我在2011年提出的解决方案，在我看来仍然是有效的。 - Stefan Steinegger

6

即使为用户提供添加自定义列的功能，查询这些列并不一定会表现良好。查询设计涉及到许多方面，其中最重要的是对应该存储什么的正确规范。因此，从根本上说，您希望允许用户创建模式而不考虑规格，并能够快速从该模式中获取信息吗？如果是这样，那么任何这样的解决方案都不太可能很好地扩展，特别是如果您想允许用户对数据进行数值分析。

选项1

在我看来，这种方法会给你一个没有关于模式含义的知识的模式，这是灾难的配方和报告设计师的噩梦。也就是说，您必须拥有元数据才能知道哪个列存储了什么数据。如果那些元数据被搞乱了，它有可能破坏您的数据。此外，它容易将错误的数据放入错误的列中。（“什么？String1包含修女的名字？我以为那是查理·辛的最爱毒品。”）

选项3,4,5

在我看来，第2、3和4个要求消除了EAV的任何变体。如果您需要查询、排序或对此数据进行计算，则EAV是Cthulhu的梦想和您的开发团队和DBA的噩梦。EAV将在性能方面创建瓶颈，并且无法为您提供快速获取所需信息所需的数据完整性。查询将很快变成交叉表Gordian knots。

选项2,6

这确实只留下了一种选择：收集规格，然后构建模式。

如果客户希望对他们希望存储的数据获得最佳性能，则需要通过与开发人员合作来了解其需求的过程，以便尽可能高效地存储它。它仍然可以存储在与其他表分开的表中，并使用基于表模式动态构建表单的代码。如果您有一个允许在列上扩展属性的数据库，您甚至可以使用这些属性来帮助表单生成器使用漂亮的标签、工具提示等，以便只需添加模式即可。无论哪种方式，要有效地构建和运行报告，数据都需要正确存储。如果所涉及的数据有很多空值，某些数据库具有存储该类型信息的能力。例如，SQL Server 2008具有称为Sparse Columns的功能，专门用于具有大量空值的数据。

如果这只是一袋不进行分析、过滤或排序的数据，我会说EAV的某些变体可能会有所作为。但是，考虑到您的要求，最有效的解决方案将是获取适当的规格，即使将这些新列存储在单独的表中并从这些表动态构建表单。

稀疏列

- Thomas

5

这是一个棘手的情况，没有一个解决方案看起来“正确”。然而，选项1可能是最好的选择，无论是从简单性还是性能方面考虑。

这也是一些商业企业应用程序中使用的解决方案。

编辑

现在还有另一种可用的选项，但当问题最初被提出时，它不存在（或者至少不成熟），那就是在数据库中使用json字段。

许多关系型数据库现在支持基于json的字段（可以包括动态子字段列表）并允许对它们进行查询。 postgress mysql

- Ophir Yoktan

1

我讨厌创建可能会有数百个未使用列的想法。这与我所学和阅读的SQL数据库设计相违背。现在，我们有超过1300个不同的用户定义值，尽管其中很多只是已命名不同的现有项目的重复项。 - Rachel

一个表格里有1300个不同的用户定义函数？每个用户都可以添加UDF吗？还是仅限于某些特权用户？ - Ophir Yoktan

这是导入过程的一部分...它将任何未映射的数据添加到用户定义字段中。由于没有人花时间将未映射的数据映射到现有的UDF字段，它只会创建新的字段，并且多年来已经添加了很多个。 - Rachel

5

根据我的研究，基于数据类型创建多个表对性能没有帮助，特别是当你有大量数据，例如20K或25K记录，每条记录具有50个以上的UDF时，性能最差。因此，您应该选择使用单个表并具有多个列，如下所示：

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

- Amit Contractor

这应该是正确的并且被点赞的。2011年Phil的先前答案今天已经不再是一个好建议了。 - Yap Kai Lun Leon

我能否得到一个简单的例子来展示如何在SQL中执行这个过程？ - Niroj

抱歉回复晚了，但您想要相同的数据库结构。我不明白你的意思@Niroj。您能否详细解释一下您想要什么。 - Amit Contractor

4

我们的数据库为一款SaaS应用（帮助台软件）提供支持，用户拥有超过7k个“自定义字段”。我们采用了综合方法：

(EntityID, FieldID, Value)表用于搜索数据
entities表中的JSON字段，包含所有实体值，用于显示数据。（这样你就不需要进行大量联接以获取值。）

你可以进一步将#1分割成“每种数据类型一个表”，就像this answer建议的那样，这样你甚至可以索引你的UDFs。

附言：为了捍卫每个人都在抨击的“实体-属性-值”方法，我想说几句话。我们使用#1没有使用#2已经有几十年了，而且效果非常好。有时这是一个商业决策。你是否有时间重写你的应用程序并重新设计数据库，或者你可以花几美元购买云服务器，这些服务器现在非常便宜？顺便说一下，当我们使用#1方法时，我们的数据库保存着数百万个实体，由数十万用户访问，而一个16GB的双核数据库服务器完全可以胜任。

- Alex from Jitbit

嗨，@Alex，我遇到了类似的问题。如果我理解得好，你有：1）一个存储值的custom_fields表，例如1 =>“last_concert_year”，2 =>“band”，3 =>“music”，然后一个带有值的custom_fields_values表 001，1，1976年 002，1，1977年 003，2，“Iron Maiden” 003，3，“Metal”希望这个例子对你有意义，对格式不好抱歉！ - thitami

@thitami 不完全是这样。按照您的示例：我有一个名为 bands 的表格，其中包含一行 1,'Iron Maiden'，然后是一个名为 custom_fields 的表格，其中包含行 1,'concert_year' | 2,'music'，最后是一个名为 custom_fields_values 的表格，其中包含行 1,1,'1977'|1,2,'metal'。 - Alex from Jitbit

@AlexfromJitbit 一个快速的问题，如果你有超过一百万的用户，当创建一个新的自定义字段时，需要使用插入触发器为每个用户插入“custom_field_value”行，对吗？ - Shyamal Parikh

还有，您如何确保json字段和custom_field_value行之间的一致性？ - Shyamal Parikh

@ShyamalParikh 我们没有使用触发器，一致性是在应用程序端进行管理的。当我们添加新的自定义字段时 - 除非您为特定用户设置该字段，否则不会发生任何事情。当我们删除一个字段时 - 也不会有任何变化，我们只是在显示应用程序端的JSON时验证该字段是否存在。 - Alex from Jitbit

2

我曾经有过处理1、3和4的经验，但它们最终都变得非常混乱，无法清楚地了解数据是什么，或者变得非常复杂，需要对数据进行柔性分类以将其分解为动态类型的记录。

我想尝试使用XML，您应该能够对xml内容执行模式强制执行以检查数据类型等，这将有助于保存不同集合的UDF数据。在较新版本的SQL Server中，您可以在XML字段上创建索引，这应该有助于提高性能。（请参见http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx）例如

- Jon Egerton

老实说，我还没有研究过 XML。主要的缺点是我需要学习它的工作原理以及如何对其进行查询，而且我听说性能可能比其他选项差。 - Rachel

1

我会避免使用 XML 来实现这个：虽然 XML 可以胜任此工作，并且我过去曾经用 XML 实现过类似的东西，但是随着数据结构的增长，性能变得相当糟糕，而且代码复杂度也很高。 - Kell

2

如果您正在使用SQL Server，请不要忽略sqlvariant类型。它非常快速，应该能够胜任您的工作。其他数据库可能有类似的东西。

出于性能原因，XML数据类型并不是很好。如果您在服务器上进行计算，则必须不断反序列化这些内容。

选项1听起来很糟糕，看起来也很糟糕，但从性能角度来看可能是您最好的选择。我以前曾创建过具有Field00-Field99列名的表，因为您无法击败其性能。如果您希望它看起来整洁，可以随时在此表上创建视图！

- Tim Rogers

谢谢，我会再仔细看看SQL变种。我最大的担忧是性能问题，特别是如果我们正在处理超过5000万行的数据时，我不确定它如何处理。 - Rachel

刚刚发现 sql_variants 不能与 LIKE 子句一起使用... 对我来说这是一个巨大的缺点。当然，如果我为每个 UDF 创建一个视图，那么我可以根据 SQL_VARIANT_PROPERTY(value,'BaseType') 将其转换为适当的数据类型... 不过，似乎这对性能不利。 - Rachel

你可以使用LIKE，但必须先转换值。LIKE仅适用于varchars，因此您必须将sql_variant转换为varchar。只要您知道您的UDF是varchar（例如，因为类型存储在其他地方），您就可以将所有行过滤为varchars，然后进行转换并运行LIKE查询：例如。选择* FROM MyTable where variant_type = 'v' Cast(variant_value as varchar(max)) LIKE 'Blah%'这样，您不会将int等转换为字符串，从而减慢速度。 - Tim Rogers

我需要运行一些测试来查看其性能，特别是在处理数百万行数据时。是否了解有关使用 sql_variants 的性能的在线文章？特别是涉及转换和大量记录的情况？ - Rachel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Phil Helmer · Accepted Answer

如果性能是主要问题，我建议采用方案#6……即每个UDF一个表（实际上这是#2的一种变体）。这个答案是专门针对这种情况和所描述的数据分布和访问模式而设计的。

优点：

因为您指出某些UDF仅为整个数据集的一小部分提供值，一个单独的表将为您提供最佳性能，因为该表的大小将根据需要支持UDF而定。同样适用于相关索引。
通过限制要处理的数据量来获得速度提升，将数据拆分成多个表可让您在UDF数据上执行一些汇总和其他统计分析，然后通过外键将该结果连接到主表以获取非汇总属性。
您可以使用反映数据实际内容的表/列名称。
您完全可以使用数据类型、检查约束、默认值等来定义数据域。不要低估由即时数据类型转换导致的性能损失。此类约束还有助于关系数据库管理系统查询优化器开发更有效的计划。
如果您需要使用外键，则内置的声明性参照完整性很少被触发器或应用程序级约束执行所超越。

缺点：

这可能会创建大量的表。强制实施架构分离和/或命名约定可以解决这个问题。
需要更多的应用程序代码来操作UDF定义和管理。我认为，这仍然比选项1、3和4需要更少的代码。

其他考虑因素：

如果数据的性质有任何东西可以使UDFs分组，则应该鼓励这样做。这样，这些数据元素可以合并到一个单独的表中。例如，假设您具有颜色、大小和成本的UDFs。数据的倾向是大多数数据实例看起来像：
```
 'red', 'large', 45.03 
```
与其说
```
 NULL, 'medium', NULL
```
在这种情况下，将3个列合并到一个表中不会带来明显的速度惩罚，因为很少有值为NULL，并且您避免了创建2个更多的表，当您需要访问所有3个列时，需要减少2个连接。

如果您遇到了一个非常频繁使用和填充的UDF导致性能问题，那么应该考虑将其包含在主表中。

逻辑表设计可以带您到一定程度，但当记录数量变得极其庞大时，您还应该开始查看您所选择的RDBMS提供的表分区选项。