如何有效地获取日期范围内的分页查询结果(How to efficiently get paged query results for a date range)

 刚开始使用NoSql，特别是DynamodB，我被问到以下问题： 
 
 有一个有大量行的现有表，其中的字段如下所示： 
 
RecordId (Guid = partition key)
RecordType (Guid = sort key)
UserId (Guid)
CreationDateTime
... and some other attributes ...
 
 还有一个GSI（比如GSI1）： 
 
UserId = partition key
RecordType = sort key
 
 某些用户使用相同的RecordType生成大量行（可能每天数百或数千），因此此索引通常具有大量具有相同UserId + RecordType的行。 
 
 我希望能够有效地执行分页查询以查找与特定UserId + RecordType匹配的结果，并在一定范围的日期内具有CreationDateTime。 
 
 达到此目的的最佳方式是什么？ 
 
 有人建议我可以用partitionKey = Userid和Sort key = CreationDateTime创建第二个GSI（比如GSI2）。 这个想法是，这将是找到所需日期范围内UserId + RecordType的第一行的有效方法，然后这将用作在GSI1中搜索的起点（PaginationToken）。 只有在GSI1中具有重复键的行按创建顺序存储时才能使用。 
 
 这有意义吗？ 如果不是，那么更好的方法是什么？ 

Just starting out with NoSql in general and DynamodB in particular, and I've been asked the following question:
 
There is an existing table with a large number of rows with fields something like:
 
RecordId (Guid = partition key)
RecordType (Guid = sort key)
UserId (Guid)
CreationDateTime
... and some other attributes ...
 
There is also a GSI (say GSI1) with:
 
UserId = partition key
RecordType = sort key
 
Some users generate a large number of rows with the same RecordType (maybe hundreds or thousands per day), so this index typically has a large number of rows with the same UserId+RecordType.
 
I want to be able to efficiently execute a paged query for results that match a specific UserId+RecordType, and have a CreationDateTime within a range of dates.
 
What's the best way to achieve this? 
 
It's been suggested to me that I can create a second GSI (say GSI2) with partitionKey = Userid and Sort key = CreationDateTime. The idea being that this would be an efficient way to find the first row for UserId+RecordType that is in the required date range, and that then this would be used as a starting point (PaginationToken) for searching in GSI1. Which would only work if the rows in GSI1 with a duplicate key are stored in order of creation.
 
Does this make sense? And if not, what would be a better approach?

原文：https://stackoverflow.com/questions/47657590