如何在MongoDB中使用`$或`和`$ in`查询进行排序? [英] How does sorting work with `$or` and `$in` queries in MongoDB?
问题描述
这是这个问题的后续行动 - 请参阅上下文。
This is a follow-up to this question - see that for context.
此问题涉及链接问题的几个特殊情况 - 即使用时MongoDB中的排序如何工作$ in
或 $或
运算符,以及如何确保使用索引进行排序与内存中的排序。
This question concerns a couple of special cases of the linked question - namely how sorting in MongoDB works when using $in
or $or
operators, and how to ensure use of an index for sorting vs. an in-memory sort.
$ in:
例如,假设我们有一个文档结构的集合
For example, assume we have a collection where the document structure is
{a: XXX, b: XXX}
...我们在 a
和 b
中有一个复合索引该订单并希望运行查询
... and we have a compound index on a
and b
in that order and want to run the query
{a: {$in: [4, 6, 2, 1, 3, 10]}, b: {$gt: 1, $lt: 6}}
如何如果是 a
或 b
,则进行排序? $ in
是一个相等的运算符,但在我看来,带有索引的 b
的排序是不可能的即使是这样。只有当 $ in
值数组首先排序时才能使用索引对 a
进行排序 - 我认为 - 但是我不知道MongoDB是否会这样做。
How would the sort proceed if it was on a
or b
? $in
is an equality operator of sorts, but it seems to me that a sort on b
with an index is impossible even so. A sort on a
using an index is only possible if the $in
value array is sorted first, I think - but I don't know if MongoDB does this.
$或:
由于 $或
查询,IIUC被处理为多个查询,并且可能会使用各自的索引进行排序,排序结果会以某种方式合并,或者 $或
强制在内存中排序所有结果?如果是前者,这个过程的时间复杂度是多少?
Since $or
queries, IIUC, are processed as multiple queries and can presumably use their respective indexes for sorts, do the sorted results get merged somehow or does $or
force an in-memory sort of all the results? If the former, what is the time complexity of this process?
推荐答案
注意:这个答案是基于MongoDB 3.2.4。
Note: This answer is based on MongoDB 3.2.4.
值得发现使用 explain()
。 explain()
输出查询(例如 db.collection.explain()。find(...)
)允许您检查查询中使用的索引,并使用< a href =https://docs.mongodb.org/manual/reference/method/db.collection.explain/#executionstats-mode =noreferrer> db.collection.explain(' executionStats')
还会显示查询是否因内存 SORT
限制而成功或失败。
It is worthwhile to discover the use of explain()
in MongoDB. The explain()
output of a query (e.g. db.collection.explain().find(...)
) allows you to check which index is used in a query, and using db.collection.explain('executionStats')
will also show you whether the query succeeds or fails due to in-memory SORT
limitation.
$ in
A $ in
查询可以被认为是一系列相等的查询。例如, {a:{$ in:[1,3,5]}}
可以被认为是 {a:1},{ a:3},{a:5}
。 MongoDB将在继续查询之前对 $ in
数组进行排序,以便 {$ in:[3,5,1]}
与 {$ in:[1,3,5]}
没什么区别。
A $in
query can be thought of as a series of equality queries. For example, {a: {$in: [1,3,5]}}
could be thought of as {a:1}, {a:3}, {a:5}
. MongoDB will sort the $in
array before proceeding with the query, so that {$in: [3,5,1]}
is no different to {$in: [1,3,5]}
.
让我们假设该集合的索引为
Let's assume the collection has an index of
{a:1, b:1}
-
按
a
排序Sorting by
a
db.coll.find({a: {$in: [1,3,5]}}).sort({a:1})
MongoDB将能够使用
{ a:1,b:1}
index,因为此查询可以被认为是{a:1},{a:3},{a:5的联合}
查询。按{a:1}
排序允许使用索引前缀,因此MongoDB不需要执行内存中的排序。MongoDB will be able to use the
{a:1,b:1}
index, since this query can be thought of as a union of{a:1}, {a:3}, {a:5}
queries. Sorting by{a:1}
allows the use of index prefix, so MongoDB does not need to perform an in-memory sort.同样的情况也适用于查询:
The same situation also applies to the query:
db.coll.find({a: {$in: [1,3,5]} ,b:{$gte:1, $lt:2}}).sort({a:1})
因为
sort({a:1})
也使用索引前缀(a
在这种情况下),因此不需要内存SORT
阶段。since
sort({a:1})
also uses the index prefix (a
in this case), an in-memorySORT
stage is therefore not required.按
b排序
Sorting by
b
与按<$ c $排序相比,这是一个更有趣的案例C> A 。例如:
db.coll.find({a: {$in: [1,3,5]}}).sort({b:1})
explain()
此查询的输出将有一个名为SORT_MERGE
的阶段。请记住,查询的find()
部分可以被认为是{a:1},{a:3},{a:5 }
。The
explain()
output of this query will have a stage calledSORT_MERGE
. Remember that thefind()
portion of the query can be thought of as{a:1}, {a:3}, {a:5}
.查询
db.coll.find({a:1})。sort({b: 1})
由于{a:1>的性质,不需要内存
索引:也就是说,在满足相等参数后,MongoDB可以简单地遍历(已排序)索引并返回按SORT
阶段,b:1}b
排序的文档在a
上。例如,对于每个a
,有许多b
已经按b排序
由于索引。The query
db.coll.find({a:1}).sort({b:1})
does not need to have an in-memorySORT
stage due to the nature of the{a:1,b:1}
index: that is, MongoDB can simply walk the (sorted) index and return documents sorted byb
after satisfying the equality parameter ona
. E.g., for eacha
, there are manyb
which are already sorted byb
due to the index.在中使用
$,整体查询可以被认为是:
Using
$in
, the overall query can be thought of as:-
db.coll.find({a:1})。sort({b:1})
-
db.coll.find({a:3})。sort({b:1})
-
db.coll.find({a:5})。sort({b:1})
- 获取上面的各个查询结果,并使用
b
的值执行合并。查询不需要内存中排序阶段,因为单个查询结果已按b
排序。 MongoDB只需将(已经排序的)子查询结果合并为一个结果。
db.coll.find({a:1}).sort({b:1})
db.coll.find({a:3}).sort({b:1})
db.coll.find({a:5}).sort({b:1})
- Take the individual query results above, and perform a merge using the value of
b
. The query does not need an in-memory sort stage because the individual query results are already sorted byb
. MongoDB just need to merge the (already sorted) sub-query results into a single result.
同样,查询
db.coll.find({a: {$in: [1,3,5]} ,b:{$gte:1, $lt:2}}).sort({b:1})
也使用
SORT_MERGE
阶段,与上面的查询非常相似。区别在于,个人查询基于 <$ em>b
的范围输出文档(而不是每个b
)每个a
(由于b
,由于index{a:1,b:1}
)。因此,查询不需要内存中的排序阶段。also uses a
SORT_MERGE
stage and is very similar to the query above. The difference is that the individual queries output documents based on a range ofb
(instead of everyb
) for eacha
(which will be sorted byb
due to the index{a:1,b:1}
). Hence, the query does not need an in-memory sort stage.$或
对于使用索引的
$或
查询,$或
表达式中的每个子句必须具有与之关联的索引。如果满足此要求,则查询可以使用SORT_MERGE
阶段,就像$ in
查询一样。例如:For an
$or
query to use an index, every clause in the$or
expression must have an index associated with it. If this requirement is satisfied, it is possible for the query to employ aSORT_MERGE
stage just like an$in
query. For example:db.coll.explain().find({$or:[{a:1},{a:3},{a:5}]}).sort({b:1})
将具有几乎相同的查询计划,索引使用和
SORT_MERGE
阶段,如上面的$ in
示例中所示。从本质上讲,查询可以被认为是:will have an almost identical query plan, index use, and
SORT_MERGE
stage as in the$in
example above. Essentially, the query can be thought as:-
db.coll.find({a:1})。 sort({b:1})
-
db.coll.find({a:3})。sort({b: 1})
-
db.coll.find({a:5})。sort({b:1})
- 获取上面的各个查询结果,并使用
b
的值执行合并。
db.coll.find({a:1}).sort({b:1})
db.coll.find({a:3}).sort({b:1})
db.coll.find({a:5}).sort({b:1})
- Take the individual query results above, and perform a merge using the value of
b
.
就像之前的
$ in
示例一样。但是,这个查询:
db.coll.explain().find({$or:[{a:1},{b:1}]}).sort({b:1})
不能使用任何索引(因为我们没有
{b:1}
索引)。此查询将导致集合扫描,因此将具有内存中排序阶段,因为不使用索引。cannot use any index (since we do not have the
{b:1}
index). This query will result in a collection scan, and consequently will have an in-memory sort stage since no index is used.但是,我们创建索引
{b:1}
,查询将继续如下:If, however, we create the index
{b:1}
, the query will proceed like:-
db.coll.find({a:1})。sort({b:1})
-
db.coll.find({b:1})。sort({b:1})
- 获取上面的各个查询结果,使用
b
的值执行合并(由于索引{a:1,b:1,因此已在两个子查询中排序}
和{b:1}
)。
db.coll.find({a:1}).sort({b:1})
db.coll.find({b:1}).sort({b:1})
- Take the individual query results above, and perform a merge using the value of
b
(which is already sorted at both sub-queries, due to the indexes{a:1,b:1}
and{b:1}
).
和MongoDB将结合
{a:1}
和{b:1}
查询的结果并执行合并结果。合并过程是线性时间,例如O(n)
。and MongoDB will combine the results of
{a:1}
and{b:1}
queries and perform a merge on the results. The merging process is linear time, e.g.O(n)
.总之,在
$或
查询,每个术语必须有一个索引,包括sort()
阶段。否则,MongoDB将不得不执行内存中的排序。In conclusion, in a
$or
query, every term must have an index, including thesort()
stage. Otherwise, MongoDB will will have to perform an in-memory sort.这篇关于如何在MongoDB中使用`$或`和`$ in`查询进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
-