sql - 动态连接列的查询性能优化

Question

SQL Server数据库现状

有一个包含以下列的表条目:

• 条目ID(整数)
• 条目名称 (nvarchar)
• 条目大小(整数)
• EntryDate(日期时间)

此外，应该可以为条目保存额外的元数据。这些元数据的名称和值应该可以自由选择，并且应该可以动态添加这些元数据而不更改数据库的表结构。每个元数据键可以是以下数据类型之一:

• 文字
• 数值
• 日期时间
• bool 值(True/False)

因此有一个表 DataKey 来表示元数据名称和数据类型，其中包含以下列:

• DataKeyID(整数)
• 数据 key 名称 (nvarchar)
• DataKeyType (smallint) 0:文本； 1:数字； 2:日期时间； 3:位

根据元数据键的数据类型，可以在表 DataValue 中插入 Entry 和 DataKey 值的每个组合。对于每种数据类型，都有一个可为空值列。该表包含以下列:

• DataValueID(整数)
• EntryID (int) 外键
• DataKeyID(int)外键
• TextValue (nvarchar) 可为空
• NumericValue( float )可为空
• DateValue(日期时间)可为空
• BoolValue(位)可为空

数据库结构图:

目标

目标是检索满足 WHERE 子句中规范的条目列表。就像下面的例子:

假设:

• 元数据键 KeyName1 是文本
• 元数据键 KeyName2 是 DateTime
• 元数据键 KeyName3 是数字
• 元数据键 KeyName4 是 bool 值

查询:

... WHERE (KeyName1 = „Test12345“ AND KeyName2 BETWEEN ’01.09.2012 00:00:00’ AND
’01.04.2013 23:59:00’) OR (KeyName3 > 15.3 AND KeyName4 = True)

目标是以非常有效的方式执行这些查询，并且还使用大量数据，例如

• 条目数 > 2.000.000
• 数据键的数量在 50 到 100 之间或可能 > 100
• 每个条目至少指定一个值的子集，或者也可能是每个键的一个值 (2.000.000 * 100)

问题

第一个问题出现在构建查询时。通常查询需要具有可在 WHERE 子句中使用的列的集合。在这种情况下，查询中使用的列是表 DataKey 中的条目，并且能够动态添加元数据，而无需更改数据库表结构。在研究过程中，我们发现了一种在运行时使用数据透视表技术的解决方案。但事实证明，当数据库中有大量数据时，这种解决方案非常慢。

问题

• 是否有更有效的方法或结构来为此目的保存数据？
• 如何满足上面列出的要求以及查询时的性能和时间消耗？

这是一个包含所描述的数据库结构和一些示例数据的 sql fiddle :http://www.sqlfiddle.com/#!3/d1912/3

Answer 1

实体属性值设计(这就是您这里所遇到的)的基本缺陷之一是高效和高性能查询的困难。

更有效的数据存储结构是放弃EAV并使用规范化的关系形式。但这必然涉及当数据结构发生变化时数据库结构的改变(这应该是不言而喻的)。

您可以放弃 TextValue/NumericValue/DateValue/BoolValue 字段，并将其替换为单个 sql_variant 列，这会稍微降低查询复杂性，但根本问题仍然存在。

顺便说一句，如果您必须处理金钱，将所有数字存储为 float 会导致问题。