blocks|key|797223|text|我连接了两个答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|797224|问题是，当你使用跳过和限制，没有排序时，它只是按照你向表中写入数据的顺序按表的顺序进行分页，所以引擎需要建立第一个临时索引。最好使用ready+_id索引:)您需要使用sort+by+_id。比使用像这样的大型表要快得多。|797225|db.myCollection.find().skip(4000000).limit(1).sort({+"_id":+1+});|code-block|syntax|javascript|797226|在PHP中，它将是|797227|$manager+=+new+\MongoDB\Driver\Manager("mongodb://localhost:27017",+[]);
$options+=+[
++++++++++++'sort'+=>+array('_id'+=>+1),
++++++++++++'limit'+=>+$limit,+
++++++++++++'skip'+=>+$skip,

++++++++];
$where+=+[];
$query+=+new+\MongoDB\Driver\Query($where,+$options+);
$get+=+$manager->executeQuery("namedb.namecollection",+$query);|797228|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

i connected two answer. 

the problem is when you using skip and limit, without sort, it just pagination by order of table in the same sequence as you write data to table so engine needs make first temporary index. is better using ready _id index :) You need use sort by _id. Than is very quickly with large tables like.

<pre><code>db.myCollection.find().skip(4000000).limit(1).sort({ "_id": 1 });
</code></pre>

In PHP it will be

<pre><code>$manager = new \MongoDB\Driver\Manager("mongodb://localhost:27017", []);
$options = [
 'sort' =&gt; array('_id' =&gt; 1),
 'limit' =&gt; $limit, 
 'skip' =&gt; $skip,

 ];
$where = [];
$query = new \MongoDB\Driver\Query($where, $options );
$get = $manager-&gt;executeQuery("namedb.namecollection", $query);
</code></pre>

blocks|key|797306|text|我的集合有大约130万个文档(不是那么大)，索引正确，但仍然受到这个问题的很大影响。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|797307|在阅读了其他答案之后，解决方案就清晰了；分页集合必须按计数整数排序，该整数类似于SQL的自动增量值，而不是基于时间的值。|797308|问题出在skip上；没有其他方法可以绕过它；如果您使用skip，那么当您的集合增长时，您肯定会遇到这个问题。|offset|length|style|CODE|797309|使用带有索引的计数整数允许您使用索引进行跳转，而不是跳过。这不适用于基于时间的值，因为您无法计算基于时间的跳转位置，因此在后一种情况下，跳过是唯一的选择。|797310|另一方面，|797311|通过为每个文档分配一个计数编号，写入性能将受到影响；因为所有文档都必须按顺序插入。这对我的用例来说很好，但我知道解决方案并不适合每个人。|797312|最受欢迎的答案似乎不适用于我的情况，但这个答案适用。(我需要能够按任意页码向前查找，而不是一次只查找一个页码。)|797313|此外，如果您正在处理delete，这也很困难，但仍然可能，因为MongoDB支持使用负值的$inc进行批量更新。幸运的是，我不必处理我正在维护的应用程序中的删除。|797314|把这个写下来，写给我未来的自己。用我正在处理的应用程序来解决这个问题可能太麻烦了，但是下一次，如果我遇到类似的情况，我会构建一个更好的。|797315|entityMap^0|0|0|4|4|R|4|0|0|0|0|0|A|6|19|4|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@$F|10|G|11|H|I]|$F|12|G|13|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|15|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|16|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|17|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|18|8|@$F|19|G|1A|H|I]|$F|1B|G|1C|H|I]]|9|@]|A|$]]|$1|T|3|U|5|6|7|1D|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|W|$]]

My collection has around 1.3M documents (not that big), properly indexed, but still takes a big performance hit by the issue.
After reading other answers, the solution forward is clear; the paginated collection must be sorted by a counting integer similar to the auto-incremental value of SQL instead of the time-based value.
The problem is with <code>skip</code>; there is no other way around it; if you use <code>skip</code>, you are bound to hit with the issue when your collection grows. 
Using a counting integer with an index allows you to jump using the index instead of skip. This won't work with time-based value because you can't calculate where to jump based on time, so skipping is the only option in the latter case.
On the other hand, 
by assigning a counting number for each document, the write performance would take a hit; because all documents must be inserted sequentially. This is fine with my use case, but I know the solution is not for everyone. 
The most upvoted answer doesn't seem applicable to my situation, but this one does. (I need to be able to seek forward by arbitrary page number, not just one at a time.)
Plus, it is also hard if you are dealing with <code>delete</code>, but still possible because MongoDB support <code>$inc</code> with a minus value for batch updating. Luckily I don't have to deal with the deletion in the app I am maintaining.
Just write this down as a note to my future self. It is probably too much hassle to fix this issue with the current application I am dealing with, but next time, I'll build a better one if I were to encounter a similar situation.

blocks|key|581359|text|我将建议一种更激进的方法。将skip/limit+(实际上是一种边缘情况)与基于存储桶的排序范围相结合，并且页面不是基于固定数量的文档，而是基于一定的时间范围(或者您的排序方式)。所以你有每个时间范围的顶级页面，如果你需要跳过/限制，你可以在这个时间范围内有子页面，但我怀疑这些存储桶可以足够小，根本不需要跳过/限制。通过使用排序索引，可以避免光标遍历整个库存以到达最后一页。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|581360|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I'm going to suggest a more radical approach. Combine skip/limit (as an edge case really) with sort range based buckets and base the pages not on a fixed number of documents, but a range of time (or whatever your sort is). So you have top-level pages that are each range of time and you have sub-pages within that range of time if you need to skip/limit, but I suspect the buckets can be made small enough to not need skip/limit at all. By using the sort index this avoids the cursor traversing the entire inventory to reach the final page.

I have over 300k records in one collection in Mongo.

When I run this very simple query:

<pre><code>db.myCollection.find().limit(5);
</code></pre>

It takes only few miliseconds.

But when I use skip in the query:

<pre><code>db.myCollection.find().skip(200000).limit(5)
</code></pre>

It won't return anything... it runs for minutes and returns nothing.

How to make it better?

Slow pagination over tons of records in mongodb

MongoDB

我在Mongo的一个收藏中有超过30万条记录。当我运行这个非常简单的查询时：db.myCollection.find().limit(5);这只需要几毫秒。但是当我在查询中使用跳过时：db.myCollection.find().skip(200000).limit(5)它不会返回任何东西。它运行几分钟，并且不返回任何...

问mongodb中大量记录的分页速度很慢
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mongodb中大量记录的分页速度很慢EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mongodb中大量记录的分页速度很慢
EN