是索引键上的cursor.skip（）总是更快吗？ [英] Is cursor.skip() on indexed keys always faster?

问题描述

我有2个数据库： slow 和 fast ;每个进料有4096个条目。 age 键是使用此脚本生成的唯一随机整数：

I have 2 databases: slow and fast; each of which was fed with 4096 entries. The age key is a unique random integer that was generated with this script:

var arr = []
while(arr.length < 4096){
  var randmnum=Math.ceil(Math.random()*1000000)
  var found=false;
  for(var i=0;i<arr.length;i++){
    if(arr[i]==randmnum){found=true;break}
  }
  if(!found)arr[arr.length]=randmnum;
}

var i=0;
for (i=0 ; i< arr.length; ++i) {
    db.fast.insert({name:"john doe", email:"[e-mail]", age:arr[i]});
    db.slow.insert({name:"john doe", email:"[e-mail]", age:arr[i]});
}

然后在mongo shell中：

Then in mongo shell:

> db.createCollection("slow")
> db.createCollection("fast")
> load("theAboveScript.js")
> db.fast.createIndex({"age":1})

如果我测试找到和排序在工作 c $ c> fast db低于预期的 slow 。此外，当我执行以下操作时，执行时间是索引/快速db的大约2倍：

If I test find and sort the amount of work that has been done on the fast db is lower than the slow as expected. Moreover the execution time is about 2 times better with the indexed/fast db.

但 b
$ b

But when I do the following:

> pageNumber=18;nPerPage=20; db.slow.find().skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("executionStats")
> pageNumber=18;nPerPage=20; db.fast.find().skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).explain("executionStats")

工作量是完全相同的，执行时间是相似的，即使快速数据库有点慢。

The amount of work is exactly the same and the execution time is similar, even the fast db is a bit slower.

为什么 cursor.skip（） doesn如果它在索引键上工作，得到任何提升？我希望分页返回分页数据，而不对其进行明确排序。

Why cursor.skip() doesn't get any boost if it works on an indexed keys? I would expect the pagination to return the paged data ordered without sorting it explicitly.

推荐答案

age ，因此没有理由使用索引。

Neither of your queries are doing a filter on age, so there is no reason to use the index.

如果您在<$ c $

If you add a condition on age, there will be a difference (even if minimal with so few documents)

> pageNumber=18;nPerPage=20; db.slow.find({age:{$gt:200}}).
      skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).
      explain("executionStats")

# "executionTimeMillis" : 14,
# "inputStage" : {
#     "stage" : "COLLSCAN",

> pageNumber=18;nPerPage=20; db.fast.find({age:{$gt:200}}).
      skip(pageNumber > 0 ? ((pageNumber-1)*nPerPage) : 0).limit(nPerPage).
      explain("executionStats"

# "executionTimeMillis" : 0,
# "inputStage" : {
#     "stage" : "IXSCAN",

这篇关于是索引键上的cursor.skip（）总是更快吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！