有很多关于数据正常化的帖子和讨论。大多数时候,我看到人们很难坚持正常化,但并不总是如此,而且这似乎是逐个案例,所以我将描述我们的案例。这看起来并不复杂,但我觉得也许我只是错过了一些优雅的东西。我很希望有人能:
最主要的是我们要做的是近乎实时的搜索,在用户输入搜索字段时逐个过滤结果,所以事情需要很好的响应。但是非常低性能的硬件--想想IoT。搜索需要返回单个项目名称、包名和找到的包中的单个项目列表。项目和捆绑有很多到多的关系,尽管在任何捆绑中的项目数量是有限的,所以有界限,这是值得的。
Ex DB:
[ items ]
int: item_id
string: name
….
[ bundles ]
int: bundle_id
string: bundle_name
….
[ items_x_bundles ]
int: item_id
int: bundle_id
想象一下礼品篮里不同的一捆食物,通常不会超过,比如说,一个篮子组合中的10个物品,但是没有绝对的固定限制。新的包很少被创建,而且永远不会改变。
让我们说有不同的个别项目,例如:
apple, orange, pear, banana, saltines, cheez-its, ritz,
potato chips, carrots, peas, beans, oreos, gummies,
hershey bars, coke, gatorade, milk, etc.
和捆绑,例如:
special : [ apple, saltines, peas, gummies, coke ],
deluxe: [ pear, carrots, potato chips, oreos ],
fancy: [ orange, ritz, beans, gummies, milk ],
mondo: [ banana, pear, saltines, carrots, peas, oreos, coke, milk ]
搜索"delu“将返回:
[ deluxe: [ pear, carrots, potato chips, oreos ]
搜索"appl“将返回:
[ apple ]
[ special : [ apple, saltines, peas, gummies, coke ] ]
搜索“牛奶”将返回:
[ milk ]
[ fancy: [ orange, ritz, beans, gummies, milk ]
[ mondo: [banana, pear, saltines, carrots, peas, oreos, coke, milk ]
如果我们保持数据完全规范化,那么很容易找到单独的项名,但是返回包含搜索字符串的每个篮子中的单个项的列表要复杂得多。效率是很重要的,因为同样,这将运行在低性能的IoT硬件上.如果这重要的话,可以使用sqlite3。
一个潜在的解决方案是在创建包时向Bundle表添加一个字段。类似于:
string: bundle_items
就特殊情况而言,它看起来可能是:
"apple / saltines / peas / gummies / coke".
这使得以冗余为代价的搜索变得更快/更容易。对我来说,这就像是一个“黑客”,但我并没有看到一个明显的优雅、高效的解决方案。
更新
我将5个更新/迭代压缩到这个更新/迭代中。
也许我上面说的不太清楚,但性能问题是固有的。低功耗物联网级硬件,和面向用户的实时过滤器,需要搜索每个字符输入的数据。我们预计,无论我们如何构造它,它都不会像我们所希望的那样快,因为任何延迟都将直接被用户注意到,甚至是一秒之内。我没有硬数字,因为在开发机器上执行基准测试模拟相当容易,在实际硬件上的情况就不那么简单了。这是否意味着无论如何我们都需要去正常化/优化呢?也许吧,但我还不太了解这个事实,所以这里的问题就来了。另外,我想知道我们正在考虑的特定的非规范化方法(上面)是否存在明显的问题。
我知道如何查询非规范化数据,但我不知道如何对规范化数据构建一个智能的、合理优化的查询。这将有助于指导我们做出决定。所以:
问题1)对标准化数据的智能(快速)查询是什么样子的,以实现上面列出的结果?
问题2)有人看到我所描述的去规范化方法有什么明显的问题吗?在所描述的上下文中,这是否有意义和/或是否有一个不同的、更好的解决方案?
过了几次之后,Bill的下面的查询起作用了,因此回答了第一部分,谢谢。最后,第2部分可能会出现另一个问题。
如果有人跟进,那么不同类型查询的真实百分比差异会有很大差异(取决于记录的数量),坦率地说,我们需要更深入地研究。它的不同并不令人惊讶,但数量是惊人的。从大约15倍到超过35 000倍不等,没有不合理的记录数量。即使是15倍,这可能更接近现实世界,我想说,我们倾向于去正常化,但这提供了一个正常工作的查询测试。
发布于 2016-07-23 01:06:00
如果将数据保存在规范化表中,则可以执行如下查询:
经过几次编辑并测试此查询(SQLFiddle)之后:
SELECT CONCAT(b1.bundle_name, ' : ', GROUP_CONCAT(i1.name))
FROM bundles b1
JOIN items_x_bundles bi1 USING (bundle_id)
JOIN items i1 USING (item_id)
WHERE b1.bundle_name LIKE CONCAT('milk', '%')
GROUP BY b1.bundle_id
UNION ALL
SELECT CONCAT(b2.bundle_name, ' : ', GROUP_CONCAT(i2b.name))
FROM bundles b2
JOIN items_x_bundles bi2 ON (b2.bundle_id=bi2.bundle_id)
JOIN items i2 ON (bi2.item_id=i2.item_id)
JOIN items_x_bundles bi2b ON (b2.bundle_id=bi2b.bundle_id)
JOIN items i2b ON (bi2b.item_id=i2b.item_id)
WHERE i2.name LIKE CONCAT('milk', '%')
GROUP BY b2.bundle_id
UNION ALL
SELECT i3.name
FROM items i3
WHERE i3.name LIKE CONCAT('milk', '%')
?
占位符是绑定搜索单词的地方。是啊,你得绑三次。
将索引放在items(name)
、bundles(bundle_name)
、items_x_bundles(item_id,bundle_id)
和items_x_bundles(bundle_id,item_id)
上。
然后使用解释确认查询是否有效地使用索引。
发布于 2016-07-21 01:05:37
这太长了,不能发表评论。
规范化是为关系数据库设计数据模型时可以使用的工具。它相当强大。但是,它的初衷是支持数据完整性。任何一项数据都存储在一个地方,准确地说是一次。更新很容易,因为更新只进行一次。在更新数据时,规范化特别重要,因此底层数据模型保持一致性。
关系数据库通常用于其他目的,如分析和报告。实际上,我经常使用创建一次、然后多次查询的表。它们在必要时被重新创建。在这种情况下,正常化不一定有帮助。
是否对数据进行规范化以及如何对其进行规范化,在很大程度上取决于应用程序。我倾向于在规范化的大小上出错;但是,如果您有充分的理由去还原数据,这是很好的,特别是对于主要是只读的应用程序。
https://stackoverflow.com/questions/38492106
复制相似问题