在 mongodb 集合上创建索引，在对大型数据集进行排序时仍然失败 [英] Created indexes on a mongodb collection, still fails while sorting a large data set

问题描述

我的查询如下:

db.chats.find({ bid: 'someID' }).sort({start_time: 1}).limit(10).skip(82560).pretty()

我在这个顺序的字段上有聊天集合的索引

I have indexes on chats collection on the fields in this order

{
  "cid" : 1,
  "bid" : 1,
  "start_time" : 1
}

我正在尝试执行排序，但是当我编写查询并检查解释()的结果时，我仍然得到了 winPlan

I am trying to perform sort, but when I write a query and check the result of explain(), I still get the winningPlan as

{  
   "stage":"SKIP",
   "skipAmount":82560,
   "inputStage":{  
      "stage":"SORT",
      "sortPattern":{  
         "start_time":1
      },
      "limitAmount":82570,
      "inputStage":{  
         "stage":"SORT_KEY_GENERATOR",
         "inputStage":{  
            "stage":"COLLSCAN",
            "filter":{  
               "ID":{  
                  "$eq":"someID"
               }
            },
            "direction":"forward"
         }
      }
   }
}

我希望在获胜计划中没有排序阶段，因为我已经为该集合创建了索引.没有索引会导致以下错误

I was expecting not to have a sort stage in the winning plan as I have indexes created for that collection. Having no indexes will result into the following error

MongoError: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM [duplicate]

但是我设法通过将 ram 上的大小分配从 32mb 增加到 64mb 来使排序工作，寻求帮助以正确添加索引

However I managed to make the sort work by increasing the size allocation on ram from 32mb to 64mb, looking for help in adding indexes properly

推荐答案

索引中字段的顺序很重要.要按不在索引键模式开头的字段对查询结果进行排序，查询必须在排序键之前的所有前缀键上包含相等条件.cid 字段不在查询中，也不用于排序，因此您必须将其省略.然后您将 bid 字段放在索引定义中，因为您在等式条件中使用它.start_time 紧随其后，用于排序.最后，索引必须如下所示:

The order of fields in an index matters. To sort query results by a field that is not at the start of the index key pattern the query must include equality conditions on all the prefix keys that precede the sort keys. The cid field is not in the query nor used for sorting, so you must leave it out. Then you put the bid field first in the index definition as you use it in the equality condition. The start_time goes after that to be used in sorting. Finally, the index must look like this:

{"bid" : 1, "start_time" : 1}

参见 文档 以供进一步参考.

See the documentation for further reference.

这篇关于在 mongodb 集合上创建索引，在对大型数据集进行排序时仍然失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！