有效地在数据库中存储项目位置（用于排序） [英] Storing item positions (for ordering) in a database efficiently

问题描述

场景：

有一个用户拥有的电影数据库，电影显示在名为我的电影的页面上，电影可以显示在用户期望的顺序。例如，在位置＃1中的战斗俱乐部，在位置＃3中的驱动等等。

显而易见的解决方案是存储每个项目的位置，例如：

movieid，userid，position

1 | 1 | 1

2 | 1 | 2

3 | 1 | 3

然后当输出数据时按位置排序。这种方法对于输出工作正常，但是它在更新时有问题：项目的位置所有其他位置需要更新，因为位置是相对的。如果电影＃3现在在位置编号2，则现在需要将电影＃3更新到位置＃2。如果数据库包含10,000个电影，并且电影从位置＃1移动到位置＃9999，几乎有10,000行要更新！

我唯一的解决方案是单独存储定位，而不是为每个项目位置一个单独的字段，它只是一个大数据转储的位置，在运行时取得并与每个项目（json，xml，任何）相关联，但感觉...低效，因为数据库不能

我总结的问题：在列表中存储项目位置的最有效的方式是获取和更新吗？ / strong>

解决方案

如果使用位置和时间戳的组合，用户将电影放在给定位置，比试图维护实际位置，那么你可以实现一个相当简单的方法同时选择和更新数据。例如;基本数据集：

  create table usermovies（userid int，movieid int，position int，positionsetdatetime datetime）
 
插入usermovies（userid，movieid，position，positionsetdatetime）
值（123,99,1，getutcdate（））
插入usermovies $ b值（123,98,2，getutcdate（））
插入usermovies（userid，movieid，position，positionsetdatetime）
值（123，97，3，getutcdate（））
插入到usermovies（userid，movieid，position，positionsetdatetime）
值（123，96，4，getutcdate（））
插入usermovies 123，95，5，getutcdate（））
 insert into usermovies（userid，movieid，position，positionsetdatetime）
 values（123，94，6，getutcdate（））
 
插入到usermovies（userid，movieid，position，positionsetdatetime）
值（987,99,1，getutcdate（））
插入usermovies 987,98,2，getutcdate（））
插入usermovies（userid，movieid，position，positionsetdatetime）
值（987,97,3，getutcdate（））
插入usermovies 
值（987,96,4，getutcdate（））
插入到usermovies（userid，movieid，position，positionsetdatetime）
值（987,95， 5，getutcdate（））
 insert into usermovies（userid，movieid，position，positionsetdatetime）
 values（987，94，6，getutcdate（））
  / pre> 
 
 
如果您使用这样的查询查询用户的电影：
 ; with usermovieswithrank as（
 select userid 
，movieid 
，dense_rank（）over（按用户ID排序，按位置asc，positionsetdatetime desc）作为movierank 
从usermovies 
）
 select * from usermovieswithrank where userid = 123 order by userid，movierank asc 
  
然后你会得到预期的结果：
  USERID MOVIEID MOVIERANK 
 123 99 1 
 123 98 2 
 123 97 3 
 123 96 4 
 123 95 5 
 123 94 6 
  
要移动电影的排名，我们需要更新position和positionsetdatetime列。例如，如果userid 123将电影95从5级移动到2级，则我们这样做：
 更新usermovies设置位置= 2 ，positionsetdatetime = getutcdate（）
其中userid = 123和movieid = 95 
  
 （使用上面的更新后的SELECT查询）：
  USERID MOVIEID MOVIERANK 
 123 99 1 
 123 95 2 
 123 98 3 
 123 97 4 
 123 96 5 
 123 94 6 
  
然后，如果userid 123将电影96移动到1：
  update usermovies set position = 1，positionsetdatetime = getutcdate（）
其中userid = 123和movieid = 96 
  
 
 
 ：
  USERID MOVIEID MOVIERANK 
 123 96 1 
 123 99 2 
 123 95 3 
 123 98 4 
 123 97 5 
 123 94 6 
  
当然，你最终会在usermovies表中重复位置列值，但是使用这种方法，你永远不会显示该列，你只需使用它和positionsetdatetime一起为每个用户确定排序的排名，你确定的排名是真实位置。 
 
 
 
如果在某个时候你想要position列正确地反映电影排名没有参考positionsetdatetime你可以使用movierank从上面的选择查询更新usermovies位置列值，因为它不会实际影响确定的电影排名。
 
Scenario:

There is a database of movies a user owns, movies are displayed on a page called "my-movies", the movies can be displayed in the order that the user desires. For example "Fight Club" in position #1, "Drive" in position #3 and so on and so forth. 

The obvious solution is to store a position with each item, for example:

movieid, userid, position

1      | 1     | 1

2      | 1     | 2

3      | 1     | 3  

Then when outputting the data is ordered by the position. This method works fine for output however it has a problem when updating: the position of an item all the other positions need to be updated because positions are relative. If movie #3 is now in position number 2 then movie #3 now needs to be updated to position #2. If the database contains 10,000 movies and a movie is moved from position #1 to position #9999 that's almost 10,000 rows to be updated!

My only solution is to store positioning separately, instead of having an individual field for each items position it's just one big data dump of positions that are taken in run time and associated with each item (json, xml, whatever) but that feels... inefficient because the database can't be left to do the sorting.

My summarised question: What's the most efficient way of storing items positions in a list that is friendly to fetching and updating?
解决方案
If you use a combination of the position and a timestamp that the user put a movie in a given position rather than trying to maintain the actual position, then you can achieve a fairly simple means of both SELECTing and UPDATEing the data. For example; a base set of data:
create table usermovies (userid int, movieid int, position int, positionsetdatetime datetime)

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 94, 6, getutcdate())

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 94, 6, getutcdate())
If you query the user's movies using a query like this:
;with usermovieswithrank as (
  select userid
  , movieid 
  , dense_rank() over (partition by userid order by position asc, positionsetdatetime desc) as movierank
  from usermovies
)
select * from usermovieswithrank where userid=123 order by userid, movierank asc
Then you'll get the expected result:
USERID  MOVIEID     MOVIERANK
123     99          1
123     98          2
123     97          3
123     96          4
123     95          5
123     94          6
To move one of the rankings of the movies we need to update the position and the positionsetdatetime columns. For example, if userid 123 moves movie 95 from rank 5 to rank 2 then we do this:
update usermovies set position=2, positionsetdatetime=getutcdate() 
where userid=123 and movieid=95 
Which results in this (using the SELECT query above following the update):
USERID  MOVIEID     MOVIERANK
123     99          1
123     95          2
123     98          3
123     97          4
123     96          5
123     94          6
Then if userid 123 moves movie 96 to rank 1:
update usermovies set position=1, positionsetdatetime=getutcdate()
where userid=123 and movieid=96 
We get:
USERID  MOVIEID     MOVIERANK
123     96          1
123     99          2
123     95          3
123     98          4
123     97          5
123     94          6
Of course you'll end up with duplicate position column values within the usermovies table, but with this method you'll never show that column, you simply use it along with positionsetdatetime to determine a sorted rank for each user and the rank you determine is the real position. 

If at some point you want the position column to properly reflect the movie rankings without reference to the positionsetdatetime you can use the movierank from the select query above to update the usermovies position column value, as it wouldn't actually affect the determined movie rankings.

                        
这篇关于有效地在数据库中存储项目位置（用于排序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！