在数据库中存储可重新排序的项目的高效方法 [英] Efficient way to store reorderable items in a database

问题描述

所以我有一张用户收藏表。它们有几百万行。

So I have a table of user favorites. There's a few million rows of them.

目前，它们只有三列： id （pk）， userId 和 someFkRef 。 userId 中有一个索引，以便我可以快速选择用户的收藏夹。

Currently, they have only three columns: id(pk),userId and someFkRef. There's an index on userId to allow me to select a user's favorites quickly.

目前，这些按 id 这只是插入顺序。我们希望为用户提供一个机会来重新订购他们的收藏，最有可能通过某种拖放式互动。

Currently these are ordered by id which is effectively just the insert order. We'd like to offer the user a chance to re-order their favorites, most likely via some sort of drag and drop interaction.

我的第一个（我怀疑天真的）方法是简单地在 userId ，订单列和复合索引>订单。然而，反思后，当用户将其项目移动到列表上一些距离时，项目的起始位置和结束位置之间的所有中间行将需要重新计算其顺序列，因此，索引也。

My first (and I suspect naive) approach to this would be to simply add an order column and a composite index over userId,order. However, upon reflection, when the user moves their item some distance over the list, all intermediate rows between the item's start position and end position will need their order column recalculated and therefore, the index too.

这是（很可能）不好。

This is (most likely) bad.

在我花费时间尝试量化完全不同之前，我想知道是否有更好的基于表的表示，使用我描述的各种操作来处理更便宜

Before I spend ages trying to quantify exactly how bad, I'm wondering if there's a better table-based representation that is cheaper to manipulate with the kinds of operations I describe above.

推荐答案

对于拖放式互动，更好的赌注是优先考虑。您将从优先级开始为1，2，3等等，就像排序顺序。

For a drag and drop interaction, the better bet is a priority. You would start with the priorities being 1, 2, 3, and so on, just like a sort order.

但是，用户想要将项5移动到1之间和2. Voila！给它的价值1.5。没有其他价值需要改变。索引更新负责其余的操作。

But then, the user wants to move item 5 between 1 and 2. Voila! Give it the value of 1.5. No other values need to change. The index update takes care of the rest.

为了正常工作，优先级需要作为浮点数存储。这可能是一个问题。此外，足够大的更改可能会导致浮点限制。所以，如果用户尝试取最后一个元素并将其插入到前两个元素之间，那么他/他可以把它关闭几十次左右。

For this to work, the priority needs to be stored as a floating point number. That might be an issue. Also, a sufficiently large number of changes could result in pushing the limits of floating point. So, if a user tries to take the last element and insert it between the first two, s/he can get away with it about few dozen times or so.

你可以通过一个过程定期重新分配一个（或所有用户，如果批量）从1开始的号码。

You can fix this with a process that periodically reassigns number for one (or all users, if in batch) starting at 1.

这篇关于在数据库中存储可重新排序的项目的高效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！