如何存储经常改变数据库位置的订购商品
我需要能够在数据库中存储大量订购的商品列表。到目前为止，这很简单：
ID Position OtherFields
1 45 ...
2 4736 ...
3 514 ...
...
在查询中，我总是需要以正确的顺序获取几个项目（根据其他字段进行过滤）。也很简单，在位置上放置索引并使用“按位置排序”。
现在的问题：项目经常更改其位置，而不仅仅是 1 或 2。如果 ID 2 将位置从 4736 更改为 2000，我需要更新其位置以及旧之间所有元素的位置位置2000和4735，每行加1。而且每笔交易改变的不仅仅是一个ID，而是几个，而且短时间内可能会有很多笔交易。
我认为处理 更新 问题的最优雅的方法是使用链接列表而不是位置列，在其中我可以通过将其前任链接到其后继来从其旧位置删除 ID 2，然后插入通过将其新的前任和后继者联系起来，它可以在其他地方使用。这将是每次位置更改时持续且少量的更新，这也是我处理更改的首选方式（在我的例子中是 Java）。然而，这引发了以正确顺序查询的 N+1 问题 - 即使对于一些元素，我也必须在最坏的情况下遍历整个列表才能找出它们的正确顺序。
所以我的问题是：为了在必要的更新和查询性能之间取得良好的平衡，您会建议什么？
到目前为止，我看到两个有希望的方向：
是否有一个 DBMS（最好是开源）不仅可以使用语法糖处理链接列表，而且还可以具有良好的性能，例如通过使用链接元素的内部索引？
也许也可以选择只使用一个 BLOB 来存储整个链接列表！这样的链接列表可以获得多大/它会在数据库中使用多少内存以及当获取 1.000.000 个条目时？我正在使用 Java + Hibernate 以防万一。我想在获取 BLOB 后处理内存中的整个列表应该相当快！？
但当然也欢迎其他想法！
I need to be able to store a large list of ordered items in the DB. So far that's straight-forward:
ID Position OtherFields
1 45 ...
2 4736 ...
3 514 ...
...
In queries, I always need to get just a few items (filtered based on OtherFields) but in the correct order. Easy as well, putting an index on Position and using "order by Position".
Now the problem: Items change their Position frequently, and not just by 1 or 2. If ID 2 changes the Position from 4736 to 2000, I need to update its Position and the Position of all elements between old Position 2000 and 4735, adding 1 in each row. And it's not only one ID that changes per transaction but a few, and there can be many transactions within a short time.
I think the most elegant way to handle the update problem would be using a linked list instead of a Position column where I can just remove ID 2 from its old position by linking its predecessor to its successor and then insert it elsewhere by linking it between its new predecessor and successor. That would be a constant and small number of updates per Position change and it would also be my preferred way of handling the changes (in Java in my case). However this raises the N+1 problem for querying in the correct order - even for a few elements, I have to go through the whole list in the worst case for finding out their correct order.
So my question is: What would you recommend to get a good balance between necessary updates and query performance?
So far I see two promising directions:
Is there a DBMS (ideally OpenSource) that can handle linked lists not only with syntactic sugar but also with a good performance, e.g. by using internal Indices for the linked elements?
Maybe it would also be an option to just have a BLOB where the whole Linked List would be stored in! How big could such a Linked List get / how much memory would it use in the DB and when fetched for let's say 1.000.000 entries? I'm using Java + Hibernate in case it matters. I imagine that processing even the whole list in memory after fetching the BLOB should be pretty fast!?
But of course other ideas are welcome as well!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您放宽
Position列必须包含 1 到 N 之间的整数的限制，并允许它包含任何数字，那么您就可以高效地进行搜索和更新。您可以通过计算平均值 (A + B) DIV 2 在位置 A 和 B 的其他两个项目之间插入一个项目。例如，如果 A 为 10000，B 为 12000，则您的新位置为 11000。有时您会用完间隙由于集群，此时您可以遍历整个表，更均匀地重新分配位置。
If you relax the constraint that the
Positioncolumn must contain integers from 1 to N and instead allow it to contain any numbers then you can do both searches and updates efficiently.You can insert an item between two other items with position A and B by calculating the average (A + B) DIV 2. For example if A is 10000 and B is 12000 then your new position is 11000. Occasionally you will run out of gaps due to clustering, at which point you can run through the whole table redistributing the positions more evenly.
使用小数表示位置怎么样？如果这样做，您可以使用以下方法将其放在其他位置之间：
原始记录是：
然后假设您将 ID 1 移动到 5000 之前
现在假设您要将 ID 2 放在 1 和 5000 之间：
这样您就只更改一条记录...
更新：
重新阅读@Mark Byers的建议后，我们的解决方案似乎非常相似，尽管使用小数对我来说似乎更简单...
What about using a decimal for position? If you do, you could use the following method to put it between other positions:
Original records are:
Then say you move ID 1 to just before 5000
Now lets say you want to put ID 2 between 1 and 5000:
This way you are only changing one record...
UPDATE:
After re-reading @Mark Byers' suggestion it appears that our solutions are very similar, though using a decimal seems much simpler to me...
另一种解决方案是使用词汇排名。每当两个字符串之间没有值时，只需添加一个新字符即可。唯一的缺点是，与其他答案中指定的数值解相比，它消耗更多的内存。
对于无错误的数据库来说，规范化不是必需的，但可以选择进行规范化以减少字符串长度。
这是一个有趣的视频，详细解释了它：https:// www.youtube.com/watch?v=OjQv9xMoFbg&feature=youtu.be
Another solution would be to use lexical ranking. Whenever there is no value between two strings, one would simply add a new character. The only disadvantage is, that it consumes more memory compared to a numerical solution, which are named in other answers.
A normalization isn't necessary for an error free database, but can be optionally done to reduce the string lenght.
Here is an interesting video, which explains it in detail: https://www.youtube.com/watch?v=OjQv9xMoFbg&feature=youtu.be
以下内容可能会有所帮助。它不会直接回答您的问题，但可以阐明如何做到这一点（如果可能满足您的要求）：
对mysql表中的条目进行排名
The following might be helpful. It does not answer your questions directly, but could shed some light on how to do this (if it is possible for your requirements):
ranking entries in mysql table